MySQL 全局锁与表锁的原理及备份方案

全局锁：概念、实现与应用

第1步：执行全局读锁实现数据一致性备份

目的：对整个数据库实例施加只读限制，确保多表备份时的数据时间点一致性。

入口：MySQL 命令行客户端或支持 SQL 执行的接口。

动作：执行以下命令锁定所有库和表：

FLUSH TABLES WITH READ LOCK;

完成状态：此时整个数据库处于只读状态。任何试图进行数据更新（增删改）、数据定义（建表/修改结构）或提交包含更新操作的事务都会被阻塞，直到锁被释放。

第2步：解锁恢复业务写入

动作：当备份操作完成或脚本结束时，执行解锁命令：

UNLOCK TABLES;

注意：若客户端异常断开，MySQL 会自动释放该全局锁，恢复数据库的可写状态，这是其相较于单纯修改配置的重要安全特性。

为什么使用全局锁而非简单只读模式？

虽然可以通过设置全局变量让数据库只读，但在实际运维中，直接修改配置存在风险：

• set global readonly=true;：一旦客户端异常断开或管理失误，数据库可能长时间处于不可写状态，且不会自动恢复。
• 全局锁优势：具有自我保护机制，客户端异常时锁会自动释放，避免了因故障导致的持续停机。

应用场景：主要用于需要保证“逻辑时间点”一致性的全库逻辑备份。若不 locking，例如先备份了账户余额表，中间用户进行了消费，再备份了商品表，会导致备份数据中余额未扣除但商品已生成，造成数据不一致。

不加锁备份的后果示例

假设购物场景中，备份顺序为：账户表 → 用户购买行为 → 商品表。若不加锁，可能出现用户账号余额未扣减却获得商品的逻辑错误，导致资损或数据混乱。全局锁确保了备份期间无更新操作，保证了各表数据的逻辑一致性。

表锁与 MDL 锁：并发控制与潜在风险

显式表锁的操作方法

动作：对特定表施加锁，不影响其他表的写操作：

LOCK TABLES tbl_name READ; -- 或 WRITE

注意：现代 MySQL 版本中，InnoDB 引擎优先使用行锁，显式表锁通常用于兼容旧版本或在特定并发控制场景下使用。解锁方式同样为 UNLOCK TABLES;。

隐式 MDL（元数据锁）的作用

原理：访问表时自动添加。查询时加 MDL 读锁，DDL 结构变更时加 MDL 写锁。目的是防止表结构变更（如删除列）导致正在进行的查询出现字段错位等错误。

关键限制：MDL 锁的粒度是表级别，且在事务内，MDL 锁会一直保持到事务提交才释放，而非语句结束即释放。

常见阻塞问题：长事务导致的死锁效应

当存在长事务持有 MDL 读锁时，新来的 DDL 请求需要 MDL 写锁，将被阻塞。更严重的是，后续新的普通查询也需要申请 MDL 读锁，但它们会被正在等待写锁的请求阻塞（部分 MySQL 版本策略）。这会导致该表完全不可读写，连接数迅速耗尽，引发雪崩。

解决方案：安全的表结构变更（DDL）

为避免上述阻塞，在执行 ALTER TABLE 等结构变更时，建议采取以下策略：

1. 检查长事务：执行 DDL 前，排查是否有未提交的长事务占用表锁。如有，可考虑暂停 DDL 或终止长事务。
2. 设置 DDL 等待超时：在 ALTER TABLE 语句中指定锁等待超时时间（如通过参数或重试机制）。如果在规定时间内未能获取 MDL 写锁，则主动放弃此次变更，避免无限期阻塞后续业务请求。
3. 错峰操作：在业务低峰期执行 DDL，减少竞争概率。

以上就是关于 MySQL 全局锁和表锁的详细技术解析，更多关于 MySQL 高级特性及性能优化的资料请关注本站其它相关文章！