1. 首页
  2. IT资讯

MySQL:死锁一例

欢迎关注我的《深入理解MySQL主从原理 32讲 》,如下:

image.png
如果图片不能显示可查看下面链接:
https://www.jianshu.com/p/d636215d767f

一、问题由来

这是我同事问我的一个问题,在网上看到了如下案例,本案例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  ;;  

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

MySQL:死锁一例

  • 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需要获取主键上的锁,因此被堵塞了如下图:

MySQL:死锁一例

  • 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需要获取唯一键上的锁,因此被堵塞了如下图:

MySQL:死锁一例

  • 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获取唯一键锁堵塞,死锁出现。如下图:

好了我们看到了死锁就这样出现。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-2651186/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183913.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code