Re: table locks in cursor. how it works?

[Yurii Korotia]

Rick James Wrote:
-------------------------------------------------------
> * Try to avoid cursors. They are much less
> efficient than if you can handle many rows in
> single statements.
>
> * SELECT ... FOR UPDATE -- This is often all you
> need for locking.
>
> * "Table" locks should be avoided in InnoDB; the
> automatic locking is almost always sufficient.
>
> What does the SP do? Would you like to show it to
> us? Also SHOW CREATE TABLE, SHOW CREATE VIEW, and
> anything else relevant.

hello

there are about 500 lines of code and it has some sensitive data.

In short, sp does next:

0. declares cursor for view we are going to create with "select ... for update" (on view)
view will be built from 4 tables
1. checks user input. variables may be var or null (where null is everything, so we need to create statement)
As we have 5 variables, that's 25 combinations.
2. prepare statement
3. execute statement. view is created
4. open cursor on view
5. loop view
- start transaction
- and do custom task on each record.
- commit
6. if any task fails we rollback last transaction.
7. finish with status


so, i did everything you said not to do.