MySQL 5.6死锁两次锁定同一行? [英] MySQL 5.6 deadlock for locking the same rows twice?

问题描述

我看到MySQL 5.6出现死锁，因为似乎试图两次将同一行锁定两次.

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

在下面的代码段中，id =(11、12、13、14、15)的行已具有锁定.当另一个事务试图获取对它们的锁定时，MySQL无法使该事务检测到死锁.

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.

我的阅读正确吗?如果是这样，MySQL 5.6中有什么要克服的? FWIW，5.5中的相同代码工作得很好(进行了数百次迭代).

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)

推荐答案

可以，

在5.6中为我的一位客户进行了排序.实际上，这些是innodb死锁，在select之后是导致死锁的更新.请更新查询并单独进行更新.

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.

您有从属服务器吗?

还要注意的另一件事– INSERT…SELECT还在锁定模式下执行读取，因此部分绕过版本控制并检索最新的提交行.因此，即使您在REPEATABLE-READ模式下操作，该操作也会在READ-COMMITTED下执行 模式，可能会产生与纯SELECT所提供的结果不同的结果.顺便说一下，这也适用于SELECT .. LOCK IN SHARE MODE和SELECT…FOR UPDATE. 我想问一问，如果我不使用复制功能并且禁用了二进制日志，该怎么办?如果不使用复制，则可以启用innodb_locks_unsafe_for_binlog选项，该选项将放宽Innodb在语句执行时设置的锁，这通常会提供更好的并发性.但是，顾名思义，它会使锁不安全地进行复制和时间点恢复，因此请谨慎使用innodb_locks_unsafe_for_binlog选项.

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.

这篇关于MySQL 5.6死锁两次锁定同一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！