MySQL锁机制全解行锁、表锁、意向锁与死锁排查作者黒漂技术佬适用读者知道有锁这回事但分不清Record/Gap/Next-Key、排查死锁没思路的同学关联场景无人售货柜并发扣库存死锁、工控设备状态更新一、为什么需要锁并发写-写冲突的最后防线上一篇讲MVCC时提过MVCC解决了读-写并发的阻塞问题——读走快照写走新版本互不打扰。但MVCC解决不了写-写冲突。售货柜最后1瓶可乐 事务AUPDATE product SET stockstock-1 WHERE id1001 事务BUPDATE product SET stockstock-1 WHERE id1001 如果没锁A读到stock1B读到stock1A写stock0B写stock0 → 实际只卖1瓶却让两个用户都下单成功 → 超卖两个事务同时改同一行必须有一个先来后到——这就是锁要管的事。锁是MySQL保证写-写串行化的最后防线。二、锁的分类维度MySQL锁可以从两个维度分类搞清楚这两个维度后面的概念都好懂。2.1 按粒度分粒度锁名说明表锁表锁锁整张表粒度大并发低行锁行锁只锁一行/几行粒度小并发高2.2 按性质分性质锁名符号兼容性共享锁Share LockSS和S兼容可同时读排他锁Exclusive LockX和任何锁互斥写独占兼容矩阵SXS兼容互斥X互斥互斥简单记忆S是读锁X是写锁。读读可共存一有写就互斥。三、表锁读锁和写锁3.1 表锁的语法-- 加表读锁S锁LOCKTABLESproductREAD;-- 加表写锁X锁LOCKTABLESproductWRITE;-- 释放UNLOCKTABLES;加表读锁后当前会话和其他会话只能读这张表不能写。加表写锁后只有当前会话能读写其他会话读写都阻塞。3.2 表锁的实际使用场景表锁在InnoDB里用得少因为粒度太粗。但有些场景必须用场景售货柜凌晨批量导入商品数据 - 要做 ALTER TABLE 修改表结构 → 自动加表锁 - 要做大批量数据导入避免逐行加锁开销 - MyISAM引擎只支持表锁现在很少用了生产忠告在线DDL改表结构要避开高峰期。ALTER TABLE会加表元数据锁期间所有DML阻塞。大表加字段用pt-online-schema-change或gh-ost工具。四、行锁Record、Gap、Next-KeyInnoDB的精华在行锁行锁又分三种。这是新手最容易搞混的地方我们慢慢拆。4.1 Record Lock记录锁锁住索引上的一条具体记录。-- 假设有唯一索引 product_id1001SELECT*FROMproductWHEREproduct_id1001FORUPDATE;这条语句会锁住product_id1001这一行其他事务想改这一行就阻塞。索引记录 ... 1000 [1001]锁住 1002 ... ↑ 只有这行被锁4.2 Gap Lock间隙锁锁住索引记录之间的间隙但不锁记录本身。Gap Lock的作用是防止其他事务在这个间隙里INSERT新数据。假设product表product_id有值1000, 1005, 1010 间隙(-∞,1000)、(1000,1005)、(1005,1010)、(1010,∞) 事务A执行SELECT * FROM product WHERE product_id BETWEEN 1001 AND 1004 FOR UPDATE → 锁住间隙(1000,1005)其他事务不能往这个间隙插入数据Gap Lock只在RR隔离级别下生效RC下关闭。它解决的经典问题是幻读。幻读同一事务里两次按条件查询第二次查到了第一次没有的新行别人INSERT进来的。Gap Lock锁住间隙让INSERT进不来就没有幻读了。4.3 Next-Key LockRecord Lock Gap Lock的组合体——既锁记录又锁记录前面的间隙。这是InnoDB在RR下行锁的默认形态。索引记录... 1000 1005 1010 ... 对1005加Next-Key Lock → 锁住 (1000, 1005] ↑ Record锁住1005这条记录 ↑ Gap锁住(1000,1005)这个间隙锁类型锁范围防什么Record Lock单条记录防止行被修改/删除Gap Lock记录间间隙防止INSERT新行幻读Next-Key Lock间隙记录既防改又防插入一个反直觉点等值查询命中唯一索引时Next-Key Lock会退化为Record Lock。因为唯一索引已经保证不会有重复值插入不需要锁间隙。五、意向锁IS和IX5.1 为什么需要意向锁假设事务A给product表某一行加了行X锁。现在事务B想给整张表加表锁比如要改表结构。B怎么知道表里有没有行锁总不能扫遍整张表所有行去查。意向锁Intent Lock就是解决快速判断表里有没有行锁的问题。5.2 意向锁的工作方式事务在给行加S锁前先给表加IS意向共享锁给行加X锁前先给表加IX意向排他锁。表锁和意向锁的兼容性表级ISIXIS兼容兼容IX兼容兼容注意IS和IX之间互相兼容——因为它们只是意向不代表真正持锁。真正判断冲突的是表锁和意向锁的组合表级ISIX表S锁兼容互斥表X锁互斥互斥这样事务B加表锁前只要看表的意向锁就够了有IX说明表里有行X锁加表S锁会被阻塞。意向锁是InnoDB自动加的开发不用手动管理。但要知道它的存在——排查锁等待时经常会看到IS/IX。六、死锁是什么两个事务互相等待6.1 死锁的产生死锁是两个或多个事务互相持有对方需要的锁形成循环等待谁也走不下去。事务A 事务B T1: UPDATE product SET stockstock-1 WHERE id1001 (持有id1001的X锁) T2: UPDATE product SET stockstock-1 WHERE id1002 (持有id1002的X锁) T3: UPDATE product SET stockstock-1 WHERE id1002 (等id1002的X锁被B锁住) T4: UPDATE product SET stockstock-1 WHERE id1001 (等id1001的X锁被A锁住) → A等BB等A死锁形成6.2 死锁的两个解决机制InnoDB有两种机制应对死锁机制1超时回滚-- 设置锁等待超时秒SETinnodb_lock_wait_timeout50;-- 超过50秒还没拿到锁直接报错回滚ERROR1205(HY000):Lockwait timeout exceeded;try restartingtransaction机制2死锁检测wait-for-graph算法InnoDB默认开启死锁检测innodb_deadlock_detectON。当事务请求锁时系统检测会不会形成等待环。如果检测到死锁立刻回滚其中一个代价较小的事务一般是undo log量少的那个让另一个继续执行。死锁检测报错 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction工程权衡死锁检测会消耗CPU。高并发写场景下比如秒杀同一行大量事务在同一行排队死锁检测开销暴涨。这种场景要靠业务设计把热点行打散或者关掉死锁检测设短超时。七、实际项目死锁排查死锁发生时数据库会回滚一个事务但不会主动告诉你为什么。要主动查。7.1 查看最近一次死锁信息SHOWENGINEINNODBSTATUS;输出里的LATEST DETECTED DEADLOCK段会显示*** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 2 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 8, OS thread handle 12345, query id 100 localhost updating UPDATE product SET stockstock-1 WHERE id1001 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table shop.product trx id 12345 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 12346, ACTIVE 2 sec starting index read UPDATE product SET stockstock-1 WHERE id1002 *** (2) HOLDS THE LOCK(S): ... index PRIMARY of table shop.product trx id 12346 lock_mode X *** (2) WAITING FOR THIS LOCK TO BE GRANTED: ... lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2)从这段日志能看出哪两个事务死锁各自执行了什么SQL在等哪个锁PRIMARY索引上的X锁哪个事务被回滚了7.2 开启完整锁等待日志-- 开启锁等待详细日志每次锁等待都记SETGLOBALinnodb_status_outputON;SETGLOBALinnodb_status_output_locksON;排查死锁三件套SHOW ENGINE INNODB STATUS看死锁现场 information_schema.innodb_trx看活跃事务 information_schema.innodb_locks/innodb_lock_waits看锁等待关系。八、无人售货柜库存扣减死锁案例分析8.1 案例背景某无人售货柜运营平台有2000台柜子每台柜子100个商品SKU。用户扫码开门取货后系统根据重量变化算出取了哪些商品然后扣库存并生成订单。高峰期经常报死锁每天上百次。8.2 复现业务代码简化TransactionalpublicvoidsettleOrder(LongcabinetId,ListOrderItemitems){for(OrderItemitem:items){// 按用户取货顺序逐个扣库存productMapper.deductStock(item.getProductId(),item.getQty());}orderMapper.insert(order);}用户A取了[可乐(id1001), 雪碧(id1002)]用户B取了[雪碧(id1002), 可乐(id1001)]。两个事务按不同顺序扣库存 → 经典死锁。8.3 解决方案方案1统一加锁顺序最简单有效扣库存前把商品ID排序按固定顺序扣。这样所有事务拿锁的顺序一致不会形成环。TransactionalpublicvoidsettleOrder(LongcabinetId,ListOrderItemitems){// 按productId升序排序保证所有事务加锁顺序一致items.sort(Comparator.comparing(OrderItem::getProductId));for(OrderItemitem:items){productMapper.deductStock(item.getProductId(),item.getQty());}orderMapper.insert(order);}方案2单条SQL批量扣减把多次UPDATE合并成一次只持有一组锁-- 用CASE WHEN一次扣多个SKUUPDATEproductSETstockCASEproduct_idWHEN1001THENstock-1WHEN1002THENstock-2ENDWHEREproduct_idIN(1001,1002);方案3乐观锁重试把扣库存改成先读后改再条件更新冲突时重试-- 先读SELECTstockFROMproductWHEREproduct_id1001;-- 假设读到48-- 条件更新带version或stock条件UPDATEproductSETstockstock-1WHEREproduct_id1001ANDstock48;-- 影响行数0说明并发改了重试死锁排查核心思路拿到死锁日志后看两个事务各执行了什么SQL、在等哪条记录的锁。如果加锁顺序相反——就是死锁成因。统一加锁顺序是解决死锁的银弹。九、总结概念一句话表锁锁整张表粒度粗并发低Record Lock锁单条索引记录Gap Lock锁索引间隙防INSERT只RR下生效Next-Key LockRecordGapRR下行锁默认形态意向锁IS/IX标记表里有没有行锁加速表锁判断死锁事务循环等待对方锁靠检测或超时打破死锁排查SHOW ENGINE INNODB STATUS看现场死锁根治统一加锁顺序、批量操作、乐观锁锁机制是MySQL并发控制的下半场。MVCC管读-写并发锁管写-写并发。理解Record/Gap/Next-Key三种行锁以及死锁的形成和排查你就能hold住90%的并发数据问题。下一篇我们看主从复制——MySQL走向分布式的第一步。