拓冰网站定制 
day70数据库系列终于写到第六部分。前面五部分从建库建表、增删改查、索引优化一路走过来我原以为SQL写得越熟练越接近“懂数据库”。直到一次线上故障教我做人跑批任务和前台订单同时更新同一行数据后台事务一直处于锁等待前端开始超时紧接着抛出一堆 Deadlock found。那一刻我才意识到数据库并发锁和数据库死锁才是真正拉开差距的地方。第六部分定的主题很明确把事务隔离级别、锁机制、死锁排查链路以及日常业务怎么避开这些坑一次性讲透。无论你是刚把CRUD练熟的新手还是已经在写存储过程的同行这部分都值得从头顺一遍。1. 为什么数据库系列写到第六部分必须专门啃并发这块硬骨头1.1 单连接时代的“正确性幻觉”是怎么来的前五部分我们做的大部分操作是单连接、顺序执行的开个客户端执行一条SELECT再执行一条UPDATE结果理所当然是对的。因为每次操作都在一个独立、干净的上下文里完成没有第二个会话来打扰你。但真实的线上数据库永远同时跑着几十上百个连接有人在下单改库存有人在跑批量报表还有人在删历史数据。这些操作在CPU眼里是交错执行的数据库必须保证交错之后的结果和某种“串行执行”的结果一致才叫正确。这个要求听着简单实现起来极其复杂。数据库靠的是两套东西一套是事务保证一个业务操作要么全部生效、要么全部回滚另一套就是锁和MVCC控制并发事务之间能“看到”什么、不能“看到”什么。第六部分之前我们不谈并发是因为单机单连接永远碰不到这些问题第六部分必须谈因为你一旦开始写真实业务多连接并发是常态而不是例外。1.2 三类并发异常脏读、不可重复读、幻读并发控制要解决的问题归根到底是三类经典异常。我先用一张表把它们放在一起后面我们会逐个在MySQL里复现。异常类型通俗解释典型危害脏读一个事务读到了另一个事务尚未提交的数据对方回滚后你基于脏数据做了判断整个业务就是错的不可重复读同一个事务内同一条SELECT执行两次结果不一样报表统计同一行余额两次金额对不上幻读同一个事务内同一个范围查询两次行数不一样分页、计数、批量处理时突然多出一行或少一行注意三者有递进关系脏读最严重读到了“最终不存在”的数据不可重复读稍微轻一点读到的至少是已经提交的数据但结果不稳定幻读是在“行数”层面出现变化。事务隔离级别就是按“容忍哪种异常”来划分档位的。1.3 为什么不用“完全串行化”一刀切理论上把并发事务完全串行执行就不会有这些异常。但串行意味着吞吐断崖式下跌数据库干脆把决定权交给你用隔离级别做权衡。SQL标准定义了四档READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE一档比一档严格一档比一档慢。你只需要明白一件事隔离级别不是数据库自嗨的参数它是业务正确性与系统吞吐量之间的天平。第六部分的人工就是学会在这个天平上找准位置。2. 用两个会话复现脏读、不可重复读与幻读眼见为实2.1 准备一张最简单的账户表先说环境本地MySQL 5.7或8.0均可开两个命令行或者两个Navicat查询窗口都行。建一张账户表插两条数据。CREATE DATABASE IF NOT EXISTS demo_db; USE demo_db; CREATE TABLE account ( id INT PRIMARY KEY, user_name VARCHAR(50), balance DECIMAL(10,2) ) ENGINEInnoDB; INSERT INTO account (id, user_name, balance) VALUES (1, zhangsan, 100.00), (2, lisi, 200.00);为什么刻意用InnoDB因为只有InnoDB支持行锁和事务MyISAM那套表锁行为完全不同后面涉及锁的原理都是按InnoDB讲。2.2 脏读复现把隔离级别调到最低会话A先把隔离级别改成READ UNCOMMITTED开启事务。会话B正常开启事务更新张三的余额但先不提交。-- 会话A SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; SELECT balance FROM account WHERE id 1; -- 会话B SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; -- 注意此时不回滚也不提交然后回会话A再执行一次查询SELECT balance FROM account WHERE id 1;你会看到A读到balance0.00而B的更新根本没有提交。如果B最后ROLLBACK这条余额本来就是100.00A却拿着0.00去做后续判断后果可想而知。这就是脏读擦干净了别人的半成品就往自己锅里倒。2.3 不可重复读复现READ COMMITTED下的“变脸”把A和B的隔离级别都调到READ COMMITTED。A先开启事务查询张三余额此时100.00。B更新张三的余额到200.00并提交。A在同一事务里再查一次结果变成200.00。-- 会话A SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; SELECT balance FROM account WHERE id 1; -- 结果 100.00 -- 会话B独立事务 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; UPDATE account SET balance 200.00 WHERE id 1; COMMIT; -- 回到会话A SELECT balance FROM account WHERE id 1; -- 结果 200.00同一个事务两次读同一行却拿到不同结果。这是READ COMMITTED的典型特征它只保证能读到已提交的数据不保证同一事务内读到的数据始终一致。如果业务逻辑是先读余额再计算折扣第二次读到的金额变了计算就错位了。2.4 幻读复现与InnoDB的“双重人格”标准SQL里REPEATABLE READ下仍可能出现幻读。但MySQL InnoDB比较特殊它用MVCC和间隙锁把幻读问题大体解决了。我们要分两种情况看。第一种普通快照读。A开启RR事务执行SELECT COUNT(*) FROM account WHERE id 0B插入一条id3并提交A再执行同样的查询结果是2还是看不到新行。所以用普通SELECTInnoDB的RR不会出现幻读。第二种当前读。如果A执行的是SELECT * FROM account WHERE id 0 FOR UPDATEB的INSERT INTO account (id, user_name, balance) VALUES (3, wangwu, 300.00)会被阻塞直到A提交才继续。因为FOR UPDATE会对满足条件的范围加间隙锁把“将来可能插入的位置”也锁死了。那幻读什么时候能直观看到把A换成READ COMMITTED再试当前读A先SELECT * FROM account WHERE id 0 FOR UPDATE结果两行B插入id3并提交A再执行同样的FOR UPDATE查询结果变成三行。这个“多出的一行”就是幻读。理解这个区别很重要遇到写冲突时报错先分清是快照读还是当前读思路才不会乱。2.5 实验结论隔离级别决定你看到的是哪一版数据做完三个实验你应该有这个直觉隔离级别本质上是在决定一个事务内部能看见外部事务的哪些“版本”。READ UNCOMMITTED看见未提交版READ COMMITTED看见每次读取时的最新已提交版REPEATABLE READ看见事务开始时的快照版。版本、快照、可见性这些概念后面对接MVCC时完全复用。3. 锁机制的本质与InnoDB锁类型全局锁等待的真相往往藏在索引里3.1 从粒度看锁表锁、行锁、间隙锁、next-key lockInnoDB锁分好几个层级平时听得最多的是行锁但实际加锁单位往往比一行更大。锁类型锁的是什么典型触发场景表锁整张表DDL、LOCK TABLES无索引更新时InnoDB可能退化为锁全表扫描记录记录锁索引记录本身条件命中唯一索引/主键的UPDATE、DELETE、FOR UPDATE间隙锁索引记录之间的空隙RR下范围条件更新、插入时的区间保护next-key lock记录锁间隙锁的组合RR下范围扫描锁住记录和它前面的间隙不少人对间隙锁很陌生我用生活类比解释你在一排有编号的柜子里存东西为了防止别人在你存取过程中见缝插针塞新柜子你把目标柜子以及它前后的空位全部占住。间隙锁锁的正是那些“还没有柜子编号”的空位。它天然是RR隔离级别防幻读的物理手段。3.2 共享锁与排他锁兼容矩阵读写锁是另外一个维度。共享锁S锁允许其他事务继续加共享锁但拒绝排他锁排他锁X锁一旦加上其他事务无论加什么锁都会被挡住。当前锁其他事务请求共享锁其他事务请求排他锁共享锁兼容不兼容排他锁不兼容不兼容普通SELECT走的是MVCC快照读不加锁会加锁的只有显式锁定读和写操作。最常见的加锁SQL是-- 共享锁 SELECT * FROM account WHERE id 1 LOCK IN SHARE MODE; -- 排他锁 SELECT * FROM account WHERE id 1 FOR UPDATE;UPDATE、DELETE、INSERT本身就会加排他锁。需要注意的是查询条件不走索引时InnoDB需要从头扫描表扫描到的每条记录都可能被加上锁。你以为只锁一行实际锁了半个表这就是线上锁等待飙升的第一大元凶。3.3 一个锁范围放大的真实例子假设我们在account表上执行UPDATE account SET balance balance - 10 WHERE user_name zhangsan;如果user_name没有索引MySQL只能全表扫描找到目标行InnoDB在扫描过程中会给扫描到的记录加锁。即使最后只更新了zhangsan它也会把其他不相关的行一并锁住。高并发下这条SQL会让所有对该表的写操作排队等待。解决办法也直接给user_name加普通索引或者在业务上改成按主键定位。我这里给个实操建议排查锁等待时第一步永远不是去猜代码而是看当前哪些事务在跑、锁定了多少行再从执行计划逆推索引是否合理。你花十分钟加个索引往往比在应用层改半天的超时参数有效得多。3.4 锁等待与超时参数事务A锁住一行不放事务B申请同一行的锁B会进入等待状态。等待超过阈值后MySQL直接报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction这个阈值由innodb_lock_wait_timeout控制默认50秒。很多人一上来就把超时调大这是不正确的方向。超时是最后兜底不是解决手段。真正要查的是谁长时间持锁不释放通常是某个事务忘了提交或者在一个循环里开了事务没关。我处理过的锁等待故障有一大半是这类低级问题。4. 数据库死锁定位全过程从报错到找到“锁环”的完整排查链路4.1 用一个能稳定复现的死锁场景两个事务按不同顺序更新同一组数据是制造死锁最经典的方式。事务1先更新id1再更新id2事务2先更新id2再更新id1。-- 事务1 START TRANSACTION; UPDATE account SET balance balance - 10 WHERE id 1; -- 模拟业务停顿先不提交 UPDATE account SET balance balance 10 WHERE id 2; COMMIT; -- 事务2 START TRANSACTION; UPDATE account SET balance balance 10 WHERE id 2; -- 此时事务2已持有id2的锁 UPDATE account SET balance balance - 10 WHERE id 1; COMMIT;如果两个事务几乎同时执行第一条UPDATE就会形成环路事务1持有id1的锁、等待id2的锁事务2持有id2的锁、等待id1的锁。MySQL的死锁检测机制会自动选一个“代价小”的事务回滚另一个继续执行。于是你会看到业务日志里出现Deadlock found when trying to get lock但实际上有一个事务已经被回滚了需要应用层感知并重试。4.2 第一步先从information_schema找“人赃并获”死锁报错出现后第一时间去查当前有没有还在跑的事务和锁等待关系。MySQL 5.7看information_schemaMySQL 8.0以后锁信息挪到了performance_schema但事务表还在原处。-- 查看当前事务 SELECT trx_id, trx_state, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx \G -- 查看锁等待关系MySQL 8.0 SELECT * FROM performance_schema.data_lock_waits \G -- 查看锁持有情况MySQL 8.0 SELECT * FROM performance_schema.data_locks \G如果用的MySQL 5.7可以看information_schema.innodb_lock_waits和information_schema.innodb_locks。这三张表能直接告诉你谁在等、等什么锁、谁拿着锁不放。注意trx_mysql_thread_id对应的是MySQL连接的线程ID拿到它就能定位到具体会话。4.3 第二步用SHOW ENGINE INNODB STATUS读死锁现场要说排查死锁最有价值的工具还得是SHOW ENGINE INNODB STATUS\G输出很长重点看最后的LATEST DETECTED DEADLOCK段。这一段会列出两个事务各自执行的SQL、持有的锁、等待的锁。我通常直接按如下顺序读找到*** (1) TRANSACTION:和*** (2) TRANSACTION:两个事务看各自的WAITING FOR THIS LOCK TO BE GRANTED这就是它没拿到的锁看各自的HOLDS THE LOCK(S)这就是它已经拿到的锁把两边拼起来画成一个环确认是否真的互等。举个实际输出片段的解读事务1显示持有id1上的排他记录锁等待id2事务2显示持有id2上的排他记录锁等待id1。看到这样的结构基本不用再怀疑其他因素就是更新顺序不一致导致的锁环。4.4 第三步执行KILL止损并修复SQL顺序读清楚现场之后若线上还有一堆事务卡成串可以先把阻塞源干掉。先从innodb_trx里找到持锁会话对应的线程IDtrx_mysql_thread_id然后执行KILL 123;这个操作会直接断开对应连接事务自动回滚锁被释放。但KILL只是止血不解决根因。真正的修复是让所有事务按照同一顺序访问资源比如统一先更新id小的记录再更新id大的记录。在我们的例子里让事务1和事务2都先更新id1就不会形成循环等待。4.5 应用层必须做死锁重试即使SQL顺序一致了数据库还是会因为各种不可控的交错出现少量死锁。死锁不是“能不能避免”的问题而是“能不能在业务层面优雅地扛过去”。所以在应用层必须写重试逻辑。Java伪代码如下int retry 3; while (retry 0) { try { orderService.updateBalance(); break; } catch (DeadlockException e) { retry--; if (retry 0) { throw new BusinessException(系统繁忙请稍后重试); } Thread.sleep(ThreadLocalRandom.current().nextLong(20, 80)); } }重试前随机sleep一小段是为了避免多客户端同时再次重试导致新的死锁风暴。不随机、立刻重试等于让刚才打仗的两批人换个姿势再打一次。5. 实际项目里的并发优化与常见禁忌从隔离级别选型到乐观锁5.1 隔离级别选型MySQL默认RRPostgreSQL默认RC别照抄很多从MySQL转到PostgreSQL的朋友会发现一个差异PG默认隔离级别是READ COMMITTEDMySQL InnoDB默认是REPEATABLE READ。这背后有历史包袱MySQL要兼容老版本基于binlog复制的语义所以RR沿用至今。但对我们业务开发来说RC通常够用而且锁竞争更小尤其适合读多写少的场景。选型建议是如果没有强一致需求比如账户余额、库存扣减这种不允许超卖的场景可以保持RR如果主要是统计数据、报表查询RC往往能减少间隙锁带来的无谓阻塞。要注意改隔离级别是全局会话或者事务级操作不能只改一条SQL后默认全局不变SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;开启事务前执行。线上切换建议在低峰期观察锁等待指标确认没有明显波动再铺开。5.2 大事务是并发锁的隐形杀手同样一把锁持有一秒钟和持有三十秒效果完全不同。大事务最容易拖长锁持有时间。我见过一个典型的错误写法在Java里开启事务然后循环调用远程接口每调一次接口都在事务里一个事务跑了二十秒期间一直握着几十行的行锁。其他请求全部锁等待最后整个服务雪崩。判断大事务有个笨办法看innodb_trx里的trx_started时间和trx_query如果事务开始时间早于当前时间很久trx_stateRUNNING且查询只有一条UPDATE多半是把外部调用塞进事务了。正确做法是把远程调用移到事务外面事务里只保留数据库读写。5.3 索引设计直接决定锁的粒度这一点前面提过但值得单独再强调InnoDB行锁是加在索引记录上的不是加在“物理行”上的。如果UPDATE条件没有可用索引锁退化成扫描锁等于全表写操作串行化。索引设计不光是查询提速的问题更是并发写性能的地基。具体到实操排查时用EXPLAIN看SQL的执行计划EXPLAIN SELECT id, balance FROM account WHERE user_name zhangsan;type字段如果出现ALL说明没有走索引这种SQL在并发写场景就是定时炸弹。加了索引之后type变为ref或const锁范围才会收敛到目标行。5.4 乐观锁替代悲观锁版本号方案有些业务场景其实不需要数据库锁来兜底可以使用乐观锁在更新时校验版本号。比如用户提交订单时前端带来一个version字段更新语句写成UPDATE account SET balance balance - 10, version version 1 WHERE id 1 AND version 5;如果影响行数是0说明version已被其他事务改掉本次更新失败业务提示“数据已变化请刷新重试”。这样既避免了持锁等待又保证了不会基于旧数据覆盖新值。代价是业务层要容忍偶尔“失败”但对很多非核心场景完全够用。5.5 一套日常可用的排查命令速查把这一部分学到的命令整理成一张速查表方便在故障时直接抄。排查目标命令/视图使用建议当前所有事务information_schema.innodb_trx看trx_started、trx_query定位长事务锁等待关系performance_schema.data_lock_waits看谁阻塞谁当前锁持有performance_schema.data_locks看锁类型、锁模式、库表名死锁历史SHOW ENGINE INNODB STATUS\G看LATEST DETECTED DEADLOCK段正在执行的SQLSHOW PROCESSLIST看State和Info区分锁等待与慢查询当前InnoDB状态SHOW ENGINE INNODB STATUS\G看事务、锁、缓冲池整体情况用这些命令排查时我建议把输出保存到文件里再去翻尤其SHOW ENGINE INNODB STATUS一次输出可能上千行直接刷屏容易看漏关键段。第六部分到这里按我的习惯还是要落到“动手”。别只满足于读懂强烈建议你开两个窗口把上面的脏读、不可重复读、死锁脚本原样跑一遍再看一次SHOW ENGINE INNODB STATUS。我每次给团队做培训都让他们实际踩一遍死锁先写一份执行顺序正好相反的UPDATE脚本故意触发死锁然后自己从data_lock_waits里找到阻塞关系。这个过程走一遍比背十遍锁理论都管用。后面遇到线上锁冲突你至少不会慌知道去查什么表、看什么字段、下一步该KILL还是该改索引。 数据库并发控制与死锁排查：事务隔离级别、锁机制及实战避坑指南
day70数据库系列终于写到第六部分。前面五部分从建库建表、增删改查、索引优化一路走过来我原以为SQL写得越熟练越接近“懂数据库”。直到一次线上故障教我做人跑批任务和前台订单同时更新同一行数据后台事务一直处于锁等待前端开始超时紧接着抛出一堆 Deadlock found。那一刻我才意识到数据库并发锁和数据库死锁才是真正拉开差距的地方。第六部分定的主题很明确把事务隔离级别、锁机制、死锁排查链路以及日常业务怎么避开这些坑一次性讲透。无论你是刚把CRUD练熟的新手还是已经在写存储过程的同行这部分都值得从头顺一遍。1. 为什么数据库系列写到第六部分必须专门啃并发这块硬骨头1.1 单连接时代的“正确性幻觉”是怎么来的前五部分我们做的大部分操作是单连接、顺序执行的开个客户端执行一条SELECT再执行一条UPDATE结果理所当然是对的。因为每次操作都在一个独立、干净的上下文里完成没有第二个会话来打扰你。但真实的线上数据库永远同时跑着几十上百个连接有人在下单改库存有人在跑批量报表还有人在删历史数据。这些操作在CPU眼里是交错执行的数据库必须保证交错之后的结果和某种“串行执行”的结果一致才叫正确。这个要求听着简单实现起来极其复杂。数据库靠的是两套东西一套是事务保证一个业务操作要么全部生效、要么全部回滚另一套就是锁和MVCC控制并发事务之间能“看到”什么、不能“看到”什么。第六部分之前我们不谈并发是因为单机单连接永远碰不到这些问题第六部分必须谈因为你一旦开始写真实业务多连接并发是常态而不是例外。1.2 三类并发异常脏读、不可重复读、幻读并发控制要解决的问题归根到底是三类经典异常。我先用一张表把它们放在一起后面我们会逐个在MySQL里复现。异常类型通俗解释典型危害脏读一个事务读到了另一个事务尚未提交的数据对方回滚后你基于脏数据做了判断整个业务就是错的不可重复读同一个事务内同一条SELECT执行两次结果不一样报表统计同一行余额两次金额对不上幻读同一个事务内同一个范围查询两次行数不一样分页、计数、批量处理时突然多出一行或少一行注意三者有递进关系脏读最严重读到了“最终不存在”的数据不可重复读稍微轻一点读到的至少是已经提交的数据但结果不稳定幻读是在“行数”层面出现变化。事务隔离级别就是按“容忍哪种异常”来划分档位的。1.3 为什么不用“完全串行化”一刀切理论上把并发事务完全串行执行就不会有这些异常。但串行意味着吞吐断崖式下跌数据库干脆把决定权交给你用隔离级别做权衡。SQL标准定义了四档READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE一档比一档严格一档比一档慢。你只需要明白一件事隔离级别不是数据库自嗨的参数它是业务正确性与系统吞吐量之间的天平。第六部分的人工就是学会在这个天平上找准位置。2. 用两个会话复现脏读、不可重复读与幻读眼见为实2.1 准备一张最简单的账户表先说环境本地MySQL 5.7或8.0均可开两个命令行或者两个Navicat查询窗口都行。建一张账户表插两条数据。CREATE DATABASE IF NOT EXISTS demo_db; USE demo_db; CREATE TABLE account ( id INT PRIMARY KEY, user_name VARCHAR(50), balance DECIMAL(10,2) ) ENGINEInnoDB; INSERT INTO account (id, user_name, balance) VALUES (1, zhangsan, 100.00), (2, lisi, 200.00);为什么刻意用InnoDB因为只有InnoDB支持行锁和事务MyISAM那套表锁行为完全不同后面涉及锁的原理都是按InnoDB讲。2.2 脏读复现把隔离级别调到最低会话A先把隔离级别改成READ UNCOMMITTED开启事务。会话B正常开启事务更新张三的余额但先不提交。-- 会话A SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; SELECT balance FROM account WHERE id 1; -- 会话B SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; -- 注意此时不回滚也不提交然后回会话A再执行一次查询SELECT balance FROM account WHERE id 1;你会看到A读到balance0.00而B的更新根本没有提交。如果B最后ROLLBACK这条余额本来就是100.00A却拿着0.00去做后续判断后果可想而知。这就是脏读擦干净了别人的半成品就往自己锅里倒。2.3 不可重复读复现READ COMMITTED下的“变脸”把A和B的隔离级别都调到READ COMMITTED。A先开启事务查询张三余额此时100.00。B更新张三的余额到200.00并提交。A在同一事务里再查一次结果变成200.00。-- 会话A SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; SELECT balance FROM account WHERE id 1; -- 结果 100.00 -- 会话B独立事务 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; UPDATE account SET balance 200.00 WHERE id 1; COMMIT; -- 回到会话A SELECT balance FROM account WHERE id 1; -- 结果 200.00同一个事务两次读同一行却拿到不同结果。这是READ COMMITTED的典型特征它只保证能读到已提交的数据不保证同一事务内读到的数据始终一致。如果业务逻辑是先读余额再计算折扣第二次读到的金额变了计算就错位了。2.4 幻读复现与InnoDB的“双重人格”标准SQL里REPEATABLE READ下仍可能出现幻读。但MySQL InnoDB比较特殊它用MVCC和间隙锁把幻读问题大体解决了。我们要分两种情况看。第一种普通快照读。A开启RR事务执行SELECT COUNT(*) FROM account WHERE id 0B插入一条id3并提交A再执行同样的查询结果是2还是看不到新行。所以用普通SELECTInnoDB的RR不会出现幻读。第二种当前读。如果A执行的是SELECT * FROM account WHERE id 0 FOR UPDATEB的INSERT INTO account (id, user_name, balance) VALUES (3, wangwu, 300.00)会被阻塞直到A提交才继续。因为FOR UPDATE会对满足条件的范围加间隙锁把“将来可能插入的位置”也锁死了。那幻读什么时候能直观看到把A换成READ COMMITTED再试当前读A先SELECT * FROM account WHERE id 0 FOR UPDATE结果两行B插入id3并提交A再执行同样的FOR UPDATE查询结果变成三行。这个“多出的一行”就是幻读。理解这个区别很重要遇到写冲突时报错先分清是快照读还是当前读思路才不会乱。2.5 实验结论隔离级别决定你看到的是哪一版数据做完三个实验你应该有这个直觉隔离级别本质上是在决定一个事务内部能看见外部事务的哪些“版本”。READ UNCOMMITTED看见未提交版READ COMMITTED看见每次读取时的最新已提交版REPEATABLE READ看见事务开始时的快照版。版本、快照、可见性这些概念后面对接MVCC时完全复用。3. 锁机制的本质与InnoDB锁类型全局锁等待的真相往往藏在索引里3.1 从粒度看锁表锁、行锁、间隙锁、next-key lockInnoDB锁分好几个层级平时听得最多的是行锁但实际加锁单位往往比一行更大。锁类型锁的是什么典型触发场景表锁整张表DDL、LOCK TABLES无索引更新时InnoDB可能退化为锁全表扫描记录记录锁索引记录本身条件命中唯一索引/主键的UPDATE、DELETE、FOR UPDATE间隙锁索引记录之间的空隙RR下范围条件更新、插入时的区间保护next-key lock记录锁间隙锁的组合RR下范围扫描锁住记录和它前面的间隙不少人对间隙锁很陌生我用生活类比解释你在一排有编号的柜子里存东西为了防止别人在你存取过程中见缝插针塞新柜子你把目标柜子以及它前后的空位全部占住。间隙锁锁的正是那些“还没有柜子编号”的空位。它天然是RR隔离级别防幻读的物理手段。3.2 共享锁与排他锁兼容矩阵读写锁是另外一个维度。共享锁S锁允许其他事务继续加共享锁但拒绝排他锁排他锁X锁一旦加上其他事务无论加什么锁都会被挡住。当前锁其他事务请求共享锁其他事务请求排他锁共享锁兼容不兼容排他锁不兼容不兼容普通SELECT走的是MVCC快照读不加锁会加锁的只有显式锁定读和写操作。最常见的加锁SQL是-- 共享锁 SELECT * FROM account WHERE id 1 LOCK IN SHARE MODE; -- 排他锁 SELECT * FROM account WHERE id 1 FOR UPDATE;UPDATE、DELETE、INSERT本身就会加排他锁。需要注意的是查询条件不走索引时InnoDB需要从头扫描表扫描到的每条记录都可能被加上锁。你以为只锁一行实际锁了半个表这就是线上锁等待飙升的第一大元凶。3.3 一个锁范围放大的真实例子假设我们在account表上执行UPDATE account SET balance balance - 10 WHERE user_name zhangsan;如果user_name没有索引MySQL只能全表扫描找到目标行InnoDB在扫描过程中会给扫描到的记录加锁。即使最后只更新了zhangsan它也会把其他不相关的行一并锁住。高并发下这条SQL会让所有对该表的写操作排队等待。解决办法也直接给user_name加普通索引或者在业务上改成按主键定位。我这里给个实操建议排查锁等待时第一步永远不是去猜代码而是看当前哪些事务在跑、锁定了多少行再从执行计划逆推索引是否合理。你花十分钟加个索引往往比在应用层改半天的超时参数有效得多。3.4 锁等待与超时参数事务A锁住一行不放事务B申请同一行的锁B会进入等待状态。等待超过阈值后MySQL直接报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction这个阈值由innodb_lock_wait_timeout控制默认50秒。很多人一上来就把超时调大这是不正确的方向。超时是最后兜底不是解决手段。真正要查的是谁长时间持锁不释放通常是某个事务忘了提交或者在一个循环里开了事务没关。我处理过的锁等待故障有一大半是这类低级问题。4. 数据库死锁定位全过程从报错到找到“锁环”的完整排查链路4.1 用一个能稳定复现的死锁场景两个事务按不同顺序更新同一组数据是制造死锁最经典的方式。事务1先更新id1再更新id2事务2先更新id2再更新id1。-- 事务1 START TRANSACTION; UPDATE account SET balance balance - 10 WHERE id 1; -- 模拟业务停顿先不提交 UPDATE account SET balance balance 10 WHERE id 2; COMMIT; -- 事务2 START TRANSACTION; UPDATE account SET balance balance 10 WHERE id 2; -- 此时事务2已持有id2的锁 UPDATE account SET balance balance - 10 WHERE id 1; COMMIT;如果两个事务几乎同时执行第一条UPDATE就会形成环路事务1持有id1的锁、等待id2的锁事务2持有id2的锁、等待id1的锁。MySQL的死锁检测机制会自动选一个“代价小”的事务回滚另一个继续执行。于是你会看到业务日志里出现Deadlock found when trying to get lock但实际上有一个事务已经被回滚了需要应用层感知并重试。4.2 第一步先从information_schema找“人赃并获”死锁报错出现后第一时间去查当前有没有还在跑的事务和锁等待关系。MySQL 5.7看information_schemaMySQL 8.0以后锁信息挪到了performance_schema但事务表还在原处。-- 查看当前事务 SELECT trx_id, trx_state, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx \G -- 查看锁等待关系MySQL 8.0 SELECT * FROM performance_schema.data_lock_waits \G -- 查看锁持有情况MySQL 8.0 SELECT * FROM performance_schema.data_locks \G如果用的MySQL 5.7可以看information_schema.innodb_lock_waits和information_schema.innodb_locks。这三张表能直接告诉你谁在等、等什么锁、谁拿着锁不放。注意trx_mysql_thread_id对应的是MySQL连接的线程ID拿到它就能定位到具体会话。4.3 第二步用SHOW ENGINE INNODB STATUS读死锁现场要说排查死锁最有价值的工具还得是SHOW ENGINE INNODB STATUS\G输出很长重点看最后的LATEST DETECTED DEADLOCK段。这一段会列出两个事务各自执行的SQL、持有的锁、等待的锁。我通常直接按如下顺序读找到*** (1) TRANSACTION:和*** (2) TRANSACTION:两个事务看各自的WAITING FOR THIS LOCK TO BE GRANTED这就是它没拿到的锁看各自的HOLDS THE LOCK(S)这就是它已经拿到的锁把两边拼起来画成一个环确认是否真的互等。举个实际输出片段的解读事务1显示持有id1上的排他记录锁等待id2事务2显示持有id2上的排他记录锁等待id1。看到这样的结构基本不用再怀疑其他因素就是更新顺序不一致导致的锁环。4.4 第三步执行KILL止损并修复SQL顺序读清楚现场之后若线上还有一堆事务卡成串可以先把阻塞源干掉。先从innodb_trx里找到持锁会话对应的线程IDtrx_mysql_thread_id然后执行KILL 123;这个操作会直接断开对应连接事务自动回滚锁被释放。但KILL只是止血不解决根因。真正的修复是让所有事务按照同一顺序访问资源比如统一先更新id小的记录再更新id大的记录。在我们的例子里让事务1和事务2都先更新id1就不会形成循环等待。4.5 应用层必须做死锁重试即使SQL顺序一致了数据库还是会因为各种不可控的交错出现少量死锁。死锁不是“能不能避免”的问题而是“能不能在业务层面优雅地扛过去”。所以在应用层必须写重试逻辑。Java伪代码如下int retry 3; while (retry 0) { try { orderService.updateBalance(); break; } catch (DeadlockException e) { retry--; if (retry 0) { throw new BusinessException(系统繁忙请稍后重试); } Thread.sleep(ThreadLocalRandom.current().nextLong(20, 80)); } }重试前随机sleep一小段是为了避免多客户端同时再次重试导致新的死锁风暴。不随机、立刻重试等于让刚才打仗的两批人换个姿势再打一次。5. 实际项目里的并发优化与常见禁忌从隔离级别选型到乐观锁5.1 隔离级别选型MySQL默认RRPostgreSQL默认RC别照抄很多从MySQL转到PostgreSQL的朋友会发现一个差异PG默认隔离级别是READ COMMITTEDMySQL InnoDB默认是REPEATABLE READ。这背后有历史包袱MySQL要兼容老版本基于binlog复制的语义所以RR沿用至今。但对我们业务开发来说RC通常够用而且锁竞争更小尤其适合读多写少的场景。选型建议是如果没有强一致需求比如账户余额、库存扣减这种不允许超卖的场景可以保持RR如果主要是统计数据、报表查询RC往往能减少间隙锁带来的无谓阻塞。要注意改隔离级别是全局会话或者事务级操作不能只改一条SQL后默认全局不变SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;开启事务前执行。线上切换建议在低峰期观察锁等待指标确认没有明显波动再铺开。5.2 大事务是并发锁的隐形杀手同样一把锁持有一秒钟和持有三十秒效果完全不同。大事务最容易拖长锁持有时间。我见过一个典型的错误写法在Java里开启事务然后循环调用远程接口每调一次接口都在事务里一个事务跑了二十秒期间一直握着几十行的行锁。其他请求全部锁等待最后整个服务雪崩。判断大事务有个笨办法看innodb_trx里的trx_started时间和trx_query如果事务开始时间早于当前时间很久trx_stateRUNNING且查询只有一条UPDATE多半是把外部调用塞进事务了。正确做法是把远程调用移到事务外面事务里只保留数据库读写。5.3 索引设计直接决定锁的粒度这一点前面提过但值得单独再强调InnoDB行锁是加在索引记录上的不是加在“物理行”上的。如果UPDATE条件没有可用索引锁退化成扫描锁等于全表写操作串行化。索引设计不光是查询提速的问题更是并发写性能的地基。具体到实操排查时用EXPLAIN看SQL的执行计划EXPLAIN SELECT id, balance FROM account WHERE user_name zhangsan;type字段如果出现ALL说明没有走索引这种SQL在并发写场景就是定时炸弹。加了索引之后type变为ref或const锁范围才会收敛到目标行。5.4 乐观锁替代悲观锁版本号方案有些业务场景其实不需要数据库锁来兜底可以使用乐观锁在更新时校验版本号。比如用户提交订单时前端带来一个version字段更新语句写成UPDATE account SET balance balance - 10, version version 1 WHERE id 1 AND version 5;如果影响行数是0说明version已被其他事务改掉本次更新失败业务提示“数据已变化请刷新重试”。这样既避免了持锁等待又保证了不会基于旧数据覆盖新值。代价是业务层要容忍偶尔“失败”但对很多非核心场景完全够用。5.5 一套日常可用的排查命令速查把这一部分学到的命令整理成一张速查表方便在故障时直接抄。排查目标命令/视图使用建议当前所有事务information_schema.innodb_trx看trx_started、trx_query定位长事务锁等待关系performance_schema.data_lock_waits看谁阻塞谁当前锁持有performance_schema.data_locks看锁类型、锁模式、库表名死锁历史SHOW ENGINE INNODB STATUS\G看LATEST DETECTED DEADLOCK段正在执行的SQLSHOW PROCESSLIST看State和Info区分锁等待与慢查询当前InnoDB状态SHOW ENGINE INNODB STATUS\G看事务、锁、缓冲池整体情况用这些命令排查时我建议把输出保存到文件里再去翻尤其SHOW ENGINE INNODB STATUS一次输出可能上千行直接刷屏容易看漏关键段。第六部分到这里按我的习惯还是要落到“动手”。别只满足于读懂强烈建议你开两个窗口把上面的脏读、不可重复读、死锁脚本原样跑一遍再看一次SHOW ENGINE INNODB STATUS。我每次给团队做培训都让他们实际踩一遍死锁先写一份执行顺序正好相反的UPDATE脚本故意触发死锁然后自己从data_lock_waits里找到阻塞关系。这个过程走一遍比背十遍锁理论都管用。后面遇到线上锁冲突你至少不会慌知道去查什么表、看什么字段、下一步该KILL还是该改索引。