SQL事务完全指南：ACID、隔离级别与锁机制实战

SQL事务详细使用详解老规矩先抛个场景。做电商的兄弟应该都处理过类似需求用户下单支付成功后要扣库存、要写订单表、要记录流水还要给用户加积分。这四件事但凡有一步失败用户那边显示支付成功仓库那边库存却扣多了或者没扣就会出现超卖或者对不上的烂账。我第一次接手这类需求时项目经理只丢给我一句用事务包起来就行当时我以为就是把几条SQL放在一起执行那么简单直到上线后线上出了数据不一致的故障我才老老实实把事务机制从头到尾啃了一遍。这篇博文就围绕SQL事务展开把事务的ACID特性、隔离级别、锁机制、回滚细节、应用层整合以及分布式场景下的方案选型一次性说透。内容偏实战既有原理拆解也有可抄作业的代码示例适合刚接触数据库事务的开发者也适合写过事务但没系统梳理过细节的工程师。1. 事务到底是什么以及它解决的核心困境1.1 从一次转账看事务存在的意义假设你要给朋友转100块钱。在数据库层面这其实是两步操作先从你的账户扣100再往朋友账户加100。如果没有事务保护扣款成功但加款失败这100块钱就凭空消失了。更麻烦的是如果两个人同时给你转账数据库的读写顺序稍微乱一点余额就可能算错。事务存在的意义就是把这多个操作捆绑成一个不可分割的单元。这个单元要么全部执行成功要么全部回滚到执行前的状态不存在执行了一半的中间状态。这种能力不是SQL语法天然自带的而是数据库存储引擎通过日志、锁、快照等一整套机制实现的。业内把这套能力总结成ACID四个特性我用自己的话翻译一遍原子性Atomicity一组操作要么全成、要么全败不存在中间状态。数据库靠undo log实现如果中途出错就按日志把已执行的操作一个个撤销回去。一致性Consistency事务执行前后数据要满足业务规则和约束。比如账户余额不能为负订单状态不能从已支付跳回待付款。原子性、隔离性都是为一致性服务的。隔离性Isolation多个事务同时操作同一批数据时互不干扰。这个特性实现起来最复杂也是事务性能损耗的主要来源下文会详细展开。持久性Durability事务一旦提交数据就永久落盘即使系统崩溃也不会丢失。主要靠redo log实现数据库重启后会根据日志重放已提交的事务。1.2 为什么说事务用不好比不用更危险网上很多教程把事务当万能药仿佛一开事务就万事大吉。实际上事务是有代价的而且用错了会造成更严重的问题。我见过最典型的一个事故某后端服务在一个大循环里逐条insert订单明细每条insert都在独立事务里提交结果一旦循环到第500条时报错前面499条已经提交入库数据就缺了一块。更隐蔽的是有些开发者把事务范围拉得特别长——比如在事务里做远程调用、查外部接口、处理大文件解析这些操作动辄几百毫秒甚至几秒结果事务从开始到提交一直持有数据库连接和锁直接拖垮同库其他业务的响应时间。事务的正确姿势是短、平、快范围尽可能小涉及的数据尽可能少持续时间尽可能短。我后面会专门写一节讲大事务的危害先在这里留个印象。2. 四大隔离级别与并发异常的取舍2.1 并发事务下出现的三类数据异常当两个事务同时读写同一行数据时数据库如果不做任何隔离就会出现以下问题脏读Dirty Read事务A修改了一条数据但还没提交事务B就读到了这个未提交的值。之后事务A回滚事务B刚才读到的就是一个凭空消失的脏数据。实际危险场景事务B基于这个脏值做了金额计算最后账目全错。不可重复读Non-Repeatable Read事务A在同一个事务里两次查询同一行数据第一次读到金额100这时事务B把金额改成了200并提交事务A第二次查询读到200。两次读到的值不一样所以叫不可重复读。问题本质是事务A读到的数据被别的事务篡改了。幻读Phantom Read事务A按条件查询一批数据比如查某天订单总数查出来10条。这时事务B插入了一条新订单并提交事务A再用同样的条件查询发现多了1条。行数变了好像产生了幻觉一样。注意一个关键区别不可重复读针对的是同一行数据内容变了幻读针对的是数据集合的行数变了。前者用行锁就能挡住后者必须上间隙锁或者表锁才能解决。2.2 隔离级别一览表与选择依据SQL标准定义了四个隔离级别由低到高如下隔离级别脏读不可重复读幻读默认数据库READ UNCOMMITTED读未提交可能可能可能极少使用READ COMMITTED读已提交避免可能可能Oracle、SQL Server、PostgreSQLREPEATABLE READ可重复读避免避免可能InnoDB已解决MySQLSERIALIZABLE串行化避免避免避免极少使用不同数据库的默认级别不同这就导致同一个事务行为在不同的库上表现完全不一样。我最早用MySQL写项目后来切到Oracle同样的代码在MySQL里数据一致到了Oracle就出现不可重复读排查了很久才发现是隔离级别差异造成的。MySQL的InnoDB引擎在REPEATABLE READ级别下通过MVCC多版本并发控制和间隙锁已经能阻止幻读所以它默认的REPEATABLE READ实际效果接近SERIALIZABLE但并发性能却高得多。而Oracle默认的READ COMMITTED下每次SELECT都会生成新的快照所以同一个事务里重复查询可能读到其他事务已提交的新值。2.3 隔离级别怎么选才合理我个人的选型建议很简单能用默认级别就用默认级别除非明确知道业务场景需要更高隔离性否则不要轻易改动。举例说明在MySQL里如果你遇到需要查询结果在事务内完全稳定的场景比如生成报表时先查总数、再查明细两次查询结果必须完全一致那么REPEATABLE READ天然满足需求。而在Oracle的READ COMMITTED下同样的需求就得手动加锁或者用可串行化快照复杂度直接上升。SERIALIZABLE级别我基本只在做数据迁移、批量对账这种低并发场景下使用。它会把所有涉及的记录全部加锁读写互相阻塞并发能力极低生产环境正常业务流量下用了它基本等于自杀。3. 锁机制事务隔离性的底层实现3.1 共享锁与排他锁事务要隔离靠的是锁。InnoDB的锁分为两类共享锁S锁Shared Lock多个事务可以同时持有共享锁都只允许读不允许写。加锁语法是SELECT ... LOCK IN SHARE MODE。排他锁X锁Exclusive Lock一个事务持有排他锁时其他事务既不能写也不能读除非走快照读加锁语法是SELECT ... FOR UPDATE。这两种锁的兼容关系是共享锁与共享锁兼容排他锁与任何锁都不兼容。换句话说只要有人加了排他锁其他人就别想碰这块数据了。我举个实际例子。在库存扣减场景中我见过很多新手直接写SELECT stock FROM product WHERE id 1; -- 然后在应用层判断stock是否大于0 -- 再执行 UPDATE product SET stock stock - 1 WHERE id 1;这段代码在并发下一定会出问题两个请求同时查到stock为1都判定库存充足然后都去执行扣减最终库存变成-1。正确做法是先加排他锁再判断SELECT stock FROM product WHERE id 1 FOR UPDATE; -- 此时其他事务的读写都会被阻塞 -- 应用层拿到锁后再判断库存是否足够 -- 足够的话执行UPDATEFOR UPDATE必须放在事务里才有意义因为锁的释放是跟事务提交或回滚绑定的。如果你不加事务直接执行SELECT ... FOR UPDATE很多数据库驱动默认自动提交锁瞬间就释放了等于白锁。3.2 间隙锁与临键锁行锁只能锁住已经存在的记录防不住别人插入一条新记录这种幻读问题。InnoDB在REPEATABLE READ级别下引入了间隙锁Gap Lock锁的是记录之间的空隙让别的事务无法在这个空隙里插入数据。更常用的是临键锁Next-Key Lock它是行锁和间隙锁的组合锁定的范围是左开右闭区间。举个例子表里有id为1、5、10三行数据执行WHERE id BETWEEN 5 AND 10 FOR UPDATE时不但锁住5和10两行还会锁住(5,10)之间的区间以及10后面的间隙其他事务想插入id为6、7、8、9的记录都会被阻塞。这里有个经典踩坑点如果WHERE条件里的字段没有索引InnoDB会退化为全表加锁也就是把所有间隙都锁住整个表的插入操作全部阻塞。生产环境遇到莫名其妙的插入超时十有八九是这种场景。排查思路就是EXPLAIN看执行计划确认锁定的字段有没有走索引。3.3 死锁的产生与处理死锁是事务并发下的交通事故事务A持有记录1的锁想获取记录2的锁事务B持有记录2的锁想获取记录1的锁。两个事务互相等对方释放锁谁也无法推进就死锁了。InnoDB有死锁检测机制检测到死锁后会牺牲其中一个事务让它回滚并报错另一个事务继续执行。所以你在生产环境偶尔看到Deadlock found when trying to get lock错误并不一定意味着系统设计有严重问题可能就是并发高峰期偶发碰撞。减少死锁的手段我总结了几条多个事务访问多张表时约定相同的访问顺序。比如先更新订单表再更新流水表所有事务都按这个顺序来。一次性锁定所需资源。在同一个事务里尽量用一条SQL锁住所有要操作的数据减少逐步加锁的时间窗口。缩短事务执行时间。事务越快结束锁持有时间越短死锁概率自然下降。合理设计索引。锁范围越小碰撞面越小。4. 事务的提交、回滚与常见误区4.1 隐式提交的陷阱MySQL中START TRANSACTION开启事务后执行COMMIT提交执行ROLLBACK回滚。但有一个隐藏的坑DDL语句CREATE、ALTER、DROP等会触发隐式提交。什么意思呢如果你在一个事务里执行了INSERT然后又执行ALTER TABLE修改表结构MySQL会先把之前的INSERT自动提交掉然后DDL本身也直接提交。这样你的事务就被拆断了前面做的所有操作瞬间固化到数据库里后续再回滚也回不去了。我有个惨痛教训某次数据订正脚本我先START TRANSACTION然后UPDATE一批数据接着为了临时加个索引执行了ALTER TABLE结果索引加失败了我想ROLLBACK把UPDATE撤销但UPDATE已经被隐式提交根本回滚不了只能手动写反向SQL修复。从那以后我立了个规矩事务块内绝不执行DDL所有表结构变更都在应用发布流程单独执行。4.2 保存点不完美的部分回滚如果你确实需要回滚到事务的某个中间点而不是全部回滚可以用保存点SAVEPOINT。语法如下START TRANSACTION; INSERT INTO orders(order_id, amount) VALUES(O1001, 100); SAVEPOINT sp_after_order; INSERT INTO order_item(item_id, order_id, sku_id) VALUES(I001, O1001, SKU888); -- 这里出错了只想回滚这一条不想撤销前面插入的订单 ROLLBACK TO SAVEPOINT sp_after_order; COMMIT;执行ROLLBACK TO SAVEPOINT后保存点之后的变更会被撤销但保存点之前已执行的语句仍然有效之后可以继续执行其他SQL最后统一提交。这个特性的使用场景其实不多毕竟大多数事务设计成一荣俱荣、一损俱损更清晰。但如果你在一个事务里处理多个独立子任务希望某个子任务失败不影响其他子任务保存点是比拆事务更优雅的方案。比如批量导入每条数据一个子任务失败的回滚到该子任务的保存点记录错误后继续处理下一条最后统一提交这样既保证了整体效率又不会让单条脏数据污染全部数据。4.3 滚回日志与快照读理解undo log的多版本机制InnoDB实现回滚的核心是undo log。事务每次修改数据前都会把旧值写入undo log。回滚时数据库根据undo log把数据恢复到修改前的版本。同时undo log也支撑了MVCC的快照读在REPEATABLE READ下事务里第一次SELECT会生成一个一致的快照视图之后即使其他事务提交了新值当前事务读到的还是第一次SELECT时的快照。这个机制带来一个容易被忽略的后果长事务会持续占用undo log导致undo log膨胀。我运维过一个MySQL实例undo表空间涨到几十个G查不出原因后来发现是有个服务开着事务跑了几个小时事务一直不提交期间产生的undo log无法被purge线程清理。所以监控事务时长非常必要MySQL里可以直接查SELECT * FROM information_schema.innodb_trx;通过trx_started字段看事务启动时间超过几秒就该报警重点关注了。5. SQL事务实操从命令行到应用代码5.1 命令行下完整演示一个事务过程直接上实测。假设有一张账户表CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50) NOT NULL, balance DECIMAL(10,2) NOT NULL ) ENGINEInnoDB; INSERT INTO account(user_name, balance) VALUES(Alice, 500), (Bob, 500);场景是Alice给Bob转账100元。开启事务执行-- 会话A START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_name Alice; UPDATE account SET balance balance 100 WHERE user_name Bob;此时如果开启另一个会话B去查会看到Alice的余额还是500因为A还没提交。这就是隔离性在起作用。然后A执行COMMIT;B再查余额变成400和600。如果执行过程中发现异常改成ROLLBACK;则两个UPDATE都被撤销余额恢复原样。一个小技巧命令行里可以用--注释标记事务的边界方便调试复杂脚本。但生产环境我更推荐把所有事务逻辑封装在存储过程或应用代码里命令行手动执行事务只适合临时数据订正。5.2 代码里的三层事务控制方式不同技术栈控制事务的方式不同本质都是围绕连接Connection做文章。先看最基础的JDBC方式import java.sql.Connection; import java.sql.DriverManager; try (Connection conn DriverManager.getConnection(url, user, password)) { // 关键关闭自动提交让多个操作处于同一个事务中 conn.setAutoCommit(false); try (var stmt1 conn.prepareStatement(UPDATE account SET balance balance - 100 WHERE user_name Alice)) { stmt1.executeUpdate(); } try (var stmt2 conn.prepareStatement(UPDATE account SET balance balance 100 WHERE user_name Bob)) { stmt2.executeUpdate(); } // 全部成功提交 conn.commit(); } catch (Exception e) { // 任一环节出错回滚 conn.rollback(); throw e; }这段代码的核心是setAutoCommit(false)很多小白忘记这一步导致每条SQL都在独立事务里执行出问题根本回滚不了。更细节的一个点rollback()也可能抛异常所以捕获块里最好再包一层异常处理或者使用IoC框架的事务模板来兜底。如果用的Spring事务可以声明式管理Transactional public void transfer(String fromUser, String toUser, BigDecimal amount) { accountDao.decreaseBalance(fromUser, amount); accountDao.increaseBalance(toUser, amount); }Transactional默认情况下只有运行时异常RuntimeException和Error才会触发回滚受检异常Checked Exception不会回滚。这是个经典大坑你调用的外部服务抛了个Exception受检异常Spring不认为事务失败直接提交了最终数据一致性被破坏。解决方案是显式声明回滚条件Transactional(rollbackFor Exception.class)这个参数我从用了Spring到现在每次写事务注解都会加上已经成了肌肉记忆。5.3 事务传播行为什么时候事务会被合并Spring事务还有个容易混淆的概念——传播行为。默认传播级别是REQUIRED如果当前存在事务就加入当前事务如果没有就新建一个事务。这种情况在同一个类内部方法互相调用时最容易出问题。假设一个Service类里方法A调用方法BB上有Transactional注解但A没有事务那么B会新建事务执行调用关系上的外层包裹并不存在A方法中B调用前后的其他数据库操作不在B的事务里。更隐蔽的问题是Spring代理机制导致的事务失效Transactional标注的方法被同类内部方法直接调用时绕过了Spring的代理对象注解根本不会被解析事务不会生效。比如public void saveOrder(Order order) { // 直接调用同类中的方法事务不生效 updateStock(order.getSkuId()); } Transactional public void updateStock(Long skuId) { // ... }解决方式是注入自身代理或者拆到另一个Service类中。还有一个常见失效场景是final方法、private方法Spring基于CGLIB代理时无法覆盖final方法事务自然失效。5.4 分布式事务场景的现实选择当服务拆成微服务后一个业务操作要跨多个数据库甚至多个中间件本地事务就管不住了。业界常用方案有这么几类两阶段提交XA数据库原生支持强一致性但性能损耗大协调者宕机时会有阻塞风险现在生产环境用得越来越少。TCCTry-Confirm-Cancel把业务拆成预留资源、确认、补偿三步灵活性高但代码侵入强实现成本高。事务消息本地消息表MQ把事务操作和写消息放在同一个本地事务里再通过消息队列异步通知下游执行。最终一致性是目前业界最常用的方案。我实际项目中用的最多的是本地消息表MQ这套方案。核心流程在本地事务里写业务表的同时写一张消息表消息状态标记为待发送事务提交后异步任务扫描消息表并发送MQ收到确认后更新消息状态。下游失败的消息可以通过重试机制补偿配合定时对账保证最终一致。分布式事务没有银弹核心思路是尽量避免跨库事务。通过聚合根设计把需要强一致的数据放在同一个库里实在拆不开的用消息驱动对账兜住最终一致性。我之前甚至见过把订单主表和订单明细强行分到两个数据库然后硬上分布式事务的方案性能和稳定性都惨不忍睹。6. 事务使用高频问题与排查思路6.1 事务不生效的排查清单日常开发中事务不生效是最令人崩溃的问题按照我的经验排查优先级如下检查方法是否被Spring代理调用确认没有同类内部调用绕过代理。检查数据库引擎是否是InnoDB。MyISAM等老引擎不支持事务。检查异常是否被吞掉。try-catch捕获异常后不抛出事务照样提交。检查异常类型。如果是受检异常且Transactional没配rollbackFor不会回滚。检查事务是否超时。Spring默认事务超时时间可能比你预期的短超时后自动回滚但日志不一定有醒目标志。检查Transactional是否放在了接口实现类上且接口和实现方法签名是否一致。我之前接手过一个老项目数据订正接口经常出现部分成功的状态查了半天发现开发在方法内部把异常catch了之后log.error就返回了事务自然认为一切正常直接提交。这里也提一下Checkstyle的重要性最好在团队规范中强制要求事务方法不允许自行catch吞异常。6.2 死锁日志怎么看死锁发生时MySQL会把最近一次死锁信息记录在SHOW ENGINE INNODB STATUS\G;输出中。关键看两个部分LATEST DETECTED DEADLOCK段落会显示两个事务各自执行的最后SQL。WE ROLL BACK TRANSACTION段会告诉你哪个事务被回滚了。分析死锁时我习惯把两个事务执行的SQL按时间线画出来看每个事务持有哪些锁、等待哪些锁。找到锁等待的交叉点后通过调整SQL顺序、缩小事务范围来规避。6.3 大事务的真面目大事务的危害不是数据库会坏而是拖垮并发我用一个真实案例说明某定时任务每天凌晨扫描全表更新数据一次执行几万条UPDATE整个事务跑了大几分钟。这期间所有涉及这些行的业务操作要么阻塞等锁要么触发锁等待超时。更致命的是由于主从复制中从库是单线程回放大事务会导致主从延迟暴涨进而让读写分离的架构出现刚写完读不到的诡异现象。拆分大事务的常见思路是按主键范围分批提交。例如把几万条数据分成每500条一批每批一个事务既能保证单批的原子性又不会长时间占用锁和连接。还有一点要提醒的是事务期间尽量避免在数据库事务内调用外部API、RPC或者等待用户输入。这些都难以预测耗时会让事务长时间占据连接池资源最终耗尽连接导致应用雪崩。7. 我的个人建议事务设计要先想后做事务虽然只是一个语法层面begin...commit一个字但设计合理的事务方案需要充分理解业务和数据特征。从我的实际经验出发给大家几个建议。第一事务尽可能短别把耗时的非数据库操作塞进事务里。第二优先依赖数据库默认的隔离级别每换一个数据库都要重新评估事务行为和锁机制。第三养成查看执行计划的习惯警惕条件字段无索引导致的全表锁。第四线上数据订正脚本务必先小范围测试确认事务边界符合预期再全量执行。事务从本质上是用性能换一致性的一种取舍在高并发场景中我们需要结合业务对一致性的要求做出选择。读多写少的报表系统与订单交易系统的事务设计思路往往截然不同没有绝对正确的方案只有适合当前业务形态的方案。希望这篇文章能让你少踩几个坑。