下面这条 SQL 使用主键定位看起来应该很快UPDATE inventory SET stock stock - 1 WHERE sku_id 1001;但在并发环境下它仍可能等待数秒甚至超时。原因不一定是没有索引也可能是另一笔事务持有锁迟迟没有结束。本文通过两个数据库会话解释普通查询为什么可能不等待。更新为什么需要等待。怎样避免库存超卖。锁等待与死锁有什么区别。示例面向MySQL 8.4、InnoDB、REPEATABLE READ。以下为复现步骤与预期现象未将其冒充实测截图。一、准备实验数据在测试数据库执行CREATE TABLE inventory ( sku_id BIGINT PRIMARY KEY, stock INT NOT NULL, CHECK (stock 0) ) ENGINE InnoDB; INSERT INTO inventory (sku_id, stock) VALUES (1001, 10), (1002, 10);然后打开两个独立连接分别称为会话 A 和会话 B。在两个会话中分别执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;二、实验一更新一行为什么会等待会话 ASTART TRANSACTION; UPDATE inventory SET stock stock - 1 WHERE sku_id 1001; -- 暂时不要提交。此时A 已经修改了记录但事务尚未结束。会话 BSTART TRANSACTION; UPDATE inventory SET stock stock - 1 WHERE sku_id 1001;预期B 的更新进入等待。因为 A 持有该记录的排他锁而 B 也需要修改同一条记录。现在回到 ACOMMIT;B 随后可以继续执行。最后在 B 中提交COMMIT;查询SELECT * FROM inventory WHERE sku_id 1001;预期库存为8。主键索引可以缩小定位和加锁范围但不能消除同一条记录上的写冲突。三、普通 SELECT 为什么可能不等待为了观察这个差异可以重新设置库存UPDATE inventory SET stock 10 WHERE sku_id 1001;A 再次执行START TRANSACTION; UPDATE inventory SET stock 9 WHERE sku_id 1001;A 不提交。B 在一个新的事务中执行START TRANSACTION; SELECT stock FROM inventory WHERE sku_id 1001;在该实验条件下普通一致性读可以通过 MVCC 读取可见的已提交版本通常会得到10而不等待 A 释放记录锁。但换成SELECT stock FROM inventory WHERE sku_id 1001 FOR UPDATE;就需要获取锁可能等待 A。普通一致性读与锁定读解决的是不同问题。MySQL 官方也指出读取后需要修改相关数据时普通SELECT不足以提供所需保护。MySQL 锁定读文档实验结束后让 A、B 分别提交或回滚避免把事务留在后台。四、为什么“先查库存再更新”容易出错考虑下面的应用逻辑查询库存 如果库存大于 0 扣减库存 创建订单假设只剩一件商品两个请求可能同时读到stock 1然后都判断库存充足。如果后续更新没有重新检查库存条件可能发生负库存如果两个请求都把库存写成0也可能出现库存没有负数却生成两笔有效订单的情况。因此检查与修改不能只依赖应用层先前读到的值。五、用条件 UPDATE 合并判断与扣减对于单个 SKU 的简单扣减可以使用UPDATE inventory SET stock stock - 1 WHERE sku_id 1001 AND stock 1;应用立即读取更新结果影响 1 行本次扣减成功。影响 0 行记录不存在或者库存不足。可以做一个并发实验先将库存设置为1再让两个会话执行这条 SQL。先获得锁的事务扣减成功。它提交后另一个更新会基于可更新记录重新判断条件因此不能再次扣减。这与“先普通查询一次再根据旧结果无条件更新”不同。实际购买数量也必须验证为正数UPDATE inventory SET stock stock - :quantity WHERE sku_id :sku_id AND stock :quantity;如果允许负数数量进入这条 SQL就可能反而增加库存。六、扣库存成功订单创建失败怎么办如果库存与订单在同一个数据库事务中可以这样组织BEGIN 条件扣减库存 检查影响行数 创建订单 COMMIT任何一步失败都回滚整个业务事务。但如果订单创建通过远程服务完成本地事务就不能自动保证远端操作的一致性需要另行设计跨服务流程。还有一个独立问题同一个请求重试两次条件扣减可能成功两次。因此防超卖与请求幂等不是同一件事。后者通常需要稳定的业务请求标识和唯一约束。七、锁等待和死锁有什么区别前面的实验是B 等 A只要 A 结束B 就可以继续。死锁则可能是A 持有商品 1001等待商品 1002 B 持有商品 1002等待商品 1001可以按顺序复现步骤会话 A会话 B1开启事务更新 10012开启事务更新 10023更新 1002进入等待4更新 1001形成循环等待启用死锁检测时InnoDB 会选择一个事务回滚解除循环。应用不能假设永远回滚某一方。可以检查SHOW ENGINE INNODB STATUS;其中包含最近一次死锁的信息。官方建议缩短事务、统一访问顺序并准备重试被回滚的事务。MySQL 死锁处理文档例如多 SKU 扣减时统一按 SKU ID 排序可以减少不同加锁顺序引发的死锁但不能保证所有死锁都消失。八、实际排查应该看什么当主键更新仍然很慢时可以优先检查SELECT * FROM sys.innodb_lock_waits;该视图需要相应环境和访问权限。重点不是只盯住正在等待的 SQL而是找出谁持有锁。持锁事务运行了多久。是否在事务中等待远程接口。是否存在忘记提交的连接。加快 SQL 本身与缩短事务持锁时间是两条不同的优化路径。
