模拟两个事务发生死锁
还是使用官方提供的actor表，其中actor_id是主键
时间 | 事务T1 | 事务T2 |
1 | begin; select * from actor where actor_id=1 for update; | |
2 | begin; select * from actor where actor_id=2 for update; | |
3 | select * from actor where actor_id=2 for update; 被阻塞 | |
4 | select * from actor where actor_id=1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 回滚掉 | |
5 | mysql> select * from actor where actor_id=2 for update; |
通过 show engine innodb status; 查看
分许结果写在#后面
LATEST DETECTED DEADLOCK
------------------------
2021-11-14 19:14:42 0x7f9f9b651700
#死锁的发生时间2021-11-14 19:14:42
*** (1) TRANSACTION:
TRANSACTION 3957, ACTIVE 39 sec starting index read
#死锁发生时的第一个事务，事务id3957，活跃39秒，正在执行starting index read，此事务id比下一个事务id小，说明该事务是T1
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1038, OS thread handle 140323910289152, query id 10706 localhost root statistics
select * from actor where actor_id=2 for update
#发生死锁时，此事务正在执行的sql
#此事务当前正在等待获取的锁信息
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3957 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
#前面说过，lock_mode X locks rec but not gap waiting Record lock 表示X型的record锁
表示当前事务需要获取X型的record锁，正在等待获取到它
0: len 2; hex 0002; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
*** (2) TRANSACTION:
TRANSACTION 3958, ACTIVE 31 sec starting index read
#死锁发生时第二个事务信息，该事务id3958，此ID比上一个小，也能得知该事务是T2，活跃31秒，正在执行starting index read操作
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1039, OS thread handle 140323483621120, query id 10708 localhost root statistics
#发生死锁时，该事务正在执行的sql
select * from actor where actor_id=1 for update
#此事务已经获取到的锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3958 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
#已获取的锁lock_mode X locks rec but not gap Record lock 记录锁
0: len 2; hex 0002; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
#此事务等待的锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3958 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0001; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b0110; asc + ;;
3: len 8; hex 50454e454c4f5045; asc PENELOPE;;
4: len 7; hex 4755494e455353; asc GUINESS;;
5: len 4; hex 43f23ed9; asc C > ;;
#innodb决定回滚第二个事务，也就是T2
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 3959
Purge done for trx's n:o < 3930 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421799341400576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421799341399664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421799341398752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 3957, ACTIVE 273 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1038, OS thread handle 140323910289152, query id 10706 localhost root
TABLE LOCK table `sakila`.`actor` trx id 3957 lock mode IX
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3957 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0001; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b0110; asc + ;;
3: len 8; hex 50454e454c4f5045; asc PENELOPE;;
4: len 7; hex 4755494e455353; asc GUINESS;;
5: len 4; hex 43f23ed9; asc C > ;;
RECORD LOCKS space id 45 page no 3 n bits 272 index PRIMARY of table `sakila`.`actor` trx id 3957 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 2; hex 0002; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
--------
这个结果分成三部分：
(1) TRANSACTION，是第一个事务的信息；
(2) TRANSACTION，是第二个事务的信息；
(3)WE ROLL BACK TRANSACTION (2)，是最终的处理结果，表示回滚了第二个事务。
第一个事务的信息中：WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
index PRIMARY of table `sakila`.`actor`,说明在等的是表actor上的主键上的记录锁；
lock_mode X locks rec but not gap waiting Record lock 表示这个语句要自己加一个X性的record锁，当前状态是等待中；
n_fields 6; compact format; info bits 0
0: len 2; hex 0002; asc ;;
1: len 6; hex 000000000ef8; asc ;;
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
n_fields 6 表示这个记录是六列
第一行是主键值
0: len 2; hex 0002; asc ;; 是第一个字段，也就是 actor_id。值是十六进制0002，也就是 2；
第二行是最近一次修改这行的事务id
1: len 6; hex 000000000ef8; asc ;; 计算得出是 3832
后面就是其它每个字段的值
2: len 7; hex cf0000032b011a; asc + ;;
3: len 4; hex 4e49434b; asc NICK;;
4: len 8; hex 5741484c42455247; asc WAHLBERG;;
5: len 4; hex 43f23ed9; asc C > ;;
查询主键，可以验证得出确实是主键为2的这一行的值
mysql> select * from actor where actor_id=2 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
这两行里面的 asc 表示的是，接下来要打印出值里面的“可打印字符”。
第一个事务信息就只显示出了等锁的状态，在等待 (主键值等于2) 这一行的锁。
当然你是知道的，既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。别着急，我们从第二个事务的信息中推导出来。
第二个事务显示的信息要多一些：
“ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
index PRIMARY of table `sakila`.`actor` trx id 3958 lock_mode X locks rec but not gap
Record lock 表示锁是在表actor主键索引 actor_id上；
0: len 2; hex 0002; asc ;; 表示这个事务持有主键=2的记录锁；
WAITING FOR THIS LOCK TO BE GRANTED，表示在等 (actor_id=1) 这个记录锁。
index PRIMARY of table `sakila`.`actor` trx id 3958 lock_mode X locks rec but not gap waiting Record lock
表示锁是在表actor主键索引 actor_id上；
0: len 2; hex 0001; asc ;;
由此可以得出在等 (actor_id=1) 这个记录锁。
从上面这些信息中，我们就知道：事务T1等待主键为2的记录锁，事务T2持有主键值为2的记录锁，等待主键为1的记录锁，innodb选择将事务T2回滚。
