线上真实死锁分析

Tomsidi

已于 2022-09-24 22:44:47 修改

阅读量679

点赞数

分类专栏： MySQL进阶踩坑日志文章标签：数据库 mysql 死锁 MySQL进阶

于 2022-09-24 17:07:51 首次发布

本文链接：https://blog.csdn.net/Tomsidi/article/details/127027661

版权

踩坑日志同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

MySQL进阶

1 篇文章 0 订阅

订阅专栏

文章目录

一、死锁场景还原

概念

这里只简单介绍下，具体各个锁产生的场景还请另外百度。
从不同角度，锁有不同的分类
粒度：行锁、表锁
读写：读锁（共享锁 S）、写锁（排它锁 X）
mysql中具体锁的具体实现：

记录锁（Record Lock，锁住具体的行）
间隙锁（Gap Lock，锁住的是记录间的空隙，例如锁住一个范围 1 < id < 5，这个范围内不存在其他记录行）
临键锁（Next Key Lock，是记录锁与间隙锁的缝合，会锁住范围内的间隙以及范围内的具体行）
插入意向锁（Insertion Intention Lock，间隙锁的变种，只锁住插入的那一行，至于为什么只锁住一行，官方解释是为了提高插入的效率）
意向共享锁（IS，表级锁，不是传统意义上的锁表，当表中某些行加了共享锁，那么表上会附加一个意向共享锁，表示该表内有行共享锁存在）
意向排它锁（IX，不是传统意义上的锁表，当表中某些行加了排它锁，那么表上会附加一个意向排他锁，表示该表内某些行被加了排它锁）

数据库表ddl

CREATE TABLE `tb_qy_old_given` (
  `GIVEN_ID` varchar(64) NOT NULL COMMENT '编号',
  `REC_ID` varchar(64) DEFAULT NULL COMMENT '负责人',
  `create_person` varchar(64) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `person_name` varchar(200) DEFAULT NULL COMMENT '用户名称',
  `head_pic` varchar(200) DEFAULT NULL COMMENT '用户头像',
  `wx_user_id` varchar(200) DEFAULT NULL COMMENT '用户微信id',
  `department_name` varchar(200) DEFAULT NULL COMMENT '用户部门名称',
  `type` varchar(1) DEFAULT NULL COMMENT '0默认负责人 1默认相关人',
  `ORG_ID` varchar(200) DEFAULT NULL COMMENT '机构ID',
  `EXT2` varchar(200) DEFAULT NULL COMMENT '扩展2',
  `EXT3` varchar(200) DEFAULT NULL COMMENT '扩展3',
  `sort` int(8) DEFAULT NULL COMMENT '排序号',
  `apply_type` varchar(64) DEFAULT NULL COMMENT '应用类型\n\n',
  `child_apply_type` varchar(64) DEFAULT NULL COMMENT '子应用类型',
  `son_org_id` varchar(36) DEFAULT '0' COMMENT '部门机构ID',
  `unit_id` varchar(36) DEFAULT NULL COMMENT '单位ID',
  PRIMARY KEY (`GIVEN_ID`),
  KEY `tb_qy_old_given` (`REC_ID`,`create_person`,`ORG_ID`,`type`),
  KEY `idx_oldgiven_oid` (`ORG_ID`),
  KEY `idx_oldgiven_cta` (`create_person`,`type`,`apply_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

事务执行情况

事务A			事务B
begin;	-	-	begin;
delete from tb_qy_old_given where create_person=‘a99c69cf924247ddbf2e3ac43a1e0466’ and apply_type=‘crm’ and type=‘0’ and org_id=‘b17efb43-292e-4cc9-ac5d-0b46bce059c4’;	-	-
-	-	-	delete from tb_qy_old_given where create_person=‘a99c69cf924247ddbf2e3ac43a1e0466’ and apply_type=‘crm’ and type=‘0’ and org_id=‘b17efb43-292e-4cc9-ac5d-0b46bce059c4’; – 这里的delete语句与事务A中的一模一样 – 被阻塞
INSERT INTO `tb_qy_old_given` (`GIVEN_ID`, `REC_ID`, `create_person`, `create_time`, `person_name` , `head_pic`, `wx_user_id`, `department_name`, `type`, `ORG_ID` , `EXT2`, `EXT3`, `sort`, `apply_type`, `child_apply_type` , `son_org_id`) VALUES (UUID(), ‘b69661aa87159d18bef40da6d79846e3’, ‘a99c69cf924247ddbf2e3ac43a1e0466’, ‘2017-11-14 22:47:26’, ‘名字’ , ‘http://shp.qpic.cn/’, ‘dd’, NULL, ‘0’, ‘b17efb43-292e-4cc9-ac5d-0b46bce059c4’ , NULL, NULL, ‘1’, ‘crm’, NULL , ‘0’);	-	-	死锁报错

二、死锁产生的原理

事务A执行delete语句，此时mysql会先去执行查询，查找符合条件的记录。走的索引是idx_oldgiven_cta，因为该索引不是聚簇索引，所以会对符合delete条件的记录及这些记录附近加上间隙锁。
事务B执行delete语句，同样的会加间隙锁，因为delete语句与事务A中执行的相同，所以事务B加锁时会等待事务A释放锁，即事务B执行delete时会处于阻塞状态。
事务A执行insert语句，insert语句会加插入意向锁，这里锁住的是单条记录。又因为这里insert的记录刚好在前面事务B中delete语句的间隙锁范围内，导致insert语句需要等待事务B释放锁，事务A被阻塞。
上面执行后，mysql就检测到死锁了，事务B等待事务A提交释放锁，而事务A的insert语句又在等待事务B提交释放锁，最终出现死锁。
mysql检测到死锁后，会检测事务影响的行数多少，并将影响行数最少的事务回滚。这个例子里回滚的是事务B。
事务B被mysql自动回滚后，事务A的insert立即被执行。mysql检测到死锁后会立刻选定事务回滚掉，这就导致实际会看到事务A的insert语句一点击运行就直接成功了，同时伴随的还有事务B抛出死锁异常信息。

MySQL死锁日志分析

-- 查询最近的死锁信息
SHOW ENGINE INNODB STATUS;

信息如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-09-08 15:58:47 0x7f03fbdb6700
*** (1) TRANSACTION:
TRANSACTION 636424171, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 256885, OS thread handle 139657455589120, query id 83158794 192.168.23.106 数据库账号 updating
delete from tb_qy_old_given where create_person='a99c69cf924247ddbf2e3ac43a1e0466' and apply_type='crm'  and type='0'   and org_id='b17efb43-292e-4cc9-ac5d-0b46bce059c4'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52804 page no 170 n bits 168 index idx_oldgiven_cta of table `tb_qy_old_given` trx id 636424171 lock_mode X waiting
Record lock, heap no 52 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 613939633639636639323432343764646266326533616334336131653034; asc a99c69cf924247ddbf2e3ac43a1e04; (total 32 bytes);
 1: len 1; hex 30; asc 0;;
 2: len 3; hex 63726d; asc crm;;
 3: len 30; hex 36633065666130382d326634382d313165642d396364312d303065303463; asc 6c0efa08-2f48-11ed-9cd1-00e04c; (total 36 bytes);

*** (2) TRANSACTION:
TRANSACTION 636424058, ACTIVE 25 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 252012, OS thread handle 139655087089408, query id 83159062 192.168.23.106 数据库账号 update
INSERT INTO `tb_qy_old_given` (

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 52804 page no 170 n bits 168 index idx_oldgiven_cta of table `tb_qy_old_given` trx id 636424058 lock_mode X
Record lock, heap no 52 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 613939633639636639323432343764646266326533616334336131653034; asc a99c69cf924247ddbf2e3ac43a1e04; (total 32 bytes);
 1: len 1; hex 30; asc 0;;
 2: len 3; hex 63726d; asc crm;;
 3: len 30; hex 36633065666130382d326634382d313165642d396364312d303065303463; asc 6c0efa08-2f48-11ed-9cd1-00e04c; (total 36 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52804 page no 170 n bits 168 index idx_oldgiven_cta of table `tb_qy_old_given` trx id 636424058 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 52 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 613939633639636639323432343764646266326533616334336131653034; asc a99c69cf924247ddbf2e3ac43a1e04; (total 32 bytes);
 1: len 1; hex 30; asc 0;;
 2: len 3; hex 63726d; asc crm;;
 3: len 30; hex 36633065666130382d326634382d313165642d396364312d303065303463; asc 6c0efa08-2f48-11ed-9cd1-00e04c; (total 36 bytes);

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 636424984
Purge done for trx's n:o < 636424834 undo n:o < 0 state: running but idle
History list length 72
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421137415119360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421137415222416, not started

首先看(1) TRANSACTION：

*** (1) TRANSACTION:
TRANSACTION 636424171, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 256885, OS thread handle 139657455589120, query id 83158794 192.168.23.106 数据库账号 updating
delete from tb_qy_old_given where create_person='a99c69cf924247ddbf2e3ac43a1e0466' and apply_type='crm'  and type='0'   and org_id='b17efb43-292e-4cc9-ac5d-0b46bce059c4'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52804 page no 170 n bits 168 index idx_oldgiven_cta of table `tb_qy_old_given` trx id 636424171 lock_mode X waiting
Record lock, heap no 52 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 613939633639636639323432343764646266326533616334336131653034; asc a99c69cf924247ddbf2e3ac43a1e04; (total 32 bytes);
 1: len 1; hex 30; asc 0;;
 2: len 3; hex 63726d; asc crm;;
 3: len 30; hex 36633065666130382d326634382d313165642d396364312d303065303463; asc 6c0efa08-2f48-11ed-9cd1-00e04c; (total 36 bytes);

TRANSACTION 636424171：事务的id为 636424171
ACTIVE 9 sec:该事务已经存在了9秒
starting index read：正在从从索引查找数据
mysql tables in use 1, locked 1：1张表在使用，有1个锁在使用
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)：LOCK WAIT表示正在等待锁, 2 lock struct(s) 表示trx->trx_locks锁链表的长度为2，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及auto_inc锁等。本案例中2 locks表示IX（意向排它锁）锁和 lock_mode X (gap lock 间隙锁)
1 row lock(s)：表示当前事务持有的行记录锁/gap 锁的个数
执行中的语句为delete from tb_qy_old_given where create_person='a99c69cf924247ddbf2e3ac43a1e0466' and apply_type='crm' and type='0' and org_id='b17efb43-292e-4cc9-ac5d-0b46bce059c4'
(1) WAITING FOR THIS LOCK TO BE GRANTED：这行下面的内容是当前事务等待获取锁的详细信息
RECORD LOCKS：行锁
index idx_oldgiven_cta of table tb_qy_old_given：使用的是tb_qy_old_given表上的索引idx_oldgiven_cta
trx id 636424171：事务id为636424171
lock_mode X waiting：当前事务等待获取的锁类型是行锁排它锁，处于等待状态
n_fields 4：事务中使用到了四个字段
后面的列表0,1,2,3就是使用中字段的实际值，0: len 30; hex 613939633639636639323432343764646266326533616334336131653034; asc a99c69cf924247ddbf2e3ac43a1e04;中最后的a99c69cf924247ddbf2e3ac43a1e04就是delete语句中的create_person条件值，不过只使用到了前30位。
以上信息可以看出，实际上，该事务其实就是事务B。

在看事务2：

*** (2) TRANSACTION:
TRANSACTION 636424058, ACTIVE 25 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 252012, OS thread handle 139655087089408, query id 83159062 192.168.23.106 数据库账号update
INSERT INTO `tb_qy_old_given` (太长省略了，看上面的事务执行顺序图

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 52804 page no 170 n bits 168 index idx_oldgiven_cta of table `tb_qy_old_given` trx id 636424058 lock_mode X
Record lock, heap no 52 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 613939633639636639323432343764646266326533616334336131653034; asc a99c69cf924247ddbf2e3ac43a1e04; (total 32 bytes);
 1: len 1; hex 30; asc 0;;
 2: len 3; hex 63726d; asc crm;;
 3: len 30; hex 36633065666130382d326634382d313165642d396364312d303065303463; asc 6c0efa08-2f48-11ed-9cd1-00e04c; (total 36 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52804 page no 170 n bits 168 index idx_oldgiven_cta of table `tb_qy_old_given` trx id 636424058 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 52 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 30; hex 613939633639636639323432343764646266326533616334336131653034; asc a99c69cf924247ddbf2e3ac43a1e04; (total 32 bytes);
 1: len 1; hex 30; asc 0;;
 2: len 3; hex 63726d; asc crm;;
 3: len 30; hex 36633065666130382d326634382d313165642d396364312d303065303463; asc 6c0efa08-2f48-11ed-9cd1-00e04c; (total 36 bytes);

与事务1类似，这里介绍不同的部分
看(2) HOLDS THE LOCK(S)后面的部分
使用的索引与事务1中的是同一个

lock_mode X：行锁，排它锁，具体类型是间隙锁。这里提一下，如果不是间隙锁会有but not gap的字眼。这里持有的锁其实就是事务A中delete语句的锁。
后面的列表0,1,2,3中的值对应的就是delete语句中的条件值。
看(2) WAITING FOR THIS LOCK TO BE GRANTED后的部分
索引当然还是与上面相同
lock_mode X locks gap before rec insert intention waiting：行锁，排它锁，locks gap before rec间隙锁，insert intention插入意向锁，waiting等待获取锁。
插入意向锁是特殊的间隙锁，它锁住的是插入的那一行。

最后是mysql自动回滚的事务

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 636424984
Purge done for trx's n:o < 636424834 undo n:o < 0 state: running but idle
History list length 72
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421137415119360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421137415222416, not started