作者:萧海豚泳_756 | 来源:互联网 | 2023-05-18 06:54
IamseeingadeadlockwithMySQL5.6becauseofwhatseemsliketryingtolockthesamerowstwice
I am seeing a deadlock with MySQL 5.6 because of what seems like trying to lock the same row/s twice.
我看到MySQL 5.6的死锁,因为似乎试图锁定同一行/两次。
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.
从下面的代码段中,id =(11,12,13,14,15)的行已经锁定。当另一个事务试图获取对这些的锁定时,MySQL无法检测到死锁的事务。
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).
我的阅读是否正确?如果是这样,MySQL 5.6中有什么可以克服这个问题吗? FWIW,5.5中的相同代码工作得很好(几百次迭代)。
------------------------
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)
1 个解决方案