8

如何查看mysql里面的死锁 -利来国际app

陈家睿 2021-11-14
8046

模拟两个事务发生死锁

还是使用官方提供的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回滚。



「喜欢文章,快来给作者赞赏墨值吧」
1人已赞赏
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论