MySQL 5.6 deadlock for locking the same rows twice?

Question

I see a dead end with MySQL 5.6 due to the fact that it seems to be an attempt to lock the same / s line twice.

In the snippet below, the lines where id = (11, 12, 13, 14, 15) already have a lock. And when another transaction tried to get a lock, MySQL did not delete the transaction, finding a deadlock.

Am I reading this correctly? If so, is there anything in MySQL 5.6 to overcome this? FWIW, the same code in 5.5 worked just fine (for several hundred 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 for one of my clients in 5.6. These are actually lock locks, followed by an update that causes deadlocks. Update the request and perform a separate update.

Do you have a slave server?

Another thing to consider: INSERT ... SELECT also reads in lock mode and therefore partially bypasses version control and retrieves the last perfect row. Therefore, even if you are working in REPEATABLE-READ mode, this operation will be performed in the READ-COMMITTED way, potentially yielding different results compared to what gives a pure SELECT. This, incidentally, applies to SELECT .. LOCK IN SHARE MODE and SELECT ... FOR UPDATE. My one question, what is it if I do not use replication and disabled my binary log? If replication is not used, you can enable the innodb_locks_unsafe_for_binlog option, which will ease the locks that Innodb sets to execute the statement, which usually gives the best concurrency. However, since the name says it makes the locks unsafe for replication and indicates the recovery time, so use the innodb_locks_unsafe_for_binlog option with caution.