Mysql – Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED

deadlockisolation-levelMySQLparallelism

Running a processing job in multiple parallel batches which basically reads big chunks of rows to update a few – it's OK here to use a lower transaction level since I know the relevant values won't be updated while this task is running so before calling each stored proc I run:

set session transaction isolation level read uncommitted

then call the stored proc which gets a subset of IDs to process. SQLFiddle of the overall operation: http://sqlfiddle.com/#!9/192d62 (somewhat contrived but keeps to the structure of the original queries)

The reason I ask is the deadlocks continue to happen and looking into the Monitor output there's one thread requesting a shared lock while another holds an exclusive lock over the same space (or vice-versa) – shouldn't setting that transaction level prevent the need for a shared lock? Is there a reason to get a shared lock aside from repeatable-read?

Using InnoDB.

Relevant lock information from show engine innodb status (edited to match table name from SQLFiddle):

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 481628 page no 24944 n bits 112 index `PRIMARY` of table `events` trx id 27740892 lock mode S locks rec but not gap waiting

and

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 481628 page no 24944 n bits 112 index `PRIMARY` of table `events` trx id 27740898 lock_mode X locks rec but not gap waiting

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 481628 page no 25267 n bits 112 index `PRIMARY` of table `events` trx id 27740898 lock_mode X locks rec but not gap waiting

Note that query 1 and 2 in the SQLFiddle match the first and second entries in the engine output. The PRIMARY key is only over the id column (as can be seen in the Fiddle)

Best Answer

"Read chunks, but only update a few of them"...

SELECT a chunk -- separate transaction, don't care about locking, etc
munch on that chunk to figure out the 'few' ids to update
BEGIN;
    SELECT ... WHERE id IN (list of the few) FOR UPDATE;
    minimal other work
    UPDATE them;
COMMIT;   -- plus checks for deadlocks, etc, and restart the code.

The idea is to pull as much time-consuming code out of the transaction as possible, thereby making the transaction faster, thereby minimizing the likelihood of conflict. (No, it probably won't eliminate all deadlocks, but it should help.)

Another note: If possible, go through the table in the same order in all threads. Deadlocks happen when one thread plays with id IN (1,2) but another does IN (2,1). If both do IN (1,2), then you get delays, not deadlocks.

Another tip: Make the chunks smaller.

Related Question