数据库锁与加锁情况介绍（记录锁、间隙锁、next-key锁）

最新推荐文章于 2024-03-29 21:14:06 发布
最新推荐文章于 2024-03-29 21:14:06 发布
阅读量1.9k 收藏 6
点赞数 1
分类专栏： 面试 数据库 文章标签： 数据库 mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42272869/article/details/122034921
版权

数据库的锁

关于mysql有哪些锁？锁的分类有哪些？可查看另外一篇博客：mysql 的锁

下面主要是记录在实际情景中的加锁情况进行分析

测试环境介绍

innodb 引擎下，隔离级别在 REPEATABLE READ 进行测试

表结构介绍

（1）简单的测试表一：study表，id为主键

create table study(

id int  primary key,

name varchar(255),

age int

) engine=innodb;
insert study values(7 , 'g' , 21);

（2）简单的测试表二：study表，id为主键

create table my_table(
id int  primary key,
year int
) engine=innodb;
insert into my_table values(1, 2000),(2,2005),(3,2007),(4,2010),(5,2012),(6,2017);

隔离级别的介绍

1、数据库的隔离级别：

能”表示能解决，“否”表示不能解决。

脏读不可重复读幻读
未提交读否否否
提交读能否否
可重复读能能否
串行读能能能

2、查询数据库的隔离级别

SELECT @@tx_isolation

3、修改数据库隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ| SERIALIZABLE}

  • 隔离级别名称可选值：READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ，SERIALIZABLE
  • global：设置全局session，但当前session不受影响。
  • session，设置当前和之后的所有session。
  • 都不写：设置当前session下一个还未开始的事务。

4、本测试环境使用的隔离级别

请添加图片描述

加锁情况介绍

具体查看这篇博客，这里大概还是列举一下锁有哪些：

mysql 的锁

注意：MyISAM引擎，如果需要进行锁表的操作，必须用lock table来进行，delete、update,insert语句会自动锁整个表

下面是对innodb引擎的锁类型进行介绍：

1、锁分类介绍：

（1）从锁的范围来分

  • 行锁：记录锁、间隙锁、next-key 锁
  • 表锁

（2）从对资源访问是否加锁来分：

  • 乐观锁：不对资源加锁，只有在更新的时候才会判断数据会不会被修改，如果被修改，则修改失败
  • 悲观锁：访问的时候就对资源加锁，加锁后别的访问不允许对其进行更新/删除操作

在这里插入图片描述

（3）从对数据的访问形式来分

  • 读锁：共享读
  • 写锁：独享写

2、加锁具体的实现方式

（1）默认的加锁及其对应加锁的类型

  • 普通的select(没有加lock in share mode或for update)不会锁表或锁行的
  • 常见的增删改（INSERT、DELETE、UPDATE）语句会自动对操作的数据行加写锁，其他对该数据进行操作的时候需要等待该事务提交后

（2）显式的加锁

  • select xxxxx lock in share mode ：加的是共享读锁，不会影响其他事务的读(这里指不加锁的读，可以再次加共享读锁，读上加写锁会失败)，但会影响其他事务的写
  • select xxxx for update ： 加的是写锁，不加锁的读不会被影响，任意加锁的读会受影响，其他事务的写也会受影响

（3）举例：（在study表上进行测试）

举例一：测试共享读（lock in share mode）

事务一：

开启一个事务，对 id 为 7的加一个共享读锁（lock in share mode），不提交事务

在这里插入图片描述

事务二：

第一次尝试：开启一个事务，对 id 为 7的进行更新（会尝试获取到锁），操作不成功

在这里插入图片描述

第二次尝试：开启一个事务，也对 id 为 7的进行查询（不加锁的查询），操作成功

在这里插入图片描述

第三次尝试：开启一个事务，也对 id 为 7的进行查询（加共享读锁的查询），操作成功

在这里插入图片描述

第四次尝试：开启一个事务，也对 id 为 7的进行查询（加独享写锁的查询），操作失败

在这里插入图片描述

举例二：测试写锁（for update）

事务一：

开启一个事务，对 id 为 7的加一个写锁（for update），不提交事务

在这里插入图片描述

事务二：

第一次尝试：开启一个事务，对 id 为 7的进行更新（会尝试获取到锁），操作不成功

在这里插入图片描述

第二次尝试：开启一个事务，也对 id 为 7的进行查询（不加锁的查询），操作成功

在这里插入图片描述

第三次尝试：开启一个事务，也对 id 为 7的进行查询（加共享读锁的查询），操作失败（加写锁的读同理，也不会操作成功）

在这里插入图片描述

不同索引下的加锁问题

判断加不同的行锁时候加锁的类型是啥

（1）主键索引

上面举例介绍得到的一些总结，当id为主键的时候 对study 表的一些sql的加锁情况：

1. select * from study where id = 7; # 快照读，对其他的读和写没有影响
2. select * from study where id = 7 lock in share mode; # 加共享读锁，不影响其他的加共享读，但是影响加任意形式的写锁
3. select * from study where id = 7 for update; # 加写锁，影响加任意形式的读或者写锁

（2）非唯一索引

基于my_table这个表进行测试，目前的数据大小范围是**[2000 , 2017]**

在这里插入图片描述

第一个事务：

在year字段上加上一个普通的索引，然后进行between查询

create index idx on my_table(year);
start transaction;
select * from my_table where year between 2007 and 2010 for update;

第二个事务：

start transaction;
insert into my_table values(10 , 2004); =>Query OK,
delete from my_table where year=2005; =>Query OK,
insert into my_table values(11 ,2005); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(11, 2006); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(11, 2007); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(11, 2008); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(11, 2010); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(11, 2011); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
delete from my_table where year=2012; => ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(11, 2012); =>Query OK,

在这里插入图片描述

加锁分析：

（1）加锁范围：(2005,2012], 也就是在2007,2010,2012上各加了一个Next key lock（记录锁+间隙锁）

在这里插入图片描述

（3）非唯一索引，唯一查询

事务一：

开启事务，查询2010这条记录，目前这个记录是唯一的一条数据

start transaction;
select * from my_table where year =2010 for update;

事务一：

delete from my_table where year=2007 => Query OK
insert into my_table values(12 ,2007); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(12 , 2009); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into my_table values(12 ,2010); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into my_table values(12 ,2011); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from my_table where year=2012; => Query OK

加锁分析：

（1）加锁范围：(2007,2012), 在2010上加了一记录 锁，在2007-2010，2010-2012上加了一个间隙锁

在这里插入图片描述

（4）唯一索引，唯一查询

创建唯一索引

drop index idx on my_table;
create unique index idx on my_table(year);

事务一：

开启事务，查询2010这条记录，目前这个记录是唯一的一条数据，year也是唯一索引

start transaction;
select * from my_table where year =2010 for update;

事务二

insert into my_table values(13, 2009); =>Query OK
insert into my_table values(14, 2010); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(15 ,2011); =>Query OK

加锁分析：只在2010上加了一个行锁（record锁）

（5）唯一索引，不唯一查询

事务一：

开启事务，between查询

start transaction;
select * from my_table where year between 2007 and 2010 for update;

事务二：

delete from my_table where year=2005; => Query OK
insert into my_table values(15, 2006); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(15, 2007); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(15, 2008); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(15, 2010); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(15, 2011); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(15, 2012); =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
delete from my_table where year=2012; =>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert into my_table values(15,2013); =>Query OK

加锁分析：

（1）加锁范围：(2005,2012], 也就是在2007,2010,2012上各加了一个Next key lock（记录锁+间隙锁），与非唯一索引between查询情况一致

（6）总结

a、只有字段是唯一索引且只有一条记录的情况下，不用加Gap锁，其他情况都要加Gap锁。

b、间隙Gap锁锁定的区域：根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。

c、如果不加索引进行加锁的扫描，那就是全表扫描，直接就是加上表锁。不涉及到更细粒度的行锁了

pan_mlpan
关注
  • 1
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL锁
Mr.xing的博客
03-12 2442
遇见已是上上签 岂敢追求到永远MyISAM使用表级锁，InnoDB采用更细粒度的行级别锁，提高了数据表的性能。InnoDB行级别的锁通过索引来实现，如果查询条件中有主键则锁定主键，如果有索引则先锁定对应索引然后再锁定对应的主键（可能造成死锁），如果没有索引则会锁定整个表。悲观锁：操作数据库时采用悲观的态度，认为别人会在此时并发访问数据库。
MySQL(一)共享锁 排它锁 意向锁 记录锁　间隙锁
qq_38478903的博客
03-09 1432
1.共享锁和排它锁 共享锁(shared lock): 又叫读锁，一个事务获取了一行的共享锁后，其它事务可以再获取这一行的共享锁，不能获取这行的排它锁 加锁: select * from tb_student where id = 1 lock in share mode 释放: commit / rollback 排它锁(excusive lock): 又叫写锁，一个...
mysql锁相关知识
qq_55819440的博客
03-13 337
系列文章目录 文章目录系列文章目录前言一、概述二、MySQL并发事务访问相同记录2.1读-读情况2.2 写-写的情况2.3读-写或写-读情况2.4 并发问题的解决方案三、 锁的不同角度分类3.1 从数据操作的类型划分：读锁、写锁1.锁定读2.写操作3.2 从数据操作的粒度划分:表级锁、页级锁、行锁1.表锁①表级别的S锁、X锁②意向锁1. 意向锁要解决的问题③自增锁（了解）④元数据锁（MDL锁）1.行锁①记录锁②间隙锁③临键锁（(Next-Key Locks)）④插入意向锁（lnsert Intention
数据库篇——锁
一个热爱编程的大学僧
05-31 1394
在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行 所在数据表的对应意向锁。
一文搞懂数据库中的“锁”（图文详解）
最新发布
cy的Java学习笔记
03-29 1810
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。锁的分类**全局锁：**锁定数据库中的所有表。**表级锁：**每次操作锁住整张表。**行级锁：**每次操作锁住对应的行数据。
MySQL数据库锁详解
iuu77的博客
02-20 1426
一、锁的分类一、按照粒度分类1、分类2、代码操作二、按照属性分类1、共享锁2、排他锁3、代码操作三、按照加锁方式分类1、隐式的自动加锁2、显示的加锁四、按照算法分类1、间隙锁2、临键锁3、记录锁五、按照模式分类1、悲观锁 2、乐观锁六、按照状态分类1、意向锁的解释1、意向共享锁2、意向排它锁二、锁的监控一、相关变量二、系统的表信息三、死锁一、如何处理死锁二、如何避免死锁
闲谈mysql三种行锁（记录锁、间隙锁与临键锁）
06-11 987
行锁 行锁在 InnoDB 中是基于索引实现的，但是如果某个加锁操作没有使用索引，那么该锁就会退化为表锁。 行锁的分类 记录锁（Record Locks） 记录锁就是为某行记录加锁，它封锁该行的索引记录： SELECT*FROMtableWHEREid = 1FORUPDATE;　　 UPDATESETage = 30WHEREid = 1; 注意： id列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。 同时查询语句必须为精准匹配（=），不能为...
MySQL InnoDB之事务与锁详解
09-11
- 间隙锁（Gap Lock）和Next-Key Lock：防止幻读，锁定索引记录以及相邻的间隙，即使数据不存在也会被锁定。 事务隔离级别的选择会影响并发性能和数据一致性。例如，读未提交级别允许脏读，可能会读到未提交的数据...
MySQL数据库-事务、锁及SQL优化
08-25
- **行锁**：包括共享锁(S)和排他锁(X)，以及记录锁、间隙锁、Next-Key Lock和插入意向锁。例如，`SELECT ... FOR SHARE/UPDATE` 可以在查询时添加共享或排他锁。 - **表锁**：分为意向锁(IS/IX)和自增锁。意向锁...
MySQL InnoDB中的锁机制深入讲解
09-09
InnoDB的Next-Key Lock实际上是结合了记录锁和间隙锁，锁定当前记录并防止在相邻间隙中插入记录。 以下是一个例子，展示了如何在不同场景下使用和应用这些锁： - 如果事务A对name为1的记录加锁，那么name为1的记录...
MySQL锁的详细介绍
09-28
* Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。 三、悲观锁 悲观锁（Pessimistic Locking）是指在数据处理过程，将数据处于锁定状态，一般使用数据库的锁机制实现。 在 MySQL 中...
mysql insert锁机制1
08-03
- Next-Key Locks：结合了行锁和Gap锁，锁定索引记录及其前后的间隙，以确保事务隔离。 当进行插入操作时，InnoDB会根据查询条件和隔离级别决定是否需要加锁以及加锁的类型。如果在索引上进行插入，InnoDB可能会...
数据库如何加锁？锁是用来干嘛的？（转载）
kikkc的博客
08-18 2543
感谢内容提供者：金牛区吴迪软件开发工作室 文章目录 数据库中的共享锁与排它锁 为什么要加锁 共享锁 用法 排他锁 用法 加锁原则 数据库中的乐观锁与悲观锁 悲观锁 悲观锁实现方式 乐观锁 乐观锁实现方式 如何选择乐观锁还是悲观锁 数据库中的共享锁与排它锁 共享锁（S锁），又称为读锁，如果数据对象加上共享锁之后，则该数据库对象可以被其他事务查看但无法修改和删除数据。 排他锁（X锁），又称为写锁、独占锁，如果数据对象加上排他锁，则其他的事务不能对它读取【如果读取没有加锁是可以读取的】和修改。 对于共享锁大家可能
Java中的锁分类
weixin_30729609的博客
05-09 1199
在读很多并发文章中，会提及各种各样锁如公平锁，乐观锁等等，这篇文章介绍各种锁的分类。介绍的内容如下： 公平锁/非公平锁 可重入锁 独享锁/共享锁 互斥锁/读写锁 乐观锁/悲观锁 分段锁 偏向锁/轻量级锁/重量级锁 自旋锁 上面是很多锁的名词，这些分类并不是全是指锁的状态，有的指锁的特性，有的指锁的设计，下面总结的内容是对每个锁的名词进行一定的解释。 公平锁/非公平锁 公平锁是指多个线程按照申请...
MySQL并发控制：锁机制
黄规速博客:学如逆水行舟，不进则退
03-13 1万+
当数据库中有多个操作需要修改同一数据时，不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力，这一切在MySQL中都是由服务器和存储引擎来实现的。 当一个select语句在执行时可以施加读锁，这样就可以允许其它的select操作进行，因为在这个过程中数据信息是不会被改变的这样就能够提高数据库的运行效率。当需要对数据更新时，就需要施加写锁了，不在允许其...
行锁、间隙锁、next-key锁
weixin_43653890的博客
05-05 1976
参考博客：cmysql锁（九）innodb下的记录锁，间隙锁，next-key锁 - 简书 Mysql 行锁、间隙锁和next-key锁详解_程序员掉头发的博客-CSDN博客_行锁 间隙锁 行锁(Record Lock)：对索引记录加锁。 间隙锁(Gap Lock)：锁住整个区间，包括：区间里具体的索引记录，不存在的空闲空间（可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引记录之后的空间）。 next-key锁：行锁和间隙锁组合起来。 注意：如果检索条件不是索引的话会全表扫描...
MySQL中一条SQL是如何加锁的？（Gap Lock、Next-key Lock使用）
Java是世界上最好的语言
02-23 1456
MySQL中一条SQL是如何加锁的？（Gap Lock、Next-key Lock使用）
MySQL-加锁规则(间隙锁、临键锁、行锁、表锁)
WSYW126的博客
04-05 7523
环境 MySQL version 5.6.47 隔离级别：可重复读(RR) 加锁规则 可重复读隔离级别下加锁规则 next-key lock = 间隙锁 + 行锁 原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。 原则2：查找过程中访问到的对象才会加锁。 原则3：索引上的等值查询，给唯一索引加锁的时候，next-key lo...
Mysql 之 插入数据（insert into 、 replace into 解析）
热门推荐
weixin_42272869的博客
05-07 5万+
Mysql 插入数据 1、插入数据方法 mysql中常用的三种插入数据的语句: insert into：正常的插入数据，插入数据的时候会检查主键或者唯一索引，如果出现重复就会报错； replace into：表示插入并替换数据，若表中有primary key或者unique索引，在插入数据的时候，若遇到重复的数据，则用新数据替换，如果没有数据效果则和insert into一样； insert ignore into：插入并忽略数据，如果中已经存在相同的记录，则忽略当前新数据。这样不用校验是否存在了，有则忽
MySQL要加上nextkey锁，语句该怎么写
07-08
在MySQL中，要使用next-key锁，可以使用以下语句示例： ```sql SELECT * FROM table_name...请注意，next-key锁是在InnoDB存储引擎中实现的，默认情况下它是开启的。确保你的表使用了InnoDB引擎，以便使用next-key锁。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值