一、实验复现

MySQL版本8.0，隔离级别RR和RC均能复现。

1.创建表，构造数据

CREATE TABLE `t2` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
insert into t2 values(1,1,1);

2.操作步骤

time	session1	session2	session3
t1	begin;update t2 set c=2 where b=1;
t2		begin;delete from t2 where a=1;
t3			begin;delete from t2 where b=1;
t4	rollback;
t5		Query OK, 1 row affected	(1213, 'Deadlock found when trying to get lock; try restarting transaction')

二、死锁分析

t1时刻

MySQL root@127.0.0.1:performance_schema> select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_S
                                      -> TATUS,LOCK_DATA from performance_schema.data_locks;
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 6689                  | 68        | test          | t2          | <null>     | TABLE     | IX            | GRANTED     | <null>    |
| 6689                  | 68        | test          | t2          | b          | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 1      |
| 6689                  | 68        | test          | t2          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+

t2时刻

+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 6690                  | 67        | test          | t2          | <null>     | TABLE     | IX            | GRANTED     | <null>    |
| 6690                  | 67        | test          | t2          | PRIMARY    | RECORD    | X,REC_NOT_GAP | WAITING     | 1         |
| 6689                  | 68        | test          | t2          | <null>     | TABLE     | IX            | GRANTED     | <null>    |
| 6689                  | 68        | test          | t2          | b          | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 1      |
| 6689                  | 68        | test          | t2          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+

t3时刻

+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 6691                  | 66        | test          | t2          | <null>     | TABLE     | IX            | GRANTED     | <null>    |
| 6691                  | 66        | test          | t2          | b          | RECORD    | X,REC_NOT_GAP | WAITING     | 1, 1      |
| 6690                  | 67        | test          | t2          | <null>     | TABLE     | IX            | GRANTED     | <null>    |
| 6690                  | 67        | test          | t2          | PRIMARY    | RECORD    | X,REC_NOT_GAP | WAITING     | 1         |
| 6689                  | 68        | test          | t2          | <null>     | TABLE     | IX            | GRANTED     | <null>    |
| 6689                  | 68        | test          | t2          | b          | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 1      |
| 6689                  | 68        | test          | t2          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+

t4时刻
session1事务回滚
t5时刻

+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 6690                  | 67        | test          | t2          | <null>     | TABLE     | IX            | GRANTED     | <null>    |
| 6690                  | 67        | test          | t2          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
| 6690                  | 67        | test          | t2          | b          | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 1      |
+-----------------------+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+

show engine innodb status\G死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-06-25 13:18:39 0x7f7dd6fd0700
*** (1) TRANSACTION:
TRANSACTION 6691, ACTIVE 84 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 27, OS thread handle 140179212433152, query id 147 127.0.0.1 root updating
delete from t2 where b=1

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 4 page no 5 n bits 72 index b of table `test`.`t2` trx id 6691 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test`.`t2` trx id 6691 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000001a22; asc      ";;
 2: len 7; hex 020000011e0151; asc       Q;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;
 
*** (2) TRANSACTION:
TRANSACTION 6690, ACTIVE 111 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 28, OS thread handle 140179212105472, query id 146 127.0.0.1 root updating
delete from t2 where a=1

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4 page no 4 n bits 72 index PRIMARY of table `test`.`t2` trx id 6690 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000001a22; asc      ";;
 2: len 7; hex 020000011e0151; asc       Q;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 5 n bits 72 index b of table `test`.`t2` trx id 6690 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

4、总结