循环update导致的mysql deadlock分析

最近工作上发生了一次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_codegid,然后进行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的发生。

参考资料