MYSQL加锁情况

最新推荐文章于 2023-06-02 10:34:30 发布
最新推荐文章于 2023-06-02 10:34:30 发布
阅读量221 收藏
点赞数
分类专栏： JAVA 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45067832/article/details/118241987
版权

下面为大家讲述mysql的加锁情况
首先要查看innodb_status_output_locks是否开启：
在这里插入图片描述
若为开启，开启它，否则无法查看锁的情况：
在这里插入图片描述
首先要知道锁模式的含义：

IX：意向排它锁

X：锁定记录本身和记录之前的间隙

S：锁定记录本身和记录之前的间隙

X,REC_NOT_GAP：只锁定记录本身

S，REC_NOT_GAP：只锁定记录本身

X，GAP：间隙锁，不锁定记录本身

S，GAP：间隙锁，不锁定记录本身

​X，GAP,INSERT_INTENTION：插入意向锁

下面进入正题：

1、REPEATABLE-READ隔离级别+表无显式主键和索引
创建表t,没有索引和主键，并插入测试数据

set autocommit=0;
create table t(id int default null,name char(20) default null);
insert into t values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

begin;
select * from t for update;//执行for update会加表锁
show engine innodb status\G

从返回的信息中，可以看到对表添加了IX锁和4个记录锁，表中的三行记录上分别添加了Next-key Lock锁，防止有数据变化发生幻读，例如进行了更新、删除操作。同时会出现“ 0: len 8; hex 73757072656d756d; asc supremum;;”这样的描述信息，此操作也是为了防止幻读，会将最大索引值之后的间隙锁住并用supremum表示高于表中任何一个索引的值。

同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：

begin;
select * from t where id = 10 for update;
show engine innodb status\G

从上述反馈信息中，可以发现跟不加where条件的加锁情况是一样的，会同时出现多个行的临键锁和supremum，这到底是为什么呢？

出现supremum的原因是：虽然where的条件是10，但是每次插入记录时所需要生成的聚簇索引Row_id还是自增的，每次都会在表的最后插入，所以就有可能插入id=10这条记录，因此要添加一个supremum防止数据插入。

出现其他行的临键锁的原因是：为了防止幻读，如果不添加Next-Key Lock锁，这时若有其他会话执行DELETE或者UPDATE语句，则都会造成幻读。

2、REPEATABLE-READ隔离级别+表有显式主键无索引
创建如下表并添加数据：

create table t2(id int primary key not null,name char(20) default null);
insert into t2 values(10,'10'),(20,'20'),(30,'30

');
在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

begin;
select * from t2 for update;
show engine innodb status\G

通过上述信息可以看到，与之前的加锁方式是相同的。

2、where条件是主键字段

begin;
select * from t2 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，只会对表中添加IX锁和对主键添加了记录锁（X locks rec but not gap）,并且只锁住了where条件id=10这条记录，因为主键已经保证了唯一性，所以在插入时就不会是id=10这条记录。

3、where条件包含主键字段和非关键字段

begin;
select * from t2 where id = 10 and name = '10' for update;
show engine innodb status\G

通过看到，加锁方式与where条件是主键字段的加锁方式相同，因为根据主键字段可以直接定位一条记录。

3、REPEATABLE-READ隔离级别+表无显式主键有索引
1、不带where条件，跟之前的情况类似

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

创建如下表：

create table t3(id int default null,name char(20) default null);
create index idx_id on t3(id);
insert into t3 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;
select * from t3 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了Next-Key Lock锁，区间是负无穷到10，对索引对应的聚集索引添加了X记录锁，为了防止幻读，对索引记录区间（10，20）添加间隙锁。

此时大家可以开启一个新的事务，插入负无穷到id=19的全部记录都会被阻塞，而大于等于20 的值不会被阻塞

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

create table t4(id int default null,name char(20) default null);
create unique index idx_id on t4(id);
insert into t4 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;
select * from t4 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，此方式与where条件是主键字段的加锁情况相同，表无显式主键则会把唯一索引作为主键，因为是主键，所以不能再插入id=10这条记录，因此也不需要间隙锁。

4、REPEATABLE-READ隔离级别+表有显式主键和索引
此情况可以分为以下几种：

1、表有显式主键和普通索引

创建如下表：

create table t5(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into t5 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t5 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁

(2)where条件是普通索引字段

begin;
select * from t5 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name添加临键锁，对主键索引列添加X记录锁，为了防止幻读，对name的（10，20）添加间隙锁

(3)where条件是主键字段

begin;
select * from t5 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。

(4)where条件同时包含普通索引字段和主键索引字段

begin;
select * from t5 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是普通索引，那么跟普通字段是类似的，其实本质点就在于加锁的字段不同而已。

2、表有显式主键和唯一索引

创建如下表：

create table t6(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into t6 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t6 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁

(2)where条件是唯一索引字段

begin;
select * from t6 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name和主键添加行锁

(3)where条件是主键字段

begin;
select * from t6 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后主键添加行锁

(4)where条件是唯一索引字段和主键字段

begin;
select * from t6 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实本质点就在于加锁的字段不同而已。

5、READ-COMMITTED隔离级别+表无显式主键和索引
创建表t,没有索引和主键，并插入测试数据

create table t7(id int default null,name char(20) default null);
insert into t7 values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

begin;
select * from t7 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：

begin;
select * from t7 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后会对聚集索引添加记录锁，因为RC隔离级别无法解决幻读问题，所以不会添加临键锁。

6、READ-COMMITTED隔离级别+表有显式主键无索引
创建如下表并添加数据：

create table t8(id int primary key not null,name char(20) default null);
insert into t8 values(10,'10'),(20,'20'),(30,'30');

在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

begin;
select * from t8 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（主键）

2、where条件是主键字段

begin;
select * from t8 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的积累添加记录锁

3、where条件包含主键字段和非关键字段

begin;
select * from t8 where id = 10 and name = '10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的积累添加记录锁

7、READ-COMMITTED隔离级别+表无显式主键有索引
创建如下表：

create table t9(id int default null,name char(20) default null);
create index idx_id on t9(id);
insert into t9 values(10,'10'),(20,'20'),(30,'30');

1、不带where条件，跟之前的情况类似

begin;
select * from t9 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

执行如下语句：

begin;
select * from t9 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁，

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

create table t10(id int default null,name char(20) default null);
create unique index idx_id on t10(id);
insert into t10 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;
select * from t10 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁。

8、READ-COMMITTED隔离级别+表有显式主键和索引
此情况可以分为以下几种：

1、表有显式主键和普通索引

创建如下表：

create table t11(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into t11 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t11 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name索引列添加记录锁，对主键索引添加X记录锁

(2)where条件是普通索引字段

begin;
select * from t11 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name添加X记录锁，对主键索引列添加X记录锁

(3)where条件是主键字段

begin;
select * from t11 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。

(4)where条件同时包含普通索引字段和主键索引字段

begin;
select * from t11 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是普通索引，那么跟普通字段是类似的，其实本质点就在于加锁的字段不同而已。

2、表有显式主键和唯一索引

创建如下表：

create table t12(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into t12 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t12 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name索引列添加X记录锁，对主键索引添加X记录锁

(2)where条件是唯一索引字段

begin;
select * from t12 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name和主键添加行锁

(3)where条件是主键字段

begin;
select * from t12 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后主键添加行锁

(4)where条件是唯一索引字段和主键字段

begin;
select * from t6 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实本质点就在于加锁的字段不同而已。
在这里插入图片描述

一个发际线正在升高的人
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
where 条件加锁分析
gol_phing的博客
10-25 460
本文前提 where提取 Index key Index Filter Table Filter 有什么用？ 参考文章 本文前提 基于InnoDB存储引擎 当前读 where提取 提取为三部分：Index Key, Index Filter, Table Filter Index key Index First Key: 用于确定索引查询的起始...
MySql行锁变表锁，性能下降？间隙锁（X,GAP），行锁（X,REC_NOT_GAP），区间锁（X）带你进阶。
a_teacher_java的博客
05-16 3039
前言：接着上篇文章说，mysql行锁是跟着索引走的，会根据不同的索引类别添加不同的锁，下面开始介绍。 准备工作 先创建一张用户表，id为主键，但是不要让id自动增长，id值不要全部连续。 CREATE TABLE `t_user` ( `id` int NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `age` int DEFAULT NULL, PR.
Mysql行锁、表锁 (2)—mysql进阶（六十九)
ke1ying的博客
10-22 968
前面说了解决并发事务访问有两种解决办法，一种用mvcc的版本链解决，读 的时候用版本链readView控制，写的时候加锁。一种是读写都加锁，比如只允许读取最后数据的银行业务。锁又分为共享锁（s锁）和排它锁（x锁），锁的颗粒度分为表锁和行锁，所以当向上表的排他锁的时候，必须里面的行没有上x锁或者s锁，当然不是遍历所有行，于是在上行锁的时候，会有一个is和ix的锁，代表当前表上了行锁。 Mysql锁共享锁排它锁 (1)—mysql进阶（六十八) 行锁、表锁 我们主要说的是innoDB存储引擎的锁，其
MYSQL-锁机制
qq_22122425的博客
07-06 375
文章目录1、MyISAM2、InnoDB锁2.1、InnoDB的行锁模式及加锁方法： 1、MyISAM   MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）；   对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求（读共享）；   对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的(写独占)； 表共享读锁： 加锁：lock t
mysql中的锁
weixin_43810802的博客
06-29 984
此次实验所使用的mysql版本为mysql8：因为我们可以在中看到锁的情况。引擎是innodb。事务隔离级别是RR。mysql中的锁可以按照锁的粒度分，表锁就是其中的一种（另一种是行锁）。虽然锁一整张表在实际操作中是不明智的，但我们还是探究一下。准备的表： 我们有主键索引以及二级索引。然后插入一些数据： 为了看到中锁的情况，我们需要设置： S锁 首先看看表级别的读锁： 读锁可以被多次获取（session1和session2同时获取到了读锁）。我们可以看到的数量增加了。然后我们查看锁的情况： 可以看到一个锁（
阿里大牛何sir 深入MySQL加锁处理分析
03-10
何登成 资深技术专家 阿里巴巴数据库内核团队负责人 深入MySQL加锁处理分析
MySQL 加锁处理分析
07-04
MySQL 加锁处理分析 MySQL 加锁处理分析 MySQL 加锁处理分析
MySQL加锁处理分析@何登成1
08-08
MySQL加锁处理分析@何登成1
mysql加锁分析
05-19
mysql加锁分析
mysql加锁分析_v1
01-25
MySQL/InnoDB 的加锁分析，一直是一个比较困难的话题。我在工作过程中，经常会有同事咨询这方面的问题。同时，微博上也经常会收到 MySQL 锁相关的私信，让我帮助解决一些死锁的问题。本文，准备就 MySQL/InnoDB 的...
mysql innodb查看锁情况
最新发布
PentaQwQ的博客
06-02 675
mysql 锁等待检测
MySQL锁情况查看
m0_37625564的博客
12-14 4131
MySQL数据库锁问题分析处理
mysql中如何查看表是否被锁
m0_67394360的博客
06-06 9124
在使用mysql的时候，如何查看表是否被锁呢？查看表被锁状态和结束死锁步骤：1.在mysql命令行执行sql语句 2.查看数据表被锁状态 该语句可以查询到当前锁表的状态3.分析锁表的SQL 通过sql日志，分析相应SQL，给表加索引，常用字段加索引，表关联字段加索引等方式对sql进行优化。4.查看正在锁的事务 在5.5中，information_schema 库中增加了三个关于锁的表（innoDB引擎）：innodb_trx ## 当前运行的所有事务 innodb_locks ## 当前出现的锁 innod
阿里P8大佬带你全面了解—MySQL锁03.InnoDB行锁
bieber007的博客
12-14 308
目录 InnoDB 行锁锁排查可以用的视图和数据字典InnoDB 行锁兼容性 InnoDB行锁之共享锁共享锁： 查看InnoDB锁InnoDB行锁实现机制对普通索引上锁InnoDB隐式、显式锁 通过索引实现行锁，在索引记录上加锁。 没有索引就无法实现行锁，升级成全表记录锁，等同于表锁。 理解InnoDB独特的行锁运行机制，认识特有的四种行锁粒度——lock_ordinary、lock_gap、lock_rec_not_gap、lock_insert_intention InnoD..
a表中不存在就插入_不知道MySQL锁没关系，图解+实操，一文带你了解清清楚楚...
weixin_35349247的博客
12-05 321
在最一开始学习数据库的时候，我们就知道数据库有一个特性：保证数据的执行，虽然数据库有这么多，但是这是一种规则，那MySQL肯定也不例外，但是同样的，MySQL由于其自身所有的架构特性，存储引擎的选择也存在多样化，针对的应用场景也不太一样，导致的结果就是不同的存储引擎所设计的锁机制也不太一样，那在工作和面试中该怎么处理呢？今天，我们就看一下最常见的InnoDB的相关锁机制吧不要觉得难，有图有实操，跟...
mysql加锁情况详细分析
yygr的博客
02-07 1243
https://blog.csdn.net/weixin_39805644/article/details/111125420 如下图所示，数据库的隔离等级，SQL 语句和当前数据库数据会共同影响该条 SQL 执行时数据库生成的锁模式，锁类型和锁数量。 下面，我们会首先讲解一下隔离等级、不同 SQL 语句 和 当前数据库数据对生成锁影响的基本规则，然后再依次具体 SQL 的加锁场景。
超全面的MySQL语句加锁分析
程序员小灰的博客
05-10 8592
说在前面的话本文是用来系统阐述在MySQL中，不同语句在各种条件下的加锁情况，并不是解释各种锁是什么（或者说加锁的本质是什么），大家如果不理解什么是MVCC、ReadVi...
什么操作mysql加锁
05-23
MySQL中可以使用以下几种锁来控制并发访问： 1. 共享锁（Shared Lock）：多个事务可以同时持有同一份资源的共享锁，但是不能持有排它锁。共享锁用于防止其他事务修改数据，但允许其他事务读取数据。 2. 排它锁...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值