文章

面试题学习笔记 | MySQL 锁机制总结

Posted 25 days ago Updated 25 days ago
By nx
11~15 min read

MySQL 中有哪些锁类型？

在 MySQL 中，锁机制可以分为两大类：共享锁（Shared Lock，简称 S 锁）和排它锁（Exclusive Lock，简称 X 锁）。这些锁是用来控制多个事务并发访问同一数据的方式

共享锁（S 锁）与排它锁（X 锁）

  • S 锁（共享锁）：当一个事务执行读取操作时，它会获取共享锁。多个事务可以同时获取共享锁，且不会相互阻塞。这意味着多个事务可以同时读取数据，但不能修改

  • X 锁（排它锁）：当一个事务需要修改数据时，它会获取排它锁。排它锁是独占的，一个事务获得排它锁后，其他事务无法再获得该数据的任何锁，必须等待

需要注意的是：

  • 共享锁（S 锁）之间不会冲突

  • 排它锁（X 锁）之间会发生冲突

  • 共享锁与排它锁之间也会发生冲突

锁的应用场景：

  • SELECT ... LOCK IN SHARE MODE：用于在查询时加共享锁，保证读取数据时其他事务不能修改数据

  • SELECT ... FOR UPDATE：用于在查询时加排它锁，确保数据在读取过程中不可被其他事务修改

表锁与行锁

  • InnoDB 支持 行锁 和 表锁

  • MyISAM 仅支持 表锁

示例：

  • LOCK TABLES abc READ：为 abc 表加共享锁（S 锁）

  • LOCK TABLES abc WRITE：为 abc 表加排它锁（X 锁）

通常来说，我们不会使用表锁，因为它会影响性能。在大多数情况下，表锁只在 DDL 操作（如 ALTER TABLE）时使用，目的是防止在修改表结构时发生错误。为了优化，MySQL 提供了一种名为 MDL（Metadata Locks） 的锁机制，用于确保 DDL 和 DML 操作的互斥

元数据锁（MDL）

元数据锁分为读锁和写锁：

  1. MDL_SHARED（读锁）：当事务需要读取表的元数据时（如执行 SELECT 操作），会获取读锁。多个事务可以同时持有读锁，互不阻塞

  2. MDL_EXCLUSIVE（写锁）：当事务需要修改表的元数据时（如执行 ALTER TABLE 操作），会获取写锁。写锁会阻塞其他事务对该表元数据的任何操作，确保对元数据的独占访问

元数据锁的作用是防止并发的 DDL 和 DML 操作冲突，确保表的元数据一致性

意向锁（Intention Locks）

意向锁是 InnoDB 引入的表级锁，用于优化表锁与行锁的冲突：

  • IS（Intention Shared Lock）：表示表中某些行上有共享锁（S 锁）

  • IX（Intention Exclusive Lock）：表示表中某些行上有排它锁（X 锁）

在需要对表施加共享锁或排它锁时，首先会在表级别加上相应的意向锁。这种设计可以避免逐行检查是否有行锁，提升性能

行锁类型

除了表锁，MySQL 还支持几种行级锁：

  • 记录锁（Record Locks）：锁定具体的索引记录。通常用于防止其他事务修改或删除该记录

  • 间隙锁（Gap Locks）：锁住记录之间的“间隙”，以防止其他事务插入数据到这些间隙中。间隙锁不锁定实际数据行，仅锁定空白位置

  • 临建锁（Next-Key Locks）：结合了记录锁和间隙锁，锁住记录和其相邻的间隙，防止幻读

插入意向锁（Insert Intention Locks）

插入意向锁是与间隙锁相关的一种锁，但它并不直接锁定间隙，而是表示事务打算在某个间隙处插入数据。如果间隙被锁定，事务会等待该锁释放。插入意向锁之间不会互相阻塞，因为它们的目的仅仅是等待间隙的释放

Auto-Inc 锁

Auto-Inc 锁是针对自增列的特殊表级锁。在插入自增列数据时，系统会加上 Auto-Inc 锁，用于分配自增值。该锁在插入操作结束后会被释放。MySQL 5.1.22 版本及之后，通过互斥量（mutex）替代了 Auto-Inc 锁，性能得到提升。互斥量在获取自增值后立即释放锁，比 Auto-Inc 锁更高效。

MySQL 还提供了 innodb_autoinc_lock_mode 配置，控制自增锁的行为。该配置有三个值：

  • 0：使用 Auto-Inc 锁

  • 1（默认）：对于已知插入行数的情况使用互斥量，对于未知插入行数的情况使用 Auto-Inc 锁

  • 2：仅使用互斥量

MySQL 中如果发生死锁应该如何解决？

死锁是指多个事务在并发执行时相互等待，导致所有事务无法继续执行。死锁的产生通常需要满足以下四个条件：

  1. 互斥条件：资源不能被多个事务共享

  2. 请求并保持条件：事务已持有至少一个资源并请求更多资源

  3. 不剥夺条件：事务已持有的资源不会被强制剥夺

  4. 循环等待条件：事务之间形成循环等待

任何一个条件被打破，死锁问题就能得到解决。为了解决 MySQL 中的死锁问题，通常采用以下三种方案：

  1. 自动死锁检测：MySQL 会自动检测死锁并回滚持有最少资源的事务，以解除死锁

  2. 设置锁等待超时：通过设置 innodb_lock_wait_timeout，当锁等待时间超过指定阈值时自动回滚事务

  3. 手动杀死事务：通过查看死锁日志，可以手动定位并杀死发生死锁的事务

扩展：如何避免死锁或降低死锁发生的概率？

  1. 避免长时间的大事务：大事务占用锁的时间长，拆分大事务可以减少死锁的风险

  2. 调整事务的隔离级别：使用 读已提交 隔离级别可以减少间隙锁的使用，从而降低死锁发生的概率

  3. 合理调整锁的申请顺序：确保事务在执行时按照固定的顺序获取锁，这样可以避免循环等待

  4. 合理使用索引：索引可以帮助 MySQL 锁定更精确的记录，减少全表扫描，从而减少死锁的可能性

  5. 启用死锁检测：开启死锁检测机制，并合理设置锁的等待超时值，帮助系统自动处理死锁

知识笔记, 面试求职
后端 中间件 服务器
License:  CC BY 4.0