通过实例理解MySQL事务及几种锁（二）

最新推荐文章于 2024-06-24 12:04:20 发布

初心绘流年

最新推荐文章于 2024-06-24 12:04:20 发布

阅读量243

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_42332985/article/details/107776018

版权

MySQL 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

通过实例理解MySQL事务及几种锁（二）

前言

接上一篇《通过实例理解MySQL事务及几种锁（一）》的事务，这一篇我们梳理认识一下MySQL中的几种锁的概念，在介绍每一种锁的时候，会尽量的用示例来体验各种锁的效果，加深理解。每次示例测试的时候依然会选择2组事务，交叉测试锁的效果，注意测试的时候均需要开启事务测试，MySQL默认是开启自动COMMIT的，所以测试时需要显式的使用BEGIN或START TRANSACTION开启事务，否则会自动提交事务，无法测试出锁的效果。

准备条件

数据库脚本：见上一篇博客。

一、Mysql中的几种锁概念及示例体验

1. 共享锁（读锁、S锁）

共享锁，又叫读锁，S锁（share lock），顾名思义就是对同一个数据加锁后，可以共享读取数据，并且可以再继续加共享锁，不影响互相读取数据，但是不允许写入修改，如果要修改，必须等待所有的共享锁释放之后方可。

语法：

select * from table lock in share mode;

示例：

-- 事务A
BEGIN;
-- 操作1：对数据进行上共享锁
select * from my where id = 1 lock in share mode;
-- 先执行到这个地方，暂不提交事务，取事务B中测试共享锁效果

-- 操作5：在事务B的操作3结束以后，可以尝试在当前事务中更改数据看是否能行
update my set money = 200 where id = 1;

COMMIT;

-- 事务B
BEGIN;
-- 操作2：测试共享锁锁定期间读取效果
select * from my where id = 1;
-- 操作3：测试在事务A的基础上，对同一数据再上一把共享锁
select * from my where id = 1 lock in share mode;
-- 操作4：测试共享锁锁定期间，对该数据进行修改操作
update my set money = 200 where id = 1;

COMMIT;

我们开启事务A，B之后，进行操作1上共享锁，然后测试操作2和操作3，我们可以发现数据可以无阻碍的正确读取到。

在这里插入图片描述

但我们在事务B中测试操作4的时候，就会发现在事务A的共享锁期间，操作4无法修改数据，执行SQL的时候会陷入等待，等待事务A的共享锁释放。

在这里插入图片描述

事务A持有该数据的共享锁时，事务B无法对其进行更新操作，update语句会持续等待，等待所有该数据行的共享锁均被释放以后，方可完成更新操作。若在等待过程中，等待时间超出mysql设置的超时时间，则会出现超时现象。

以上就是共享锁的基本概念和示例效果

2. 排它锁（写锁、X锁）

排它锁，又叫写锁，X锁（exclusive lock），它与共享锁则相反，排它锁与其他锁互斥，当一个事务A持有数据的排它锁时，事务B则不能再获取该数据的其他锁，包括共享锁和排它锁。只有获取排他锁的事务才可以对数据进行修改。

语法：

select * from table for update;

示例：

-- 事务A
BEGIN;
-- 操作1：对数据进行上排它锁
select * from my where id = 1 for update;
-- 先执行到这个地方，暂不提交事务，取事务B中测试共享锁效果

COMMIT;

-- 事务B
BEGIN;
-- 操作2：测试排它锁锁定期间读取效果
select * from my where id = 1;
-- 操作3：测试在事务A的基础上，对同一数据再上一把共享锁
select * from my where id = 1 lock in share mode;
-- 操作4：测试在事务A的基础上，对同一数据再上一把排它锁
select * from my where id = 1 for update;
-- 操作5：测试排它锁锁定期间，对该数据进行修改操作
update my set money = 200 where id = 1;

COMMIT;

在事务A中开启操作1，然后在事务B中分别测试操作2、3、4、5，会发现除了2以外均出现等待现象，无法执行成功。

在这里插入图片描述

**至于操作2为啥可以，这一点可能有些人对排它锁理解不到位就会产生疑惑？**回头再看看排它锁的概念是，排斥其他事务再获得数据的其他锁，至于其他事务能否读取和修改，我们得看其他事务的操作是否需要获取该数据的锁，mysql的修改操作，如update、delete、insert语句都会给涉及到的数据加锁，而普通的select语句并不需要加锁，所以操作2并不会受到影响。关于操作2为什么不需要加锁，官方给出的解释是：SELECT ...... FROM 是以一种一致性的读取，它读取的是数据库的快照，并且不会上锁，除非我们将事务的隔离级别调整为SERIALIAZBLE。以下是官方的原文：

SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

3. 意向锁：意向共享锁（IS锁）和意向排它锁（IX锁）

意向锁(intention lock)，从名称上含义来很容易理解，在获取锁之前先要获取该锁的意向锁，具体分为意向共享锁和意向排它锁。意向共享锁就是事务在获取共享锁之前需要先获取的一把意向锁，同理意向排它锁。

意向锁是一种表锁，它存在的意义是为了协调行锁与表锁的关系，使MySQL支持多粒度的锁的并存。

4. 表锁

表锁，即持锁期间，锁住整个表，可以用读锁或写锁锁表，读锁锁表其他事务无法获取表的任何数据的写锁，即其他事务无法修改数据。写锁锁表时，其他事务无法进行数据的读取和修改。

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

语法：

-- 读锁锁表，会导致其他事务无法修改任意数据
LOCK TABLE 表名 READ;
LOCK TABLE 表名 WRITE

示例：

-- 事务A
BEGIN;
-- 操作1：测试读锁锁表时，其他事务读写情况
LOCK TABLE my READ;
-- 操作2：测试写锁锁表时，其他事务读写情况
LOCK TABLE my WRITE;

ROLLBACK;

-- 事务B,分别在事务A的操作1和操作2场景下测试读写情况
BEGIN;
-- 测试1：正常读取数据
select * from my where id = 1;
-- 测试2：获取共享锁
select * from my where id = 1 lock in share mode;
-- 测试3：获取排它锁
select * from my where id = 1 for update;
-- 测试4：更新操作
update my set money = 200 where id = 1;

COMMIT;

测试结果：

当事务A用读锁锁表时，测试1、2 均正常通过，3、4均出现等待现象。

当事务B用写锁锁表时，测试1、2、3、4均不可操作，出现等待现象。

5. 行级锁

行级锁，与表锁相对，只锁定特定的数据行。有的也称之为记录锁（Record Lock）。

示例：

-- 事务A
BEGIN;
-- 操作1：锁定主键为1的数据行
select * from my where id = 1 for update;

COMMIT;

-- 事务B
BEGIN;
-- 获取主键为1的排它锁
select * from my where id = 1 for update;
-- 获取主键不为1的排它锁
select * from my where id = 2 for update;
-- 测试更新操作
update my set money = 200 where id = 2;

COMMIT;

测试结果：

当事务A锁定主键为1的数据行时，事务B无法获取该数据行的排它锁

在这里插入图片描述

但事务B可以获取到主键不为1的数据行的排它锁，并进行更新操作，说明事务A只对主键为1的数据行上了锁，即行锁

在这里插入图片描述

MySQL何时用表锁，何时用行锁呢？

其实很简单，我们上面的例子中明确看到是用的行锁，那么接下来我们用一个示例来测试一下

-- 事务A
BEGIN;
select * from my where name = '张三' for update;
-- 此时我们再到事务B获取名为张三的共享锁或排它锁
COMMIT;

-- 事务B
BEGIN;
-- 当事务A获取排它锁之后，尝试获取其他行数据的共享锁
select * from my where name = '李四' lock in share mode;
-- 当事务A获取排它锁之后，尝试获取排他锁
select * from my where name = '李四' for update;

COMMIT;

我们会发现事务B无法获取其他数据行的共享锁或排它锁，说明事务A不再是锁定张三这一样数据，即事务A持有的排它锁不可能是行级锁，经过测试你就会发现，事务A持有的是表锁。

在这里插入图片描述

而我们接下来再做一个操作后，在按照上述的测试场景测试，就会发现事务A会持有张三的行级锁，不再是表级锁了。

alter table my add index name_idx(name);

再看查询结果

在这里插入图片描述

所以我们讲行锁也叫记录锁，锁的并不是数据行，而是索引记录，行锁（记录锁）使用的条件是：where条件字段存在索引。

6. 间隙锁

间隙锁（Gap Lock）, 它是基于非唯一索引的，锁定一段范围内的索引记录。间隙锁是为了解决幻读/幻行的问题。

为了直观测试，我们在测试表my上的money字段加上普通索引

alter table my add index money_idx(money);

在这里插入图片描述

示例：

-- 事务A
BEGIN;
-- 操作1 where条件中的数据不存在于数据库中
select * from my where money = 80 for update;
ROLLBACK;

-- 事务B
BEGIN;
-- 测试1
insert into my (name, money, sort) values ("柳勾勾",81,15)
-- 测试2
insert into my (name, money, sort) values ("柳勾勾",78,15)
-- 测试3
insert into my (name, money, sort) values ("柳勾勾",98,15)
insert into my (name, money, sort) values ("柳勾勾",77,15)

ROLLBACK;

上述测试中，我们在事务A中对一个数据库中不存在的记录获取了一个排它锁，然后在事务B对进行一系列的测试，测试结果我们会发现money为78和81的都无法插入进去，而98和77的可以插入进去，说明数据库针对事务A，获取了一把间隙锁，锁定了money范围为[78，98），左闭右开的区间（网上很多人说是左开右闭，但是实测确实是左闭右开，而且我没有从官方文档关于间隙锁的描述中有关于范围开闭的描述。而针对下面要提到的临键锁，确实是左开右闭的，临键锁也是一种特殊的间隙锁，估计很多人就是看到这个才说间隙锁是左开右闭的吧，如果有大佬能解释，欢迎评论！）。

在这里插入图片描述

简单的做个总结：

当使用InnoDB时，如果where条件可以精确匹配到行，并且列存在索引，那么获得的锁为行级锁/记录锁
当where条件不能匹配到精确的行时，并且列存在索引，那么获得的锁就是一个范围锁，专业术语叫间隙锁（gap lock）
当where条件匹配的列不存在索引，那么获取的将会是表锁

7. 临键锁

临键锁（next-key）其实是一种特殊的间隙锁，按照官方的说法，它是记录锁和间隙锁的一个组合。记录锁是锁定符合条件的行，间隙锁锁定的是包含这些行的一个范围，所以临键锁锁定的也是一个范围，一个左开右闭的范围。

附官方说明：

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.

示例：

在这里插入图片描述

-- 事务A
BEGIN;
-- 操作1
select * from my where money >= 78 and money <= 100 for update;
ROLLBACK;

-- 事务B
BEGIN;
-- 测试1
insert into my (name, money, sort) values ("柳勾勾",100,15)
-- 测试2
insert into my (name, money, sort) values ("柳勾勾",78,15)
-- 测试3
insert into my (name, money, sort) values ("柳勾勾",200,15)
-- 测试4
insert into my (name, money, sort) values ("柳勾勾",77,15)

ROLLBACK;

测试结果：

事务B中的测试1、2、4均出现等待超时的现象，测试3正常插入，说明锁定范围并不是[78，100]，而是相邻的键记录所对应的范围[77, 200) (关于范围，实测也是左闭右开，但是目前水平有限，找不出原因为啥？！先不纠结，以后再说吧)

在这里插入图片描述

8. 自增锁

这个锁其实就是在插入时，如果字段涉及自增字段，那么事务执行时会获得一个自增锁，自增锁是一种表锁。当另外一个事务也想获得自增锁时，必须得等待其他事务释放自增锁才可以。

9. 悲观锁（抽象概念）

悲观锁和乐观锁其实都是一种概念锁，悲观锁是指在操作数据的过程中，会使数据处于锁定状态，它具有强烈的独占和排他特性。而其中的“悲观”指的是它对数据被外界修改的可能性持保守态度，所以为了避免被修改，会对数据加上锁。悲观锁的实现，往往依靠数据库提供的锁机制(只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据)

我们前面所讲到的共享锁和排它锁都属于是悲观锁的范畴。悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证，但是因为要加锁和释放锁，所以开销上会有所增加。

10. 乐观锁（抽象概念）

乐观锁是相对于悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。乐观锁的实现一般不会采用数据库层的机制，一般是通过记录数据的版本来解决。乐观锁主要就是两个步骤：冲突检测和数据更新。其实现方式有一种比较典型的就是CAS(Compare and Swap)。

11. 死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

参考资料

《高性能MySQL》
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
https://www.cnblogs.com/mengbin0546/p/10987679.html
https://www.jianshu.com/p/8a70a4af7eac
https://blog.csdn.net/zdklhh/article/details/89766379

初心绘流年

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
通过实例理解MySQL事务及几种锁（二）

通过实例理解MySQL事务及几种锁（二）前言准备条件一、Mysql中的几种锁概念及示例体验1. 共享锁（读锁、S锁）2. 排它锁（写锁、X锁）3. 意向锁：意向共享锁（IS锁）和意向排它锁（IX锁）4. 表锁5. 行级锁MySQL何时用表锁，何时用行锁呢？6. 间隙锁7. 临键锁8. 自增锁9. 悲观锁（抽象概念）10. 乐观锁（抽象概念）11. 死锁参考资料前言接上一篇《通过实例理解MySQL事务及几种锁（一）》的事务，这一篇我们梳理认识一下MySQL中的几种锁的概念，在介绍每一种锁的时候，会尽量的用
复制链接

扫一扫