大促前压测,两个并发请求各买 1 件,库存 1 件,最后卖出 2 件。压测同学把日志发过来,里面还有另一种报错:Lock wait timeout exceeded; try restarting transaction。后台运营改价格的页面也卡住,点保存一直转圈,最后报错。
这三个现象的根都在锁:读的时候没锁住、写的时候锁太宽、两个事务互相等对方。这篇用两个真实连接把过程跑一遍,顺便把 MVCC、隔离级别、间隙锁和死锁日志拆开。看完至少能做到:看到 1205 和 1213 不再慌,知道先查什么。
场景走查:同一行被两个人改
库存表只有一行 id = 1, qty = 10。两个事务都执行 UPDATE stock SET qty = qty - 1 WHERE id = 1。InnoDB 会给这一行加排他锁,第一个事务拿到锁,第二个只能等。第一个提交后,第二个才继续执行,读到最新的 9,减成 8。这个过程是正确的,不会超卖——前提是扣减判断在同一条语句里。
如果代码写成「先 SELECT qty,在应用里判断,再 UPDATE」,普通 SELECT 不加锁,两个事务都读到 10,都判断够,最后都写 9。锁在这里没帮上忙,因为读的时候根本没参与竞争。
锁的几个层次
InnoDB 的行锁是加在索引上的,不是加在「行号」上。这点很关键:如果 WHERE 条件用不上索引,数据库只能扫全表,沿途碰到的行都可能被锁住,效果接近表锁。线上「改一行卡住全表」的事故,很多都是因为条件列没索引。
普通 SELECT 不加锁,SELECT ... FOR UPDATE 加排他锁,SELECT ... FOR SHARE(老写法 LOCK IN SHARE MODE)加共享锁。UPDATE、DELETE 默认加排他锁。INSERT 也是排他锁,还会涉及自增锁和唯一键冲突检查。
共享锁和排他锁的兼容关系用一张表最清楚:
| 已有锁 \ 新请求 | 共享锁 S | 排他锁 X |
|---|---|---|
| 共享锁 S | 兼容 | 冲突,等 |
| 排他锁 X | 冲突,等 | 冲突,等 |
意向锁是表级标记,分意向共享(IS)和意向排他(IX),作用是不用逐行检查就知道「这张表里已经有人加了行锁」。事务想锁整张表时,先看意向锁就能判断有没有冲突的行锁,不用扫全表所有行。它们彼此兼容,和表级 S/X 的关系按上面的矩阵判断。实际业务几乎不需要手动加意向锁,它是 InnoDB 内部协调表锁和行锁用的,但看 data_locks 时会出现,认得出就行。
翻车现场:改一条备注,锁了 2005 条记录
运营后台有个「批量标记」功能,SQL 是 UPDATE orders SET remark = '加急' WHERE remark LIKE '%急%'。remark 上没有索引,执行期间 C 端下单和改单大量报 1205。开发觉得奇怪:只是改备注,怎么会影响下单?
原因就在上一节那句话:InnoDB 的行锁加在索引上。条件用不上索引,数据库只能全表扫描,扫到的每一行都要加排他锁;RR 下还会把记录之间的间隙一起锁住。在本机实测,用 performance_schema.data_locks 统计同一个 UPDATE 在不同条件下产生的记录锁数量:
| WHERE 条件 | 是否走索引 | RECORD 锁数量 |
|---|---|---|
id = 7 | 主键 | 1 |
customer_id = 7 | 二级索引,匹配 20 行 | 41 |
status = 'paid' | 有索引,但优化器选了全表扫 | 2005 |
remark LIKE '%急%' | 无索引,全表扫 | 2005 |
统计方法是在一个未提交的事务里执行 UPDATE,再用另一个连接查:
SELECT LOCK_TYPE, LOCK_MODE, COUNT(*)
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = 'docs_sql_demo'
AND OBJECT_NAME = 'orders'
GROUP BY LOCK_TYPE, LOCK_MODE;
2005 条记录锁,基本等于整张表。表只有 1998 行,锁条目却超过行数,是因为还包括了间隙和 supremum 这类边界记录。注意 customer_id = 7 那行:匹配 20 行却锁了 41 条,多出来的是记录之间的间隙锁——这就是 RR 下「明明只改 20 行,插入却被挡住」的原因。
更值得警惕的是 status = 'paid':它明明有索引,却和「无索引」锁了一样多。因为状态区分度低,优化器算账后选了全表扫描,锁的范围也就跟着变成全表。这说明「建了索引」不等于「锁的范围就小」,还要看优化器实际选了什么计划。
修法有两层。第一层是让条件走索引:remark 这种模糊匹配不适合做业务筛选,改成按标签表或 id 列表批量更新。第二层是把大批量更新拆小,每批几十行、单独短事务,别让一个 UPDATE 锁住整表几秒钟。批量任务应该跑在低峰期,并且限制单次影响行数。
EXPLAIN 的 type 是不是 ALL,再用 data_locks 数一下锁条目。锁的范围由「访问路径」决定,不由「你改了几行」决定。隔离级别与三种异常
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 典型实现 |
|---|---|---|---|---|
| 读未提交(RU) | 可能 | 可能 | 可能 | 几乎不加隔离 |
| 读已提交(RC) | 不会 | 可能 | 可能 | 每条语句一个新快照 |
| 可重复读(RR,MySQL 默认) | 不会 | 不会 | 标准说可能,InnoDB 用间隙锁基本挡住 | 事务开始一个快照 + next-key lock |
| 串行化(SERIALIZABLE) | 不会 | 不会 | 不会 | 读也加锁,并发最低 |
脏读是读到了别人没提交的数据;不可重复读是同一个事务里两次读同一行,值变了;幻读是两次查同一个范围,多出或少了行。MySQL 默认 RR,很多业务依赖「同一事务里读到的数据不变」这个特性。PostgreSQL 默认 RC,同一个事务里两次读可能不同,跨库迁移时要特别注意。
MVCC:为什么读不加锁也能一致
InnoDB 用多版本并发控制(MVCC)实现「快照读」。每行记录有两个隐藏字段:创建事务 id 和删除事务 id,undo log 里保存旧版本。普通 SELECT 按事务开始时的快照,找到「当时可见」的版本,所以不加锁也能读到一致结果。
实测两个连接:
-- 连接 A
BEGIN;
SELECT amount FROM orders WHERE id = 1; -- 10.00
-- 连接 B
BEGIN;
UPDATE orders SET amount = 20 WHERE id = 1;
COMMIT;
-- 连接 A 再读
SELECT amount FROM orders WHERE id = 1; -- 仍是 10.00
COMMIT;
-- 连接 A 提交后再读
SELECT amount FROM orders WHERE id = 1; -- 20.00
事务 A 两次读都是 10.00,因为它在读自己的快照;提交后再开新事务,才看到 20.00。这就是「可重复读」的实际表现,也是为什么「同一个查询白天晚上结果不一样」的排查里,要先确认是不是不同事务/不同连接。
但 UPDATE、DELETE、SELECT ... FOR UPDATE 是「当前读」,读的是最新版本,并且加锁。快照读和当前读混用,是很多「读到的值和更新时判断的值不一致」问题的来源。
Read View 里记着几个关键数字:当前活跃事务 id 列表 m_ids、其中最小的 id、已经分配到的最大 id,以及创建它的时刻。读一行时,用这行的创建事务 id 去比对:比最小活跃 id 还小的是已提交的旧版本,可见;落在活跃列表里的是别人还没提交的,不可见;比最大 id 还大的是之后才开始的,也不可见。不可见就沿 undo 链往前找一个更老的版本。这就是「快照读」在代码层面的样子。
RR 和 RC 的差别只在于 Read View 什么时候建:RR 在事务第一次读时建一个,整个事务复用;RC 每条语句都新建一个,所以能立刻看到别人刚提交的数据。同一条 SQL,在 RR 的同一个长事务里跑两次结果一样;在 RC 里跑两次,中间别人提交了,结果就可能变。生产上遇到「同一个查询白天晚上结果不一样」,先确认连接池里的隔离级别,再确认查询是不是在不同事务里执行的。
间隙锁与 next-key lock
RC 级别下,InnoDB 基本只锁记录本身;RR 级别下,为了防止幻读,还会锁住记录之间的「间隙」,叫间隙锁(gap lock)。记录锁加间隙锁合起来叫 next-key lock。
比如索引上有 1、5、10 三个值,执行 WHERE id BETWEEN 5 AND 10 FOR UPDATE,锁的不只是 5 和 10,还可能包括 (1,5)、(5,10)、(10, 正无穷) 这些区间。别人想往这些区间插入新行,会被挡住。
这带来两个后果。好处是 RR 下范围查询不会突然多出行;坏处是锁范围变大,两个事务只要插入位置交叉,就很容易死锁。很多团队把隔离级别改成 RC,就是为了减少间隙锁带来的并发问题,代价是接受幻读风险,用业务唯一约束兜底。
举个具体的:索引上有 1、5、10。事务 A 执行 SELECT ... WHERE id BETWEEN 5 AND 10 FOR UPDATE,锁住了 5、10 以及它们之间的间隙。这时事务 B 想插入 id = 7,虽然 7 这一行还不存在,也会被挡住,报锁等待。B 的错觉是「我插入的是新行,跟别人没关系」,实际上新行的位置落在 A 锁住的区间里。插入位置在索引上是有序的,锁区间就按这个顺序划,这也是为什么「往中间插」比「往末尾插」更容易撞锁。
BEGIN; SELECT * FROM stock WHERE id BETWEEN 5 AND 10 FOR UPDATE; 不提交;B 查 SELECT INDEX_NAME, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks WHERE OBJECT_NAME = 'stock';,应该看到 LOCK_MODE 里带 GAP 的条目,LOCK_DATA 给出边界值;再让 B 插入 id = 7,应该进入 LOCK WAIT。8.0.18 及以上也是这几张表,字段可能略有增减,具体输出和边界以你的版本为准,标记为待确认。SELECT 是快照读,不加任何锁;SELECT ... FOR UPDATE 才会触发 next-key lock。实测锁等待:1205
连接 A 先锁住 id = 1,连接 B 再更新同一行,并设置 3 秒锁等待超时:
-- 连接 A
BEGIN;
UPDATE stock SET qty = qty - 1 WHERE id = 1; -- 持有排他锁
-- 连接 B
BEGIN;
SET SESSION innodb_lock_wait_timeout = 3;
UPDATE stock SET qty = qty - 1 WHERE id = 1; -- 等待
实测 3 秒后 B 报错:InternalError 1205 (1205, 'Lock wait timeout exceeded; try restarting transaction')。A 一直不提交,B 就一直等,直到超时。超时时间由 innodb_lock_wait_timeout 控制,默认 50 秒。
遇到 1205,先别急着重试。要查清楚是谁持有锁、持有了多久:
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;
SHOW PROCESSLIST;
常见原因是某个事务忘了提交、事务里包了慢查询或网络调用,或者更新条件没走索引锁了太多行。重试只能缓解,根因不解决会反复出现。
实测死锁:1213 与日志怎么读
两个事务按相反顺序更新两行:A 先锁 id=1 再锁 id=2,B 先锁 id=2 再锁 id=1。等 A 去拿 id=2、B 去拿 id=1 时,双方都在等对方释放,形成环。实测结果:A 成功拿到 id=2,B 报 1213,被 InnoDB 回滚。
*** (1) TRANSACTION:
UPDATE stock SET qty = qty - 1 WHERE id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `docs_sql_demo`.`stock`
trx id 40443 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
UPDATE stock SET qty = qty - 1 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... trx id 40444 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
... lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
读日志的顺序:先看两个事务各自执行了什么 SQL,再看「HOLDS」持有哪些锁、「WAITING FOR」等哪些锁,最后看 InnoDB 选择回滚哪个事务(通常是代价小的那个)。这段日志的意思很直白:事务 1 拿着 id=1 等 id=2,事务 2 拿着 id=2 等 id=1,环闭合,必须牺牲一个。
查看命令:SHOW ENGINE INNODB STATUS,里面会有 LATEST DETECTED DEADLOCK 段;MySQL 8.0 还可以查 performance_schema.data_locks 和 data_lock_waits,实时看锁等待关系。
怎么减少死锁
死锁没法完全消灭,只能降低概率、缩短影响。几条实操:
- 所有事务按同一顺序访问资源。比如都按主键升序更新,别一个正序一个倒序。
- 事务尽量短,别在事务里做网络调用、发消息、等用户输入。
- 更新条件走索引,避免锁范围扩大到整表或大量间隙。
- 热点行(秒杀库存)用单行条件更新,或把热点拆成多行(库存分桶),减少同一行的竞争。
- 捕获 1213 后做有限次数重试,并加退避;不要无限重试。
- 能不用
SELECT ... FOR UPDATE就不用,优先条件更新或乐观锁。
如果业务允许,把隔离级别从 RR 调到 RC 可以显著减少间隙锁导致的死锁,但要评估幻读对业务的影响,并用唯一约束兜底。这是取舍,不是「RC 更先进」。
还有一个专门为「多消费者抢任务」设计的写法:SELECT ... FOR UPDATE SKIP LOCKED。它只锁住没被别人占用的行,跳过已锁的,适合任务队列、订单捞取这类场景,能避免所有消费者排在同一行上。代价是「跳过」意味着某些行会被暂时看不见,不适合「必须按顺序处理每一行」的业务。用之前先确认语义能接受。
线上还应该盯几个指标,不要等用户报错才发现锁问题。innodb_row_lock_current_waits 是当前正在等锁的请求数,innodb_row_lock_time_avg 是平均等待毫秒数,innodb_row_lock_waits 是累计等待次数。这几个值在高峰期持续爬升,说明有热点行或长事务。再配合 performance_schema.data_lock_waits 看具体的等待关系,定位到哪张表、哪一行、哪两个事务。指标只能提示「有问题」,具体原因还得回到 SQL 和事务边界。
现象对照
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 并发下超卖 | 先查后改,普通读不加锁 | 看更新是否带 qty >= ? 条件并检查受影响行数 |
| 报 1205 锁等待超时 | 另一个事务长期持有行锁,或更新没走索引锁太多行 | information_schema.innodb_trx 找长事务,查它的 SQL 和开始时间 |
| 报 1213 死锁 | 两个事务加锁顺序相反,形成等待环 | SHOW ENGINE INNODB STATUS 看 LATEST DETECTED DEADLOCK |
| 改一行导致整表卡住 | WHERE 列没索引,行锁退化成大量记录锁 | EXPLAIN 看是否全表扫描;补索引 |
| 同一事务两次查询结果不同 | 隔离级别是 RC,或中间做了当前读 | 查 @@transaction_isolation,确认读的是快照还是最新值 |
| RR 下插入新行被莫名挡住 | 间隙锁锁住了插入位置 | 看是否用了 FOR UPDATE 范围查询,考虑缩小范围或改 RC |
| 改一条备注,别的业务全在等锁 | 条件列无索引或优化器选全表扫,每行都加了锁 | 看 EXPLAIN 的 type,用 data_locks 数锁条目 |
| 有索引却仍然锁一大片 | 区分度低,优化器放弃索引改走全表扫 | 对比 FORCE INDEX 前后的 type 和锁数量 |
边界与代价
锁不是越少越好。没有锁,并发写会互相覆盖;锁太多、范围太大,吞吐下降、死锁增加。关键是锁住「真正需要互斥的最小范围」,而这依赖索引设计。
MVCC 也不是免费的:每个事务都要保留 undo 版本,长事务会让 undo 无法回收,磁盘和内存压力上升。一个跑了半小时的事务,可能让整个库的 purge 停滞,表现为「磁盘突然涨得很快」。
分布式锁、Redis 锁解决的是跨进程互斥,不能替代数据库事务。它们有自己的失效、网络分区、脑裂问题。数据库内的一致性,优先用数据库自己的能力解决。
还有一种容易被忽略的「锁」是元数据锁(MDL)。它不是行锁,不需要你写 FOR UPDATE,但只要有人执行 DDL,就会去申请表的 MDL,而普通的 SELECT、UPDATE 也会持有 MDL 的共享版本。结果是一条 ALTER TABLE 在等长事务释放,后续所有查询又排在 ALTER 后面,整张表看起来「卡死」,但 CPU 和行锁指标都不高。排查要看 performance_schema.metadata_locks,而不是 data_locks。
小结
InnoDB 的行锁加在索引上,条件不走索引就会锁一大片;普通读是 MVCC 快照读,不加锁,当前读才加锁;RR 用 next-key lock 防幻读,代价是间隙锁更容易死锁;1205 是等太久,1213 是环,先看 innodb_trx 和死锁日志再动手。
最后补一个排查顺序:遇到并发问题,先问「这是不是同一组写操作、能不能用一个事务解决」,再问「锁的范围是不是由索引访问路径决定」,最后才考虑调隔离级别或引入分布式锁。顺序反了,容易在错误的层面堆复杂度,问题却还在。
当天可以自己验证的练习:
- 用两个连接复现「A 读 10、B 改 20、A 仍读 10、A 提交后读 20」。
- 让一个事务持有行锁 3 秒,另一个事务设置
innodb_lock_wait_timeout=3,记录 1205。 - 按相反顺序更新两行,制造死锁,读出
LATEST DETECTED DEADLOCK段。 - 把更新条件改成不走索引的列,观察锁等待范围变大。
- 用
performance_schema.data_lock_waits查看当前的锁等待关系。
下一篇讲执行计划:看懂 EXPLAIN。锁的问题看日志,性能的问题看计划。很多人 EXPLAIN 只看「有没有走索引」,却忽略了 type、key_len、rows 和 Extra 才是判断快慢的关键。接下来逐列拆开。