文章

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

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