MySQL之锁-行级锁_mysql 行级锁

MySQL之锁

1.行级锁分类

行级锁行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中,MyISAM不支持行级锁

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。
行锁分以下三类：
行锁（Record Lock）
间隙锁（Gap Lock）
临键锁（Next-Key Lock）

2.行锁(Record Locks)

2.1概念

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。（ RR:read committed、RC:repeatable read）

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

如下锁住id为34对应的那一行的数据

2.2分类、兼容互斥情况

InnoDB实现了以下两种类型的行锁：
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
注：行锁中的共享锁在lock_mode字段中叫"S,REC_NOT_GAP",
行锁中的排他锁在lock_mode字段中分别叫"X,REC_NOT_GAP"

2.3查看意向锁与行锁

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;
1
2

2.4操作讲解(共享锁与共享锁兼容)

创键表：
id有索引，name与age是没有索引的

CREATE TABLE `stu` (
`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
INSERT INTO `stu` VALUES (1, 'tom', 1);
INSERT INTO `stu` VALUES (3, 'cat', 3);
INSERT INTO `stu` VALUES (8, 'rose', 8);
INSERT INTO `stu` VALUES (11, 'jetty', 11);
INSERT INTO `stu` VALUES (19, 'lily', 19);
INSERT INTO `stu` VALUES (25, 'luci', 25);
1
2
3
4
5
6
7
8
9
10
11

左窗口看开启事务，并进行查询

右窗口查看意向锁与行锁:
发现没有任何锁，原因是select语句的话不加任何锁

右窗口开启事务，也进行查询：
发现成功，原因是：select不会加锁

左窗再次select并在后面加一把共享锁

右窗口再次查看意向锁与行锁:
此时可以看到有两个锁，一个意向共享锁；
S是指行锁中的共享锁，REC_NOT_GAP是指没有间隙，即为id为1的这一行数据加了一行共享锁

右窗口再次select查询并加lock in share mode，可以成功查到：
原因：共享锁与共享锁之间是可以兼容的
id为1的那一行数据被加了两把共享锁

右窗口提交事务,右窗口再次开启事务：
发现刚刚右窗口的事务给加的锁释放掉了，但是左窗口的事务里的锁还一直存在

2.5操作讲解(共享锁与排他锁互斥)

紧接着上面继续进行操作

右窗口为id为3的数据进行update，发现成功：
对id为3的数据进行操作，与id为1的那一行之间没有任何关系

右窗口为id为1的数据进行update，发现失败，阻塞住了：
update想要加一把排他锁，排他锁与共享锁之间是互斥的，所以失败

右窗口ctrl c 中断命令，左窗口把事务进行提交，右窗口再次查看锁的情况：
id为1的那一行的数据的共享锁也就释放了，但是id为3的排他锁还存在着

右窗口提交事务，并再次查看锁：
empty set，现在所有锁都已经释放

2.6操作讲解(排他锁与排他锁互斥)

紧接着上面的操作
左窗口开启事务，并进行update更新id为1的数据，成功：

在窗口也开始一个事务，右窗口也update更新id为1的数据，发现失败：
原因：排他锁与排他锁之间冲突

左窗口把事务提交，右侧update id为1的数据成功：
原因是事务提交后，会释放左窗口加的那个排他锁

2.7操作讲解(无索引行锁升级为表锁)

左窗口查询查看当前表，左窗口开启事务，右窗口也开启事务

左窗口进行update更新name “Lei”，成功：
name不是索引，所以升级为表锁(锁因为行锁是对索引项加的锁，而name没有索引)

右窗口进行update更新id为3 的数据行，发现处于阻塞状态：
原因，刚刚update name的时候加了个表锁

左窗口把事务进行提交，右窗口的update立马成功，右窗口也把事务进行提交

左窗口进行查询，做窗口对name字段创建一个普通索引

左窗口开启事务，进行update更新name “Lei”，成功

右窗口开启事务，右窗口update id为3的行，发现更新成功了

3.间隙锁&临键锁(Gap Locks&Next-Key Lock)

3.1间隙锁概念

间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持（RR:repeatable read）

比如6与12之间有一定的间隙，16到18之间有一定的间隙，29到34之间有一点定的间隙，18到29之间有一定的间隙

3.2临键锁概念

临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

注：行锁中的临键锁在lock_mode字段中叫"S",行锁中的间隙锁在lock_mode字段中叫"X,GAP",

3.3间隙锁&临键锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key临键锁进行搜索和索引扫描，以防止幻读。
1)索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
2)索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
3)索引上的范围查询(唯一索引),会访问到不满足条件的第一个值为止，加上next-key临键锁。
注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
注：主键索引也是唯一索引；

讲解索引上的等值查询(普通索引):
我们在加行锁的时候，是为索引加的锁，索引是一个B+树，B+ 树的叶子节点是一个双向链表;
假如id不是唯一索引，即普通索引(二级索引)时，我们要根据id=18的那一行数据进行查询并给它加上一个行锁中的共享锁，此时它仅仅将id为18的这条记录锁住就完事了么？并不是，现有的记录里有18，将来的事务有可能再添加一条数据段为18的记录，对于普通索引来说，即18之后可能会再添加一条18的数据，因为它不是唯一索引，所以需要有一定的解决方案：
向右遍历时还需要为18之后的间隙加锁，找到最后一个不满足查询的，即29，那么会对18到29之间的间隙进行加锁以及18之前的间隙进行加锁（并不会对29加锁），

3.4查看间隙锁&临键锁、行锁、意向锁

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;
1
2

3.5操作讲解(等值查询唯一索引)

左窗口进行查询，左窗口开启事务，左窗口update id =5 的数据行成功(表中是没有id=5这条数据的)：
索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁

查看锁：
有一个意向排他锁，还有一个行锁中的排他锁，还有一个间隙锁，PRIMARY指主键索引，lock_data为8指锁的是3到8之间的间隙(不包含8与3)

右窗口开启一个事务，右窗口插入一个id为7的数据，发现失败，处于阻塞：

左窗口进行commit提交事务，右窗口的selet语句立马成功

右窗口提交事务，左窗口进行查询，发现插入成功

3.6操作讲解(等值查询普通索引)

左窗口为name字段创键普通索引，并开启事务

在左窗口进行对age字段进行等值查询，并在select 后面加上行锁中的共享锁，查询成功:

在右窗口进行查看锁：
第一行，表级的意向锁
表中第二行讲解：S代表加了把临键锁，“3，3”是指首先会对”age=3“的那一行数据进行锁住，然后会对3之前的间隙锁住；
表中第二行讲解：“S，REC_NOOT_GAP"代表加了把行锁，“3，3”是指首先会对”age=3“的那一行数据进行锁住，然后会对3之前的间隙锁住；
表中第四行讲解："S,Gap"代表间隙锁，把3与7之间的间隙锁住，为什么要把3与7之间的间隙锁住呢？原因是age是非唯一索引，为了防止其他事务再往间隙里再去插入记录出现幻读现象，所以要把与7之间的间隙也锁住，

3.7操作讲解(临键锁)

紧接着上面的操作，左窗口提交事务；
左窗口开启事务，并进行范围查询且在后面加上lock in mode

在右窗口进行查看索引:
第二行讲解：对19的记录加了个行锁
第三行讲解：supermum pseudo-record 指的是正无穷的意思，指的是大于25的正无穷大的后面都加了个临键锁
第四行讲解：加了个临键锁，锁的是25那一行及25之前的间隙

查询的条件为id>=19，并添加共享锁。 此时我们可以根据数据库表中现有的数据，将数据分为三个部
分：
[19]
(19,25]
(25,+∞]
所以数据库数据在加锁是，就是将19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷的临键锁(正无穷及之前的间隙)。

4.附

4.1无索引行锁升级为哪种表锁

经过以下实验，并没有看得出来升级为了哪种表锁