如何查看mysql里面的死锁 -m6米乐安卓版下载

模拟两个事务发生死锁

还是使用官方提供的actor表，其中actor_id是主键

通过 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回滚。