为什么这是一个死锁(使用InnoDB的MySQL)

SHOW INNODB ENGINE STATUS向我展示了这个并将其称为死锁:

LATEST DETECTED DEADLOCK 

 100923 22:29:21
* (1) TRANSACTION:
TRANSACTION 0 5335752, ACTIVE 0 sec, OS thread id 7992 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 3
MySQL thread id 26, query id 14422 localhost 127.0.0.1 root update
insert into history_messagearguments (history_id, messageArguments_ORDER, messageArguments) values (69, 1, '1')
* (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 179145 n bits 304 index fk_history_msgargs of table zvs_rkl_01_test.history_messagearguments trx id 0 5335752 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 198 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000048; asc        H;; 1: len 6; hex 0000006fe7c5; asc    o  ;; 

 * (2) TRANSACTION:
TRANSACTION 0 5335748, ACTIVE 0 sec, OS thread id 6988 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 2
MySQL thread id 25, query id 14424 localhost 127.0.0.1 root update
insert into history_messagearguments (history_id, messageArguments_ORDER, messageArguments) values (71, 0, '0')
* (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 179145 n bits 304 index fk_history_msgargs of table zvs_rkl_01_test.history_messagearguments trx id 0 5335748 lock_mode X locks gap before rec
Record lock, heap no 198 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000048; asc        H;; 1: len 6; hex 0000006fe7c5; asc    o  ;; 

 * (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 179145 n bits 304 index fk_history_msgargs of table zvs_rkl_01_test.history_messagearguments trx id 0 5335748 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 198 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000048; asc        H;; 1: len 6; hex 0000006fe7c5; asc    o  ;; 

 * WE ROLL BACK TRANSACTION (2)

我不明白为什么这是一个僵局 . 对于事务1,没有“等待这个锁被授予:”的行 . 如果事务1没有持有任何锁,它就不能阻止任何人,因此它不能成为死锁的一部分 .

更理论上,我不能看到条件4满足如此写:http://en.wikipedia.org/wiki/Deadlock#Necessary_conditions

我看到它的方式,MySQL应该让事务2继续 . 完成后,事务1可以继续 .

这里(http://stackoverflow.com/questions/1851528/mysql-deadlock-explanation-needed)BrainCore写道:

事务2被“卡住”在事务1的请求之后,即一个FIFO队列 .

任何人都可以指向MySQL文档来确认这一点吗?我发现很难相信交易是按照它们到达的顺序严格执行的 .

在关于表格布局,隔离级别等的所有问题出现之前:我现在没有请求帮助来解决死锁问题 . 我问如何阅读SHOW ENGINE STATUS OUTPUT .

回答(1)

2 years ago

读取事务1和2:从头到尾阅读:

  • 什么类型和原因: lock_mode X locks gap before rec insert intention waiting

  • 什么被阻止:RECORD LOCKS空格id 0页号179145 n位304索引 fk_history_msgargs 表zvs_rkl_01_test.history_messagearguments trx id 0 5335748

  • 等待获得此锁定

InnoDB存在并发写入问题,特别是如果您在innodb表的末尾插入数据 .