MySQL：锁机制

概述

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

总体锁的知识分类架构如下：

三种层级的锁

1. 表级锁定

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

2. 页级锁定

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁。

3. 页级锁定

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大

从上到下，锁的粒度逐渐细粒化, 但实现开销逐渐增大。 同时我们也要须知，表锁，页锁，行锁并不是一个具体的锁，仅代表将数据库某个层级上的数据进行锁定。具体怎么去锁这个数据，还要看具体的锁实现是什么

锁相关的 SQL

要认识事务，SQL，与锁的知识，最好的办法就是调试。所以我这里提供给大家MySQL 8.0以及MySQL 8.0以下的查询事务持有锁情况的SQL

MySQL 8.0 以下

1. 在TRANSACTION位置可以看到事务持有锁的情况，但是需要经验分析，名词比较难懂

show engine innodb status;

2. 查看innodb引擎下，所有表所有事务的加锁情况，无法分别gap,record,next-key锁的具体类型。没有冲突情况无法显示持有的锁

select * from information_schema.innodb_locks`; 

3. 查看innodb引擎下，所有表所有事务因锁的阻塞情况，谁被谁block住了

select * from information_schema.innodb_locks_wait;

MySQL 8.0

1. 在TRANSACTION位置可以看到事务持有锁的情况，但是需要经验分析，名词比较难懂

show engine innodb status;

2. 查看数据库中所有表所有事务的加锁情况

没有冲突，也看到持有了什么锁，具有具体的分类，简单好用，更容易分析

select * from performance_schema.data_locks`;  

3. 查看数据库中，所有表所有事务因锁的阻塞情况，谁被谁block住了

select * from performance_schema.data_locks_wait;

MyISAM引擎下的锁

MyISAM只支持表锁，不支持行锁和页面锁。

表锁并不是一个真正的锁，只是代表对数据库表层级的数据进行锁定。具体以什么形式锁定，则要看具体的具体锁实现。

MyISAM表锁的具体实现有表级共享锁和表级排他锁。

• 表共享读锁（表级共享锁）

获得表共享读锁的事务可以读取该表的任意数据。MyISAM引擎在执行select语句前，会自动给涉及的表加表共享读锁

• 表独占写锁（表级排他锁）

获得表独占写锁的事务可以更新或删除该表中任意数据。在执行update,delete,insert等语句前，会自动给涉及的表加表独占写锁

很多多场合更喜欢把表级共享锁和表级排他锁叫做表共享读锁和表独占写锁，实际上它们的意思是一样的。

表共享读锁和表独占写锁的兼容性

即MyISAM的表锁可以做到，读读共享，读写互斥，写写互斥的功能

• 读读共享： 事务A获得表读锁，事务B依然可以获得表读锁
• 读写互斥： 事务A获得表读锁，事务B申请该表写锁则会阻塞，因为事务A获得了该表读锁，有互斥性
• 写写互斥： 事务A获得表写锁，事务B申请该表写锁则会阻塞，因为事务A获得了该表写锁，也有互斥性

MyISAM在一定程度上还是可以支持并发的进行查询和插入操作的。即如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这属于MyISAM的特性，所以InnoDB存储引擎是不支持的！

总结

• 虽然MySQL支持表，页，行三级锁定，但MyISAM存储引擎只支持表级的数据锁定，即表锁。所以MyISAM的加锁相对比较开销低，但数据操作的并发性能相对就不高。但如果写操作都是尾插入，那么还是可以支持一定程度的读写并发
• MyISAM的数据操作如果导致加锁行为，那么该锁肯定是一个表锁，会对全表数据进行加锁。但也要看具体什么形式的表锁，不同形式的表锁有着不同的特性
• 从MyISAM所支持的锁中也可以看出，MyISAM是一个支持读读并发，但不支持通用读写并发，写写并发的数据库引擎，所以它更适合用于读多写少的应用场合

InnoDB引擎下的锁

InnoDB功能则更为强大，锁也更为复杂，主要以下几种：

• 共享锁和排他锁 (Shared and Exclusive Locks)

• 意向锁（Intention Locks）

• 记录锁（Record Locks）

• 间隙锁（Gap Locks）

• 临键锁 （Next-Key Locks）

• 插入意向锁（Insert Intention Locks）

• 主键自增锁 (AUTO-INC Locks)

• 空间索引断言锁（Predicate Locks for Spatial Indexes）

这里只讲重点，不讲主键自增锁，空间索引断言锁。

InnoDB下的表锁和行锁

表锁，行锁并不是一个真正的锁，只是代表可以对数据库对应层级的数据进行锁定。具体以什么形式去锁定，则要看具体的具体锁实现。

表锁具体的锁实现则有共享锁，排他锁，意向锁等。而行锁则有共享锁和排他锁，另外行锁本身还有记录锁，间隙锁和临键锁的不同算法实现。

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁的实现也意味着，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，还是会出现锁冲突的，因为他们使用的是同一把锁。

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。但是使用不同索引查询相同行会有锁冲突。

InnoDB下的共享锁和排他锁

• 共享锁(Shared Lock)，既S锁，又称读锁。事务拿到某一行记录的S锁，才可以读取这一行的数据。共享锁可以实现读读共享，读写互斥

• 排他锁(Exclusive Lock)，既X锁，又称写锁，独占锁。事务拿到某一行记录的X锁，才可以修改或者删除这一行的数据。排他锁可以实现读写互斥，写写互斥。

因为InnoDB支持表锁和行锁。所以在数据库层次结构的表级和行级，都可以对数据进行锁定。

• 表级共享锁：又称为表共享读锁，即在表的层级上对数据加以共享锁，实现读读共享
• 表级排他锁：又称为表独占写锁，即在表的层级上对数据加以排他锁，实现读写互斥，写写互斥
• 行级共享锁：即在行的层级上，对数据加以共享锁，实现对该行数据的读读共享
• 行级排他锁：即在行的层级上，对数据加以排他锁，实现对该行数据的读写互斥，写写互斥

共享锁和排它锁的兼容性

标题	共享锁	排他锁
共享锁	兼容	不兼容
排他锁	不兼容	不兼容

多个事务可以拿到一把S锁，可以实现读读共享；而只有一个事务可以拿到X锁，可以实现读写互斥，写写互斥。

一句话，就是读读共享，读写互斥，写写互斥。

怎么显式地加共享锁或排他锁？

• select * from table lock in share mode 。为table的所有数据加上共享锁，既表级共享锁
• select * from table for update。 为table的所有数据加上排他锁，既表级排他锁
• select * from table where id = 1 for update。 为table中id为1的那行数据加上排他锁，既行级排他锁
• select * from table where id = 1 lock in share mode。为table中id为1的那行数据加上共享锁，既行级共享锁

当然以上，加的是行锁的前提是，id为主键且在查询命中，否则行锁会轮为表锁。

InnoDB下的意向锁

通常情况下，表锁和行锁是相互冲突的，获得了表锁，就无法再获得该表具体行的行锁，反之亦然。但是有的时候表锁和行锁实现部分的共存有利于更细粒度的对锁进行控制，以便得到更加的并发性能。

例如，

事务 A 获取了某一行的排他锁，并未提交：

SELECT * FROM users WHERE id = 6 FOR UPDATE;

事务 B 想要获取 users 表的表锁：

LOCK TABLES users READ;

因为共享锁与排他锁互斥，所以事务 B 在视图对 users 表加共享锁的时候，必须保证：

• 当前没有其他事务持有 users 表的排他锁。
• 当前没有其他事务持有 users 表中任意一行的排他锁 。

为了检测是否满足第二个条件，事务 B 必须在确保 users 表不存在任何排他锁的前提下，去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法，但是有了意向锁之后，情况就不一样了：事务 B 只要看表上有没有意向共享锁，有则说明表中有些行被共享行锁锁住了，因此，事务 B 申请表的写锁会被阻塞。这样是不是就高效多了。

所以InnoDB为了实现行锁和表锁共存的多粒度锁机制特性，InnoDB存储引擎也就还支持一种额外的锁方式以对多粒度锁机制进行支持，称之为意向锁（Intention Lock）。

意向锁的维护是由存储引擎隐式帮我们做了，意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在的表的对应意向锁。

怎么理解意向锁？

• 意向锁（Intention Lock）就是一种不与行级锁冲突的表级锁（重点！！）
• 意向锁的主要目的展示出某事务已对表中某行加锁，或即将对表中某行加锁
• 意向锁就是指，未来的某个时刻，事务可能要对某行加共享锁或排它锁了，先提前声明一个意向

并且意向锁具体实现又可以分为以下两种：

• 意向共享锁（intention shared lock,IS）：事务打算给表中的某些行加行级共享锁，事务在给某些行加S锁前必须先取得该表的IS锁。
• 意向排他锁（intention exclusive lock,IX）：事务打算给表中的某些加行级排他锁，事务在给某些行加X锁前必须先取得该表的IX锁。

总之所谓意向锁，就是提前表明了一个要加行锁的“意向”。当某表存在排他意向锁时，代表该表的某行可能存在行级排他锁。同理当某表存在共享意向锁时，代表该表的某行可能存在行级共享锁

意向锁的兼容性

标题	意向共享锁(IS)	意向排他锁(IX)	表级共享锁(S)	表级排他锁(X)
意向共享锁(IS)	兼容	兼容	兼容	不兼容
意向排他锁(IX)	兼容	兼容	不兼容	不兼容
表级共享锁(S)	兼容	不兼容	兼容	不兼容
表级排他锁(x)	不兼容	不兼容	不兼容	不兼容

意向锁与意向锁之间永远是兼容的，因为当你不论加行级的 X 锁或 S 锁，都会自动获取表级的 IX 锁或者 IS 锁。也就是你有 10 个事务，对不同的 10 行加了行级 X 锁，那么这个时候就存在 10 个 IX 锁。

这 10 个 IX 存在的作用是啥呢，就是假如这个时候有个事务，想对整个表加排它 X 锁,那它不需要遍历每一行是否存在 S 或 X 锁，而是看有没有存在 意向锁，只要存在一个意向锁，那这个事务就加不了表级排它(X)锁，要等上面 10 个 IX 全部释放才行。

意向锁还有一个好处

（1）事务A向student表申请某个行记录的S锁（行级），所以首先要为student表上IS锁。
（2）事务A获得锁完毕后，事务B向student表申请全表的S锁（表级）。于是数据库开始锁冲突的判定
（3）数据库首先判断事务B申请表锁前，student表是否已经存在表级S,X锁，发现没有
（4）数据库再判断student是否已经存在意向锁IS,IX，然后发生之前有事务A申请过IS锁
（5）根据表级S锁和意向IS锁的兼容性策略，两者是兼容的。所以事务B直接就获得对student表的表级S锁
（6）此时student表已经共存了表级S锁和行级S锁。

所以意向锁的出现也让表锁和行锁得到一定的共存，提供了部分的并发性能！

InnoDB下的记录锁，间隙锁，临键锁

我们的行锁根据锁的粗细粒度又可以分为三种锁，记录锁，间隙锁和临键锁。

记录锁（Record Locks）

记录锁(Record Lock)就是我们最单纯认知的行锁，既只锁住一条行记录，准确的说是一条索引记录。
InnoDB的行锁是依赖索引实现的，而其锁住某行数据的本质是锁住行数据对应在聚簇索引的索引记录。

例如某个事务执行了select * from t where id=1 for update;语句，就相当于它会在id = 1的索引记录上加上一把锁，以阻止其他事务插入，更新，删除id = 1的这一行。

• 记录锁是锁住记录，锁住索引记录，而不是真正的数据记录。
• 如果要锁的列没有索引，进行全表记录加锁。
• 记录锁也是排它 (X) 锁，所以会阻塞其他事务对其插入、更新、删除。

间隙锁（Gap Locks）

间隙锁（Gap Lock），顾名思义，它会封锁索引记录中的“缝隙”，不让其他事务在“缝隙”中插入数据。 它锁定的是一个不包含索引本身的开区间范围 (index1,index2)。间隙锁是封锁索引记录之间的间隙，或者封锁第一条索引记录之前的范围，又或者最后一条索引记录之后的范围

说白了，间隙锁的目的就是为了防止索引间隔被其他事务的 “插入”。 间隙锁是InnoDB权衡性能和并发性后出来的特性。

间隙锁之间是互相兼容的，既不同事务的Gap锁是可以共存的，可以存在重合区域或是完全重合。多个事务的Gap锁重合代表多个事务都想阻止其他事务往该索引间隙 “插入” 数据。也因为Gap一次就对插入行为锁住了一片的区域，不利于多事务并发写。所以后续MySQL也就提供了一种特殊的间隙锁（插入意向锁），以解决并发写的问题

间隙锁实际也分共享间隙锁，排他间隙锁，虽然有分类，但事实上他们的意义是等价毫无区别的，所以可以忽略这一层，只要知道它们的作用都是阻止其他事务往间隙插入数据。

间隙锁只发生在事务隔离级别为RR(Repeatable Read)的情况下，它用于在隔离级别为RR时，阻止幻读(phantom row)的发生；隔离级别为RC时，搜索和索引扫描时，Gap锁是被禁用的，只在外键约束检查和 重复key检查时Gap锁才有效，正是因为此，RC时会幻读问题。

比如某个事务执行select * from table where id between 10 and 20 for update;语句，当其他事务往表里 “插入” id在(10,20)之间的值时，就会被(10,20)的间隙锁给阻塞。

临键锁（Next-Key Locks）

Next-key 锁是记录锁和间隙锁的组合，它指的是加在 某条记录以及这条记录前面间隙上 的锁。

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列（该索引里面的值允许重复）上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段 左开右闭 区间的数据。

需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

假设有如下表：id 主键，age 普通索引。

id	name	age
1	zhangsan	10
3	lisi	24
5	wangwu	32
7	zhaoliu	45

该表中 age 列潜在的临键锁有：(-∞, 10]，(10, 24]，(24, 32]，(32, 45]，(45, +∞]。

在事务 A 中执行如下命令：

-- 根据非唯一索引列 UPDATE 某条记录 
UPDATE table SET name = Vladimir WHERE age = 24; -- 或根据非唯一索引列 锁住某条记录 
SELECT * FROM table WHERE age = 24 FOR UPDATE;

不管执行了上述 SQL 中的哪一句，之后如果在事务 B 中执行以下命令，则该命令会被阻塞：

INSERT INTO table VALUES(100, 26, 'tianqi');

很明显，事务 A 在对 age 为 24 的列进行 UPDATE 操作的同时，也获取了 (10, 32] 这个区间内的 临键锁。

这里对 记录锁、间隙锁、临键锁 做一个总结：

我们知道InnoDB的行锁默认是基于B+Tree的。所以行锁依赖的索引是有序的。

• 记录锁就是单纯意义上的行锁，锁的就是单行数据，该数据是真实存在的
• 而间隙锁则是锁住一个区间中多行数据，但这些多行的数据实际是并不存在的。既只锁住真实数据对应索引项之间的一个空间范围
• 而临键锁说白了就是记录锁+间隙锁的组合。只要把记录锁和间隙锁组合在一起，就是临键锁，既锁住索引项本身的真实数据，又锁住两两索引之间没有数据的空间范围。

InnoDB下的插入意向锁

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种特殊的间隙锁。

该锁用以表示插入意向，当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待。

假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的（排他）锁前，都会获取（4，7 ] 之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突（阻塞等待）。

总结来说，插入意向锁 的特性可以分成两部分：

插入意向锁是 一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。

• 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。
• 虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁，而插入意向锁是行锁。

插入意向锁的作用？以及为何替代Gap锁

为普通的Gap锁一旦锁住了某个间隙，这个间隙就不再允许其他事务插入数据了。既其他事务需要等待当前事务释放该区间的Gap锁后，才能插入数据。所以我们可以知道，因为Gap锁对插入行为的相对锁粒度比较大，就不利于提高多事务对同一个索引间隙的数据插入性能了。

插入意向锁就不一样了，插入意向锁虽然跟Gap锁一样，一次锁的是一个区间。但是它可以允许多个事务在这个区间内执行不同数据的插入，互不干扰。

参考：
【MySQL笔记】正确的理解MySQL中让你想到就烦的各种锁

【数据库】MySQL 中的锁机制