第七讲总结 MySQL 行锁深度剖析：原理、问题与优化策略

MySQL 行锁深度剖析：原理、问题与优化策略

在数据库领域，MySQL 的行锁机制对于保证数据的一致性和完整性起着关键作用，但其复杂性也给数据库性能和并发处理带来了挑战。今天我们就来深入探讨 MySQL 行锁，从其基本概念到两阶段锁协议，再到死锁及死锁检测，以及如何优化行锁对性能的影响。

一、行锁与两阶段锁协议

（一）行锁的基本概念

行锁是针对数据表中行记录的锁，不同引擎对行锁的支持情况不同，InnoDB 支持行锁，而 MyISAM 不支持（使用表锁）。这意味着在 InnoDB 中，事务可以更细粒度地控制对行记录的并发访问，提高业务并发度。

（二）两阶段锁协议

例子：事务B的update语句执行时会是什么现象呢？假设字段id是表t的主键。**B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。**在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释事务中，而是要等到事务结束时才释放。这个就是两阶段锁协议。

1. 锁的获取与释放时机
   在 InnoDB 事务中，行锁在需要时获取，但直到事务结束（commit 或 rollback）才释放，这就是两阶段锁协议。
2. 对事务设计的影响
   两阶段锁协议指导我们在设计事务时，应将最可能造成锁冲突、影响并发度的锁尽量往后放。例如在电影票在线交易业务中，涉及从顾客账户扣除票价、给影院账户增加票价和记录交易日志三个操作，应将更新影院账户余额（最易冲突）的操作放在最后，以减少锁等待时间，提升并发度。

二、死锁与死锁检测

例子：事务A一直等待事务B释放id=2的行锁，事务B一直等待事务A释放id=1的行锁，形成死锁。解决办法有两个：

• 通过设置innodb_lock_wait_timeout参数设置等待超时时间，如果超过这个时间就退出事务
• 通过将innodb_deadlock_detect设置为on，一但发生死锁，主动回滚某个事务，让其他事务得以执行
  

（一）死锁的产生

当并发事务中不同线程出现循环资源依赖，彼此等待对方释放资源时，就会形成死锁。例如事务 A 等待事务 B 释放 id=2 的行锁，而事务 B 等待事务 A 释放 id=1 的行锁。

（二）死锁的处理策略

1. 等待超时
   通过参数 innodb_lock_wait_timeout 设置超时时间（默认 50s），超时后线程退出，其他线程可能继续执行。但对于在线服务，50s 的等待时间往往过长。
2. 死锁检测
   开启 innodb_deadlock_detect（默认开启），当发生死锁时主动回滚死锁链条中的某一事务，使其他事务继续执行。然而，死锁检测在高并发场景下（如大量线程同时更新同一行）会消耗大量 CPU 资源，影响数据库性能。

（三）热点行更新导致的性能问题及解决方案

1. 关闭死锁检测（风险操作）
   若能确保业务无死锁，可临时关闭死锁检测，但这可能导致大量超时，影响业务正常运行。
2. 控制并发度

• 客户端控制（不可行）：在客户端做并发控制在多客户端场景下效果不佳，如 600 个客户端即使每个控制到 5 个并发线程，汇总到数据库服务器后并发数仍可能很高。
• 服务端控制：在数据库服务端（中间件或 MySQL 源码中）对相同行的更新进行排队，可降低死锁检测成本。

1. 优化设计
   将一行改为逻辑上的多行，如影院账户余额放在多条记录上，每次随机选择一条记录操作，降低锁冲突概率，减少死锁检测的 CPU 消耗，但需考虑业务逻辑（如退票时部分行记录变为 0 的处理）。

三、总结与思考

MySQL 的行锁机制在保证数据一致性的同时，其两阶段锁协议和死锁问题需要我们在事务设计和数据库优化中谨慎处理。通过合理安排事务语句顺序、选择合适的死锁处理策略以及优化业务设计，我们可以有效减少锁冲突，提升数据库性能和业务并发度。

思考问题

如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

1. 直接执行 delete from T limit 10000;
2. 在一个连接中循环执行 20 次 delete from T limit 500;
3. 在 20 个连接中同时执行 delete from T limit 500。

第二种方式是相对较好的。 第一种方式（即：直接执行delete from T limit 10000）里面，单个语句占用时间长，锁的时间也 比较长；而且大事务还会导致主从延迟。 第三种方式（即：在20个连接中同时执行delete from T limit 500），会人为造成锁冲突。

希望通过这篇博客，能让你对 MySQL 行锁有更深入的理解，在实际数据库操作和优化中更加得心应手。感谢阅读，如有疑问或建议，欢迎交流！