blog:mysql:lock-scene-1

MySQL的死锁场景【不存在就新增】

背景

MySQL版本：5.7
事务隔离级别：RR
CREATE TABLE `t1` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `a` INT(11) DEFAULT NULL,
  `b` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idxa` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
INSERT INTO t1(a,b) VALUES(2,3),(5,4),(6,7);
session 1 session 2
step-1 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
step-2 
mysql> select * from t1 where a = 10 for update;
Empty set (0.00 sec)
step-3 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
step-4 
mysql> select * from t1 where a = 11 for update;
Empty set (1.02 sec)
step-5 
mysql> insert into t1 (a, b) value (10, 10);
step-6 
mysql> insert into t1 (a, b) value (11, 10);
step-7 
Query OK, 1 row affected (23.00 sec)
 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

分析

mysql> SHOW engine innodb STATUS\G
......
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-19 17:32:01 0x7f021e357700
*** (1) TRANSACTION:
TRANSACTION 555832003, ACTIVE 26 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3627607, OS thread handle 139647049254656, query id 1752531735 localhost root UPDATE
INSERT INTO t1 (a, b) VALUE (10, 10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555832003 lock_mode X INSERT intention waiting
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
*** (2) TRANSACTION:
TRANSACTION 555832066, ACTIVE 17 sec inserting
mysql TABLES IN USE 1, locked 1
3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3627608, OS thread handle 139647073482496, query id 1752532311 localhost root UPDATE
INSERT INTO t1 (a, b) VALUE (11, 10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555832066 lock_mode X
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555832066 lock_mode X INSERT intention waiting
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
---TRANSACTION 555832003, ACTIVE 51 sec
4 LOCK struct(s), heap SIZE 1136, 3 ROW LOCK(s), undo log entries 1
MySQL thread id 3627607, OS thread handle 139647049254656, query id 1752531735 localhost root cleaning up
TABLE LOCK TABLE `okami`.`t1` trx id 555832003 LOCK mode IX
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555832003 lock_mode X
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555832003 lock_mode X INSERT intention
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555832003 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; ASC     ;;
 1: len 4; hex 80000004; ASC     ;;
- 事务1：
  - 持有锁：
      - LOCK mode IX
      - Next-Key Lock：索引idxa的 lock_mode X
      - II Gap Lock：索引idxa的 lock_mode X INSERT intention
      - Gap Lock：索引idxa的 lock_mode X locks gap BEFORE rec
  - 等待锁
    - II Gap Lock：lock_mode X INSERT intention waiting


- 事务2：
  - 持有锁 
    - Next-Key Lock：INDEX idxa， lock_mode X
  - 等待锁 
    - II Gap Lock：INDEX idxa，lock_mode X INSERT intention waiting

- 事务1要获取II Gap Lock，需要事务2释放Next-Key Lock
- 事务2要获取II Gap Lock，需要事务1释放Next-Key Lock
- 死锁形成
  • 当对不存在的索引列操作，会加Gap锁。Gap锁的区间(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)
  • 目前已知表中idxa有的数据为 2， 5， 6
  • session1 执行select * from t1 where a = 10 for update ，由于a=10不存在，添加Next-Key锁。锁住(6, +∞)
---TRANSACTION 555929313, ACTIVE 97 sec
2 LOCK struct(s), heap SIZE 1136, 1 ROW LOCK(s)
MySQL thread id 3628452, OS thread handle 139647055644416, query id 1752909792 localhost root cleaning up
TABLE LOCK TABLE `okami`.`t1` trx id 555929313 LOCK mode IX
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555929313 lock_mode X
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
  • session2 执行select * from t1 where a = 11 for update，由于a=11不存在，添加Next-Key锁
---TRANSACTION 555930276, ACTIVE 11 sec
2 LOCK struct(s), heap SIZE 1136, 1 ROW LOCK(s)
MySQL thread id 3628453, OS thread handle 139647039670016, query id 1752916041 localhost root starting
SHOW engine innodb STATUS
TABLE LOCK TABLE `okami`.`t1` trx id 555930276 LOCK mode IX
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555930276 lock_mode X
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
  • session1 执行insert into t1 (a, b) value (10, 10);，lock_mode X 表示该记录锁为排他锁，insert intention waiting 表示要加的锁为插入意向锁，并处于锁等待状态
  • 由于插入意向锁和Gap锁冲突，所以需要等待获取插入意向锁
---TRANSACTION 555929313, ACTIVE 286 sec inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 3 LOCK struct(s), heap SIZE 1136, 2 ROW LOCK(s), undo log entries 1
MySQL thread id 3628452, OS thread handle 139647055644416, query id 1752929007 localhost root UPDATE
INSERT INTO t1 (a, b) VALUE (10, 10)
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555929313 lock_mode X INSERT intention waiting
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
------------------
TABLE LOCK TABLE `okami`.`t1` trx id 555929313 LOCK mode IX
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555929313 lock_mode X
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555929313 lock_mode X INSERT intention waiting
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
  • session2 执行insert into t1 (a, b) value (11, 10);，等待插入意向锁lock_mode X insert intention
  • 由于session1 和 sesson2 同时持有Next-Key Lock，并等待意向插入锁，产生死锁
---TRANSACTION 555929313, ACTIVE 362 sec
4 LOCK struct(s), heap SIZE 1136, 4 ROW LOCK(s), undo log entries 1
MySQL thread id 3628452, OS thread handle 139647055644416, query id 1752933820 localhost root cleaning up
TABLE LOCK TABLE `okami`.`t1` trx id 555929313 LOCK mode IX
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555929313 lock_mode X
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555929313 lock_mode X INSERT intention
Record LOCK, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; ASC supremum;;
 
RECORD LOCKS SPACE id 5472 page no 4 n bits 72 INDEX idxa OF TABLE `okami`.`t1` trx id 555929313 lock_mode X locks gap BEFORE rec
Record LOCK, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; ASC     ;;
 1: len 4; hex 80000007; ASC     ;;
, ,
  • blog/mysql/lock-scene-1.txt
  • 最后更改: 2022/04/21 16:24
  • 由 okami