MySQL 锁机制

一、为什么需要锁？

核心目的：处理数据库的 并发访问 问题，控制共享资源的访问规则，保证数据一致性。
分类 (按范围)：全局锁、表级锁、行锁。

二、全局锁 (Global Lock)

定义：对 整个数据库实例 加锁。
命令：Flush tables with read lock (FTWRL)。
效果：
- 使整个库处于 只读状态。
- 阻塞 DML (增删改)、DDL (结构变更)、更新类事务的 COMMIT。
典型场景：全库逻辑备份 (如 mysqldump)。
问题与风险：
- 主库备份：业务更新停摆。
- 从库备份：主从延迟增大。
备份为何要加锁：防止备份过程中数据发生变化，导致备份数据在逻辑上不一致（如：账户扣款了但课程没加上，或者反之）。
更好的备份方式 (针对 InnoDB 等事务引擎)：
- mysqldump --single-transaction：
  - 原理：在备份开始时开启一个事务，利用 MVCC (多版本并发控制) 和可重复读隔离级别，获取一致性视图。
  - 优点：备份期间 不阻塞 正常的 DML 操作。
  - 前提：库中 所有表 都必须使用支持事务的引擎 (如 InnoDB)。
为何 FTWRL 仍需存在：对于包含 MyISAM 等 非事务引擎 的库，--single-transaction 无效，只能用 FTWRL 保证一致性。这也是推荐使用 InnoDB 的原因之一。
FTWRL vs set global readonly=true：
- 推荐 FTWRL，原因：
  - readonly 可能被用于其他逻辑 (如判断主备)，影响面广。
  - FTWRL 在客户端异常断开时会自动释放锁，而 readonly 会一直保持，风险更高。

三、表级锁 (Table-Level Lock)

种类：表锁、元数据锁 (MDL)。
表锁 (Table Lock)：
- 语法：lock tables t1 read, t2 write; ... unlock tables;
- 效果：
  - 限制其他线程对锁定的表进行指定操作（如 read 锁阻塞写，write 锁阻塞读写）。
  - 也限制加锁线程自身：只能访问被锁定的表，且操作受限（如加了 read 锁不能写）。
- 使用场景：主要用于 MyISAM 等不支持行锁的引擎。InnoDB 引擎一般不使用，因为粒度太粗，影响并发。若代码中存在，需检查是否是引擎升级后的遗留代码。
元数据锁 (Metadata Lock - MDL)：
- 目的：保证读写的正确性，防止 DML 操作期间表结构发生变更。在访问表时 自动添加。
- 机制：
  - DML (增删改查)：加 MDL 读锁。
  - DDL (结构变更)：加 MDL 写锁。
- 兼容性：
  - 读锁之间 不互斥 (多个查询可同时进行)。
  - 读写锁之间、写锁之间互斥 (保证 DDL 安全)。
- 重要特性与风险点：
  - MDL 锁会 持有到整个事务提交后才释放。
  - 潜在问题：若一个长事务持有某表的 MDL 读锁，此时一个 DDL 操作（需要 MDL 写锁）会被阻塞；更严重的是，后续所有需要该表 MDL 读锁的新 DML 请求也会被这个 DDL 阻塞，导致表 完全无法访问，进而可能拖垮整个实例（线程耗尽）。
- 如何安全地给表加字段 (DDL)：
  - 处理长事务：在 DDL 前，通过 information_schema.innodb_trx 查找持有 MDL 锁的长事务，考虑 kill 或等待其结束。
  - 设置 DDL 等待超时：使用 ALTER TABLE ... WAIT N (指定等待 N 秒) 或 ALTER TABLE ... NOWAIT (不等待)，若无法立即获取 MDL 写锁则放弃，避免阻塞业务。（此语法在 MariaDB/AliSQL 中支持）。

四、行锁 (Row Lock) - 主要针对 InnoDB

定义：针对 数据表中行记录 的锁。实现并发控制的基础。
引擎支持：InnoDB 支持，MyISAM 不支持。
两阶段锁协议 (Two-Phase Locking - 2PL)：
- 规则：行锁在 需要时才加上，但并 不是不需要了就立刻释放，而是要 等到事务结束 (commit 或 rollback) 时才统一释放。
- 实践指导：在事务中，应将 最可能引发锁冲突、影响并发度 的操作（锁定的行） 尽量往后放，以缩短这些热点行的锁定时间。（例如：电影票业务中，更新影院总账户余额的操作应放在最后）。
死锁 (Deadlock)：
- 定义：两个或多个事务循环等待对方持有的资源，导致所有涉及的事务都无法继续执行。
- 例子：事务 A 锁住行 1 等待行 2，事务 B 锁住行 2 等待行 1。
- 处理策略：
  - 等待超时：innodb_lock_wait_timeout (默认 50s)。事务一直等待直到超时。缺点：时间长，影响业务；设置太短易误伤正常锁等待。
  - 主动死锁检测：innodb_deadlock_detect=on (默认开启)。发现死锁后，主动回滚 其中一个事务，让其他事务继续。优点：快速解决死锁。
死锁检测的性能问题：
- 高并发更新同一行 时，死锁检测会消耗大量 CPU。每个新来的被阻塞线程都要检查是否会形成死锁环，复杂度高（可理解为 O(N) 对每个等待者，总体近 O(N^2)）。导致 CPU 很高但 TPS 很低。
解决热点行更新性能问题/减少死锁影响：
- 关闭死锁检测 (不推荐)：innodb_deadlock_detect=off。风险高，可能导致大量超时，造成业务有损。仅在完全确定无死锁风险时考虑。
- 控制并发度：减少同时更新同一行的事务数量。
  - 客户端控制：难于管理。
  - 服务端控制 (更优)：通过中间件或修改 MySQL 源码，在引擎层前对相同行的更新请求进行排队。
- 业务/设计层面优化：
  - 分拆热点行：将单个热点行（如账户余额）逻辑上拆分为多行（如 10 个子账户），更新时随机选择一个。冲突概率降低为 1/N。需要仔细设计业务逻辑（如处理扣减、子账户为 0 的情况）。
  - 优化事务逻辑：遵循 2PL 指导，将锁热点资源的操作后移。

五、总结与建议

理解不同锁的适用场景和影响范围。
优先使用 InnoDB 引擎及其行锁机制以获得更好的并发性能。
利用 --single-transaction 进行 InnoDB 表的逻辑备份，避免阻塞业务。
谨慎使用 lock tables，尤其是在 InnoDB 环境下。
高度关注 MDL 锁，避免长事务阻塞 DDL，考虑使用 WAIT N/NOWAIT。
理解两阶段锁协议，优化事务内语句顺序，减少锁冲突。
了解死锁成因和检测机制，在高并发更新场景下注意其性能影响，并采取合适的策略（控制并发、优化设计）来缓解。

MySQL 锁机制

一、 为什么需要锁？

二、 全局锁 (Global Lock)

三、 表级锁 (Table-Level Lock)

四、 行锁 (Row Lock) - 主要针对 InnoDB

五、 总结与建议

一、为什么需要锁？

二、全局锁 (Global Lock)

三、表级锁 (Table-Level Lock)

四、行锁 (Row Lock) - 主要针对 InnoDB

五、总结与建议