blog:mysql:lock-scene-5

MySQL死锁场景【加锁顺序不同导致的死锁】

CREATE TABLE `t5` (
  `id` INT(11) NOT NULL,
  `a` VARCHAR(32) NOT NULL,
  `b` VARCHAR(32) NOT NULL,
  `c` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
mysql> INSERT INTO t5 VALUES (1, 'aaa', 'bbb', 'ccc'), (2, 'aaa', 'ccc', 'ccc'), (3, 'abc', 'bba', 'ccc'), (4, 'bbb', 'bbb', 'ccc'), (5, 'bbb', 'ccc', 'ccc'), (6, 'ccc', 'bba', 'ccc');
Query OK, 6 ROWS affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM t5;
+----+-----+-----+-----+
| id | a   | b   | c   |
+----+-----+-----+-----+
|  1 | aaa | bbb | ccc |
|  2 | aaa | ccc | ccc |
|  3 | abc | bba | ccc |
|  4 | bbb | bbb | ccc |
|  5 | bbb | ccc | ccc |
|  6 | ccc | bba | ccc |
+----+-----+-----+-----+
6 ROWS IN SET (0.00 sec)
 
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 ROWS affected (0.00 sec)
session 1 session 2 
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t5 where a = 'aaa' for update;
+----+-----+-----+-----+
| id | a   | b   | c   |
+----+-----+-----+-----+
|  1 | aaa | bbb | ccc |
|  2 | aaa | ccc | ccc |
+----+-----+-----+-----+
2 rows in set (0.02 sec)
      
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql>  select * from t5 where a = 'bbb' for update;
+----+-----+-----+-----+
| id | a   | b   | c   |
+----+-----+-----+-----+
|  4 | bbb | bbb | ccc |
|  5 | bbb | ccc | ccc |
+----+-----+-----+-----+
2 rows in set (0.02 sec)
      
mysql> select * from t5 where b = 'bbb' for update;

+----+-----+-----+-----+
| id | a   | b   | c   |
+----+-----+-----+-----+
|  1 | aaa | bbb | ccc |
|  4 | bbb | bbb | ccc |
+----+-----+-----+-----+
2 rows in set (39.57 sec)

mysql> select * from t5 where b = 'ccc' for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

查看死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-24 21:31:40 0x7fc46ca86700
*** (1) TRANSACTION:
TRANSACTION 2257896, ACTIVE 21 sec fetching ROWS
mysql TABLES IN USE 1, locked 1
LOCK WAIT 6 LOCK struct(s), heap SIZE 1136, 8 ROW LOCK(s)
MySQL thread id 432541, OS thread handle 140481603176192, query id 9345647 10.10.133.115 root Sending DATA
SELECT * FROM t5 WHERE b = 'bbb' FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 7956 page no 3 n bits 80 INDEX PRIMARY OF TABLE `lock_test`.`t5` trx id 2257896 lock_mode X locks rec but NOT gap waiting
Record LOCK, heap no 13 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000004; ASC     ;;
 1: len 6; hex 0000002273c2; ASC    "s ;;
 2: len 7; hex 310000041e09b7; asc 1      ;;
 3: len 3; hex 626262; asc bbb;;
 4: len 3; hex 626262; asc bbb;;
 5: len 3; hex 636363; asc ccc;;
 
*** (2) TRANSACTION:
TRANSACTION 2257897, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 7 row lock(s)
MySQL thread id 433603, OS thread handle 140481613293312, query id 9345706 10.10.133.115 root Sending data
select * from t5 where b = 'ccc' for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7956 page no 3 n bits 80 index PRIMARY of table `lock_test`.`t5` trx id 2257897 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000002273ba; asc    "s ;;
 2: len 7; hex ac00000209011d; ASC        ;;
 3: len 3; hex 626262; ASC bbb;;
 4: len 3; hex 636363; ASC ccc;;
 5: len 3; hex 636363; ASC ccc;;
 
Record LOCK, heap no 13 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000004; ASC     ;;
 1: len 6; hex 0000002273c2; ASC    "s ;;
 2: len 7; hex 310000041e09b7; asc 1      ;;
 3: len 3; hex 626262; asc bbb;;
 4: len 3; hex 626262; asc bbb;;
 5: len 3; hex 636363; asc ccc;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7956 page no 3 n bits 80 index PRIMARY of table `lock_test`.`t5` trx id 2257897 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000002273c0; asc    "s ;;
 2: len 7; hex 3000000f2f26d5; ASC 0   /& ;;
 3: len 3; hex 616161; ASC aaa;;
 4: len 3; hex 636363; ASC ccc;;
 5: len 3; hex 636363; ASC ccc;;
 
*** WE ROLL BACK TRANSACTION (2)

死锁日志分析

事务1执行的SQL： SELECT * FROM t5 WHERE b = 'bbb' FOR UPDATE
事务1等待的锁：（主键的记录锁） RECORD LOCKS SPACE id 7956 page no 3 n bits 80 INDEX PRIMARY OF TABLE `lock_test`.`t5` trx id 2257896 lock_mode X locks rec but NOT gap waiting

事务2执行的SQL： select * from t5 where b = 'ccc' for update
事务2持有的锁： （主键的记录锁）RECORD LOCKS space id 7956 page no 3 n bits 80 index PRIMARY of table `lock_test`.`t5` trx id 2257897 lock_mode X locks rec but not gap
事务2等待的锁： （主键的记录锁）RECORD LOCKS space id 7956 page no 3 n bits 80 index PRIMARY of table `lock_test`.`t5` trx id 2257897 lock_mode X locks rec but not gap waiting

发现：事务2持有的锁和等待的锁都是主键的记录锁，但是锁的内容不一样
记录锁和记录锁之间相互冲突

复盘加锁过程

 
session1 执行 select * from t5 where a = 'aaa' for update; 对id为1和2的记录加锁
session2 执行 select * from t5 where a = 'bbb' for update; 对id为4和5的记录加锁
session1 执行 select * from t5 where b = 'bbb' for update; 对id为1和4的记录加锁。此时由于id为4的记录已经被session2加了记录锁。需要等待
session2 执行 select * from t5 where b = 'ccc' for update; 符合条件的数据：id为2和5。 这两条记录分别被session1和session2加了记录锁。需要等待锁释放

形成了死锁
  • blog/mysql/lock-scene-5.txt
  • 最后更改: 2022/04/24 13:49
  • 由 okami