搜索

首页 >数据库 >mysql教程 >一起聊聊MYSQL中锁的各种模式与类型

一起聊聊MYSQL中锁的各种模式与类型

WBOY
WBOY转载
2022-01-29 06:00:303115浏览

本篇文章给大家带来了关于mysql中锁的各种类型与模式的相关知识，希望对大家有帮助。

一起聊聊MYSQL中锁的各种模式与类型

在日常开发工作中，我们几乎需要天天与数据库打交道，作为一名只会CRUD的SQL BOY，除了每天用mybatis-generator自动生成DAO层代码之外，我们几乎不用去care数据库中如何处理并发请求，但是突然某一天MYSQL数据库告警了，出现了死锁，我们的内心慌的一匹，不禁想问：这不就是个普通查询吗，咋还锁起来了？

为了避免慌乱的表情被主管捕捉到，我们需要提前了解一下数据库中到底有哪些锁。

在MySQL中，其实将锁分成了两类：锁类型（lock_type）和锁模式（lock_mode）。

锁类型描述的锁的粒度，也就是把锁具体加在什么地方；而锁模式描述的是到底加的是什么锁，是读锁还是写锁。锁模式通常和锁类型结合使用。

按锁的模式分

读锁

读锁，又叫共享锁/S锁/share locks。

读锁是某个事务（比如事务A）在进行读取操作（比如读一张表或者读取某一行）时创建出来的锁，其他的事务可以并发地读取这些数据（被加了锁的），但是不能修改这些数据（除非持有锁的用户已经释放锁）。

事务A对数据加上读锁之后，其他事务依然可以对其添加读锁（共享），但是不能添加写锁。

在记录上加读锁

InnoDB支持表锁和行锁，在行（也就是记录）上加锁，并不是锁住该条记录，而是在记录对应的索引上加锁。如果where条件中不走索引，则会对所有的记录加锁。

显式加锁语句为：

01.png

注意：这里所说的读，是指当前读，快照读是无需加锁的。普通select读一般都是快照读，除了select...lock in share mode这样的显式加锁语句下会变成当前读，在InnoDB引擎的serializable级别下，普通select读也会变成快照读。

另外需要注意，对于行锁的加锁过程分析，要根据事务隔离级别、是否使用索引（哪种类型的索引）、记录是否存在等因素结合分析，才能判断在哪里加上了锁。

innodb引擎中的加读锁的几种情形

普通查询在隔离级别为 serializable 会给记录加S锁。但这也取决于场景：非事务读（auto-commit）在 Serializable 隔离级别下，无需加锁；

Serializable隔离级别时：如果查询条件为唯一索引且是唯一等值查询时：是在该条记录上加S锁；非唯一条件查询（查询会扫描到多条记录时）：记录本身+记录的间隙（需要具体分析间隙的范围），加S锁；

select … in share mode，会给记录加S锁，但是根据隔离级别的不同，加锁的行为有所不同：

RC隔离级别：是在记录上加S锁。RR/Serializable隔离级别：如果查询条件为唯一索引且是唯一等值查询时：是在该条记录上加S锁；非唯一条件查询（查询会扫描到多条记录时）：记录本身+记录的间隙（需要具体分析间隙的范围），加S锁；

通常insert操作是不加锁的，但如果在插入或更新记录时，检查到 duplicate key（或者有一个被标记删除的duplicate key），对于普通的insert/update，会加S锁，而对于类似replace into或者insert … on duplicate 这样的SQL语句加的是X锁。

insert … select 插入数据时，会对 select 的表上扫描到的数据加S锁；

外键检查：当我们删除一条父表上的记录时，需要去检查是否有引用约束，这时候会扫描子表上对应的记录，并加上S锁。

在表上加读锁

表锁由 MySQL服务器实现，无论存储引擎是什么，都可以使用表锁。一般在执行 DDL 语句时，譬如 ALTER TABLE 时就会对整个表进行加锁。在执行 SQL 语句时，也可以明确对某个表加锁。

给表显式加锁语句为：

02.png

在使用MYISAM引擎时，通常我们不需要手动加锁，因为MYISAM引擎会针对我们的sql语句自动进行加锁，整个过程不需要用户干预：

  • 查询语句（select）：会自动给涉及的表加读锁；

  • 更新语句（update、delete、insert）：会自动给涉及的表加写锁。

写锁

写锁，排他锁/X锁/exclusive locks。写锁的阻塞性比读锁要严格的多，一个事务对数据添加写锁之后，其他的事务对该数据，既不能读取也不能更改。

与读锁加锁的范围相同，写锁既可以加在记录上，也可以加在表上。

在记录上加写锁

在记录上加写锁，引擎需要使用InnoDB。

通常普通的select语句是不会加锁的（隔离级别为Serializable除外），想要在查询时添加排他锁需要使用以下语句：

查询时加写锁：

03.png

与加读锁相同，写锁也是加在索引上的。

更新时加写锁：

04.png

在表上加写锁

显式给表加写锁的语句为：

05.png

当引擎选择myisam时，insert/update/delete语句，会自动给该表加上排他锁。

读写锁兼容性：

  • 读锁是共享的，它不会阻塞其他读锁，但会阻塞其他的写锁；

  • 写锁是排他的，它会阻塞其他读锁和写锁；

  • 总结：读读不互斥，读写互斥，写写互斥

意向锁

意向锁是一种不与行级锁冲突的表级锁，表示表中的记录所需要的锁(S锁或X锁)的类型（其实就是告诉你，这张表中已经存在了行锁（行锁的类型），所以叫意向锁）。InnoDB支持多种粒度的锁，允许行级锁和表级锁的共存。

意向锁分为：

意向共享锁(IS锁)：IS锁表示当前事务意图在表中的行上设置共享锁

下面语句执行时会首先获取IS锁，因为这个操作在获取S锁：获取S锁：select ... lock in share mode

意向排它锁(IX锁)：IX锁表示当前事务意图在表中的行上设置排它锁

下面语句执行时会首先获取IX锁，因为这个操作在获取X锁：获取X锁：select ... for update

事务要获取某个表上的S锁和X锁之前，必须先分别获取对应的IS锁和IX锁。

意向锁有什么作用呢：

如果另一个事务试图在该表级别的共享锁或排它锁，则受到由第一个事务控制的表级别意向锁的阻塞。第二个事务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。

示例：表test_user：

06.png

07.png

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

事务 B 想要获取 test_user 表的表共享锁；

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

  • 当前没有其他事务持有 users 表的排他锁（表排他锁）。

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

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

因为此时事务A获取了两把锁：users 表上的意向排他锁与 id 为 28 的数据行上的排他锁。

事务 B 想要获取 test_user 表的共享锁：

事务 B 只需要检测事务 A 是否持有 test_user 表的意向排他锁，就可以得知事务 A 必然持有该表中某些数据行的排他锁，那么事务 B 对 test_users 表的加锁请求就会被排斥（阻塞），从而无需去检测表中的每一行数据是否存在排他锁。

事务 C 也想获取 users 表中某一行的排他锁：

  • 事务 C 检测到事务 A 持有 test_user 表的意向排他锁；

  • 意向锁之间并不互斥，所以事务 C 获取到了 test_user 表的意向排他锁；

  • 因为id 为 31 的数据行上不存在任何排他锁，最终事务 C 成功获取到了该数据行上的排他锁。

意向锁与意向锁之间是不互斥的，但是意向锁与其他表锁之间存在一定的兼容互斥，具体如下：

意向锁之间的兼容互斥性：

08.png

意向锁与普通的排他 / 共享锁互斥性：

09.png

自增锁

我们在设计表结构的时候，通常会把主键设置成自增长（思考一下为什么？）。

在InnoDB存储引擎中，针对每个自增长的字段都设置了一个自增长的计数器。我们可以执行下面的语句来得到这个计数器的当前值：

10.png

当我们进行插入操作的时候，该操作会根据这个自增长的计数器的当前值进行+1操作，并赋予自增长的列，这个操作我们称之为auto-inc Locking，也就是自增长锁，这种锁其实采用的是特殊的表锁机制，如果insert操作出现在一个事务中，这个锁是在insert操作完成之后立即释放，而不是等待事务提交。

按锁的类型分

全局锁

所谓全局锁，其实就是给整个数据库实例加锁。

数据库实例与数据库是有所区别的：

数据库，就是保存数据的仓库，具体到mysql中，数据库其实是一系列数据文件集合（也就是我们通常所说的database，比如创建数据库语句就是 create database...）。

数据库实例，是指访问数据库的应用程序，在Mysql中，就是mysqld进程了。

简单来理解，数据库实例中包含了你创建的各种数据库。

如果给数据库实例加全局锁会导致整个库处于只读状态（这是非常危险的）。

一般来说，全局锁的典型使用场景是用于全库备份，即把数据库中所有的表都select出来。但是要注意，让整个库都处于只读状态，会导致一些严重的问题：

  • 在主库上加全局锁，在加锁期间，不能执行任何更新操作，业务基本上很多功能都不可用了；

  • 在从库上加全局锁，在加锁期间，不能执行主从同步，会导致主从同步延迟。

全局锁的加锁语句是：

11.png

解除全局锁的方法是：

  • 断开执行全局锁的session即可；

  • 执行解锁sql语句：unlock tables；

如果需要个数据库备份的话，可以使用官方自带的逻辑备份工具mysqldump。

既然已经有了dump工具，为什么还需要 FTWRL 呢？一致性读是好，但前提是引擎要支持这个隔离级别。比如，MyISAM 这种不支持事务的引擎。这时，我们就需要使用 FTWRL 命令了。

FTWRL 前有读写的话，FTWRL 都会等待读写执行完毕后才执行。

FTWRL 执行的时候要刷脏页的数据到磁盘，因为要保持数据的一致性 ，所以执行FTWRL时候是所有事务都提交完毕的时候。

全局锁的实现还是依赖于元数据锁的。

元数据锁

元数据锁（MetaData Lock），也叫MDL锁，是用来保护元数据信息，系统级的锁无法主动控制。在MySQL5.5版本，开始引入MDL锁，主要是为了在并发环境下对DDL、DML同时操作下保持元数据的一致性。比如下面这种情况：

隔离级别：RR

12.png

如果没有元数据锁的保护，那么事务2可以直接执行DDL操作，导致事务1出错。MYSQL5.5版本的时候加入 MDL 锁，是为了保护这种情况的发生。由于事务1开启了查询，那么获得了元数据锁，锁的模式为MDL读锁，事务2要执行DDL，则需获得 MDL 写锁，由于读写锁互斥，所以事务2需要等待事务1释放掉读锁才能执行。

  • 对表中的记录进行增删改查（DML操作）的时候，自动加MDL读锁；

  • 对表的结构（DDL操作）进行修改的时候，自动加MDL写锁。

MDL锁的粒度

MDL锁是Mysql服务器层面中实现的，而不是在存储引擎插件中实现。按照锁定的范围，MDL锁可以分为以下几类：

13.png

MDL锁的模式

14.png

页级锁

MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。不同的存储引擎支持不同的锁机制。根据不同的存储引擎，MySQL中锁的特性可以大致归纳如下：

15.png

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

锁定粒度大小比较：表级锁 > 页级锁 > 行级锁

表级锁

表锁在上文我们已经介绍过，相比于行锁的细粒度加锁，表锁是对整张表加锁。由于是对整张表加锁，就没有行锁的加锁方式那么复杂，所以加锁比行锁快，而且不会出现死锁的情况（因为事务是一次性获取想要加的表表锁），但是表锁也存在一些问题：锁的范围过大，在并发比较高的情况下，会导致抢锁的冲突概率变高，这样并发性能就大打折扣了。

表锁的加锁方式

引擎选择MYISAM时

MYISAM引擎只支持表锁，不支持行锁。

手动添加表级锁的语句如下：

16.png

在使用MYISAM引擎时，通常我们不需要手动加锁，因为MYISAM引擎会针对我们的sql语句自动进行加锁，整个过程不需要用户干预：

  • 查询语句（select）：会自动给涉及的表加读锁；

  • 更新语句（update、delete、insert）：会自动给涉及的表加写锁

引擎选择InnoDB时

InnoDB引擎同时支持行级锁和表级锁，默认为行级锁。

给InnoDB引擎的表手动加锁，也同样使用 lock table {tableName} read/write 语句进行读/写锁的添加。

除此之外，innodb还支持一种表级锁：意向锁（上文已经介绍过）。

总的来说，InnoDB引擎的表级锁包含五种锁模式：

  • LOCK_IS：表意向读锁

  • LOCK_IX：表意向写锁

  • LOCK_S：表读锁

  • LOCK_X：表写锁

  • LOCK_AUTO_INC：自增锁

行级锁

在编写业务代码的过程中，我们接触最多的就是行级锁了（表级锁由于性能问题，一般不推荐使用）。相比于表级锁，行级锁具有明显的性能优势：

  • 冲突少：多线程中访问不同的记录时只存在少量锁定冲突；

  • 锁的粒度小：可以长时间锁定单一的行，对其他的行没有影响，所以并发度是最高的；

但是使用行锁时，一旦稍不注意，是非常容易出现死锁的（表锁就不存在死锁现象），所以使用行锁需要注意加锁的顺序和锁定的范围。

InnoDB的行锁是通过对索引项加锁实现的，这表示只有通过索引查询记录时才会使用行锁，如果不走索引查询数据将使用表锁，则性能会大打折扣。

需要记住：行锁也叫记录锁，记录锁都是加在索引上的。

  • where条件指定的是主键索引：则在主键索引上加锁；

  • wehre条件指定的是二级索引：记录锁不仅会加在这个二级索引上，还会加在这个二级索引所对应的聚簇索引上；

  • where条件如果无法走索引：MySQL会给整张表所有数据行加记录锁，存储引擎层将所有记录返回由MySQL服务端进行过滤。

记录锁：LOCK_REC_NOT_GAP（只锁记录）

记录锁是最简单的行锁。比如在RR隔离级别时，执行 select * from t_user where id = 1 for update 语句时，实际上是对 id = 1 (这里id为主键)这条记录上锁（锁加在聚簇索引上）。

记录锁永远都是加在索引上的，就算一个表没有建索引，数据库也会隐式的创建一个索引。如果 WHERE 条件中指定的列是个二级索引，那么记录锁不仅会加在这个二级索引上，还会加在这个二级索引所对应的聚簇索引上。

注意，如果 SQL 语句无法使用索引时会走主索引实现全表扫描，这个时候 MySQL 会给整张表的所有数据行加记录锁。

如果一个 WHERE 条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由 MySQL Server 层进行过滤。在没有索引时，不仅会消耗大量的锁资源，增加数据库的开销，而且极大的降低了数据库的并发性能，所以说，更新操作一定要记得走索引（因为更新操作会加X锁）。

行级锁的几种类型：

间隙锁：LOCK_GAP（只锁间隙）

间隙锁是一种区间锁。锁加在不存在的空闲空间上，或者两个索引记录之间，或者第一个索引记录，或者最后一个索引之后的空间，用来表示只锁住一段范围（一般在进行范围查询时且隔离级别在RR或Serializable隔时）。

一般在RR隔离级别下会使用到GAP锁。使用GAP锁，主要是为了防止幻读产生，在被GAP锁锁住的区间，不允许插入数据或者更新数据。

间隙锁的产生条件：innodb的隔离级别为 Repeatable Read 或者 Serializable。

间隙锁的作用范围说明：

隔离级别：RR

17.png

以Student表作为样例数据，id为主键，stu_code为学生编号，添加普通索引。

间隙锁区域定义：

根据检索条件向左寻找最靠近的值A，作为左区间，向右寻找最靠近的值B，作为右区间，间隙锁为（A，B）

向左找不到最近的值A，也是就无穷小，作为左区间，向右寻找最靠近的值B，作为右区间，间隙锁为（无穷小，B）

向左找到最近的值A，作为左区间，向右寻找不到最近的值B，也就是无穷大，作为右区间，间隙锁为（A，无穷大）

区间（A,B）示例：

事务1：

select * from student where stu_code = 4 for update

事务2：

insert into student vaues(2, 2, 'A')；
insert into student values(4, 5, 'B');

根据事务1的sql语句分析，间隙锁的范围是：stu_code = 4记录是存在的，所以左区间为最近的索引值为stu_code = 3,右区间为最近的索引值为stu_code =7，所以间隙范围为：（3，7），因此事务2的两个insert 语句，一个在范围外，一个在范围内，在范围外的能插入，而范围内的则阻塞，所以（2，2, 'A'）能插入成功；（4，5, 'B'）插入阻塞。

区间（无穷小,B）示例：

事务1:

select * from student where stu_code = 1 for update

事务2:

insert into student vaues(2, 0, 'c')；
insert into student vaues(2, 2, 'r')；
insert into student vaues(5, 2, 'o')；

根据事务1的sql语句分析，间隙锁的范围是：stu_code = 1 是存在的，左边最近没有记录，所以是左边的无穷小，右边最近的索引值为 stu_code = 3，所以间隙锁范围为：（无穷小，3）。所以事务2的第一个和第二个insert sql语句执行被阻塞，是在间隙锁范围内的。第三个insert sql语句能执行成功，不在间隙锁范围内。

区间（A,无穷大）示例：

事务1:

select * from student where stu_code = 7 for update

事务2:

insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；

根据事务1的sql语句分析，间隙锁的范围是：stu_code = 7 是存在的，左边最近的索引值为 stu_code = 4，而右边是没有索引值的，所以间隙锁的范围为：（4，无穷大），第一个inset语句能执行成功，不在间隙范围内；第二个insert语句执行被阻塞，是在间隙锁范围内的。

如果查询语句在数据库中没有记录，那该怎么锁呢？

以上是查询是有记录的，如果查询语句在数据库中没有记录，那该怎么锁呢？咱们继续往下：

事务1：

update student set stu_name = '000' where stu_code = 10

事务2:

insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；

根据上面的执行语句是找不到记录的，向左取最近的记录（10，7，‘小明’）作为左区间，即间隙锁的范围是：（7， 无穷大），第一个insert语句不在区间范围内，能执行成功；第二个insert执行语句在区间内被阻塞，执行失败。如果事务1的where 条件是大于10，也是向左找最近的记录值作为左区间，所以间隙锁的范围也是：（7， 无穷大）

总结：间隙锁产生的条件

RR/Serializable隔离级别下：Select ... Where...For Update 时：

只使用唯一索引查询，并且只锁定一条记录时，InnoDB会使用行锁。

只使用唯一索引查询，但是检索条件是范围检索，或者是唯一检索然而检索结果不存在（试图锁住不存在的数据）时，会产生 Next-Key Lock。

使用普通索引检索时，不管是何种查询，只要加锁，都会产生间隙锁。

同时使用唯一索引和普通索引时，由于数据行是优先根据普通索引排序，再根据唯一索引排序，所以也会产生间隙锁。

下一键锁：LOCK_ORDINARY，也称Next-Key Lock

Next-Key锁是 record lock + gap lock 的组合。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁（除非通过修改配置强制开启），只有 RR/Serializable隔离级别才有。

MySQL InnoDB工作在可重复读隔离级别（RR）下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

当查询的索引含有唯一属性（唯一索引，主键索引）时，Innodb存储引擎会对next-key lock进行优化，将其降为record lock，即仅锁住索引本身，而不是范围。

插入意向锁：LOCK_INSERT_INTENSION

插入意向锁，插入记录时使用，是一种特殊的间隙锁。这个锁表示插入的意向，只有在执行insert语句的时候才会有这个锁。

假设有索引记录的值分别是id = 1和id = 5（1到5之间没有记录），单独的事务分别尝试插入id = 2和 id = 3，在获得插入行的排它锁之前，每个事务都是用插入意图锁来锁定1和5之间的空间，但是不会相互阻塞。因为插入意向锁之间是不会冲突的。

插入意向锁会跟间隙锁或者Next-Key锁冲突：间隙锁的作用是锁住区间防止其他事务插入数据导致幻读。

在上面的场景中，假设提前有事务A获取了id 在（1，5）区间的间隙锁，那么事务B尝试插入 id = 2时，会先尝试获取插入意向锁，但是由于插入意向锁和间隙锁冲突，导致插入失败，也就避免了幻读产生。

结语

MYSQL的锁机制非常复杂，在实际的开发工作中，对于隔离级别的设置都需要非常谨慎，比如RR级别会比RC级别多出一个间隙锁，这就可能导致严重的性能问题。本文从锁的模式和锁的范围对MYSQL锁的分类进行了简单介绍，希望我们在面向数据库开发的过程中，能够仔细分析研究我们的SQL语句是否合理（尤其需要注意是否会产生死锁等问题）！

推荐学习：mysql视频教程

以上是一起聊聊MYSQL中锁的各种模式与类型的详细内容。更多信息请关注PHP中文网其他相关文章！

声明：
本文转载于：juejin.im。如有侵权，请联系admin@php.cn删除
上一篇：一起聊聊Mycat实现 Mysql 集群读写分离下一篇：你真的了解MySQL的order by吗

相关文章

查看更多