MySQL死锁的原因及解决方法

37次阅读

共计 5673 个字符，预计需要花费 15 分钟才能阅读完成。

这篇文章主要介绍“MySQL 死锁的原因及解决方法”，在日常操作中，相信很多人在 MySQL 死锁的原因及解决方法问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL 死锁的原因及解决方法”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

一、问题由来

这是我同事问我的一个问题，在网上看到了如下案例，本案例 RC RR 都可以出现，其实这个死锁原因也不叫简单，我们来具体看看：

构造数据

CREATE database deadlock_test;
use deadlock_test;
CREATE TABLE `push_token` ( `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `token` varchar(128) NOT NULL COMMENT  push token ,
 `app_id` varchar(128) DEFAULT NULL COMMENT  appid ,
 `deleted` tinyint(1) NOT NULL COMMENT  是否已删除  0：否  1：是 ,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_token_appid` (`token`,`app_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3384 DEFAULT CHARSET=utf8 COMMENT= pushtoken 表 
insert into push_token (id, token, app_id, deleted) values(1, token1 ,1,0);

操作数据 s1（TRX_ID367661）s2（TRX_ID367662）s3（TRX_ID367663）begin; UPDATE push_token SET deleted = 1 WHERE token =‘token1’AND app_id =‘1’;

begin; DELETE FROM push_token WHERE id IN (1);

begin; UPDATE push_token SET deleted = 1 WHERE token =‘token1’AND app_id =‘1’;commit;

Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (17.32 sec)ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 二、分析方法

我使用的分析方法是把整个加锁的日志打印出来，当然需要用到我自己做了输出修改的一个版本，如下：
https://github.com/gaopengcarl/percona-server-locks-detail-5.7.22

这个版本我打开了的日志记录参数如下：

mysql  show variables like  %gaopeng% 
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| gaopeng_mdl_detail | OFF |
| innodb_gaopeng_row_lock_detail | ON |
+--------------------------------+-------+
2 rows in set (0.01 sec)

这样大部分的 innodb 加锁记录都会记录到 errlog 日志了。好了下面我详细分析一下日志：

三、分析过程

初始化的情况整个表只有 1 条记录，本表包含一个主键和一个唯一键。

s1（TRX_ID367661）执行语句

begin;
UPDATE push_token SET deleted = 1 WHERE token =  token1  AND app_id =  1

日志输出：

2019-08-18T19:10:05.117317+08:00 6 [Note] InnoDB: TRX ID:(367661) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 6; hex 746f6b656e31; asc token1;;
 1: len 1; hex 31; asc 1;;
 2: len 8; hex 8000000000000001; asc ;;
2019-08-18T19:10:05.117714+08:00 6 [Note] InnoDB: TRX ID:(367661) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc ;;
 1: len 6; hex 000000059c2c; asc ,;;
 2: len 7; hex bf000000420110; asc B ;;
 3: len 6; hex 746f6b656e31; asc token1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 80; asc ;;

我们看到主键和唯一键都加锁了如下图：

s2（TRX_ID367662）执行语句

begin;DELETE FROM push_token WHERE id IN (1);
`

日志输出：

2019-08-18T19:10:22.751467+08:00 9 [Note] InnoDB: TRX ID:(367662) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc ;;
 1: len 6; hex 000000059c2d; asc -;;
 2: len 7; hex 400000002a1dc8; asc @ * ;;
 3: len 6; hex 746f6b656e31; asc token1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 81; asc ;;
2019-08-18T19:10:22.752753+08:00 9 [Note] InnoDB: Trx(367662) is blocked!!!!!

这个时候 S2 需要获取主键上的锁，因此被堵塞了如下图：

s3（TRX_ID367663）执行语句

begin; UPDATE push_token SET deleted = 1 WHERE token =  token1  AND app_id =  1 
`

日志输出：

019-08-18T19:10:30.822111+08:00 8 [Note] InnoDB: TRX ID:(367663) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 6; hex 746f6b656e31; asc token1;;
 1: len 1; hex 31; asc 1;;
 2: len 8; hex 8000000000000001; asc ;;
2019-08-18T19:10:30.918248+08:00 8 [Note] InnoDB: Trx(367663) is blocked!!!!!

这个时候 S3 需要获取唯一键上的锁，因此被堵塞了如下图：

s1（TRX_ID367661）执行语句

这一步完成后死锁出现。

commit;

日志输出如下：

367663 和 367662 各自获取需要的锁
2019-08-18T19:10:36.566733+08:00 8 [Note] InnoDB: TRX ID:(367663) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 6; hex 746f6b656e31; asc token1;;
 1: len 1; hex 31; asc 1;;
 2: len 8; hex 8000000000000001; asc ;;
2019-08-18T19:10:36.568711+08:00 9 [Note] InnoDB: TRX ID:(367662) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc ;;
 1: len 6; hex 000000059c2d; asc -;;
 2: len 7; hex 400000002a1dc8; asc @ * ;;
 3: len 6; hex 746f6b656e31; asc token1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 81; asc ;;
367663 获取主键锁堵塞、367662 获取唯一键锁堵塞，死锁形成
2019-08-18T19:10:36.570313+08:00 8 [Note] InnoDB: TRX ID:(367663) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc ;;
 1: len 6; hex 000000059c2d; asc -;;
 2: len 7; hex 400000002a1dc8; asc @ * ;;
 3: len 6; hex 746f6b656e31; asc token1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 81; asc ;;
2019-08-18T19:10:36.571199+08:00 8 [Note] InnoDB: Trx(367663) is blocked!!!!!
2019-08-18T19:10:36.572481+08:00 9 [Note] InnoDB: TRX ID:(367662) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP|
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 6; hex 746f6b656e31; asc token1;;
 1: len 1; hex 31; asc 1;;
 2: len 8; hex 8000000000000001; asc ;;
2019-08-18T19:10:36.573073+08:00 9 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

这个时候我们看到 s2 和 s3 先是获取了各自需要的锁，s3 获取主键锁堵塞，s2 获取唯一键锁堵塞，死锁出现。如下图：

到此，关于“MySQL 死锁的原因及解决方法”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！

正文完