Mysql的加锁情况详解

news/2024/11/26 4:27:09/

最近在复习mysql的知识点,像索引、优化、主从复制这些很容易就激活了脑海里尘封的知识,但是在mysql锁的这一块真的是忘的一干二净,一点映像都没有,感觉也有点太难理解了,但是还是想把这块给啃下来,于是想通过这篇文章把复盘过程中所有的知识点给记录下来,特别是实操部分

文章目录

  • 一、mysql的锁介绍
    • 共享锁
    • 排他锁
    • 记录锁(Record Locks)
    • 间隙锁
    • 临键锁
  • 二、加锁情况实操
    • 1. 表无显式主键和索引
      • 数据准备
      • 不加任何查询条件的查询
      • 添加条件查询
    • 2. 表有显式主键无索引
      • 数据准备
      • 不带任何条件的查询
      • 通过id查询
    • 3. 表无显式主键有索引
      • 数据准备
      • 不带任何条件的查询
      • 带普通索引ID的查询
      • 带唯一索引的id查询
    • 4.表有显式主键和普通索引
      • 数据准备
      • 不带任何条件的查询
      • where条件是普通索引字段
      • where条件是主键字段
      • where条件同时包含普通索引字段和主键索引字段
    • 5.表有显式主键和唯一索引
      • 数据准备
      • 不带条件查询
      • where条件是唯一索引字段
      • where条件是主键
      • where条件是主键和唯一索引
  • 总结


mysql_9">一、mysql的锁介绍

mysql里有一堆锁,但是会存在一些概念上的交叉,所以这里只列举出常用的,或者说面试中常问的锁进行介绍,如下:

共享锁

共享锁又叫读锁,S锁,加了共享锁的数据还可以被其他事务加共享锁,但是不能被其他事务加排他锁,主要用于保证数据在读取过程中的一致性。可通过lock in share mode显示的给sql加上共享锁

select * from t lock in share mode;

排他锁

排他锁又叫写锁,X锁,加了排他锁的数据不能被其他事务再加排他锁或者共享锁,直到该排他锁被释放;可以通过for update显示的给sql加排他锁,当然,实际工作中不会使用for update,mysql在执行update、insert、delete等DML操作的时候会自动加排他锁;

select * from t for update;

记录锁(Record Locks)

记录锁也可以理解成行锁,作用在索引上,只锁某一行记录

间隙锁

间隙锁锁定的是索引记录锁之间的间隙,或者是索引最小值之前后者最大值之后的间隙,只在RR级别以上被使用;只阻止其他事务插入到间隙中,他们不阻止其他事务在同一个间隙上获得间隙锁

临键锁

临键锁=记录锁+间隙锁

二、加锁情况实操

约定:

  1. 关闭事务自动提交,我们需要在事务里观察加锁情况:set autocommit=0
  2. 使用show engine innodb status\G观察锁的情况
  3. 查看mysql关于锁日志的输出是否打开(默认关闭):show variables like ‘innodb_status_output%’;
  4. 打开开关:set global innodb_status_output_locks=on; set global innodb_status_output=on;
  5. 只演示mysql的默认隔离界别RR下锁的情况,RC隔离级别大部分加的都是行锁
  6. mysql的表一定会有聚簇索引的,有主键使用主键作为聚簇索引,没主键使用唯一且非空列作为聚簇索引,否则使用自己维护的rowid作为聚簇索引
  7. mysql的锁都是加在索引上的

1. 表无显式主键和索引

数据准备

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

不加任何查询条件的查询

  • sql
begin;
select * from t for update;
show engine innodb status\G
  • 图示
    在这里插入图片描述
  • 分析
    • 表t没有主键没有唯一非空列,所以会创建一条聚簇索引
    • 查询的是全表,没有限制任何条件,所以加的是聚簇索引的临键锁,由于聚簇索引是rowid,所以范围这里我们看不懂
    • 由于没有主键和索引,所以加上条件的查询和不加条件是一样的,这里就不再重复展示了

添加条件查询

  • sql
begin;
select * from t where id = 10 for update;
show engine innodb status\G
  • 图示同上
  • 分析
    • 可以发现,从结果上课与不加条件加锁情况是一致的,都是加的聚簇索引的临键锁;
    • 限制了条件和全表查询一样是因为mysql锁是加在索引上的,并不是加在数据10上的;
    • 不加的话理论上在插入一条id=10的记录也是可以的,这一就出现幻读了

2. 表有显式主键无索引

数据准备

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

不带任何条件的查询

  • sql
begin;
select * from t2 for update;
show engine innodb status\G
  • 图示
    在这里插入图片描述
  • 分析
    • 结果与1中一样,区别在于这里的id是主键,聚簇索引就是id了,在图里就能显示的看到临建锁的范围了,虽然是16进制的,转换后就是表里对应的10,20,30

通过id查询

  • sql
begin;
select * from t2 where id = 10 for update;
show engine innodb status\G
  • 图示
    在这里插入图片描述
  • 分析
    • 可以看到,此情况只加了id的记录锁,因为id就是聚簇索引,锁住这一行记录就可以了

3. 表无显式主键有索引

数据准备

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

不带任何条件的查询

与1.2类似,加的都是聚簇索引的临键锁

带普通索引ID的查询

  • sql
begin;
select * from t3 where id = 10 for update;
show engine innodb status\G
  • 图示
    在这里插入图片描述
  • 分析
    • 如图,会加聚簇索引的记录所,id的临建锁,id的间隙锁,锁的范围如上图,由此可见我们此时可以插入30之后的数据,不能插入10之前的数据,如下图:
      在这里插入图片描述

带唯一索引的id查询

  • sql
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
  • 图示
    在这里插入图片描述
  • 分析
    • 因为是唯一索引,且可以为空,所以mysql还会创建聚簇索引,所以此时加的是唯一索引id的记录锁,聚簇索引

4.表有显式主键和普通索引

数据准备

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

不带任何条件的查询

  • sql
begin;
select * from t5 for update;
show engine innodb status\G
  • 图示
    在这里插入图片描述

  • 分析

    • 主键id的记录锁(锁表里的每行记录),普通索引name的临键锁

where条件是普通索引字段

  • sql
begin;
select * from t5 where name='10' for update;
show engine innodb status\G
  • 图示
    在这里插入图片描述
  • 分析
    • 主键id的记录锁(锁id=10一条记录)、普通索引name的临键锁,范围:(-∞,10],普通索引name的间隙锁:(10,20)

where条件是主键字段

  • sql
begin;
select * from t5 where id=10 for update;
show engine innodb status\G
  • 图示
    在这里插入图片描述

  • 分析

    • 查询条件是主键的话就简单了,直接加id的记录锁即可

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

  • sql
begin;
select * from t5 where id=10 and name='10' for update;
show engine innodb status\G
  • 分析
    • 与单查主键一致,都只是上主键id的记录锁

5.表有显式主键和唯一索引

数据准备

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

不带条件查询

  • sql
begin;
select * from t6 for update;
show engine innodb status\G
  • 图示
    在这里插入图片描述
  • 分析
    与普通索引类似,会加主键的记录锁,锁定表里的每条记录,然后加唯一索引的临键锁,范围是:(-∞,10],(10,20],(20,30],(30,10)

where条件是唯一索引字段

  • sql
begin;
select * from t6 where name='10' for update;
show engine innodb status\G
  • 图示
    在这里插入图片描述
  • 分析
    • 两个行锁,分别是主键索引的行锁和唯一索引的行锁

where条件是主键

  • sql
begin;
select * from t6 where id=10 for update;
show engine innodb status\G
  • 分析
    • 只会加主键的行锁

where条件是主键和唯一索引

  • sql
begin;
select * from t6 where id=10 and name='10' for update;
show engine innodb status\G
  • 分析
    • 与只加主键一致,还是只会加主键的行锁;一般涉及到主键的查询都会只锁主键的行锁

总结

至此,mysql加锁的分析就结束了,其实挺绕的,实际总是与自己想的不一致,但是要秉持一个原则,就是mysql的锁除了保证并发事务的情况下对数据的安全读写外,还会用来解决幻读问题;有时候从幻读的角度出发,那么也就理解了为什么会这样加锁了


http://www.ppmy.cn/news/1549978.html

相关文章

SpringMVC接收请求参数

(5)请求参数》五种普通参数 1.普通参数 代码块 RequestMapping("/commonParam") ResponseBody public String commonParam(String name,int age){System.out.println("普通参数传递 name > "name);System.out.println("普通…

量化交易系统开发-实时行情自动化交易-4.2.3.指数移动平均线实现

19年创业做过一年的量化交易但没有成功,作为交易系统的开发人员积累了一些经验,最近想重新研究交易系统,一边整理一边写出来一些思考供大家参考,也希望跟做量化的朋友有更多的交流和合作。 接下来继续说说指数移动平均线实现。 …

Golang语言系列-Channel

Golang语言系列-Channel 源码分析结构体定义和构造函数发送操作接受操作关闭操作select 操作 实验参考 golang里的channel信道是golang里一个独特的概念,基于消息通信的方式来实现并发控制。信道有两种类型,缓存型和非缓存型,其中缓冲型底层基…

Python Selenium:Web自动化测试与爬虫开发

Python Selenium:Web自动化测试与爬虫开发 Python Selenium:Web自动化测试与爬虫开发安装Selenium设置WebDriver基础示例页面元素交互处理JavaScript和Cookies浏览器控制屏幕截图Headless Mode结束会话错误处理与调试 ***本文由AI辅助生成*** Python Se…

深度优先搜索题目合集

本片为洛谷题目 纯手打,请您放心食用! 目录 U121029 全排列(可重复) 题目描述 输入格式 输出格式 输入输出样例 题解 P1157 组合的输出 题目描述 输入格式 输出格式 输入输出样例 题解 P2404 自然数的拆分问题 题目描述 输入格式 输出格…

设计模式之 桥接模式

桥接模式(Bridge Pattern)是一种结构型设计模式,其核心思想是将抽象部分和实现部分分离,使它们可以独立地变化。通过桥接模式,抽象部分和实现部分可以独立扩展,从而避免了继承层次过深和高耦合的问题。 桥…

#渗透测试#SRC漏洞挖掘#网络运维# 黑客脚本编写终章之重定向与脚本调用

免责声明 本教程仅为合法的教学目的而准备,严禁用于任何形式的违法犯罪活动及其他商业行为,在使用本教程前,您应确保该行为符合当地的法律法规,继续阅读即表示您需自行承担所有操作的后果,如有异议,请立即停…

【贪心算法】贪心算法四

贪心算法四 1.最长回文串2.增减字符串匹配3.分发饼干4.最优除法 点赞👍👍收藏🌟🌟关注💖💖 你的支持是对我最大的鼓励,我们一起努力吧!😃😃 1.最长回文串 题目链接&…