Mysql的加锁情况详解

ops/2024/11/25 21:36:48/

最近在复习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/ops/136673.html

相关文章

【LeetCode热题100】队列+宽搜

这篇博客是关于队列宽搜的几道题&#xff0c;主要包括N叉树的层序遍历、二叉树的锯齿形层序遍历、二叉树最大宽度、在每个数行中找最大值。 class Solution { public:vector<vector<int>> levelOrder(Node* root) {vector<vector<int>> ret;if(!root) …

服务器数据恢复—DS5300存储硬盘指示灯亮黄灯的数据恢复案例

服务器存储数据恢复环境&#xff1a; 某单位一台某品牌型号为DS5300的服务器存储&#xff0c;1个机头4个扩展柜&#xff0c;底层是2组分别由数十块硬盘组建的RAID5阵列。存储系统上层一共分了11个卷。 服务器存储故障&分析&#xff1a; 存储设备上一组raid5阵列上的2块磁盘…

计算机网络(14)ip地址超详解

先看图&#xff1a; 注意看第三列蓝色标注的点不会改变&#xff0c;A类地址第一个比特只会是0&#xff0c;B类是10&#xff0c;C类是110&#xff0c;D类是1110&#xff0c;E类是1111. IPv4地址根据其用途和网络规模的不同&#xff0c;分为五个主要类别&#xff08;A、B、C、D、…

IT人员面试重点底层逻辑概念

arrayList的底层原理 ArrayList是个动态数组&#xff0c;实现List接口&#xff0c;主要用来存储数据&#xff0c;如果存储基本类型的数据&#xff0c;如int&#xff0c;long&#xff0c;boolean&#xff0c;short&#xff0c;byte&#xff0c;那只存储它们对应的包装类。 它的…

嵌入式学习-C嘎嘎-Day06

嵌入式学习-C嘎嘎-Day06 1. 什么是异常&#xff1f; 2. 抛出异常 3. 捕获异常 4. 标准异常族 5. 异常捕获技巧 5.1 捕获基类异常 5.2 多重捕获 1. 什么是异常&#xff1f; 异常是程序在运行期间产生的问题&#xff0c;即没有语法错误&#xff0c;出现的是逻辑错误&#xff0c;C…

HarmonyOS Next 浅谈 发布-订阅模式

HarmonyOS Next 浅谈 发布-订阅模式 前言 其实在目前的鸿蒙应用开发中&#xff0c;或者大前端时代、vue、react、小程序等等框架、语言开发中&#xff0c;普通的使用者越来越少的会碰到必须要掌握设计模式的场景。大白话意思就是一些框架封装太好了&#xff0c;使用者只管在它…

河道水位流量一体化自动监测系统:航运安全的护航使者

在广袤的水域世界中&#xff0c;航运安全始终是至关重要的课题。而河道水位流量一体化自动监测系统的出现&#xff0c;如同一位强大的护航使者&#xff0c;为航运事业的稳定发展提供了坚实的保障。 水位传感器&#xff1a;负责实时监测河道的水位变化。这些传感器通常采用先进的…

基于Java Springboot汽车4s店管理系统

一、作品包含 源码数据库设计文档万字PPT全套环境和工具资源部署教程 二、项目技术 前端技术&#xff1a;Html、Css、Js、Vue、Element-ui 数据库&#xff1a;MySQL 后端技术&#xff1a;Java、Spring Boot、MyBatis 三、运行环境 开发工具&#xff1a;IDEA/eclipse 数据…