★ FEATURED ARTICLE 1. 行级锁的本质与分类MySQL的行级锁是InnoDB引擎实现并发控制的核心机制。与表锁不同行级锁可以精确锁定数据行在保证事务隔离性的同时大幅提升并发性能。实际工作中我发现很多开发者对行级锁的理解停留在表面这会导致死锁排查和性能优化时遇到困难。InnoDB的行级锁主要分为三类记录锁Record Lock锁定索引记录本身。当SQL通过索引条件精确匹配某条记录时如WHERE id1InnoDB会给该索引记录加记录锁间隙锁Gap Lock锁定索引记录之间的间隙。例如WHERE id BETWEEN 5 AND 10会锁定(5,10)这个区间防止其他事务插入id6的新记录临键锁Next-Key Lock记录锁间隙锁的组合。InnoDB默认使用这种锁模式既锁定记录本身也锁定记录之前的间隙注意行级锁是基于索引实现的。如果查询没有使用索引InnoDB会退化为表锁这也是为什么我们强调要为查询条件建立合适的索引。2. 加锁的触发条件与规则2.1 不同隔离级别下的锁行为在RR可重复读隔离级别下InnoDB的加锁策略最为复杂普通SELECT使用一致性非锁定读MVCC不加锁SELECT...FOR UPDATE会对扫描到的记录加临键锁UPDATE/DELETE语句会根据WHERE条件加临键锁唯一索引上的等值查询会退化为记录锁而在RC读已提交级别下间隙锁会被禁用只有记录锁生效减少了死锁概率但可能出现幻读2.2 实际案例分析假设有表t(id主键, a int, b int, key idx_a(a))数据如下---------------- | id | a | b | ---------------- | 1 | 10 | 100 | | 5 | 50 | 500 | | 10 | 100 | 1000 | ----------------案例1主键等值查询-- 事务1 BEGIN; UPDATE t SET b200 WHERE id5;此时会对id5的记录加记录锁X锁其他事务无法修改这行数据。案例2非唯一索引范围查询-- 事务2 BEGIN; SELECT * FROM t WHERE a 50 FOR UPDATE;这个查询会加三个锁对a50的记录加临键锁锁定(10,50]区间对a100的记录加临键锁锁定(50,100]区间对supremum伪记录加间隙锁锁定(100,∞)区间3. 锁的兼容性与冲突检测InnoDB的锁兼容矩阵决定了并发事务能否同时持有锁。常见的锁类型包括IS意向共享锁事务打算给某些行加S锁IX意向排他锁事务打算给某些行加X锁S共享锁允许其他事务读但不能写X排他锁禁止其他事务读写兼容性规则如下表当前锁\请求锁ISIXSXIS兼容兼容兼容不兼容IX兼容兼容不兼容不兼容S兼容不兼容兼容不兼容X不兼容不兼容不兼容不兼容在实际开发中我遇到过这样一个死锁场景事务A执行SELECT * FROM t WHERE id1 FOR UPDATE持有id1的X锁事务B执行SELECT * FROM t WHERE id2 FOR UPDATE持有id2的X锁事务A尝试执行SELECT * FROM t WHERE id2 FOR UPDATE等待事务B事务B尝试执行SELECT * FROM t WHERE id1 FOR UPDATE等待事务A这种交叉等待就形成了死锁。InnoDB会检测到这种情况并回滚其中一个事务。4. 锁的监控与性能优化4.1 锁等待监控通过以下命令可以查看当前锁状态-- 查看锁等待 SELECT * FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE %lock%; -- 查看InnoDB锁信息 SELECT * FROM information_schema.INNODB_TRX; SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS;4.2 减少锁冲突的实践技巧根据我的项目经验优化锁竞争可以从以下几个方面入手索引设计优化确保查询使用合适的索引避免全表扫描在频繁更新的列上避免使用长字符串索引考虑使用覆盖索引减少回表操作事务设计原则尽量缩短事务执行时间避免在事务中进行网络IO等耗时操作大事务拆分为小事务分批处理SQL优化技巧精确指定查询条件缩小锁定范围对于批量更新考虑使用LIMIT分批提交在RC隔离级别下使用SELECT...FOR UPDATE NOWAIT避免长时间等待架构层面优化热点数据考虑使用缓存减少DB压力读写分离分散负载对必须高频更新的计数器类数据可以使用乐观锁5. 特殊场景下的锁行为5.1 无索引查询的锁升级当查询无法使用索引时InnoDB会进行锁升级。例如-- b列没有索引 UPDATE t SET a20 WHERE b500;这个语句会导致全表扫描并对所有扫描到的记录加锁实际是给整个表加锁。我在一次性能优化中就发现这样的案例一个简单的UPDATE语句导致整个系统卡顿原因就是缺失了b列的索引。5.2 插入意向锁Insert Intention Lock这是一种特殊的间隙锁在INSERT操作前设置。例如-- 事务A BEGIN; SELECT * FROM t WHERE id BETWEEN 5 AND 10 FOR UPDATE; -- 加(5,10]的临键锁 -- 事务B BEGIN; INSERT INTO t VALUES(7, 70, 700); -- 需要获取(5,10)的插入意向锁此时事务B会被阻塞直到事务A提交。插入意向锁的存在是为了提高并发插入性能它允许多个事务同时向同一个间隙插入不同的记录。5.3 自增锁AUTO-INC Lock这是一种特殊的表级锁用于保证自增ID的唯一性。在MySQL 8.0之前这个锁会在整个INSERT语句执行期间持有。从8.0开始引入了轻量级的自增锁机制大大提升了并发插入性能。6. 实战中的锁问题排查6.1 死锁日志分析当发生死锁时InnoDB会将相关信息写入错误日志。例如LATEST DETECTED DEADLOCK ------------------------ 2023-01-01 12:00:00 *** (1) TRANSACTION: TRANSACTION 1000, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1, OS thread handle 12345, query id 100 localhost root UPDATE t SET b200 WHERE a50 *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 10 page no 3 n bits 72 index idx_a of table test.t trx id 1000 lock_mode X locks rec but not gap *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 4 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 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 2, OS thread handle 12346, query id 101 localhost root UPDATE t SET a60 WHERE id5 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table test.t trx id 1001 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 3 n bits 72 index idx_a of table test.t trx id 1001 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2)这个日志显示两个事务互相持有对方需要的锁形成了死锁环。InnoDB选择回滚事务(2)来解除死锁。6.2 锁等待超时处理当锁等待超过innodb_lock_wait_timeout默认50秒时会报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction处理这类问题的步骤通过SHOW PROCESSLIST找到阻塞的事务分析锁等待关系优化事务设计或调整超时时间7. MySQL 8.0的锁优化改进MySQL 8.0在锁机制方面做了多项改进原子DDLDDL操作现在具有原子性减少了元数据锁的持有时间自增锁优化采用轻量级锁机制提升并发插入性能跳过锁等待新增NOWAIT和SKIP LOCKED语法-- 如果记录被锁立即返回错误 SELECT * FROM t WHERE id1 FOR UPDATE NOWAIT; -- 跳过被锁的记录 SELECT * FROM t FOR UPDATE SKIP LOCKED;性能Schema增强提供了更详细的锁监控指标在实际项目中这些新特性可以帮助我们更好地处理高并发场景。例如使用SKIP LOCKED可以实现高效的作业队列处理系统。
阅读完成 · 觉得有帮助？
