MySQL死锁案例分析(一)

一、前言

打算写一系列死锁分析的例子,将平时遇到的死锁例子记录下来,做好记录,也当做积累。

二、死锁输出

2017-10-10 17:07:21 7f45a5104700InnoDB: transactions deadlock detected, dumping detailed information.
2017-10-10 17:07:21 7f45a5104700
*** (1) TRANSACTION:
TRANSACTION 47225424098, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 40396441, OS thread handle 0x7f569a68e700, query id 9746347697 10.200.181.72 trade updating
update table_b
        set updated_at = now(),
         price = 36900,
        where id = 1 and sku_id = 36171933 AND goods_id = 2
        and kdt_id = 3 and offline_id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13387 page no 67 n bits 344 index `PRIMARY` of table `dbname`.`table_b` trx id 47225424098 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 47225424090, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 13 row lock(s), undo log entries 1
MySQL thread id 40397515, OS thread handle 0x7f45a5104700, query id 9746347700 10.200.181.72 trade updating
update table_a
        set updated_at = now(),
        stock_num = 0,
        where goods_id = 2
        and offline_id = 1
        and kdt_id = 3
        and id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 13387 page no 67 n bits 344 index `PRIMARY` of table `dbname`.`table_b` trx id 47225424090 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13451 page no 193 n bits 192 index `PRIMARY` of table `dbname`.`table_a` trx id 47225424090 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
table_a 的索引
UNIQUE KEY `uniq_gid_oid_sid` (`goods_id`,`offline_id`,`sku_id`),

table_b 的索引 
UNIQUE KEY `uniq_gid_oid_sid` (`goods_id`,`offline_id`,`sku_id`)

具体的表名以及关键信息已经做了脱敏处理,采用table_a,table_b 。

三、死锁分析

要了解死锁的产生,必须先了解具体的事务逻辑,因此和开发进行沟通,这个事务的逻辑过程

  1. 首先会开启一个会话,查询table_a 表里面根据(goods_id,offline_id)查询是否存在对应的记录,如果存在执行第二步,如果不存在执行第三步
  2. 另外开启一个事务,执行select * from table_a where goods_id=xx and offline_id=yy for update,然后update table_b 表对应(goods_id,offline_id,sku_id)的记录,然后再次更新table_a 表的记录(根据ID)
  3. 另外开启一个事务,执行select * from table_a where goods_id=xx and offline_id=yy ,如果存在,则update table_b 表对应(goods_id,offline_id,sku_id)的记录+update table_a(根据ID),否则执行插入table_b 的操作+插入table_a 的操作

看死锁输出的等待 + 业务操作过程,画出等待矩阵图。

Time Sess 1 Sess 2
@t1 SELECT TABLE_A FOR UPDATE
@t2 SELECT TABLE_A FOR UPDATE
@t3 UPDATE TABLE_B
@t4 UPDATE TABLE_B(wating)
@t5 UPDATE TABLE_A(wating)

整个等待如上表所示,在@t4 时刻,Sess 1 对TABLE B 执行更新操作,发生等待,因为Sess 2 在@t3 时刻对TABLE B 表进行了更新操作。Sess 2在@t5时刻进行 UPDATE TABLE_A 发生了等待,因为Sess 1在@t2 时刻发生了更新操作。

但是这个图,我们仔细一想就是不可能的,因为在Sess 2 对 Table_A 进行了FOR UPDATE 后,那么Sess 1是不可能拿到TABLE A 的X lock的。

再次分析业务逻辑,我们发现在第三步的操作过程中,如果第一次查询不存在的时候,进入事务中,再次查询和第一次查询的结果可能存在不一致,也就是说,事务里面可能查询到记录。因此就会导致更新TABLE A 的时候,事务里面是没有执行SELECT TABLE_A FOR UPDATE的!

那么整个执行过程应该如下:

Time Sess 1 Sess 2
@t2 SELECT TABLE_A FOR UPDATE
@t3 UPDATE TABLE_B
@t4 UPDATE TABLE_B(wating)
@t5 UPDATE TABLE_A(wating)

@t4 的Sess 1 在等待 @t3 的Sess2, @t5的Sess 2 在等待 @t2 的Sess1,形成典型的交叉等待。其中 Sess2 没有执行FOR UPDATE。

整个业务逻辑就是:

  1. Sess 2 查询发现没有记录,开启一个事务
  2. Sess 1 查询发现有记录(其他会话插入),开启一个事务,执行FOR UPDATE
  3. Sess 2 执行表B 的update操作
  4. Sess 1 执行表A 的update 操作
  5. ...

那么如何避免这种典型的死锁呢?

  1. 修改业务逻辑,在第一次查询A表的时候,如果查到记录,可以传个FLAG,到事务中,那么事务中就执行执行插入操作,如果已经存在记录,就报错
  2. 修改SQL执行顺序,那么首先 A表 for update,然后更新A表,再更新B表,整个执行逻辑总是A表现操作,在操作B表,不会形成因为执行顺序不相同的死锁

四、小结

死锁的分析,一定要结合业务执行过程,否则凭空想象猜测,脑细胞要不够用哈哈。。

Write a Reply or Comment

电子邮件地址不会被公开。 必填项已用*标注