Mannequins guides for locking in innodb

Here are my notes on working with MySQL support on a recent, strange locking issue (version 5.1.37):

All row and index records moved to jump to mutable rows will be locked. It covers:

http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html

"A committed read, UPDATE, or DELETE will typically commit a write lock on each index record that is checked during the processing of the SQL statement. It does not matter if there are WHERE clauses in the statement that would exclude the row. InnoDB does not remember the exact WHERE clause, but only knows which the indexes have been checked ... If you do not have indexes suitable for your statement and MySQL has to scan the entire table to process the statement, every row in the table will be locked, which in turn will block all other users' inserts into the table. "

This is the BASIC headache, if true.

It. A workaround that is often helpful is as follows:

UPDATE whihevertable sets everything where the primary key resides (select primarykey from whichevertable, where the order of constraints is done with the primarykey);

Internal picking does not require locks and the upgrade will have less work to upgrade. The order by clause ensures that the update is done in primary key order according to InnoDB physical order, the fastest way to do it.

With a large number of rows, as in your case, it is better to store the selection result in a temporary table with the flag column added. Then select from temporary table where no flag is set to get each batch. Run updates with a limit of 1000 or 10000 and set the flag for the batch after the update. The limits will hold the blocking amount up to an acceptable level, while the selection work will only be performed once. Commit the release of the locks after each batch.

You can also speed up this work by running the allocated non-indexed column amount before performing each batch of updates. This will load the data pages into the buffer pool without blocking. Then the lock will continue for a shorter amount of time because there will be no read on the disk.

This is not always practical, but when possible it can be very helpful. If you can't do it in batches, you can at least try to prefetch the data first if it's small enough to fit into the buffer pool.

If possible, use READ COMMITTED transaction isolation mode. Cm:

http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html

To obtain this shorthand lock, you must use row-based binary logging (rather than the default binary logging).

Two known issues:

• Subqueries can be less than ideally optimized. In this case, it was an unwanted dependent subquery - the suggestion I made for using the subquery turned out to be useless compared to the alternative in this case because of this.

• Deletes and updates do not have the same range of query plans as select statements, so it is sometimes difficult to optimize them without measuring the results to determine exactly what they are doing.

Both are gradually improving. This bug is one example where we just improved the optimizations available for an update, although the changes are significant and are still under QA to make sure it doesn't have large adverse effects:

http://bugs.mysql.com/bug.php?id=36569