MySQL 锁机制详解

数据库锁设计的初衷是为了处理并发访问时的数据一致性和完整性问题。根据加锁的范围，MySQL 中的锁大致可以分为全局锁、表级锁和行锁三类。

1. 全局锁 (Global Lock)

• 定义: 对整个数据库实例加锁。
• 命令: Flush tables with read lock (FTWRL)。
• 效果: 执行后，整个数据库实例处于只读状态。其他线程的数据更新语句（DML）、数据定义语句（DDL）以及更新类事务的提交语句（如 COMMIT）都将被阻塞。
• 典型场景: 全库逻辑备份 (mysqldump)。为了保证备份数据的一致性，需要在备份期间阻止数据修改。
• 与 set global readonly=true 的区别:
  • 范围: FTWRL 仅阻塞 DML 和 DDL，允许正常的读操作；readonly=true 会限制所有更新操作，包括来自具有 SUPER 权限用户的更新（除非他们显式设置 super_read_only=off，MySQL 8.0 引入）。
  • 权限: FTWRL 需要较高的权限（通常是 RELOAD 或 FLUSH_TABLES）；readonly=true 需要 SUPER 权限。
  • 异常处理: 如果发出 FTWRL 的客户端连接断开，MySQL 会自动释放全局锁；如果设置 readonly=true 后客户端异常断开，数据库会一直保持只读状态，风险较高。
  • 内部逻辑: readonly 标志有时会被系统内部（如主从复制）用于判断库的角色，修改它可能影响系统行为。
  • 推荐: 在备份场景下，推荐使用 FTWRL。
• InnoDB 备份: 对于支持事务的 InnoDB 引擎，进行逻辑备份时，推荐使用 mysqldump 的 --single-transaction 参数。它利用 MVCC 在备份开始时创建一个一致性快照，从而避免了加全局锁，允许备份期间正常的读写操作。

2. 表级锁 (Table-level Lock)

表级锁锁定整个表。开销小，加锁快；但粒度大，并发度低。主要包括表锁和元数据锁 (MDL)。

2.1 表锁 (Table Lock)

• 语法:
  • 加锁: LOCK TABLES table_name [READ | WRITE], ...
  • 解锁: UNLOCK TABLES
• 类型:
  • 读锁 (READ Lock): 加读锁的线程可以读取该表，其他线程也可以读取该表，但不能写入。当前线程也不能写入该表。
  • 写锁 (WRITE Lock): 加写锁的线程可以读写该表，其他线程既不能读也不能写。
• 引擎: MyISAM 引擎主要使用表锁。InnoDB 引擎通常使用行锁，但在特定情况下（如 LOCK TABLES 语句或某些内部操作）也可能使用表锁。
• 注意: LOCK TABLES 不仅限制其他线程，也限制了当前线程。例如，加了 READ 锁后，当前线程也无法对该表执行写操作。UNLOCK TABLES 会释放当前会话持有的所有表锁。

2.2 元数据锁 (Meta Data Lock, MDL)

• 引入: MySQL 5.5 引入。
• 目的: 保护表的元数据（表结构）信息，保证读写的正确性，防止在查询或更新操作期间表结构被修改。
• 机制: 自动加锁。当对一个表进行操作（CRUD 或 DDL）时，会自动加上 MDL。
• 类型与兼容性:
  • MDL 读锁 (SHARED_READ / SHARED_WRITE): 执行普通 CRUD 操作时加。读锁之间不互斥，多个线程可以同时对一张表进行增删改查。
  • MDL 写锁 (EXCLUSIVE): 执行表结构变更操作（DDL，如 ALTER TABLE）时加。写锁与读锁、写锁与写锁之间都互斥。
• 生命周期: MDL 锁在事务提交或回滚后才释放，而不是语句执行完就释放。
• 潜在问题: 阻塞 DDL。如果一个表上有长事务在执行（持有 MDL 读锁），那么尝试对该表进行 DDL 操作（需要 MDL 写锁）就会被阻塞。更严重的是，一旦 DDL 被阻塞，后续所有对该表的新请求（需要 MDL 读锁）也会被阻塞，因为写锁优先级更高且与读锁互斥，导致连接堆积，可能拖垮数据库。
• 安全地执行 DDL:
  1. 监控和处理长事务: 在执行 DDL 前，检查是否有涉及该表的长事务 (information_schema.innodb_trx)，考虑 kill 掉或等待其结束。
  2. 设置 DDL 等待超时: 在 ALTER TABLE 语句中使用 LOCK=NONE (部分 DDL 支持在线操作) 或设置 lock_wait_timeout (控制获取 MDL 锁的等待时间)，避免长时间阻塞。
  3. 使用在线 DDL 工具: 如 pt-online-schema-change 或 gh-ost，它们通过创建临时表、数据同步和重命名的方式，在不阻塞读写的情况下完成表结构变更。MySQL 8.0 对在线 DDL 提供了更好的原生支持。

3. 行锁 (Row-level Lock)

• 定义: 锁定表中的特定行记录。
• 引擎: InnoDB 引擎支持行锁，MyISAM 不支持。
• 优点: 锁粒度最小，并发度最高。
• 缺点: 开销大，加锁慢，可能出现死锁。
• 实现: InnoDB 的行锁是通过给索引项加锁来实现的。这意味着：
  • 只有通过索引条件检索数据，InnoDB 才会使用行级锁，否则会使用表锁（或扫描整个表并锁定所有行）。
  • 不同的索引会锁定不同的索引项。

3.1 两阶段锁协议 (Two-Phase Locking, 2PL)

• 定义: 在 InnoDB 事务中，行锁是在需要的时候才加上（例如执行 UPDATE 或 SELECT ... FOR UPDATE 时），但并不是不需要了就立刻释放，而是要等到事务结束（COMMIT 或 ROLLBACK）时才统一释放。
• 影响: 如果事务中需要锁定多行，持有锁的时间会比较长。
• 优化建议: 如果事务需要锁定多个资源，并且可能存在锁冲突，应尽量将最可能引发冲突、影响并发度的锁操作放到事务的最后执行，以缩短该锁的持有时间，提高并发性。

3.2 死锁 (Deadlock)

• 定义: 两个或多个事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象，若无外力干涉它们都将无法推进下去。
• 示例:
  • 事务 A 锁定了行 1，然后尝试锁定行 2。
  • 事务 B 锁定了行 2，然后尝试锁定行 1。
• 处理策略:
  1. 等待超时: 一个事务等待锁超过一定时间后自动放弃。由参数 innodb_lock_wait_timeout 控制（默认 50 秒）。缺点是无法区分死锁还是单纯的锁等待，且等待时间可能过长。
  2. 死锁检测 (Deadlock Detection): InnoDB 主动检测循环依赖的锁等待。由参数 innodb_deadlock_detect 控制（默认开启 on）。一旦检测到死锁，InnoDB 会选择一个回滚代价最小的事务进行回滚，让其他事务继续执行。
• 死锁检测的性能问题: 当并发非常高，大量线程同时竞争少量热点行时，死锁检测本身会消耗大量 CPU 资源（检测复杂度接近 O(n^2)）。
• 处理高并发死锁:
  • 关闭死锁检测 (不推荐): set global innodb_deadlock_detect=off;。这会导致依赖 innodb_lock_wait_timeout 超时，可能引发大量超时错误，影响业务。
  • 控制并发度:
    • 在应用层或中间件层限制对热点资源的并发访问。
    • 在数据库层，可以考虑修改 MySQL 源码或使用特定补丁，对同一行的更新请求进行排队。
  • 优化业务逻辑:
    • 减少锁冲突: 调整事务逻辑，按固定顺序访问资源；将大事务拆分为小事务。
    • 降低锁粒度: 如果业务允许，将对一行的更新分散到多行（例如，账户余额更新可以拆分到多个子账户记录）。

---