Skip to content

Latest commit

 

History

History
75 lines (60 loc) · 2.45 KB

11.md

File metadata and controls

75 lines (60 loc) · 2.45 KB
Raw

update-wait-lock-mode-x-locks-rec-but-not-gap-vs-update-wait-lock-mode-s-holds-lock-mode-x-locks-rec-but-not-gap

死锁特征

  1. update WAITING FOR lock_mode X locks rec but not gap
  2. update WAITING FOR lock mode S, HOLDS lock_mode X locks rec but not gap

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-01-23 14:24:16 10ceed000
*** (1) TRANSACTION:
TRANSACTION 24897, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 8, OS thread handle 0x10cea5000, query id 138 127.0.0.1 root updating
update tt set id = 4 where fileid = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24897 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 24896, ACTIVE 8 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 7, OS thread handle 0x10ceed000, query id 136 127.0.0.1 root updating
update tt set id = 3 where fileid = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24896 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24896 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

表结构

CREATE TABLE `tt` (
  `id` int(11) NOT NULL DEFAULT '0',
  `fileid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `fileid` (`fileid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

重现步骤

Session 1 Session 2 Session 3
update tt set id = 2 where fileid = 1;
update tt set id = 3 where fileid = 1;
update tt set id = 4 where fileid = 1;
commit; .

分析

TODO

参考

  1. MySQL问题两则