深入理解MySQL select for update：锁类型、坑与并发实践

写这篇文章的起因是上周一个技术群里又有人甩出一行SQL问“为什么我加了select for update并发下数据还是被改坏了”。这句话我见过太多次了每次底下一堆人讨论最后发现大家连“for update到底锁住了什么”都没完全搞明白。实际上很多开发者对select for update的理解停留在“这玩意能锁行、防并发更新”这一层真到了生产环境锁没生效、范围超预期、连接池被打满、死锁日志看不懂各种问题接踵而至。这篇文章就把我这些年踩过的坑和排查思路完整捋一遍适合所有在业务代码里用过或准备用select for update的同学尤其是做订单、库存、账户、支付这类强一致性场景的。1. for update 到底是哪种锁先把锁的坐标系建立起来很多人一上来就问“这个SQL锁的是行还是表”这个问题的前提就错了。for update是MySQL InnoDB里的一种锁定读也叫当前读它会读最新版本的数据同时对命中的记录加排他锁。至于这个排他锁最终是行锁、间隙锁还是表锁完全取决于你的SQL走什么索引、怎么走。1.1 快照读和当前读普通select与for update的根本差别先记住一个核心概念InnoDB的普通select是不加锁的快照读。它在MVCC机制下读到的是某个时间点的快照所以你在一个事务里连续两次select哪怕别的事务中间把数据改了提交了你看到的可能还是同一个旧值可重复读隔离级别下尤其如此。正是因为这个特性普通select在多读场景下性能极好但它完全不管别人是不是正在改这行数据。for update就不一样了。它会读当前最新版本并且对检索到的记录加独占锁X锁保证在自己持有锁期间其他事务不能修改这些记录也不能对它们加锁。同行的事务来select for update会被阻塞但普通select不受影响——因为快照读根本不碰锁。这一点非常容易误判你以为加了for update就能“挡住”所有并发读实际上它只挡“写”和“锁定读”普通select照样能读到旧快照。1.2 InnoDB锁类型速查行锁、表锁、间隙锁与临键锁要真正理解for update必须先看InnoDB提供了哪些锁。我把常用的几类整理成了表格按“锁定的范围从小到大”排锁类型锁定范围常见触发条件记录锁Record Lock单条索引记录where条件命中唯一索引/主键等值查询间隙锁Gap Lock索引记录之间的间隙禁止他人插入可重复读隔离级别下范围查询或非唯一索引等值查询临键锁Next-Key Lock记录锁前面的间隙锁左开右闭可重复读隔离级别下范围查询的默认加锁方式表锁/意向锁整张表全表扫描、DDL、无索引条件查询等情况这里最关键的一点是InnoDB的锁都是加在索引上的。如果你查询条件的字段建了索引那锁的是索引记录如果没有索引InnoDB只能全表扫描扫描过程中会给所有聚簇索引记录加锁这时的for update实际上就是把整张表“锁”住了其他事务的插入、更新、删除全部会堵住。关于这个下一章我会给具体的踩坑现场。1.3 for update 的语法边界锁的生命周期由事务决定语法本身很简单-- 显式开启事务 START TRANSACTION; -- 或 BEGIN; SELECT * FROM t_account WHERE user_id 100 FOR UPDATE; UPDATE t_account SET balance balance - 10 WHERE user_id 100; COMMIT; -- 或 ROLLBACK锁才会释放for update的锁从执行这条SQL开始持有直到事务提交或回滚才释放。如果你把autocommit开着跑一条select for update那就是拿锁后立刻释放跟没锁一样。这是新手最容易踩的坑后面我会专门展开。提示for update只能用在InnoDB事务里MyISAM引擎不支持事务和行锁for update会直接落成表锁而且在事务里根本不会释放生产环境选错引擎是灾难。2. 九成开发者踩坑的四个现场为什么for update看着加了却没用这一章讲的都是我在实际项目和帮人排查问题中遇到的典型场景。每一种现象的第一反应都是“怎么锁没生效”逐层查下去根因各不相同。2.1 第一坑autocommit没关SQL一执行锁就没了最基础也最常见的坑。很多同学在Navicat里手动执行select for update发现锁是有的于是照搬到代码里但代码却是这样写的// 错误示例 String sql SELECT * FROM t_account WHERE user_id ? FOR UPDATE; PreparedStatement ps conn.prepareStatement(sql); ps.setInt(1, 100); ResultSet rs ps.executeQuery(); // 这里直接开始修改值 int balance rs.getInt(balance); conn.createStatement().executeUpdate(UPDATE t_account SET balance (balance - 10) WHERE user_id 100);问题在哪默认情况下MySQL的autocommit是开启的每条语句执行完自动提交。上面这个select for update执行完事务立刻提交锁瞬间释放后面再执行update的时候根本没有任何锁保护。两个并发请求同时读到balance100然后都减10最终结果变成90而不是80。正确做法是使用框架的事务注解或者手动setAutoCommit(false)确保select for update和update在同一个事务里最后统一commitconn.setAutoCommit(false); // select ... for update // 业务判断与update conn.commit();提示在排查“锁没生效”问题时第一件事永远是确认这条SQL所在的事务边界。我曾经见过Spring的Service方法里没加Transactional但MyBatis却开启了SqlSession最后提交时机完全不可控锁被提前释放的例子。2.2 第二坑where条件没走索引以为是行锁其实是锁表这是另一个高频翻车点。业务表里有个字段叫order_status开发觉得“我按订单状态查加for update肯定锁住这几行”但实际order_status上根本没有索引。执行计划里typeALL走的是全表扫描InnoDB在扫描过程中给所有聚簇索引记录都加了锁最终效果就是整张表被间接锁住。验证方式很简单-- 走全表扫描的for update BEGIN; SELECT * FROM t_order WHERE order_status 1 FOR UPDATE; -- 在另一个会话里插入一条order_status2的记录会一直阻塞只要这个事务不结束其他事务对这张表的插入操作都会被堵在锁等待里。如果是线上订单表一旦这个事务多跑几秒订单创建接口直接雪崩。正确的做法是为查询条件建立合适的索引然后用explain确认执行计划EXPLAIN SELECT * FROM t_order WHERE order_status 1 FOR UPDATE;执行计划里看到typeALL或typeindex就说明扫描范围过大了必须优化索引。记住一句话for update锁的粒度是由你的执行计划决定的不是由你“以为”的粒度决定的。2.3 第三坑非唯一索引与间隙锁锁住的范围远超预期即使你给字段建了索引也未必万事大吉。假设t_order的order_status字段建了非唯一索引idx_status事务A执行BEGIN; SELECT * FROM t_order WHERE order_status 1 FOR UPDATE;在可重复读隔离级别下InnoDB为了防幻读加的是临键锁Next-Key Lock。它不仅锁住所有status1的记录行还会锁住这些记录之间的间隙以及记录前后的开放区间。此时事务B想插入一条status2的记录只要这条记录落进了被锁的间隙一样会被阻塞。这种现象非常容易误伤正常业务。比如你只想锁住“待支付”的订单结果“已支付”的订单在另一个分支里也插入不了。生产环境遇到这类问题常见的解法有两条一是如果业务可以接受幻读把隔离级别改成读已提交RCRC下for update对命中记录加的是记录锁间隙锁基本不生效并发范围大幅缩小二是精细化设计索引让“锁住少量行”和“防幻读”两者兼顾。关于隔离级别怎么选第三大节我会详聊。2.4 第四坑事务没提交就归还连接池应用线程被拖死这个坑更加隐蔽。Spring的Transactional默认情况下如果方法内部抛出了非受检异常事务会回滚但如果异常被try-catch吞掉了事务不会回滚锁会一直持有到方法正常返回才提交。与此同时连接池里的连接被这个“悬挂事务”占用不放后面的请求拿不到连接最终应用整体卡死。我遇到过一个真实案例一个扣减接口在调用外部服务时出现了网络超时代码catch住异常后直接返回没有标记回滚也没有重新抛出。这个接口的数据库事务就一直挂着持有对用户余额行的排他锁。结果所有人都查不到这个用户的余额更新其他用户也逐步被殃及——因为连接池的连接被耗尽整个应用无响应。排查这类问题最快的方式是先看数据库侧SHOW PROCESSLIST;重点关注State列。如果看到大量连接处于Sleep状态且infomation_schema.innodb_trx里有长时间未提交的事务基本可以断定是应用层没提交或没回滚SELECT * FROM information_schema.innodb_trx\G提示代码里凡是涉及select for update的事务方法强烈建议在finally块中确保事务的提交或回滚或者统一用Spring的声明式事务控制不要在方法内部catch掉异常后当作无事发生。我自己的规范是事务方法内只做数据库读写和必要校验任何外部调用放到事务提交之后去做从根源上缩短持有锁的时间。3. 隔离级别、MVCC与分布式边界for update 的适用半径把前两章的坑避开之后你已经能在单库单表里正确使用for update了。但接下来更考验认知的是它到底适用于多大范围、哪些场景下根本不该用它。3.1 为什么“普通select查到没变”不等于“别人没在改”这是并发领域最容易产生错觉的地方。事务A开启了一个可重复读快照事务B对同一行update并commit之后事务A再执行普通select看到的还是旧值。于是事务A判断“这行数据没被人动过”放心去更新结果一更新就把事务B的改动覆盖了。for update解决的就是这个问题它强制读取当前最新已提交版本并锁定这行记录后面的update必然是“基于最新状态”的。所以任何“先查后改”的业务逻辑只要必须基于最新值做判断查的这一下就必须是for update或for share不能是普通select。最常见的例子就是库存扣减BEGIN; -- 必须当前读否则读到的是快照 SELECT stock FROM t_stock WHERE product_id 1 FOR UPDATE; -- Java业务判断stock是否充足 -- 充足才执行UPDATE UPDATE t_stock SET stock stock - #{buyNum} WHERE product_id 1; COMMIT;千万不要把select换成普通查询觉得“查出来有库存就行”。快照读下两个并发事务会同时看到库存为10然后都去做扣减最终导致超卖。3.2 隔离级别对锁范围的影响RC与RR的细微差别for update在InnoDB里的表现和当前隔离级别强相关。默认的MySQL隔离级别是可重复读RR在RR下InnoDB会启用临键锁防止幻读锁的范围比较大。读已提交RC下InnoDB只对精准命中的记录加记录锁不再加间隙锁并发吞吐通常更高但要接受幻读的存在。隔离级别普通select是否快照for update锁范围能否防幻读读已提交RC每次都是最新快照命中的记录行不能可重复读RR事务内快照一致记录行间隙锁临键锁能业务选型上我的建议是如果你的场景只需要防止并发覆盖、扣减超卖这类“行级覆盖问题”RCfor update是更高效的选择锁范围小、并发高如果你的场景明确需要防幻读比如在事务里多次范围查询并要求结果一致那就必须用RR并把索引和查询条件设计精准尽量缩小临键锁的范围。3.3 本地行锁的边界跨库、跨表、跨服务时for update帮不了你for update是数据库本地的锁它有天然的边界。分库分表之后同一行数据散落在不同库的物理节点上单库的for update无从协调跨服务的调用链里订单服务和库存服务各管各的表你也没法用库存表的for update去锁订单表的操作。这种情况下就需要分布式锁或业务层的乐观锁方案。我把几种方案放到一起对比一下方案粒度典型实现优势短板for update悲观锁行级数据库事务内锁定读强一致、实现简单依赖连接和事务并发上限低乐观锁version行级UPDATE ... SET versionversion1 WHERE version旧值并发高、无锁等待需要处理冲突重试强冲突场景成功率低分布式锁跨进程/跨库Redis SETNX / etcd能协调多服务需要额外组件锁续期和容错复杂如果你确实在单库单表内做连续操作for update是足够可靠的一旦业务链路拉长到多个服务就别勉为其难继续依赖数据库锁直接引入分布式锁或改成异步队列串化更好。我在实践中见过最多的问题不是“选错了工具”而是“一个工具试图解决所有问题”最后把数据库的行锁用成了分布式锁的替代品锁等待、长事务、死锁接踵而至。4. 从理论到现场一个库存扣减场景的完整拆解理论说得再多都不如把一个场景从头到尾拆一遍来得直观。这里我以最经典的库存扣减为例把“正确使用for update”和“看似正确实则错误”的路径都走一遍。4.1 表结构与并发目标先建一张非常简单的库存表CREATE TABLE t_stock ( product_id BIGINT PRIMARY KEY, stock INT NOT NULL, version INT NOT NULL DEFAULT 0, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;insert一条数据作为演示基础INSERT INTO t_stock (product_id, stock, version) VALUES (1, 10, 0);并发目标很简单两个请求同时买3件商品时库存只能扣到4绝不能扣成负值也不能出现两边都基于10去做扣减然后都提交的情况。4.2 两个并发事务的时间线谁成功、谁阻塞、为什么假设事务A和事务B同时执行下面这段逻辑-- 事务A START TRANSACTION; SELECT stock FROM t_stock WHERE product_id 1 FOR UPDATE; -- 读到stock10 UPDATE t_stock SET stock stock - 3 WHERE product_id 1; COMMIT;-- 事务B START TRANSACTION; SELECT stock FROM t_stock WHERE product_id 1 FOR UPDATE; -- 这里会阻塞直到事务A提交 UPDATE t_stock SET stock stock - 3 WHERE product_id 1; COMMIT;实际发生的事件序列是这样的时间点事务A事务B库存值提交后T1加排他锁成功等待锁10T2update成功继续等待10未提交T3commit释放锁等到锁当前读到77T4完成update执行库存从7扣到44关键点在于事务B不是基于自己最初读到的10去扣减而是在抢到锁之后重新读到最新的7再扣3得到4。这就是for update的价值——把“并发冲突”变成“串行执行”保证最终结果的正确性。如果这里用的是普通selectT1时刻事务A和B都会读到10然后都执行扣3的update最终结果要么是7要么是4取决于谁后更新但两条update必然有一条是基于过期数据执行的正好踩了第2章第2.1节那个autocommit坑的变体。4.3 即使加了for update仍可能出现的三个问题第一个问题是加了for update却仍然用了快照读逻辑。很多人事务里确实写了select for update但业务判断却依赖其他普通select查出来的值等于锁了一个条件判断了另一个条件这个锁就白加了。锁保护的数据必须和业务判断的数据严格一致。第二个问题是锁定的行数过多或加锁顺序不一致导致的死锁。两个事务分别按不同顺序锁A行和B行时就会互相等对方释放锁。比如-- 事务1先锁A再锁B SELECT * FROM t_stock WHERE product_id IN (1, 2) FOR UPDATE; -- 事务2先锁B再锁A SELECT * FROM t_stock WHERE product_id IN (2, 1) FOR UPDATE;两个事务各持一把锁等对方释放InnoDB检测到死锁后会回滚其中一方。解决思路是让所有事务按相同的顺序加锁比如统一按product_id升序处理。第三个问题是锁等待超时后应用层没有重试机制。MySQL默认的innodb_lock_wait_timeout是50秒如果某个热点行等待时间超过这个阈值MySQL会报Lock wait timeout exceeded错误。业务接口如果没做重试或快速失败用户看到的就是“系统繁忙”。我的建议是热点数据场景下手动调低事务超时时间比如5秒让失败来得快一点再配合重试队列兜底。5. 锁等待的监控与压测让“锁没锁住”不再靠猜最后这部分写给已经会写for update、但还想进一步把问题量化的人。很多团队等到线上出问题才去翻日志其实数据库把你需要的信息都暴露在系统表里了只看你愿不愿意查。5.1 快速定位锁等待来源的查询发生锁等待时最常用的三张表/视图-- 1. 查看当前所有事务 SELECT trx_id, trx_state, trx_started, trx_query, trx_mysql_thread_id FROM information_schema.innodb_trx; -- 2. 查看锁等待关系5.7及以下 SELECT * FROM information_schema.innodb_lock_waits; -- 3. 查看当前持有的锁8.0用performance_schema SELECT * FROM performance_schema.data_locks WHERE ENGINE InnoDB;这三条能回答三个问题谁在跑事务、等了多久、被哪条SQL堵住。如果是在生产环境紧急处理先看innodb_trx里trx_state是LOCK WAIT的事务然后按trx_mysql_thread_id去SHOW PROCESSLIST里拿对应的SQL基本就能定位到元凶。另外用官方自带的sys库可以更直观SELECT * FROM sys.innodb_lock_waits\G这个视图会把阻塞和被阻塞的事务ID、语句、持续时间直接列出来排查效率高很多。5.2 死锁日志与锁等待超时值的正确解读死锁发生时MySQL会自动检测并回滚代价较小的事务然后在错误日志里记录。查看死锁细节SHOW ENGINE INNODB STATUS\G重点看LATEST DETECTED DEADLOCK部分。里面会有两个事务的加锁顺序以及谁被回滚。日志里通常出现类似这样的关键信息每段事务列表中的“WAITING FOR THIS LOCK TO BE GRANTED”说明它正等着拿锁没拿到“HOLDS THE LOCK(S)”说明这个事务持有哪把锁不放手最后一行如果出现“WE ROLL BACK TRANSACTION (1)”说明事务1被选为牺牲者回滚。死锁不是bug是数据库的自我保护机制。真正的问题是你没有控制好加锁顺序和加锁范围。看完日志之后调整SQL顺序或缩小锁范围比加任何“重试”都治本。5.3 并发压测数据与调优取向不同的并发控制方案在同一张表上的表现差异很大。我拿一台8核16G的MySQL 5.7实例对t_stock表做了简单压测场景是100个并发请求抢购同一个product_id1的库存比较三种方案的吞吐和失败率数据如下不同机器差异会很大仅供参考思路方案并发成功扣减次数/秒异常情况无任何锁100非常高严重超卖数据错误乐观锁版本号重试100高但有大量重试冲突率随并发上升for update100较低锁等待增多TPS明显下降压测结果说明的一点是for update在热点行高并发下吞吐确实有限但它的正确性是底线。如果你需要更高的吞吐优先考虑的不是去掉for update而是把热点拆散比如库存分桶、按用户维度拆分账户行让锁竞争从单行分散到多行。对余额账户这类“不可拆”的场景就老老实实用for update或分布式锁并做好重试和告警。我在实际项目中的体会是for update是那种“简单但需要敬畏”的能力。它的语法只有一句话背后却连着索引设计、隔离级别、事务边界、连接池管理、死锁检测一整套体系。每次写完一个涉及for update的接口我都会要求自己回答三个问题这个锁的范围是什么、持有多久、如果超时了怎么办。三个问题都能脱口而出才敢说真正用好了它。如果你现在刚好遇到select for update锁不生效的诡异问题别急着在业务层找原因先打开innodb_trx看一眼事务到底提交了没有十有八九问题就出在这里。