mysql select for updae语句死锁_select for update引发死锁分析

本文针对MySQL中在Repeatable Read的隔离级别下使用select for update可能引发的死锁问题进行分析。

1. 案例

业务中需要对各种类型的实体进行编号，例如对于x类实体的编号可能是x201712120001，x201712120002，x201712120003类似于这样。可以观察到这类编号有两个部分组成：x+日期作为前缀，以及流水号(这里是四位的流水号)。

如果用数据库表实现一个能够分配流水号的需求，无外乎就可以建立一个类似于下面的表

CREATE TABLE number (

prefix VARCHAR(20) NOT NULL DEFAULT ‘‘ COMMENT ‘前缀码‘,

value BIGINT NOT NULL DEFAULT 0 COMMENT ‘流水号‘,

UNIQUE KEY uk_prefix(prefix)

);

那么在业务层，根据业务规则得到编号的前缀比如x20171212，接下去就可以在代码中起事务，用select for update进行如下的控制。

@Transactional

long acquire(String prefix) {

SerialNumber current = dao.selectAndLock(prefix);

if (current == null) {

dao.insert(new Record(prefix, 1));

return 1;

}

else {

current.number++;

dao.update(current);

return current.number;

}

}

这段代码做的事情其实就是加锁筛选，有则更新，无则插入，然而在Repeatable Read的隔离级别下这段代码是有潜在死锁问题的。(另一处与事务相关的问题也会在下文提及)。

2. 死锁的原因

当可以通过select for update的where条件筛出记录时，上面的代码是不会有deadlock问题的。然而当select for update中的where条件无法筛选出记录时，这时在有多个线程执行上面的acquire方法时是可能会出现死锁的。

2.1 死锁的简单复现

下面通过一个比较简单的例子复现一下这个场景

首先给表里初始化3条数据。

insert into number select ‘bbb‘,2;

insert into number select ‘hhh‘,8;

insert into number select ‘yyy‘,25;

接着按照如下的时序进行操作：

session 1

session 2

begin;

begin;

select * from number where prefix=‘ddd‘ for update;

select * from number where prefix=‘fff‘ for update

insert into number select ‘ddd‘,1

阻塞中

insert into number select ‘fff‘,1

插入成功

死锁，session 2的事务被回滚

2.2 死锁的分析

通过show engine innodb status，我们慢慢地观察每一步的情况：

2.2.1 session1做了select for update

------------

TRANSACTIONS

------------

Trx id counter 238435

Purge done for trx‘s n:o < 238430 undo n:o < 0 state: running but idle

History list length 13

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 281479459589696, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 281479459588792, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 238434, ACTIVE 3 sec

2 lock struct(s), heap size 1136, 1 row lo