mysql中的死锁:如何找出无法获取锁的原因?

7gyucuyw  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(408)

如show innodb status result所示,我们有以下情况导致死锁:
transaction1正在尝试更新id=5的mytable,并等待x类型的行锁记录
transaction2正在尝试更新mytable,其中id=5持有s类型的行锁记录,并等待x类型的行锁记录
这两个事务中没有一个实际持有正确的记录,因此我们无法真正理解为什么这会导致死锁,除非有第三个事务拥有该锁并且也处于死锁状态。如何进一步调查这个问题,并以某种方式与触发器有关?

LATEST DETECTED DEADLOCK
------------------------
2018-07-28 08:27:08 0x7f1a08537700

***(1) TRANSACTION:

TRANSACTION 2183, ACTIVE 0 sec starting index read
mysql tables in use 5, locked 5
LOCK WAIT 7 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 47, OS thread handle 139750051079936, query id 1481 172.24.0.1 myuser updating
update `mytable` set `status` = 'OK' where `mytable`.`id` = 5

***(1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `mydb`.`mytable` trx id 2183 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000885; asc       ;;
 2: len 7; hex 2c0000018b0110; asc ,      ;;
 3: len 30; hex 66643164396162382d663462642d343237652d626461362d316430626634; asc fd1d9ab8-f4bd-427e-bda6-1d0bf4; (total 36 bytes);
 4: len 4; hex 5b5c28d7; asc [\( ;;
 5: len 4; hex 5b5c28dc; asc [\( ;;
 6: len 6; hex 80000186a000; asc       ;;
 7: len 4; hex 80000005; asc     ;;
 8: len 3; hex 455552; asc EUR;;
 9: len 4; hex 80000001; asc     ;;
 10: len 17; hex 52454144595f464f525f414456414e4345; asc OK;;
 11: SQL NULL;
 12: len 3; hex 8fc55a; asc   Z;;
 13: len 3; hex 8fc55a; asc   Z;;
 14: SQL NULL;
 15: len 15; hex 30303141307965644562686c466966; asc 001A0yedEbhlFif;;
 16: SQL NULL;
 17: len 1; hex 80; asc  ;;

***(2) TRANSACTION:

TRANSACTION 2187, ACTIVE 0 sec starting index read
mysql tables in use 5, locked 5
7 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 50, OS thread handle OK, query id 1482 172.24.0.1 myuser updating
update `mytable` set `status` = 'OK' where `mytable`.`id` = 5

***(2) HOLDS THE LOCK(S):

RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `mydb`.`mytable` trx id 2187 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000885; asc       ;;
 2: len 7; hex 2c0000018b0110; asc ,      ;;
 3: len 30; hex 66643164396162382d663462642d343237652d626461362d316430626634; asc fd1d9ab8-f4bd-427e-bda6-1d0bf4; (total 36 bytes);
 4: len 4; hex 5b5c28d7; asc [\( ;;
 5: len 4; hex 5b5c28dc; asc [\( ;;
 6: len 6; hex 80000186a000; asc       ;;
 7: len 4; hex 80000005; asc     ;;
 8: len 3; hex 455552; asc EUR;;
 9: len 4; hex 80000001; asc     ;;
 10: len 17; hex 52454144595f464f525f414456414e4345; asc OK;;
 11: SQL NULL;
 12: len 3; hex 8fc55a; asc   Z;;
 13: len 3; hex 8fc55a; asc   Z;;
 14: SQL NULL;
 15: len 15; hex 30303141307965644562686c466966; asc 001A0yedEbhlFif;;
 16: SQL NULL;
 17: len 1; hex 80; asc  ;;

***(2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `mydb`.`mytable` trx id 2187 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000000885; asc       ;;
 2: len 7; hex 2c0000018b0110; asc ,      ;;
 3: len 30; hex 66643164396162382d663462642d343237652d626461362d316430626634; asc fd1d9ab8-f4bd-427e-bda6-1d0bf4; (total 36 bytes);
 4: len 4; hex 5b5c28d7; asc [\( ;;
 5: len 4; hex 5b5c28dc; asc [\( ;;
 6: len 6; hex 80000186a000; asc       ;;
 7: len 4; hex 80000005; asc     ;;
 8: len 3; hex 455552; asc EUR;;
 9: len 4; hex 80000001; asc     ;;
 10: len 17; hex 52454144595f464f525f414456414e4345; asc OK;;
 11: SQL NULL;
 12: len 3; hex 8fc55a; asc   Z;;
 13: len 3; hex 8fc55a; asc   Z;;
 14: SQL NULL;
 15: len 15; hex 30303141307965644562686c466966; asc 001A0yedEbhlFif;;
 16: SQL NULL;
 17: len 1; hex 80; asc  ;;

***WE ROLL BACK TRANSACTION (2)
mcdcgff0

mcdcgff01#

第二个事务不包含共享的( S )锁,它阻止第一个事务获得独占( X )锁上。
但是mysql注册了第一个事务想要拥有一个排他锁,该锁进入队列。
然后,第二个事务希望将锁升级为独占锁,但它不能这样做,因为第一个事务已经注册了独占锁的意图。
这是mysql死锁的教科书案例,参见14.5.5.1节mysql手册中的innodb死锁示例

相关问题