mysql锁机制

2025-03-13 本文已影响0人 _刘小c

MySQL 锁机制与事务控制

1. MySQL 锁分类

1.1 全局锁（Global Lock）

使用 FLUSH TABLES WITH READ LOCK (FTWRL) 命令。
作用于整个数据库，常用于 逻辑备份，但会阻塞所有写入操作。
InnoDB 中，一般使用 --single-transaction 进行逻辑备份，减少对业务的影响：

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

mysqldump --single-transaction -u root -p mydb > backup.sql

如果要全库只读，可以使用 set global readonly=true 的方式吗？确实 readonly 方式也可以让全库进入只读状态，但还是会建议用 FTWRL 方式，主要有两个原因：

在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议使用。
在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方法，一个库被全局锁上以后，要对里面任何一个表做加字段操作，都是会被锁住的。

1.2 表级锁（Table Lock）

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

通过 LOCK TABLES ... READ/WRITE 语句加锁。
读锁（READ LOCK）：允许多个会话读取，不允许写入。
写锁（WRITE LOCK）：独占表，其他会话不能读写。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

MDL 锁（Metadata Lock）
- 作用： 保护表结构，防止 DDL 与 DML 冲突。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
- 读锁（MDL-S）：允许并发查询，不允许修改表结构。
- 写锁（MDL-X）：阻塞所有查询和写入。
- 避免长事务占用 MDL 锁，导致 DDL 阻塞：
```
SELECT * FROM performance_schema.metadata_locks;
KILL <thread_id>;
```

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

1.3 行级锁（Row Lock）（InnoDB 专属）

作用于 特定行，并发性能高。
共享锁（S 锁）：允许多个事务读取数据。
排他锁（X 锁）：独占数据，防止其他事务读写。

2. InnoDB 事务锁机制

2.1 间隙锁（Gap Lock）

锁住 索引区间，防止并发插入，避免幻读。
仅适用于 可重复读（REPEATABLE READ）。

2.2 提链锁（Next-Key Lock）

间隙锁（Gap Lock）+ 行锁（Record Lock） 组合，锁住索引范围。
适用于 SELECT ... FOR UPDATE，防止幻读。

2.3 自增锁（AUTO-INC Lock）

作用：保证 AUTO_INCREMENT 并发安全，防止主键冲突。
默认行为：INSERT 语句执行时，锁住自增计数器。

3. 两阶段提交（2PC）

3.1 介绍

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

作用： 确保分布式事务一致性，防止数据不一致问题。
适用于： MySQL XA 事务、分布式数据库。

3.2 执行过程

准备阶段（Prepare）：所有参与者预执行 SQL，记录 undo log，不提交。
提交阶段（Commit）：
- 所有参与者成功 → 事务提交。
- 任一失败 → 事务回滚。

4. MySQL 死锁与死锁检测

4.1 死锁的常见原因

事务访问资源顺序不同（A 先锁 X，B 先锁 Y，导致循环等待）。
索引未命中（导致全表扫描，增加锁冲突）。
Next-Key Lock 造成锁冲突（可重复读级别）。
长事务持有大量行锁（影响并发性能）。

4.2 MySQL 如何处理死锁？

当出现死锁以后，有两种策略：

可以通过参数 innodb_lock_wait_timeout 来设置，直接进入等待，直到超时。
发起死锁检测，innodb_deadlock_detect 是默认开启的。发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。
在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。
所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

你可以想象一下这个过程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。那如果是我们上面说到的所有事务都要更新同一行的场景呢？
每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

如何解决由热点行更新导致的性能问题呢？

问题的症结在于，死锁检测要耗费大量的 CPU 资源。

一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

另一个思路是控制并发度。根据上面的分析，你会发现如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。我见过一个应用，有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。

因此，这个并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

还有一个思路是考虑通过将一行改成逻辑上的多行来减少锁冲突

4.3 如何避免死锁？

✅ 最佳实践：

统一事务访问顺序，避免循环等待。
优化索引，减少表扫描。
减少事务持锁时间，及时提交。
使用行锁代替表锁，降低锁粒度。
避免大事务，拆分为小事务。

5. 常见锁相关 SQL

5.1 查询锁信息

SHOW ENGINE INNODB STATUS;

5.2 查看等待事务

SELECT * FROM information_schema.innodb_trx;

5.3 终止死锁事务

KILL <thread_id>;

6. 总结

主题	关键点
全局锁	主要用于逻辑备份，InnoDB 推荐 `--single-transaction`。
表级锁	影响整个表，适用于 MyISAM，包含 MDL 锁。
行级锁	InnoDB 专属，适用于高并发。
两阶段提交	适用于分布式事务，MySQL XA 事务支持。
死锁原因	资源访问顺序不同、索引未命中、Next-Key Lock 影响、大事务锁等待。
死锁检测	InnoDB 自动检测死锁，可能导致 CPU 过载，超时回滚可缓解。
如何避免死锁	事务顺序统一、优化索引、减少事务持锁时间、避免大事务。

🔹 在高并发环境下，优化事务逻辑和索引，合理设计锁策略，是防止死锁的关键！如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。但是，调整语句顺序并不能完全避免死锁。所以还引入了死锁和死锁检测的概念，以及提供了三个方案，来减少死锁对数据库的影响。减少死锁的主要方向，就是控制访问相同资源的并发事务量。 🚀