一文详解MySQL的锁机制

2021/11/23 19:14:38

编程Tag： mysql 索引插入事务记录锁定详解意向锁一文

本文主要是介绍一文详解MySQL的锁机制，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、表级锁、行级锁、页级锁

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

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

MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

1、表级锁

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。

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

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

2、行级锁

行级锁定最大的特点就是锁定对象的颗粒度很小，由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。

由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎。

3、页级锁

页级锁定是MySQL中比较独特的一种锁定级别。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。

使用页级锁定的主要是BerkeleyDB存储引擎。

4、总结

总的来说，MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

二、共享锁、排它锁

InnoDB 实现了标准的行级锁，包括两种：共享锁（简称 s 锁）、排它锁（简称 x 锁）。

对于共享锁而言，对当前行加共享锁，不会阻塞其他事务对同一行的读请求，但会阻塞对同一行的写请求。只有当读锁释放后，才会执行其它事物的写操作。

对于排它锁而言，会阻塞其他事务对同一行的读和写操作，只有当写锁释放后，才会执行其它事务的读写操作。

简而言之，就是读锁会阻塞写(X)，但是不会堵塞读(S)。而写锁则会把读(S)和写(X)都堵塞

对于InnoDB 在RR(MySQL默认隔离级别) 而言，对于 update、delete 和 insert 语句，会自动给涉及数据集加排它锁（X）；

对于普通 select 语句，innodb 不会加任何锁。如果想在select操作的时候加上 S锁或者 X锁，需要我们手动加锁。

-- 加共享锁（S）
select * from table_name where ... lock in share mode

-- 加排它锁（X)
select * from table_name where ... for update

用 select... in share mode 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 update 或者 delete 操作。

但是如果当前事务也需要对该记录进行更新操作,则有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 select... for update 方式获得排他锁。

三、记录锁

1、记录锁（Record Locks）

记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。比如

SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

需要注意的是：

- d 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁(有关临键锁下面会讲)。
- 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

其他实现

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

-- id 列为主键列或唯一索引列 
UPDATE SET age = 50 WHERE id = 1;

记录锁是锁住记录，锁住索引记录，而不是真正的数据记录.

如果要锁的列没有索引，进行全表记录加锁

记录锁也是排它(X)锁,所以会阻塞其他事务对其插入、更新、删除。

四、间隙锁

1、间隙锁（Gap Locks）

间隙锁是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。

请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

举例来说，假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101，下面的SQL：

  SELECT * FROM emp WHERE empid > 100 FOR UPDATE

当我们用条件检索数据，并请求共享或排他锁时，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

这个时候如果你插入empid等于102的数据的，如果那边事物还没有提交，那你就会处于等待状态，无法插入数据。

有关间隙锁所需讲的东西还是蛮多的，我会单独写一篇文章来分析间隙锁，并在文章中附上完整的示例。

五、临键锁

1、临键锁（Next-Key Locks）

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

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，

InnoDB 中行级锁是基于索引实现的。

临键锁只与 非唯一索引列 有关，在 唯一索引列（包括主键列）上不存在临键锁。

假设有如下表：

id主键, age 普通索引

该表中 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 操作的同时，也获取了 (24, 32] 这个区间内的临键锁。

总结

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

InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

六、意向锁

1、意向锁

意向锁又分为 意向共享锁（IS）和 意向排他锁（IX）

意向共享(IS)锁：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他(IX)锁：事务有意向对表中的某些行加排他锁（X锁）

 -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
 SELECT column FROM table ... FOR UPDATE;

首先我们要明白四点

意向共享锁（IS）和意向排他锁（IX）都是表锁。
意向锁是一种 不与行级锁冲突的表级锁，这一点非常重要。
意向锁是 InnoDB 自动加的，不需用户干预。
意向锁是在 InnoDB 下存在的内部锁，对于MyISAM 而言没有意向锁之说。

这里就会有疑惑，既然前面已经有了共享锁（S锁）、排它锁（X锁）。那么为什么需要引入意向锁呢？它能解决什么问题呢？

我们可以理解意向锁存在的目的就是为了让 InnoDB 中的行锁和表锁更高效的共存。

为什么这么说，我们来举一个例子。

举例

下面有一张表 InnoDB RR隔离级别 id是主键

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

SELECT * FROM users WHERE id = 6 FOR UPDATE;

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

LOCK TABLES users READ;

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

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

为了检测是否满足第二个条件，事务 B 必须在确保users表不存在任何排他锁的前提下，去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法，但是有了意向锁之后，情况就不一样了：事务B只要看表上有没有

意向共享锁，有则说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。这样是不是就高效多了。

这也解释就应该清楚，为什么有意向锁这个东西存在了。

我们可以举个生活中的例子，再来理解下为什么需要存在意向锁。

打个比方，就像有个游乐场，很多小朋友进去玩，看门大爷如果要下班锁游乐场的门(加表锁)，他必须确保每个角落都要去检查一遍，确保每个小朋友都离开了(释放行锁)，才可以锁门。

假设锁门是件频繁发生的事情，大爷就会非常崩溃。那大爷想了一个办法，每个小朋友进入，就把自己的名字写在本子上，小朋友离开，就把自己的名字划掉，那大爷就能方便掌握有没有小朋友在游乐场里，不必每个角落都去寻找一遍。

例子中的“小本子”，就是意向锁，他记录的信息并不精细，他只是提醒大爷，有人在屋里。

这里我们再来看下共享(S)锁、排他(X)锁、意向共享锁（IS）、意向排他锁（IX）的兼容性

可以看出 意向锁之间是互相兼容的.那你存在的意义是啥？

意向锁不会为难意向锁。也不会为难行级排他(X)/共享(X)锁,它的存在是为难表级排他(X)/共享(X)锁。

注意这里的排他(X)/共享(S)锁指的都是表锁！意向锁不会与行级的共享/排他锁互斥！ 行级别的X和S按照上面的兼容性规则即可。

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

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

七、插入意向锁

1、插入意向锁

在讲解插入意向锁之前，先来思考一个问题

下面有张表 id主键，age普通索引

首先事务 A 插入了一行数据，并且没有 commit：

INSERT INTO users SELECT 4, 'Bill', 15;

随后事务 B 试图插入一行数据：

INSERT INTO users SELECT 5, 'Louis', 16;

请问：

1、事务A使用了什么锁？

2、 事务 B 是否会被事务 A 阻塞？

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

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

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

并不会产生冲突（阻塞等待）。

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

插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

需要强调的是，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。

现在我们可以回答开头的问题了：

1、使用插入意向锁与记录锁。

2、事务 A 不会阻塞事务 B。

为什么不用间隙锁

如果只是使用普通的间隙锁会怎么样呢？我们在看事务A,其实它一共获取了3把锁

id 为 4 的记录行的记录锁。
age 区间在（10，15）的间隙锁。
age 区间在（15，20）的间隙锁。

最终，事务 A 插入了该行数据，并锁住了（10，20）这个区间。