MySQL 5.6 deadlock for locking the same rows twice?

Question

I am seeing a deadlock with MySQL 5.6 because of what seems like trying to lock the same row/s twice.

From the snippet below, rows where id = (11, 12, 13, 14, 15) already have a lock. And when another transaction tried to acquire a lock on these, MySQL failed the transaction detecting a deadlock.

Is my reading of this correct? If so, is there anything in MySQL 5.6 to get over this? FWIW, the same code in 5.5 worked just fine (for several hundreds of iterations).

------------------------
LATEST DETECTED DEADLOCK
------------------------
2013-07-25 11:46:05 13a515000
*** (1) TRANSACTION:
TRANSACTION 2333130, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 31 lock struct(s), heap size 6960, 6 row lock(s)
MySQL thread id 2944, OS thread handle 0x13ae88000, query id 184533 localhost 127.0.0.1 root Sending data
SELECT id FROM table_meta WHERE id IN (11, 12, 13, 14, 15) FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 128954 page no 5 n bits 176 index `PRIMARY` of table `db_test1`.`table_meta` trx id 2333130 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 2333255, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 11 row lock(s)
MySQL thread id 2927, OS thread handle 0x13a515000, query id 186769 localhost 127.0.0.1 root Sending data
SELECT id FROM table_meta WHERE id IN (1, 2, 3, 4, 5, 6, 8, 10, 11, 12, 13, 14, 15) FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 128954 page no 5 n bits 176 index `PRIMARY` of table `db_test1`.`table_meta` trx id 2333255 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 128954 page no 5 n bits 176 index `PRIMARY` of table `db_test1`.`table_meta` trx id 2333255 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

Answer 1

Sure,

Just sorted this for one of my clients in 5.6. Actually these are innodb deadlocks, select is followed by an update which is causing the deadlocks. Please update the query and do a separate update.

Do you have slave server ?

One more thing to keep into account – INSERT … SELECT also performs read in locking mode and so partially bypasses versioning and retrieves latest committed row. So even if you’re operation in REPEATABLE-READ mode, this operation will be performed in READ-COMMITTED mode, potentially giving different result compared to what pure SELECT would give. This by the way applies to SELECT .. LOCK IN SHARE MODE and SELECT … FOR UPDATE as well. One my ask what is if I’m not using replication and have my binary log disabled ? If replication is not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which generally gives better concurrency. However as the name says it makes locks unsafe fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog option with caution.