MySQL&InnoDB锁机制全面解析

目录

*
– 一、前言
– 二、锁的类型
–
+ 2.1 全局锁
+ 2.2 表级锁
+
* 2.2.1 表锁
* 2.2.2 元数据锁（Meta Data Locks）
* 2.2.3 自增列锁（AUTO-INC Locks）
* 2.2.4 意向锁 （Intention Locks）
+ 2.3 行级锁
+
* 2.3.1 Record Locks
* 2.3.2 Gap Locks
* 2.3.3 Next-Key Locks
* 2.3.4 插入意向锁（Insert Intention Locks ）
– 三、死锁
– 四、小结

一、前言

数据库使用锁是为了支持对共享资源的并发访问，同时保证数据的完整性和一致性。其中，MySQL在Server层和InnoDB引擎设计了多种类型的锁机制，用于实现不同场景下的并发控制，下面我们分析一下这些锁的定义和使用场景。

二、锁的类型

作用范围划分

• 全局锁
• FTWRL(Flush tables with read lock)
• 表级锁
• 元数据锁MDL（meta data lock）
• 表锁
• 意向锁
• AUTO-INC Locks
• 行级锁
• Record Locks
• Gap Locks
• Next-Key Locks
• Insert Intention Locks

权限互斥划分

• 共享锁
• 意向共享锁IS
• 表共享锁
• 行共享锁
• 排它锁
• 意向排它锁IX
• 表排它锁
• 行排它锁

2.1 全局锁

FLUSH TABLES WITH READ LOCK: Closes all open tables and locks all tables for all databases with a global read lock.

This operation is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.

全局锁意味着对整个数据库实例加上锁。通常使用的是全局读锁——Flush tables with read lock (FTWRL)。
使用这个命令，可以使整个库处于只读状态，其他的线程无论使用DML、DDL甚至是事务的提交语句都会无法正常执行。

对整个数据库进行逻辑备份，锁定所有表数据，保证数据的一致性。

问题

但是在进行备份时使用FTWRL的全局锁方案有比较严重的缺陷：

• 如果对主库进行备份，主库在整个备份期间不能进行任何数据更新操作，业务无法正常进行，这是不可接受的。
  
  [En]

  if the backup is carried out on the main database, the main database cannot perform any data update operation during the whole backup period, and the business cannot proceed normally, which is unacceptable.*

• 如果是在从库上进行备份，整个备份期间从库都不能执行主库同步过来的 binlog，会直接导致主从延迟。

这个方案一般会使用在MyISAM 这种不支持事务的引擎，而对于InnodDB来说，可以在主从备份时使用mysqldump 参数–single-transaction开启一个事务， 利用MVCC的特性，拿到一致性视图数据，保证数据的一致性和业务正常运行。

2.2 表级锁

2.2.1 表锁

表锁通常指的是 表级别的S锁和X锁，命令是 lock tables … read/write。 当使用lock tables … read时，任何线程对该表进行DDL和DML都会失败；使用lock tables … write时,只允许当前持有表锁的线程才能读和写该表。

对于支持行锁的InnoDB引擎来说，一般不会使用表级别的S锁和X锁，因此显得比较”鸡肋”。
而实际项目过程中，经常会有这样的场景，在对一个表进行DDL表结构变更时，对表记录的增删改查操作会被阻塞；反之对表数据进行增删改查时，也不允许执行表结构变更，如果不使用表锁怎么实现呢？答案是： 通过元数据锁进行控制。

2.2.2 元数据锁（Meta Data Locks）

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).

Meta Data Lock 简称MDL，是在MySQL server层使用的一种表级别锁，并不是InnoDB引擎中实现的。使用时不需要显式声明

• 当对表进行增删改查操作的时候，会自动加 MDL 读锁；
• 当要对表做结构变更操作的时候，会自动加 MDL 写锁。

读读共享，因此可以同时对一张表进行增删改查； 读写互斥，写写互斥，多个线程同时修改表结构时，需要排队等待执行。保证表结构变更操作的安全性。

元数据锁的兼容性如下：

兼容性MDL 读锁MDL 写锁 MDL 读锁 兼容 不兼容 MDL 写锁 不兼容 不兼容

2.2.3 自增列锁（AUTO-INC Locks）

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

AUTO-INC锁是一种特殊的表级锁，当表使用了AUTO_INCREMENT列时，插入数据时需要获取AUTO-INC锁。AUTO-INC锁是作用范围是语句级别，也就是说当执行完成插入语句后，哪怕整个事务还没结束，AUTO-INC锁也会被释放。因此会出现：一个事务在持有AUTO-INC锁进行插入操作时，其他事务的插入操作就会被阻塞，以此来保证自增值是连续的。

问题

使用AUTO-INC Locks会出现这样的问题：如果一个插入语句执行过长（比如insert … select大数据量插入），会导致后面的插入语句 阻塞时间久，整体性能降低。

解决方案

所以MySQL InnoDB引擎还会采用另一种 轻量级锁（互斥量）的方式，在执行插入语句之前先获取该轻量级锁，生成AUTO_INCREMENT的值后就释放锁，不需要等到插入语句执行完成后才释放。这种方式会大大提高AUTO_INCREMENT值插入的性能，但是也会带来的问题是—— 并发时事务的自增列值是不连续的，主从复制时可能是不安全的。

使用innodb_autoinc_lock_mode系统变量可以控制选择哪一种锁来为AUTO_INCREMENT赋值

• innodb_autoinc_lock_mode=0:统一使用AUTO-INC 锁
• innodb_autoinc_lock_mode=2:统一使用轻量级锁
• innodb_autoinc_lock_mode=1：插入记录数确定时，采用轻量级锁；不确定时使用AUTO-INC 锁

2.2.4 意向锁 （Intention Locks）

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES … WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

• An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.

• An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

假设有这样的一种场景：我们想对某张表加X锁，此时就必须先保证表中的记录都没有被加S锁和X锁。那么该如何去检测呢？可以采用循环遍历每一条记录有没有被上锁，这种方式明显效率太低了。所以InnoDB设计了另一种特殊的表级锁——意向锁。 使用它是为了表在后续被加上X锁或者S锁时，能快速判断表记录之前是否有被加锁，从而避免通过遍历的方式一个个去检测行锁的存在。

意向锁也分为 意向共享锁（IS）和意向排它锁(IX)。

• 意向共享锁（IS）：当事务准备给表记录加S锁时，需要先对表加上IS锁
• 意向排它锁 (IX) ：当事务准备给表记录加X锁时，需要先对表加上IX锁

表级别锁的兼容性如下：

兼容性S锁IS锁X锁IX锁 S锁 兼容 兼容 不兼容 不兼容 IS锁 兼容 兼容 不兼容 兼容 X锁 不兼容 不兼容 不兼容 不兼容 IX锁 不兼容 兼容 不兼容 兼容

（表1）

其中，IS锁和IX锁、IS锁和IS锁、IX锁和IX锁之间都是兼容的。这个如何理解呢？

刚刚有提到，意向锁是为了可以快速判断表记录是否被加了锁，方便判断事务是否可以对表加锁。这就意味着，不管有事务对表记录中加了S锁，还是加了X锁，只需要加上对应的IS锁和IX锁就好了，不需要关心其他事务加的是IS锁还是IX锁。

也就是说，IS锁和IX锁只是为了后续对表加S锁或者X锁时才起作用。

• IS锁不兼容表级X锁，兼容表级S锁。意思是表中记录加了S锁的，只允许对表整体加S锁
• IX锁不兼容表级X锁和S锁。表中记录加了X锁的，不只允许对表整体加S锁和X锁

2.3 行级锁

如果表级锁要锁定整个表，那么顾名思义，行级锁就是基于行为单元的锁定机制。

• 表级锁：优点是锁开销低、速度快，但锁的粒度比较粗，缺点是并发性能低。
  
  [En]

  Table-level locks: the advantages are low locking overhead and high speed, but the granularity of locks is coarse, and the disadvantage is low concurrency performance.*

• 行级锁：相对开销较大，速度较慢，但锁的粒度细，并发性能更高，更适合OLTP的场景。

MySQL 的行级锁是在引擎层由各个引擎自己来实现的。行级锁也是 InnoDB引擎对比传统的MyISAM引擎的一大优势特性。下面重点介绍一下InnoDB中行级锁的类型。

2.3.1 Record Locks

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

Record Lock直译过来就是记录锁。但Record Lock锁的都是索引的记录，作用于聚簇索引或者二级索引之上。即使一个表没有定义索引，InnoDB也会自动创建一个隐藏的聚簇索引并使用该索引进行记录锁定，所以Record Lock也称为 索引记录锁。

对于下面的例子：

SELECT c1 FROM t WHERE c1 = 10

使用show engine innodb status命令查看：

RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table test. t
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc ‘O;;
2: len 7; hex b60000019d0110; asc ;;

记录锁又分为共享记录锁和排他记录锁，也遵循读共享、读写互斥的原则。

2.3.2 Gap Locks

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

Gap Lock直译过来就是间隙锁。间隙锁的引入是作为记录锁的补充。我们知道MySQL在可重复读RR隔离级别下，是可以解决大部分幻读问题的。

幻读:指一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

• RR级别下，事务中如果是使用快照读（也称一致性读）的，如：普通的select查询，会利用MVCC的一致性视图方案来避免幻读。
• RR级别下，事务中如果是使用当前读的，如：加锁的select语句和更新语句（更新数据都是先读后写的，此时的【读】，必须读当前的值，故称为”当前读”）。 只能用加锁的方案来避免幻读。

假设在没有间隙锁的时候，MySQL只能使用Record Lock记录锁来对数据进行加锁，但是Record Lock只作用在索引行数据上，没办法限制住范围的数据
比如下面这条语句：

select * from t where id>1 and id

在RR隔离级别下，如果只对id=1和id=5这两行记录加锁，就没办法限制住其他事务在（1,5）这个范围之间插入新的记录，所以引入了Gap Lock间隙锁来对索引行（1,5）之间的空隙，也加上锁。

对于行级锁来说， 和行锁产生冲突的是对同一行数据加锁另外的行锁，兼容关系如下：

兼容性S锁X锁 S锁 兼容 不兼容 X锁 不兼容 不兼容

但对于缝隙锁，他们也有共享缝隙锁和独占共享锁，但缝隙锁之间并不冲突，这与缝隙锁冲突：在缝隙中间插入数据的操作。再次证明，缝隙锁的作用只是为了防止错觉问题。

2.3.3 Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

Next-Key Lock 就是Record Lock+Gap Lock，锁住行记录，以及中间的空隙。
还是举例下面这条语句：

select * from t where id>1 and id

问题

间隙锁和 next-key lock 的引入，在为了解决RR隔离级别下出现幻读的问题。但同时由于锁住更大的范围，在一定程度上影响了并发性能。

解决方案

虽然RR是MySQL默认的隔离级别，但是很多线上业务系统都会选择使用 RC读提交作为默认的隔离级别，同时将binlog_format设置为row。因为RC级别是允许幻读情况发生的，所以绝大部分场景下RC是不会采用间隙锁的方式（外键场景可能会使用），binlog_format设置为row则是为了防止可能出现数据和日志不一致的问题。

2.3.4 插入意向锁（Insert Intention Locks ）

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

介绍间隙锁的时候，我们知道，在某个索引区间如（1,5）加上间隙锁后，是无法插入id=3和id=4的数据，除非该间隙锁被释放。
当两个事务分别执行插入id=3和id=4的记录时，会在区间上加插入意向锁且锁状态是等待状态（is_waiting=true），等到间隙锁释放时，将插入意向锁状态is_waiting=false，唤醒两个插入的事务，且这两个事务之间是不阻塞的。

• 插入意向锁是在INSERT插入操作时设置的一种特殊间隙锁 ，注意它并不属于意向锁而是属于间隙锁。
• 插入意图锁不是互斥的，当多个事务在同一时间间隔插入记录时，只要记录本身(主键索引、唯一索引)不冲突，则事务不会阻止等待。
  
  [En]

  insert intention locks are not mutually exclusive, and when multiple transactions insert records in the same interval, as long as the records themselves (primary key index, unique index) do not conflict, then the transactions will not block waiting.*

三、死锁

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds.

死锁是指不同事务之间的每个事务持有其他事务需要获取的锁资源，从而导致事务无法继续。因为事务正在等待资源变得可用，但不会释放它持有的锁。

也就是说，当不同线程的并发执行存在资源依赖循环时，所涉及的线程都在等待其他线程释放资源，这会导致这些线程进入无限等待状态，这称为死锁。

僵局发生后，通常有两种策略。第一个是：

不作处理，直到锁超时，超时后的事务会进行回滚释放锁资源，另外的事务就能继续执行。锁超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

innodb_lock_wait_timeout 的默认值是 50s，这对于在线业务而言，是难以接受的，如果将超时时间改小，又可以误伤到其他正常的操作。

因此，第二种策略通常被使用：

• 使用wait-for graph算法主动进行发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（一般是回滚影响行最小的事务），从而释放锁让其他事务可以继续执行。将参数 innodb_deadlock_detect 设置为 on（默认on），表示开启这个逻辑。

但是如果出现” 热点行“更新的情况——很多事务都要更新同一行的数据，此时死锁检测就需要消耗大量的 CPU 资源，此时必须要 限制访问相同资源的并发事务数。

MySQL避免死锁的方法

1. 一次性锁定所有需要的资源
2. 按照一致的顺序进行加锁
3. 缩小锁冲突的范围

• 避免做多交易，拆解它们。
  
  [En]

  avoid long transactions and disassemble them.*

• 当事务需要锁定多行时，尽量将最有可能导致锁冲突和影响并发性的锁应用程序操作放在后面。
  
  [En]

  when a transaction needs to lock multiple rows, try to put behind the lock application operations that are most likely to cause lock conflicts and affect concurrency.*

• 在业务允许不可重复读和幻读的情况下，可使用使用RC的隔离级别，避免间隙锁锁定范围过大造成的死锁。
• 为DML语句加上合适的索引，防止由于不走索引时为表每一行记录添加上锁。

四、小结

本文系统性介绍了MySQL&InnoDB的锁机制。按照锁的作用范围，主要分为全局锁、表锁和行锁，而共享锁和排它锁则定义了锁的互斥方式。同时介绍了死锁的发生、检测机制和如何避免死锁的方法。

• 使用共享锁提高读操作的并发性
  
  [En]

  use shared locks to improve the concurrency of read operations*

• InnoDB使用行记录锁和间隙锁，为了保证RR可重复读级别下的强一致性解决，幻读问题；
• InnoDB使用插入意向锁，可以提高插入并发性能；

参考资料

1. MySQL官方文档
2. 《MySQL技术内幕-InnoDB存储引擎》
3. 《MySQL是怎样运行的-从跟上理解MySQL》
4. 极客时间专栏《MySQL实战45讲》

Original: https://www.cnblogs.com/codeMavs/p/15361470.html
Author: CodeMavs
Title: MySQL&InnoDB锁机制全面解析