循环update导致的mysql deadlock分析

Posted on | Edited on | Comments:

最近工作上发生了一次mysql的deadlock事件，发生死锁的是几个批处理的脚本，没有显示执行transaction，而且是一条一条sql执行的，一次更新或者插入一条记录。以往介绍死锁的案例都是使用事务，然后事务中执行多条sql的，所以面对此情况瞬间蒙蔽。不过也借此机会整理与mysql锁相关的基础知识。

案例描述

有一个表stat_day，表结构如下（由于实际的数据表不方便直接贴出来，因此进行了修饰）

1
2
3
4
5
6
7
8
9
10
11
create table stat_day (
    money int not null default 0,
    statdate datetime not null,
    package_code varchar(20) not null default '',
    gid varchar(20) not null default '',
    cid varchar(20) not null default '',
    index statdate(statdate),
    index package_code(package_code),
    index gid(gid),
    index cid(cid)
)

工作中需要统计数据，汇总出统计表（stat_day），会分别启动进程进行统计，每个进程统计完成之后，会对stat_day逐行进行update或者insert，统计脚本中没有显示地开启事务，而且update的结果集是不会有交集的（不会同时更新同一行记录），结果却发生了mysql的死锁。
以下是使用show engine innodb status输出信息中，截取的关于deadlock的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
*** (1) TRANSACTION:
TRANSACTION 5773770925, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 25 lock struct(s), heap size 6544, 212 row lock(s)
MySQL thread id 166136353, OS thread handle 0x7f281dbff700, query id 25400301595 10.105.215.155 921_mixsdk Searching rows for update
UPDATE `stat_day` SET `money`='1' WHERE `package_code` = 'P0003263' AND `gid` = 'PM000399' AND `cid` = 'C0000215' AND `statdate` = '2018-10-19'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 602 page no 3981 n bits 184 index `GEN_CLUST_INDEX` of table `test_database`.`stat_day` trx id 5773770925 lock_mode X locks rec but not gap waiting
Record lock, heap no 63 PHYSICAL RECORD: n_fields 67; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 5773770923, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
52 lock struct(s), heap size 6544, 2747 row lock(s)
MySQL thread id 166135142, OS thread handle 0x7f27fee7c700, query id 25400301589 10.105.215.155 921_mixsdk Searching rows for update
UPDATE `stat_day` SET `money`='20' WHERE `package_code` = 'P0002754' AND `gid` = 'PM000362' AND `cid` = 'C0000215' AND `statdate` = '2018-09-20'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 602 page no 3981 n bits 184 index `GEN_CLUST_INDEX` of table `test_database`.`stat_day` trx id 5773770923 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 602 page no 3993 n bits 216 index `GEN_CLUST_INDEX` of table `test_database`.`stat_day` trx id 5773770923 lock_mode X locks rec but not gap waiting

问题研究

死锁信息描述

由show engine innodb status中关于deadlock的描述，可以得出以下信息：

  • TRANSACTION(1)执行的语句UPDATE `stat_day` SET `money`='1' WHERE `package_code` = 'P0003263' AND `gid` = 'PM000399' AND `cid` = 'C0000215' AND `statdate` = '2018-10-19'等待着GEN_CLUST_INDEX上一批记录的x锁
  • TRANSACTION(2)执行的语句UPDATE `stat_day` SET `money`='20' WHERE `package_code` = 'P0002754' AND `gid` = 'PM000362' AND `cid` = 'C0000215' AND `statdate` = '2018-09-20'拥有GEN_CLUST_INDEX上一批记录的x锁，并且等待着GEN_CLUST_INDEX上另一批记录的x锁
  • 刚好，TRANSACTION(1)中等待的锁在TRANSACTION(2)上，而TRANSACTION(2)等待着的另外的锁

解决方案

alter table stat_day add key new_index(statdate, gid, cid, package_code)

探究

疑问

从死锁信息中得到以下问题

  • 什么是x锁
  • 什么事GEN_CLUST_INDEX
  • 各个进程不会同时更新同一行记录，为何会产生死锁
  • update语句中究竟发生了什么

资料收集与总结

s锁与x锁

InnoDB支持的行锁有两种类型，共享锁（S锁）和排它锁（X锁）

  • 获取S锁的事务，才可以读取对应的行
  • 获取X锁的事务，才可以update或者delete对应的行

如果事务T1持有第r行的S锁，然后另一个事务T2请求第r行的锁（S锁或者X锁），会发生以下情况：

  • T2请求的是S锁，则会被马上允许，最终T1和T2都持有S锁
  • T2请求的是X锁，则会被拒绝
    如果T1持有的是第r行的X锁，那么无论T2请求的是第r行的S锁还是X锁，都会被拒绝，必须等待T1释放掉X锁为止。

GEN_CLUST_INDEX

对于InnoDB，如果没有创建主键，那么InnoDB会自动创建一个隐藏的字段作为表的主键。上面的实例中，由于stat_day没有创建主键，自动创建了一个隐藏的“主键”，而GEN_CLUST_INDEX就是这个隐藏主键的索引。

B+Tree索引与聚簇索引

InnoDB默认的索引是B+Tree类型，关于B+Tree不作详细描述，请移步到谷歌或者百度。
InnoDB主键是聚簇索引，聚簇索引的索引页面指向数据页面，即B+Tree索引，其叶子节点是数据本身。而对于二级索引（非主键索引），索引页面指向的是主键索引。
如果对二级索引进行加锁的时候，其实最终是对主键进行加锁。两个不同的B+Tree二级索引，它们是两棵不同的B+Tree，他们的叶子节点可能只存在交集，即指向相同的主键。

案例中死锁原因

通过查看mysql执行计划，可以看到实例中update语句where中4个字段，只用到了2个字段的索引，分别是package_code和gid，然后进行merge，因此出现了以下的情况：

  • TRANSACTION(2)对符合package_code搜索条件的数据row1申请了X锁，二级索引、GEN_CLUST_INDEX均进行了锁操作
  • 然后TRANSACTION(2)再对符合gid搜索条件的数据row2申请X锁，但是row2被另外的TRANSACTION(3)加上了X锁，因此TRANSACTION(2)只能等待TRANSACTION(3)释放锁
  • TRANSACTION(1)对符合package_code搜索条件的数据row1申请X锁，但是row1被TRANSACTION(2)申请了X锁，因此TRANSACTION(1)也只能等待TRANSACTION(2)释放锁

解决方案

因为存在两个不同的二级索引，update的时候需要分别对两个二级索引加锁，产生了deadlock的可能性，因此解决方案就是创建一个联合索引new_index(statdate, gid, cid, package_code)，这样对于上述案例中的update语句，就会使用此联合索引进行锁操作，从而避免了deadlock的发生。

参考资料