1. 从一次线上事故说起为什么我们需要理解MySQL锁那天晚上系统监控突然报警核心交易接口的响应时间从平时的几十毫秒飙升到了十几秒TPS断崖式下跌。登录数据库一看SHOW PROCESSLIST里塞满了状态为Waiting for table metadata lock的会话。一个看似简单的ALTER TABLE ADD COLUMN操作卡住了后续所有的SELECT和INSERT。我们紧急KILL了那个DDL操作线程业务才逐渐恢复。事后复盘根本原因是一个未提交的长事务持有了表的元数据锁而后续的DDL操作在等待这个锁进而阻塞了所有需要访问该表元数据的查询。这次事故给我上了深刻的一课在并发量稍高的生产环境里如果你对MySQL的锁机制一知半解就像在雷区里闭眼狂奔随时可能“炸库”。锁是数据库协调多用户并发访问同一资源的基石也是导致性能瓶颈和死锁的罪魁祸首。很多人对锁的印象停留在“锁表”、“行锁”这些名词上但真正遇到问题时却不知道锁在哪里、谁持有的、为什么阻塞。所以今天我们不谈枯燥的理论就从实战角度把MySQL的锁机制掰开揉碎了讲清楚。我会围绕“是什么锁”、“在哪加锁”、“怎么加锁”、“为何阻塞”以及“如何规避”这条主线结合真实的场景和命令让你不仅能理解概念更能具备实际排查和优化的能力。无论你是开发还是DBA吃透这套机制都能让你在设计和排查数据库问题时心里更有底。2. 锁的宏观分类表锁、行锁与元数据锁当我们谈论MySQL锁时首先要建立一个清晰的层次概念。不同的存储引擎、不同的操作加的锁天差地别。我习惯把它们分为三个层面全局层面的元数据锁表级别的表锁以及行级别的行锁。理解它们的共存与互斥关系是解开一切锁争用问题的钥匙。2.1 元数据锁DDL与DML的隐形守护者元数据锁是MySQL 5.5引入的它的主要目的是保证在并发环境下表结构定义DDL和表数据操作DML的一致性。想象一下一个查询正在读取某一行另一个线程突然把这列删了这肯定会出问题。MDL就是防止这种情况的发生。MDL锁的加锁规则DML操作如SELECT,INSERT,UPDATE,DELETE会对涉及的表加一个MDL读锁。这个锁是共享的多个DML操作可以同时持有同一张表的MDL读锁。DDL操作如ALTER TABLE,DROP TABLE,RENAME TABLE会对涉及的表加一个MDL写锁。这个锁是排他的同一时间只能有一个DDL操作持有该表的MDL写锁。关键冲突MDL读锁与MDL写锁互斥。这就是我们开头事故的原因。一个长查询持有着MDL读锁不结束后续的DDL操作申请MDL写锁就会一直等待。更糟糕的是在DDL操作等待期间它后面所有新的、试图申请MDL读锁的DML操作也都会被阻塞这就形成了典型的“MDL锁等待链”导致雪崩效应。注意MDL锁的持有周期是事务生命周期。即使你的SELECT语句已经执行完毕但只要事务没有提交在REPEATABLE-READ隔离级别下这个MDL读锁就会一直持有。这也是为什么建议在业务中避免使用长事务并尽快提交事务的重要原因之一。2.2 表级锁简单粗暴的守护者表级锁是MySQL服务器层实现的锁与存储引擎无关。主要有两种表共享读锁LOCK TABLES table_name READ。允许其他会话加读锁或执行无锁查询但不允许加写锁。表独占写锁LOCK TABLES table_name WRITE。不允许其他会话进行任何读/写操作。在InnoDB成为绝对主流的今天我们很少会手动使用LOCK TABLES因为它的粒度太粗并发性能极差。但是在某些特定情况下MySQL会自动加表锁当InnoDB表上没有合适的索引时。例如你对一个没有索引的字段进行UPDATE ... WHERE操作InnoDB无法精确定位到行就会退而求其次锁住整个表实际上是锁住所有行效果等同表锁。执行ALTER TABLE等DDL时在等待MDL写锁之前或之后也可能涉及表锁。一个经典误区很多人认为MyISAM只支持表锁InnoDB只支持行锁。这不完全准确。MyISAM确实只有表锁但InnoDB是支持行锁和表锁共存的。例如一个ALTER TABLE操作在InnoDB表上仍然需要获取表级的排他锁。2.3 行级锁InnoDB高并发的核心武器行级锁是InnoDB存储引擎实现的也是支撑MySQL高并发的基石。它允许只锁定需要修改的行其他行依然可以被并发访问。行锁的种类更多样理解其细分类型至关重要。2.3.1 记录锁记录锁是最简单的行锁它锁住索引上的一条具体记录。例如UPDATE t SET name‘a’ WHERE id 10;如果id是主键就会在id10的索引记录上加一个记录锁。2.3.2 间隙锁这是InnoDB在可重复读隔离级别下引入的用于解决幻读问题。它锁住的是一个索引记录之间的“间隙”而不是记录本身。例如表中有id为5和10的记录执行SELECT * FROM t WHERE id BETWEEN 7 AND 15 FOR UPDATE;就会在(5, 10)和(10, ∞)这两个间隙范围上加锁。这意味着其他事务无法在这个间隙内插入新的记录比如id8。实操心得间隙锁是导致很多死锁的“元凶”。因为它的锁定范围是“开区间”两个事务可能以相反的顺序请求不同间隙的锁从而形成循环等待。在业务允许的情况下将隔离级别降为读已提交可以避免绝大部分间隙锁提升并发度但需要业务层自己处理幻读问题。2.3.3 临键锁临键锁是记录锁和间隙锁的结合。它既锁住记录本身也锁住该记录之前的间隙。可以理解为一种“左开右闭”的区间锁。例如对于唯一索引id10临键锁锁定的范围可能是(5, 10]。这是InnoDB默认的行锁算法。2.3.4 插入意向锁这是一种特殊的间隙锁表示一个事务准备在某个间隙插入记录。多个事务可以在同一个间隙上持有兼容的插入意向锁因为它们只是“意向”实际插入的位置可能不同。但是插入意向锁会与已经存在的间隙锁或临键锁互斥。例如事务A锁定了间隙(5,10)事务B想在这个间隙插入id7的记录就需要申请插入意向锁此时就会被事务A阻塞。理解这四种行锁及其互斥关系是分析复杂死锁场景的基础。它们的兼容矩阵比简单的读写锁要复杂得多。3. 锁在何处深入索引与锁的耦合关系行锁加在哪里这是一个核心问题。答案是加在索引上。更准确地说是加在满足查询条件的索引记录上。如果语句用到了哪个索引锁就加在那个索引对应的记录上。这里有几个关键场景场景一主键索引查询UPDATE user SET score100 WHERE id 1;id是主键锁直接加在主键索引id1的记录上。这是最清晰、冲突最少的情况。场景二唯一索引查询UPDATE user SET score100 WHERE email ‘aliceexample.com’;email是唯一索引锁首先加在唯一索引email‘aliceexample.com’的记录上。同时InnoDB还会去主键索引上找到对应的主键记录也加上锁。这是为了防止在通过唯一索引定位到行后该行的主键被其他事务修改。场景三非唯一索引查询UPDATE user SET score100 WHERE age 20;age是一个普通的非唯一索引。这时InnoDB会锁住所有age20的索引记录。由于是非唯一索引满足条件的记录可能有多条每条索引记录及其对应的主键记录都会被加锁。如果age20的记录有1000条就会产生至少2000个行锁索引记录主键记录。这就是为什么在非唯一索引字段上做范围更新或删除非常危险极易导致大量锁竞争甚至锁表。场景四无索引查询UPDATE user SET score100 WHERE name ‘张三’;name字段没有索引。对于InnoDB没有索引就意味着无法通过索引快速定位记录它只能进行全表扫描。在扫描过程中每一条被扫描到的记录无论是否符合WHERE条件都会被加上锁。在可重复读隔离级别下为了确保一致性还会在每条记录之间的间隙加上间隙锁。最终效果就是锁定了全表的所有记录和间隙等同于一个表级锁并发性能归零。核心避坑指南务必为你的UPDATE和DELETE语句的WHERE条件建立合适的索引。这是避免锁范围过大、提升并发能力的首要原则。即使是一个很差的索引也比没有索引要好得多。EXPLAIN命令是你的好朋友执行更新前先看看执行计划确认是否用上了索引。4. 锁的观测与实战排查当问题发生时理论懂了线上真出问题了怎么办你需要一套清晰的排查链路。我通常的排查步骤是现象定位 - 锁信息采集 - 关联分析 - 解决方案。4.1 现象定位识别锁等待业务侧反馈“卡住了”。首先连上数据库查看当前线程状态SHOW PROCESSLIST;重点关注State列。常见的锁等待状态有Waiting for table metadata lock: MDL锁等待。Waiting for table level lock: 表锁等待MyISAM或显式LOCK TABLES。State显示为updating、deleting等但长时间不变化且Info是某条DML语句很可能在等待行锁。State显示statistics、copying to tmp table等也可能是MDL锁等待的一种表现。4.2 锁信息采集使用InnoDB锁信息表MySQL提供了performance_schema库中的表来监控锁信息但需要开启相关监控器有一定性能开销。更常用的是information_schema库中的表5.7及以上版本支持较好-- 查看当前正在发生的锁等待 SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 查看当前所有持有的锁和等待的锁的详细信息 SELECT * FROM information_schema.INNODB_LOCKS; -- 注意8.0中此表已移除被performance_schema.data_locks取代 -- 在MySQL 8.0中使用以下视图 -- SELECT * FROM performance_schema.data_locks; -- 显示持有的锁 -- SELECT * FROM performance_schema.data_lock_waits; -- 显示锁等待关系通过INNODB_LOCK_WAITS你可以看到blocking_trx_id阻塞者的事务ID和waiting_trx_id被阻塞者的事务ID。再结合INNODB_TRX表查看事务详情就能定位到罪魁祸首。4.3 一个完整的死锁排查案例假设我们收到报警日志中出现Deadlock found when trying to get lock; try restarting transaction。开启死锁日志确保innodb_print_all_deadlocks ON这样死锁详情会输出到错误日志中。分析错误日志日志会记录最后一次检测到的死锁信息包括两个事务各自持有的锁、等待的锁以及被回滚的事务。格式类似LATEST DETECTED DEADLOCK ------------------------ 2023-10-27 10:00:00 0x7f123456 *** (1) TRANSACTION: TRANSACTION 1000, ACTIVE 10 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 123, OS thread handle 140123, query id 456 localhost root updating UPDATE t SET cc1 WHERE a1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 3 n bits 72 index PRIMARY of table test.t trx id 1000 lock_mode X locks rec but not gap waiting ... *** (2) TRANSACTION: TRANSACTION 1001, ACTIVE 8 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 124, OS thread handle 140124, query id 457 localhost root updating UPDATE t SET cc1 WHERE a2 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 3 n bits 72 index PRIMARY of table test.t trx id 1001 lock_mode X locks rec but not gap waiting ... *** WE ROLL BACK TRANSACTION (1)解读日志上面这个经典死锁通常是因为两个事务以相反的顺序更新了多行记录。比如事务1先锁了行A再请求行B事务2先锁了行B再请求行A。解决这类问题通常需要让业务代码以固定的顺序访问资源例如按主键ID排序后再更新。4.4 关联分析与解决拿到阻塞或死锁信息后你需要关联业务日志找到对应的代码逻辑。问自己几个问题这个事务是做什么的为什么执行这么久它的SQL语句是否走了合适的索引事务的边界是否合理能否拆分成更小的事务业务逻辑上对资源的访问顺序能否统一解决方案无外乎几种优化SQL和索引、缩短事务时间、重写业务逻辑以固定访问顺序、在可接受的情况下降低隔离级别。5. 隔离级别与锁的联动理解你的“交易规则”事务隔离级别定义了数据库处理并发读写的严格程度它直接决定了InnoDB的加锁策略。不同级别下锁的行为差异巨大。读未提交几乎不加读锁除了防止数据定义冲突的锁会读到未提交的数据脏读。生产环境严禁使用。读已提交这是Oracle等数据库的默认级别。在这个级别下普通的SELECT使用快照读不加锁除非用FOR UPDATE/LOCK IN SHARE MODE。UPDATE/DELETE语句只锁住需要修改的行没有间隙锁。这大大减少了锁冲突但引入了“不可重复读”和“幻读”的问题。可重复读这是MySQL InnoDB的默认级别。核心特点是在一个事务内多次读取同一范围的数据结果是一致的。为了实现这一点InnoDB使用了MVCC和多版本并发控制和间隙锁。普通的SELECT也是快照读不加锁。但UPDATE/DELETE/SELECT ... FOR UPDATE等当前读操作不仅会锁住记录还会锁住间隙以防止其他事务插入新行幻读。这也是锁问题最复杂的级别。串行化所有读操作都会隐式转换为SELECT ... LOCK IN SHARE MODE加上共享锁读写严重互斥性能最差一般不用。选择建议如果你的业务能接受“不可重复读”和“幻读”例如一些报表查询、实时性要求不高的统计并且追求更高的并发性能可以考虑使用读已提交隔离级别它能避免绝大部分恼人的间隙锁死锁。如果你的业务要求严格的一致性如金融交易那么默认的可重复读是更安全的选择但要求开发人员必须深刻理解间隙锁并精心设计索引和事务。个人经验我曾经将一个并发冲突严重的系统从“可重复读”降级到“读已提交”配合业务逻辑的微调例如使用乐观锁version字段系统的死锁频率从每天几次降到了几乎为零吞吐量提升了近30%。但这步操作需要完整的测试和评估确保业务逻辑在“读已提交”下依然正确。6. 意向锁表锁与行锁的沟通桥梁意向锁是InnoDB为了协调表级锁和行级锁而设计的一种表级锁。它本身并不锁定具体数据而是一种“宣告”。意向共享锁当一个事务准备给某些行加共享锁S锁之前它必须先获得该表的意向共享锁。意向排他锁当一个事务准备给某些行加排他锁X锁之前它必须先获得该表的意向排他锁。为什么需要意向锁假设没有意向锁。事务A锁定了表中的一行行级X锁。此时事务B想对整个表加一个表级X锁比如LOCK TABLES ... WRITE。事务B如何判断自己能加锁呢它必须逐行检查表中是否有行被锁定效率极低。有了意向锁事务A在加行锁前先对表加了一个IX锁。事务B申请表级X锁时发现表上已经存在IX锁而IX锁与表级X锁是互斥的于是事务B就能被快速阻塞无需遍历每一行。兼容性矩阵简化理解意向锁之间是兼容的。IS和IX可以共存因为大家只是“有意向”去锁不同的行并不冲突。意向共享锁与表级共享锁兼容但与表级排他锁互斥。意向排他锁与任何表级锁共享或排他都互斥。你几乎不需要手动操作意向锁但理解它能让你明白SHOW ENGINE INNODB STATUS输出中那些LOCK_IX、LOCK_IS的含义知道表级锁和行级锁是如何协同工作的。7. 自增锁与插入缓冲针对特殊场景的优化除了常见的锁InnoDB还有一些针对特定场景的、比较特殊的锁机制。7.1 自增锁涉及AUTO_INCREMENT列的插入操作需要获取一种特殊的表级锁——自增锁。这是为了确保并发插入时每个事务都能拿到唯一且连续的自增ID在默认“连续”模式下。在MySQL 5.1之后InnoDB提供了几种自增锁模式innodb_autoinc_lock_mode参数0传统模式所有INSERT语句都会使用表级自增锁语句执行结束后释放。最安全但并发性最差。1连续模式默认。对于能预先确定插入行数的INSERT语句如INSERT INTO ... VALUES (...), (...)使用一个轻量级的互斥量在分配ID时加锁分配完即释放不需要等到语句结束。对于INSERT ... SELECT这类不确定行数的语句仍使用表级自增锁。这是安全性和性能的折中。2交错模式。所有插入都不使用表级自增锁完全靠互斥量分配ID。性能最高但可能导致自增ID不连续并且在基于语句的复制下是不安全的。建议除非你使用行级复制并且可以接受自增ID不连续否则保持默认的innodb_autoinc_lock_mode1是最佳选择。7.2 插入缓冲严格来说插入缓冲不是一种锁而是一种为了减少随机I/O、提升插入性能的优化机制。对于非唯一的二级索引的插入InnoDB不会直接写入磁盘的索引页而是先缓存到“Change Buffer”中等到未来该索引页被读到内存时再合并进去。这减少了磁盘的随机写操作。 但这里有一个与锁相关的点因为插入缓冲延迟了索引的更新所以在事务提交后对应的二级索引变更可能并没有真正写入磁盘索引树。这不会影响数据一致性因为通过主键依然能查到最新数据。但在某些极端并发场景下如果大量事务同时提交并合并插入缓冲可能会带来短暂的I/O压力。理解MySQL的锁机制是一个从“知其然”到“知其所以然”再到“知其不得不然”的过程。它不仅仅是数据库的知识更是设计高并发、高可靠应用系统时必须考虑的一环。下次当你编写一条UPDATE语句或设计一个事务边界时不妨在脑海里过一遍这条语句会在哪些索引上加什么锁会不会和隔壁服务的事务打架这个事务持有锁的时间是不是太长了多问几个为什么很多潜在的线上问题就能被提前消灭在萌芽里。锁的世界很复杂但驾驭了它你就能真正掌控数据库的并发命脉。 MySQL锁机制深度解析：从原理到实战排查死锁与性能优化
1. 从一次线上事故说起为什么我们需要理解MySQL锁那天晚上系统监控突然报警核心交易接口的响应时间从平时的几十毫秒飙升到了十几秒TPS断崖式下跌。登录数据库一看SHOW PROCESSLIST里塞满了状态为Waiting for table metadata lock的会话。一个看似简单的ALTER TABLE ADD COLUMN操作卡住了后续所有的SELECT和INSERT。我们紧急KILL了那个DDL操作线程业务才逐渐恢复。事后复盘根本原因是一个未提交的长事务持有了表的元数据锁而后续的DDL操作在等待这个锁进而阻塞了所有需要访问该表元数据的查询。这次事故给我上了深刻的一课在并发量稍高的生产环境里如果你对MySQL的锁机制一知半解就像在雷区里闭眼狂奔随时可能“炸库”。锁是数据库协调多用户并发访问同一资源的基石也是导致性能瓶颈和死锁的罪魁祸首。很多人对锁的印象停留在“锁表”、“行锁”这些名词上但真正遇到问题时却不知道锁在哪里、谁持有的、为什么阻塞。所以今天我们不谈枯燥的理论就从实战角度把MySQL的锁机制掰开揉碎了讲清楚。我会围绕“是什么锁”、“在哪加锁”、“怎么加锁”、“为何阻塞”以及“如何规避”这条主线结合真实的场景和命令让你不仅能理解概念更能具备实际排查和优化的能力。无论你是开发还是DBA吃透这套机制都能让你在设计和排查数据库问题时心里更有底。2. 锁的宏观分类表锁、行锁与元数据锁当我们谈论MySQL锁时首先要建立一个清晰的层次概念。不同的存储引擎、不同的操作加的锁天差地别。我习惯把它们分为三个层面全局层面的元数据锁表级别的表锁以及行级别的行锁。理解它们的共存与互斥关系是解开一切锁争用问题的钥匙。2.1 元数据锁DDL与DML的隐形守护者元数据锁是MySQL 5.5引入的它的主要目的是保证在并发环境下表结构定义DDL和表数据操作DML的一致性。想象一下一个查询正在读取某一行另一个线程突然把这列删了这肯定会出问题。MDL就是防止这种情况的发生。MDL锁的加锁规则DML操作如SELECT,INSERT,UPDATE,DELETE会对涉及的表加一个MDL读锁。这个锁是共享的多个DML操作可以同时持有同一张表的MDL读锁。DDL操作如ALTER TABLE,DROP TABLE,RENAME TABLE会对涉及的表加一个MDL写锁。这个锁是排他的同一时间只能有一个DDL操作持有该表的MDL写锁。关键冲突MDL读锁与MDL写锁互斥。这就是我们开头事故的原因。一个长查询持有着MDL读锁不结束后续的DDL操作申请MDL写锁就会一直等待。更糟糕的是在DDL操作等待期间它后面所有新的、试图申请MDL读锁的DML操作也都会被阻塞这就形成了典型的“MDL锁等待链”导致雪崩效应。注意MDL锁的持有周期是事务生命周期。即使你的SELECT语句已经执行完毕但只要事务没有提交在REPEATABLE-READ隔离级别下这个MDL读锁就会一直持有。这也是为什么建议在业务中避免使用长事务并尽快提交事务的重要原因之一。2.2 表级锁简单粗暴的守护者表级锁是MySQL服务器层实现的锁与存储引擎无关。主要有两种表共享读锁LOCK TABLES table_name READ。允许其他会话加读锁或执行无锁查询但不允许加写锁。表独占写锁LOCK TABLES table_name WRITE。不允许其他会话进行任何读/写操作。在InnoDB成为绝对主流的今天我们很少会手动使用LOCK TABLES因为它的粒度太粗并发性能极差。但是在某些特定情况下MySQL会自动加表锁当InnoDB表上没有合适的索引时。例如你对一个没有索引的字段进行UPDATE ... WHERE操作InnoDB无法精确定位到行就会退而求其次锁住整个表实际上是锁住所有行效果等同表锁。执行ALTER TABLE等DDL时在等待MDL写锁之前或之后也可能涉及表锁。一个经典误区很多人认为MyISAM只支持表锁InnoDB只支持行锁。这不完全准确。MyISAM确实只有表锁但InnoDB是支持行锁和表锁共存的。例如一个ALTER TABLE操作在InnoDB表上仍然需要获取表级的排他锁。2.3 行级锁InnoDB高并发的核心武器行级锁是InnoDB存储引擎实现的也是支撑MySQL高并发的基石。它允许只锁定需要修改的行其他行依然可以被并发访问。行锁的种类更多样理解其细分类型至关重要。2.3.1 记录锁记录锁是最简单的行锁它锁住索引上的一条具体记录。例如UPDATE t SET name‘a’ WHERE id 10;如果id是主键就会在id10的索引记录上加一个记录锁。2.3.2 间隙锁这是InnoDB在可重复读隔离级别下引入的用于解决幻读问题。它锁住的是一个索引记录之间的“间隙”而不是记录本身。例如表中有id为5和10的记录执行SELECT * FROM t WHERE id BETWEEN 7 AND 15 FOR UPDATE;就会在(5, 10)和(10, ∞)这两个间隙范围上加锁。这意味着其他事务无法在这个间隙内插入新的记录比如id8。实操心得间隙锁是导致很多死锁的“元凶”。因为它的锁定范围是“开区间”两个事务可能以相反的顺序请求不同间隙的锁从而形成循环等待。在业务允许的情况下将隔离级别降为读已提交可以避免绝大部分间隙锁提升并发度但需要业务层自己处理幻读问题。2.3.3 临键锁临键锁是记录锁和间隙锁的结合。它既锁住记录本身也锁住该记录之前的间隙。可以理解为一种“左开右闭”的区间锁。例如对于唯一索引id10临键锁锁定的范围可能是(5, 10]。这是InnoDB默认的行锁算法。2.3.4 插入意向锁这是一种特殊的间隙锁表示一个事务准备在某个间隙插入记录。多个事务可以在同一个间隙上持有兼容的插入意向锁因为它们只是“意向”实际插入的位置可能不同。但是插入意向锁会与已经存在的间隙锁或临键锁互斥。例如事务A锁定了间隙(5,10)事务B想在这个间隙插入id7的记录就需要申请插入意向锁此时就会被事务A阻塞。理解这四种行锁及其互斥关系是分析复杂死锁场景的基础。它们的兼容矩阵比简单的读写锁要复杂得多。3. 锁在何处深入索引与锁的耦合关系行锁加在哪里这是一个核心问题。答案是加在索引上。更准确地说是加在满足查询条件的索引记录上。如果语句用到了哪个索引锁就加在那个索引对应的记录上。这里有几个关键场景场景一主键索引查询UPDATE user SET score100 WHERE id 1;id是主键锁直接加在主键索引id1的记录上。这是最清晰、冲突最少的情况。场景二唯一索引查询UPDATE user SET score100 WHERE email ‘aliceexample.com’;email是唯一索引锁首先加在唯一索引email‘aliceexample.com’的记录上。同时InnoDB还会去主键索引上找到对应的主键记录也加上锁。这是为了防止在通过唯一索引定位到行后该行的主键被其他事务修改。场景三非唯一索引查询UPDATE user SET score100 WHERE age 20;age是一个普通的非唯一索引。这时InnoDB会锁住所有age20的索引记录。由于是非唯一索引满足条件的记录可能有多条每条索引记录及其对应的主键记录都会被加锁。如果age20的记录有1000条就会产生至少2000个行锁索引记录主键记录。这就是为什么在非唯一索引字段上做范围更新或删除非常危险极易导致大量锁竞争甚至锁表。场景四无索引查询UPDATE user SET score100 WHERE name ‘张三’;name字段没有索引。对于InnoDB没有索引就意味着无法通过索引快速定位记录它只能进行全表扫描。在扫描过程中每一条被扫描到的记录无论是否符合WHERE条件都会被加上锁。在可重复读隔离级别下为了确保一致性还会在每条记录之间的间隙加上间隙锁。最终效果就是锁定了全表的所有记录和间隙等同于一个表级锁并发性能归零。核心避坑指南务必为你的UPDATE和DELETE语句的WHERE条件建立合适的索引。这是避免锁范围过大、提升并发能力的首要原则。即使是一个很差的索引也比没有索引要好得多。EXPLAIN命令是你的好朋友执行更新前先看看执行计划确认是否用上了索引。4. 锁的观测与实战排查当问题发生时理论懂了线上真出问题了怎么办你需要一套清晰的排查链路。我通常的排查步骤是现象定位 - 锁信息采集 - 关联分析 - 解决方案。4.1 现象定位识别锁等待业务侧反馈“卡住了”。首先连上数据库查看当前线程状态SHOW PROCESSLIST;重点关注State列。常见的锁等待状态有Waiting for table metadata lock: MDL锁等待。Waiting for table level lock: 表锁等待MyISAM或显式LOCK TABLES。State显示为updating、deleting等但长时间不变化且Info是某条DML语句很可能在等待行锁。State显示statistics、copying to tmp table等也可能是MDL锁等待的一种表现。4.2 锁信息采集使用InnoDB锁信息表MySQL提供了performance_schema库中的表来监控锁信息但需要开启相关监控器有一定性能开销。更常用的是information_schema库中的表5.7及以上版本支持较好-- 查看当前正在发生的锁等待 SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 查看当前所有持有的锁和等待的锁的详细信息 SELECT * FROM information_schema.INNODB_LOCKS; -- 注意8.0中此表已移除被performance_schema.data_locks取代 -- 在MySQL 8.0中使用以下视图 -- SELECT * FROM performance_schema.data_locks; -- 显示持有的锁 -- SELECT * FROM performance_schema.data_lock_waits; -- 显示锁等待关系通过INNODB_LOCK_WAITS你可以看到blocking_trx_id阻塞者的事务ID和waiting_trx_id被阻塞者的事务ID。再结合INNODB_TRX表查看事务详情就能定位到罪魁祸首。4.3 一个完整的死锁排查案例假设我们收到报警日志中出现Deadlock found when trying to get lock; try restarting transaction。开启死锁日志确保innodb_print_all_deadlocks ON这样死锁详情会输出到错误日志中。分析错误日志日志会记录最后一次检测到的死锁信息包括两个事务各自持有的锁、等待的锁以及被回滚的事务。格式类似LATEST DETECTED DEADLOCK ------------------------ 2023-10-27 10:00:00 0x7f123456 *** (1) TRANSACTION: TRANSACTION 1000, ACTIVE 10 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 123, OS thread handle 140123, query id 456 localhost root updating UPDATE t SET cc1 WHERE a1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 3 n bits 72 index PRIMARY of table test.t trx id 1000 lock_mode X locks rec but not gap waiting ... *** (2) TRANSACTION: TRANSACTION 1001, ACTIVE 8 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 124, OS thread handle 140124, query id 457 localhost root updating UPDATE t SET cc1 WHERE a2 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 3 n bits 72 index PRIMARY of table test.t trx id 1001 lock_mode X locks rec but not gap waiting ... *** WE ROLL BACK TRANSACTION (1)解读日志上面这个经典死锁通常是因为两个事务以相反的顺序更新了多行记录。比如事务1先锁了行A再请求行B事务2先锁了行B再请求行A。解决这类问题通常需要让业务代码以固定的顺序访问资源例如按主键ID排序后再更新。4.4 关联分析与解决拿到阻塞或死锁信息后你需要关联业务日志找到对应的代码逻辑。问自己几个问题这个事务是做什么的为什么执行这么久它的SQL语句是否走了合适的索引事务的边界是否合理能否拆分成更小的事务业务逻辑上对资源的访问顺序能否统一解决方案无外乎几种优化SQL和索引、缩短事务时间、重写业务逻辑以固定访问顺序、在可接受的情况下降低隔离级别。5. 隔离级别与锁的联动理解你的“交易规则”事务隔离级别定义了数据库处理并发读写的严格程度它直接决定了InnoDB的加锁策略。不同级别下锁的行为差异巨大。读未提交几乎不加读锁除了防止数据定义冲突的锁会读到未提交的数据脏读。生产环境严禁使用。读已提交这是Oracle等数据库的默认级别。在这个级别下普通的SELECT使用快照读不加锁除非用FOR UPDATE/LOCK IN SHARE MODE。UPDATE/DELETE语句只锁住需要修改的行没有间隙锁。这大大减少了锁冲突但引入了“不可重复读”和“幻读”的问题。可重复读这是MySQL InnoDB的默认级别。核心特点是在一个事务内多次读取同一范围的数据结果是一致的。为了实现这一点InnoDB使用了MVCC和多版本并发控制和间隙锁。普通的SELECT也是快照读不加锁。但UPDATE/DELETE/SELECT ... FOR UPDATE等当前读操作不仅会锁住记录还会锁住间隙以防止其他事务插入新行幻读。这也是锁问题最复杂的级别。串行化所有读操作都会隐式转换为SELECT ... LOCK IN SHARE MODE加上共享锁读写严重互斥性能最差一般不用。选择建议如果你的业务能接受“不可重复读”和“幻读”例如一些报表查询、实时性要求不高的统计并且追求更高的并发性能可以考虑使用读已提交隔离级别它能避免绝大部分恼人的间隙锁死锁。如果你的业务要求严格的一致性如金融交易那么默认的可重复读是更安全的选择但要求开发人员必须深刻理解间隙锁并精心设计索引和事务。个人经验我曾经将一个并发冲突严重的系统从“可重复读”降级到“读已提交”配合业务逻辑的微调例如使用乐观锁version字段系统的死锁频率从每天几次降到了几乎为零吞吐量提升了近30%。但这步操作需要完整的测试和评估确保业务逻辑在“读已提交”下依然正确。6. 意向锁表锁与行锁的沟通桥梁意向锁是InnoDB为了协调表级锁和行级锁而设计的一种表级锁。它本身并不锁定具体数据而是一种“宣告”。意向共享锁当一个事务准备给某些行加共享锁S锁之前它必须先获得该表的意向共享锁。意向排他锁当一个事务准备给某些行加排他锁X锁之前它必须先获得该表的意向排他锁。为什么需要意向锁假设没有意向锁。事务A锁定了表中的一行行级X锁。此时事务B想对整个表加一个表级X锁比如LOCK TABLES ... WRITE。事务B如何判断自己能加锁呢它必须逐行检查表中是否有行被锁定效率极低。有了意向锁事务A在加行锁前先对表加了一个IX锁。事务B申请表级X锁时发现表上已经存在IX锁而IX锁与表级X锁是互斥的于是事务B就能被快速阻塞无需遍历每一行。兼容性矩阵简化理解意向锁之间是兼容的。IS和IX可以共存因为大家只是“有意向”去锁不同的行并不冲突。意向共享锁与表级共享锁兼容但与表级排他锁互斥。意向排他锁与任何表级锁共享或排他都互斥。你几乎不需要手动操作意向锁但理解它能让你明白SHOW ENGINE INNODB STATUS输出中那些LOCK_IX、LOCK_IS的含义知道表级锁和行级锁是如何协同工作的。7. 自增锁与插入缓冲针对特殊场景的优化除了常见的锁InnoDB还有一些针对特定场景的、比较特殊的锁机制。7.1 自增锁涉及AUTO_INCREMENT列的插入操作需要获取一种特殊的表级锁——自增锁。这是为了确保并发插入时每个事务都能拿到唯一且连续的自增ID在默认“连续”模式下。在MySQL 5.1之后InnoDB提供了几种自增锁模式innodb_autoinc_lock_mode参数0传统模式所有INSERT语句都会使用表级自增锁语句执行结束后释放。最安全但并发性最差。1连续模式默认。对于能预先确定插入行数的INSERT语句如INSERT INTO ... VALUES (...), (...)使用一个轻量级的互斥量在分配ID时加锁分配完即释放不需要等到语句结束。对于INSERT ... SELECT这类不确定行数的语句仍使用表级自增锁。这是安全性和性能的折中。2交错模式。所有插入都不使用表级自增锁完全靠互斥量分配ID。性能最高但可能导致自增ID不连续并且在基于语句的复制下是不安全的。建议除非你使用行级复制并且可以接受自增ID不连续否则保持默认的innodb_autoinc_lock_mode1是最佳选择。7.2 插入缓冲严格来说插入缓冲不是一种锁而是一种为了减少随机I/O、提升插入性能的优化机制。对于非唯一的二级索引的插入InnoDB不会直接写入磁盘的索引页而是先缓存到“Change Buffer”中等到未来该索引页被读到内存时再合并进去。这减少了磁盘的随机写操作。 但这里有一个与锁相关的点因为插入缓冲延迟了索引的更新所以在事务提交后对应的二级索引变更可能并没有真正写入磁盘索引树。这不会影响数据一致性因为通过主键依然能查到最新数据。但在某些极端并发场景下如果大量事务同时提交并合并插入缓冲可能会带来短暂的I/O压力。理解MySQL的锁机制是一个从“知其然”到“知其所以然”再到“知其不得不然”的过程。它不仅仅是数据库的知识更是设计高并发、高可靠应用系统时必须考虑的一环。下次当你编写一条UPDATE语句或设计一个事务边界时不妨在脑海里过一遍这条语句会在哪些索引上加什么锁会不会和隔壁服务的事务打架这个事务持有锁的时间是不是太长了多问几个为什么很多潜在的线上问题就能被提前消灭在萌芽里。锁的世界很复杂但驾驭了它你就能真正掌控数据库的并发命脉。