MySQL之锁-行级锁

已于 2022-05-23 10:32:15 修改
阅读量1.7k 收藏 6
点赞数 2
分类专栏： MySql 文章标签： mysql 数据库 database
于 2022-03-15 07:41:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_52690231/article/details/123483019
版权

文章目录

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;

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);

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

右窗口查看意向锁与行锁:
发现没有任何锁，原因是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;

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无索引行锁升级为哪种表锁

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

在这里插入图片描述

GoGo在努力
关注
  • 2
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL行级锁、表级锁、页级锁详细介绍
09-10
主要介绍了MySQL行级锁、表级锁、页级锁详细介绍,同时列举了一些实例说明,需要的朋友可以参考下
Mysql 行级锁的使用及死锁的预防方案
09-09
mysql的InnoDB,支持事务和行级锁，可以使用行锁来处理用户提现等业务。使用mysql锁的时候有时候会出现死锁，要做好死锁的预防。这篇文章通过实例应用给大家讲解
(12) 锁
杉杉coming！
04-14 890
1 概述 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。 如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。 MySQL中的锁，按照锁的粒度分，分为以下三类： 全局锁：锁定数据库中的所有表。 表级锁：每次操作锁住整张表。 行级锁：每次操作锁住对应的行数据。 2 全局锁 2.1 介绍 全局锁就是对整个数据库实例加锁
死磕数据库系列（十四）：MySQL 锁机制详解（表级锁、页级锁、行级锁）
民工哥的博客
02-24 2153
点关注公众号，回复“1024”获取2TB学习资源！MySQL 的锁知识，跟索引知识一样，都同样的复杂，甚至更复杂。所以还需要一些耐心哟！MySQL中锁的分类下图为MySQL部分存储引擎所支持的锁MySQL支持三种层级的锁定我们知道，MySQL支持三种层级的锁定，分别为：表级锁定表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持...
mysql行锁查询
xyz的博客
01-23 8213
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
MySQL中的行级锁
个人专长：数据结构和算法、大数据、数据库、主流框架
06-13 6865
学习林老师大作，醍醐灌顶，面试高频
Mysql行级锁
梁诗傑的博客
11-08 1051
等值查询命中加记录锁，辅助索引两侧加间隙锁；范围查询命中加临键锁，主键索引加记录锁；索引未命中加间隙锁。
MySQL行锁
weixin_56289362的博客
04-30 5521
行锁： 行锁也称为记录锁，顾名思义，就是锁住某一行（某条记录row）。需要的注意的事，MySQL服务器层并没有实行行锁机制，行级锁只在储存引擎实现。 优点：锁的力度小，发生锁冲突概率低，可以实现高并发。 缺点：对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。 InnoDB与MySAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。 ...
MySQL行锁浅析
沛哥儿的专栏
06-01 2332
本文介绍了MySQL中行级锁的基础概念、实现原理、类型及其使用场景。MySQL行级锁是细粒度锁，可以保证不同线程对同一数据的访问的隔离性。行锁使用的锁类型多种多样，我们需要根据实际情况选择不同的锁类型，避免出现无法获取到锁的情况。通过使用行级锁，我们可以较好地解决高并发情况下的数据一致性问题，提高系统的并发性能。
MySQL数据库——锁-行级锁（行锁、间隙锁和临键锁）
最新发布
li13437542099的博客
12-22 1390
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
MySQL中的行级锁、表级锁、页级锁
09-10
主要介绍了MySQL中的行级锁、表级锁、页级锁，以及分享了多种避免死锁的方法，感兴趣的小伙伴们可以参考一下
脏读 不可重复读 幻读 行级锁（共享锁，排它锁，死锁现象）表级锁 页级锁 innodb存储引擎 innodb三种行锁的算法 事务隔离级别 乐观锁与悲观 mvcc机制锁
Yosigo_的博客
02-25 1763
脏读（无效的数据） a事务把数据改完之后并没有提交，b事务读到这个改完数据之后的事务， b事务读完之后，a事务又把数据做了一个回滚操作，这种现象叫脏读 不可重复读 a事务把数据读完拿去用了，b事务刚好直接把数据给改了，a事务会发现之前的数据不准确了 幻读现象 是不可重复读的一种特殊现象， 举例：假设一张表一共有10条数据，a事务把id大于3的数据name全部改成了xx， 就在刚刚改完的那一刻，b事务又插入一条数据，a事务改完之后，会发现有一条数据没有修改成功 以上现象都是并发带来的一些问题，mysql的底层
MySQL 行级锁（行锁、临键锁、间隙锁）
weixin_44300779的博客
02-11 1万+
三、行级锁 行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。 InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。 对于行级锁，主要分为以下三类： 1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。 2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行i
MySQL中的锁（表锁、行锁）
2301_78145678的博客
06-15 5048
锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。 MySQL大致可归纳为以下3种锁： 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，
Mysq锁机制，行级锁和表锁
weixin_37674052的博客
07-23 139
mysql行级锁和表锁
MySQL数据库的全局锁、表级锁和行级锁的全面深度解析
m0_73311735的博客
08-18 2076
详细介绍了MySQL数据库中的全局锁、表级锁和行级锁的概念、使用方式，以及相关特性。
就这一次，带你彻底搞清 MySQL行级锁的加锁规则
weixin_50205273的博客
11-12 2060
是不是很多人都对 MySQL 加行级锁的规则搞的迷迷糊糊，一会是 next-key 锁，一会是间隙锁，一会又是记录锁。 坦白说，确实还挺复杂的，但是好在我找点了点规律，也知道如何如何用命令分析加了什么类型的行级锁。 之前我写过一篇关于「MySQL 是怎么加行级锁的？」的文章，随着我写 MySQL 锁相关的文章越来越多时，后来发现当时的文章写的不够详细。 为了让大家很清楚的知道 MySQL 是怎么加行级锁的，以及如何用命令分析加了什么行级锁，所以我重写了这篇文章。
MySQL全局锁、表级锁、行级锁介绍&演示（详细）
weixin_45151960的博客
04-14 990
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）等争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。全局锁就是对整个数据库实例加锁，加锁后整个数据库实例处于只读状态，提交的写入、更新、删除操作语句都会被阻塞。最典型的使用场景就是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
五分钟了解Mysql的行级锁——《深究Mysql锁》
热门推荐
爱雨轩
08-23 4万+
我们首先需要知道的一个大前提是：mysql的锁是由具体的存储引擎实现的。所以像MyISAM和InnoDB引擎的锁实现机制是有区别的。 Mysql有三种级别的锁定：表级锁定、页级锁定、行级锁定 表级锁定参考：三分钟了解Mysql的表级锁——《深究Mysql》 页级锁定参考：[三分钟了解Mysql的页级锁——《深究Mysql》] 一、定义 每次锁定的是一行数据的锁机制就是行级别锁定(r......
mybatis mysql 行级锁解锁
08-18
在MySQL中，行级锁的解锁是由数据库管理的，而不是由MyBatis控制的。MySQL会在事务结束时自动释放行级锁。 如果您想手动解锁行数据，可以通过提交或回滚事务来释放行级锁。以下是一个示例： ```java try { // 开启事务 sqlSession.getConnection().setAutoCommit(false); // 锁定行数据 YourEntity lockedEntity = yourMapper.selectForUpdate(id); // 执行其他操作 // 提交事务，释放行级锁 sqlSession.commit(); } catch (Exception e) { // 发生错误，回滚事务，释放行级锁 sqlSession.rollback(); } finally { // 关闭会话 sqlSession.close(); } ``` 在上述示例中，我们首先手动开启了事务，并将自动提交设置为false。然后，我们执行了`selectForUpdate`方法来锁定一行数据，并在此之后执行其他操作。如果在执行过程中发生错误，可以通过回滚事务来释放行级锁。最后，无论是否发生错误，都需要关闭会话。 请注意，手动解锁行数据可能会破坏数据库的事务一致性，因此请谨慎使用。只有在确保解锁操作不会引发其他问题时，才应该手动解锁行数据。同时，还要注意MySQL的隔离级别对行级锁的影响，不同的隔离级别可能会导致不同的锁定行为。建议在使用行级锁之前，了解并理解MySQL的隔离级别和行级锁的工作原理。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

GoGo在努力

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值