您的位置:首页 > 教程 > Mysql/MariaDB > MySQL串行化隔离级别(间隙锁实现)

MySQL串行化隔离级别(间隙锁实现)

2022-06-15 17:09:59 来源:易采站长站 作者:

MySQL串行化隔离级别(间隙锁实现)

目录
一、间隙锁的概念二、测试间隙锁范围加锁场景1:用不可重复的主键id测试间隙锁场景2:用可重复的age(有索引)测试间隙锁场景3:实际情况需要具体分析用的到底是行锁还是表锁三、测试间隙锁等值加锁1. 测试不能重复的主键索引2. 测试能重复的辅助索引

串行化隔离级别怎么解决幻读问题?
先说下幻读的含义,幻读就是在事务中按照同样的条件前后两次查询的结果数据量不同。

解决串行化的幻读问题用间隙锁(gap lock),间隙锁是给不存在的记录加锁,要正确理解间隙,知道间隙的范围。条件无非就是两类:范围查询和等值查询。再说下范围查询和等值查询都是怎么加间隙锁的,分别从主键索引和辅助索引两个场景来说。

一、间隙锁的概念

我们把事务2 select的指定的条件分为2类:范围查询、等值查询

record lock(记录锁,就是行锁)
gap lock(间隙锁)
next-key lock:record lock 和 gap lock

二、测试间隙锁范围加锁

设置事务为手动提交,隔离级别设置成串行化

查看表结构,id、age和name都有索引

场景1:用不可重复的主键id测试间隙锁

做范围查询

事务2的select操作只给三行数据加了排它锁,为什么插入id=24的数据也不行?

这是因为在串行化隔离级别中,不仅仅是获取了满足条件的这3行的行锁,而且把表数据后边空洞的地方也上了间隙锁。

图中红色线的地方都上了间隙锁,上锁范围(左开右闭)为:( 11 , 12 ] ∪ ( 12 , 22 ] ∪ ( 22 , 23 ] ∪ ( 23 , + ∞ ]

12,22,23是三个行记录,因为过滤条件是用id带有索引的,所以select获取了12,22,23的共享行锁(record-lock), 还把间隙加了间隙锁,其实就是给间隙加上共享锁或者排他锁,将间隙锁和行锁统称next-key lock(record-lock和gap-lock),也就是说where id>11加了next-key lock。正是因为给空洞也加锁了,所以事务1再想获取间隙的排它锁是不可以的,因为共享锁和排它锁是不能共存的。

由于事务2是select,所以是给间隙加上了共享锁,如果事务1做select id>11还是可以的,不能update、insert、delete id>11的数据。

场景2:用可重复的age(有索引)测试间隙锁

测试辅助索引树上,间隙锁的范围

我们先查看表结构、表数据,然后回滚。

根据表的内容建简单的辅助索引

开启事务进行测试

很明显,由于age>20的区间都被事务1加上了间隙锁(这里加的是共享锁),所以事务2插入age=22和age=21都失败了

幻读就是同一事务两次用相同的条件查询数据,下一次查出的数据量和上一次的数据量不一样,就算事务1把age=20的数据插入表,事务2再用age>20查询,得到的数据量也不会改变。

那事务1插入age=20的数据能否成功呢?


依然不能成功,这是因为我们插入的数据id是自增的,所以这条数据为(age=20,id=24),位于辅助索引树中(age=20,id=12)的右边,由于(age=20,id=12)右边都被上了锁,(age=20,id=24)自然无法插入。

辅助索引值相等的话。主键按升序排列。

很显然,事务1插入的age=18和age=19都不在事务2上锁的范围,所以可以插入

场景3:实际情况需要具体分析用的到底是行锁还是表锁

回滚,重新开启事务

开始测试

我们发现事务1无论是插入age>18范围内的数据,还是范围外的数据,都无法成功

这时我们就要分析了,这应该没有用到索引,因为我们用索引,过滤出的数据占了整张表的一大半,MySQL server没使用索引。

没有加行锁,只能加表锁(这时加的是共享锁),所以事务1无论插入什么数据都不行

果然,没有用到索引

age>20用到了索引,所以可以用行锁

三、测试间隙锁等值加锁

查看表结构和表数据

设置手动提交,设置串行化隔离级别,回滚然后启动事务

1.>

此时事务2做select操作,由于是等值查询,所以给这条数据加了共享锁。

事务2的主键或者唯一键进行等值查询的时候,事务1插入一个新的数据是可以成功的,因为主键id不能重复,我们不能再插入主键id=9的数据。

在这种情况下,主键或者唯一键是不能重复的,事务2进行等值查询时,事务1插入一个新的数据,不用担心这条数据和查询条件是一样的,所以肯定能成功

2.>

回滚并重启事务

事务2等值查询,给age=18这行数据加上了共享锁(record-lock)

这是一个等值查询,而且用的是辅助索引age,那么在辅助索引age的辅助索引树上叶子节点存的是age的辅助索引值和它所在行的主键值,

事务1插入age=18是不被允许的,否则事务2再查询age=18就有两条记录了。

奇怪的是,我们插入age=17,16,15也被阻塞住了

这是因为,为了防止幻读,除了age=18这条数据加了共享锁,其两侧也被加了间隙锁。

如果插入(age=15,id=1)就可以成功,根据辅助索引值相同,按照主键值升序排列,(age=15,id=1)应该放在(age=15,id=7)前面,不在间隙锁范围内

插入age=14,13都可以成功,不在间隙锁范围内。

间隙锁是给不存在的数据记录的范围加锁:

    对于辅助索引,若值允许重复,在串行隔离级别中如果进行等值查询,InnoDB会给数据加上行锁和间隙锁(防止别的事务插入索引值重复的数据,造成幻读)对于主键索引,或者唯一键索引,值不允许重复,那只需要加行锁就够了(对于唯一键索引,不可能发生插入索引值重复的数据)

    到此这篇关于MySQL串行化隔离级别(间隙锁实现)的文章就介绍到这了,更多相关MySQL 间隙锁内容请搜索易采站长站以前的文章或继续浏览下面的相关文章希望大家以后多多支持易采站长站!

    如有侵权,请发邮件到 gridf@126.com

相关文章

  • mysql 5.7.30安装配置方法图文教程

    mysql 5.7.30安装配置方法图文教程

    之前把服务器里面的MySQL卸了重装,安装mysql时未做总结,换新电脑,补上安装记录,安装的时候,找了些网友的安装记录,发现好多坑 截个图,作为笔记,也正好留给需要的朋友们。
    2019-01-03
  • MySQL5.6免安装版环境配置图文教程

    MySQL5.6免安装版环境配置图文教程

    MySQL是一个小巧玲珑但功能强大的数据库,目前十分流行。但是官网给出的安装包有两种格式,一个是msi格式,一个是zip格式的。很多人下了zip格式的解压发现没有setup.exe,面对一堆文
    2019-01-04
  • Linux下安装mysql-8.0.20的教程详解

    Linux下安装mysql-8.0.20的教程详解

    ** Linux下安装mysql-8.0.20 ** 环境介绍 操作系统:CentOS 7 mysql下载地址:https://dev.mysql.com/downloads/mysql/ 下载版本:mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz 卸载mysql 查看是否安装过mysql,命令:fin
    2020-05-24
  • linux环境下安装mysql数据库的详细教程

    linux环境下安装mysql数据库的详细教程

    1.安装数据库 1)yum -y install mysql-server(简单) yum命令自动从网上寻找mysql服务资源,下载至本地并完成安装 2)也可以自己在网上下载mysql服务,通过xftp传输至Linux系统,自己安装(一般安
    2020-06-20
  • MySQL8.0.20安装教程及其安装问题详细教程

    MySQL8.0.20安装教程及其安装问题详细教程

    原文地址:https://blog.csdn.net/m0_46579864/article/details/105981304 官网下载MySQL的安装包 1.下载链接如下: MySQL8.0.20版本 https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html 其他版本:MySQL8.0.16版本
    2020-05-10
  • MySQL对数据库操作(创建、选择、删除)

    MySQL对数据库操作(创建、选择、删除)

    MySQL 创建数据库 我们可以在登陆 MySQL 服务后,使用 create 命令创建数据库,语法如下: CREATE DATABASE 数据库名; 以下命令简单的演示了创建数据库的过程,数据名为 RUNOOB: [root@host]# mysql
    2020-07-01
  • Linux系统MySQL8.0.19快速安装配置教程图解

    Linux系统MySQL8.0.19快速安装配置教程图解

    一、环境介绍 Linux系统使用yum安装MySQL需要从网上下载MySQL的一系列组件,这个过程非常耗时且有下载中断的可能,如果想要快速安装MySQL,可以先在网上将MySQL的离线包下载下来传到系
    2020-02-27
  • mysql 加了 skip-name-resolve不能链接数据库问题的解决方法

    mysql 加了 skip-name-resolve不能链接数据库问题的解决方法

    mysql 加了 skip-name-resolve不能链接的问题, 要确认 MySql 是否采用过主机名的授权 在 MySql Server 的配置文件 My.ini 中,增加如下两行: [mysqld] skip-name-resolve 它将禁止 MySql Server 对外部连接进
    2019-01-04