库存扣减时，出现 MySQL 死锁是什么原因？

[jonsmith]

在研究并发请求商品超售问题时，测试了几种 MySQL 的 sql 语句，其中一种情况产生死锁的问题很困惑，求大佬指点。

SQL 语句

UPDATE inventory SET stock = stock - Y WHERE product_id = X AND stock >= Y;

• product_id 主键索引，此语句本意想避免超售，即 stock 库存不能为负。
• 测试发现，当满足以下两种情况时，并发请求会死锁堵塞：
  • 此 sql 语句放在事务内执行。
  • stock 库存不足。
  • 以上两种情况只满足其一，不会造成死锁。

对比 SQL

// 事务中，先对库存加锁 for update ，避免其他事务修改库存
SELECT stock FROM inventory WHERE product_id = X FOR UPDATE;
// 再进行修改
UPDATE inventory SET stock = stock - Y WHERE product_id = X;

• 如果换成这个 sql 语句不会出现死锁的堵塞。

请教下第一种 sql 是什么原因造成的死锁？

抱歉，我代码的错误，第1个SQL事务中忘记rollback。导致锁没释放，现在没问题了。

那么这三种语句在事务中，实际性能有差异吗？

// 第1
UPDATE inventory SET stock = stock - Y WHERE product_id = X AND stock >= Y;

// 第2
SELECT stock FROM inventory WHERE product_id = X FOR UPDATE;
// 一些业务逻辑...
UPDATE inventory SET stock = stock - Y WHERE product_id = X;

// 第3
UPDATE inventory SET stock = (case when (stock >= Y) then (stock - Y) else stock end) WHERE product_id = X;

[keymao]

for update 会阻塞其他行级锁的请求，你上面那个 update 在库存不足的时候会执行失败，这样事务没执行完就锁着吧。

你不开事务的话，for update 也是不生效的。 所以也没有问题。

[jonsmith]

@keymao 两种 SQL 都在事务中执行的，当库存不足时，只有第 1 种会死锁。
我怀疑是不是 where 条件里 `stock >= Y` 在事务中会扩大锁的范围，产生一些奇怪的死锁逻辑。

[jonsmith]

又测试了第 3 种 SQL 语句：
```
UPDATE inventory SET stock = (case when (stock >= Y) then (stock - Y) else stock end) WHERE product_id = X;
```
这个在事务中也不会造成死锁。

[jonsmith]

MySQL 是默认 RR 事务级别

[ezwd]

MySQL 的 InnoDB 存储引擎用行级锁来实现多版本并发控制（ MVCC ），同时也支持 "SELECT ... FOR UPDATE" 这种显式锁定。在解决超售问题的场景中，死锁的出现可能是由于并发事务试图在同一时间内锁定同一行数据导致的。

对于你的第一种 SQL 语句：

```
UPDATE inventory SET stock = stock - Y WHERE product_id = X AND stock >= Y;
```
这个语句在更新前会先检查 stock >= Y 条件，如果不满足这个条件，该行不会被锁定也不会被更新。当你有多个并发事务试图更新同一个 product_id ，并且 stock 库存不足时，这些事务可能会互相等待其他事务释放锁，这就可能导致死锁。

对于你的第二种 SQL 语句：

```
SELECT stock FROM inventory WHERE product_id = X FOR UPDATE;
UPDATE inventory SET stock = stock - Y WHERE product_id = X;
```
在这个情况下，你先显式地获取了一个行级锁，这会阻止其他事务在此期间修改这一行。然后，你再执行更新操作。由于你已经持有了行级锁，所以其他试图更新这一行的事务会被阻塞，直到你的事务完成，这样就避免了死锁。

总的来说，第一种 SQL 语句在高并发的情况下可能会导致死锁，因为它试图在同一时间内更新同一行。而第二种 SQL 语句通过显式获取行级锁来避免这个问题。这就是为什么第二种 SQL 语句在你的测试中没有出现死锁。

[vvvVictoria]

@ezwd #5 stock 库存不足就不满足 stock >= Y 的条件了啊，按照你说的不满足这个条件，该行不会被锁定也不会被更新，就不会死锁了...

[jonsmith]

@janwarlen 对，代码 bug ，抱歉，已经 append 了

[asasjajsajsd]

用加行锁的，防止脏数据

[bololobo]

这个案例体现了 innoDB 锁体系的一个特点 就是两阶段锁：在执行语句时加的锁，要到提交事务或者回滚事务的时候才释放

[Pythoner666666]

我们线上用的是第二种，就是为了避免死锁的情况

[Oilybear]

还有其他索引吗比如 stock 之类的

[F281M6Dh8DXpD1g2]

UPDATE inventory SET stock = stock - Y WHERE product_id = X AND stock >= Y
这种除非你还有个索引在 stock 上而且是倒序的而且还有另外一个 sql 去更新这个索引才会出现死锁

[ZZ74]

1 3 无差别...
2 会略微低一些 主要是并发性上。
另外 忘记 rollback.....话说还在手工控制事务么........

[xiang0818]

用了分布式事务吧

[urlk]

可以换别的实现 , 比如库存丢 redis , 然后用 redis 实现锁; 总感觉 MySQL 并发跟不上, 秒杀/营销 场景 容易超卖 ...

[wu00]

单看这 3 个语句：
1-乐观锁，stock >= Y 锁粒度最小化，性能最好，体验方面也比时间戳的 version 强得多；
2-悲观锁，碰到其它线程对这条数据进行 for update 时会进行排队，性能最低，但是优点是当你处理"一些业务逻辑"时不用担心当前事务并行执行；
3-没有存在的必要，似乎跟 1 没什么区别。

[leorealman]

for update 简单说就是一致性锁定读，因为由于 MVCC 的存在每个事务都可以获取一个数据版本且可以修改它，所以有可能修改到和其他事务正在处理相同的某一行数据，不知道我解释清楚了没？

[mmdsun]

@Pythoner666666 第一种应该不会死锁，楼主笔误写错了。


@shenjinpeng 性能差了点，但用 update + where 条件 不会超卖的。