Mysql锁机制详解

最新推荐文章于 2024-08-31 07:38:48 发布
最新推荐文章于 2024-08-31 07:38:48 发布
阅读量67 收藏
点赞数
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zbwsxbc/article/details/134528887
版权

Mysql锁

对数据库的操作有读、写、组合起来就有读读、读写、写读、写写、读读不存在安全问题，安全问题加锁都可以解决，但是所有操作都加锁太重了，只有写写必须要求加锁、读写、写读、都可以用MVCC、MySQL的默认隔离级别是RR也就是读已提交。
但是RR在MVCC的加持下还是存在幻读，这个时候就还是需要加锁，间隙锁就是用来在RR级别下解决幻读问题的。

幻读的概念： 比如有两个事物A，B，A事物在多次读取的时候，读取到的数据有出现不一致的情况。

共享锁、排他锁

共享锁（shared lock） 也叫读锁、S锁、数据被某个事务获取了共享锁之后，还可以被其他事务获取共享锁，但是不能被其他事务获取排他锁。

排他锁（exclusive Lock） 也叫写、X锁、数据被某股事务获取之后就不能被其他事务获取任何锁、

总结： 共享锁和共享锁之间不冲突，排他锁和共享锁、排他锁都冲突，默认select的时候是不加锁的，更新，添加、删除的时候会加排他锁。

强制加共享锁lock in share mode ，强制加排他锁for update

select * from my_tabel  where id =1 lock in share mode;
select * from my_tabel  where id =1 lock in for update;

意向锁：意向共享锁、意向排他锁

加锁的时候可能锁某行或者几行的数据，也可能锁整个表，但上面共享锁只能和共享锁兼容。排他锁和两者都不兼容，虽然锁表的场景很少，如果要用共享锁锁表不得一行行去遍历看看数据又没有被排他锁锁过，如果要用排他锁表就得一行行去遍历看是否存在数据被共享锁或排他锁占用。

实际上是不可能遍历的，代价太大，索引在对某些数据加共享锁的时候，就会给表上意向共享锁，对某些数据加排他锁的时候就会对表上意向排他锁。

这样再对表加锁的时候只需要判断是否有对应的意向锁就好啦，而不需要遍历，意向锁之间是不冲突的。

记录锁、间隙锁、临键锁

前置

上面说了一些锁的概念，现在来尝试一下给数据加锁，在正式开始之前需要了解一下Mysql的数据存储结构。
1，在主键索引形成的B+Tree里面，非叶子节点存储的是主键索引、叶子节点存储的是数据
2，在非主键索引形成的B+Tree里面，非叶子节点存储的是当前索引，叶子节点存储的是主键索引
3，如果没有主键索引，会拿第一个唯一索引来做聚簇索引，如果也没有唯一索引就会创建一个看不见的唯一键。
4，所以当通过非主键索引取找数据的时候，其实先通过非主键索引找到主键索引，在通过主键索引取找数据，这个过程叫作回表。

锁理论

记录锁（Record Locks）： 记录锁是基于索引记录上的锁，它锁定的行数是固定的，明确的。根据情况它可以是共享锁，排他锁。
间隙锁（Gap Locks）： 间隙锁的目的是在RR级别下，防止幻读，幻读的产生是当前事务多次查询的结果数量上不一致。间隙锁的目的就是保住当前范围内的数据不会被更改，所以它会锁住某个区域间的数据。
临键锁（Next-key Locks）： 等于记录锁+间隙锁，所以我们只需要知道这两个锁的定义就可以了。Mysql默认级别是RR。默认上临键锁。

锁总结

锁都是基于索引取找到数据记录再加锁的，而索引的规则是：通过其他索引找到主键索引，所以：
1，没有使用索引做更新操作会锁表。
2，通过唯一/主键索引等值加锁，只会锁具体的行，非唯一索引则不一定，SQL优化器会基于数据分布选择记录锁，或临键锁。
3，只有在RR级别下才有间隙锁，目的是为了解决幻读，如果操作的数据是跨多个范围，就会加多个区间的间隙锁。
4，Mysql默认的锁就是临键锁，所以在执行SQL的时候，记录锁和间隙锁会同时存在的，范围是左开右闭的区间。

正在SQL查询的时候，我们知道是先通过索引取找数据的，其实加锁也是基于索引的，通过索引找到对应的数据然后把相关的数据行都锁上，如果没有使用索引就会扫描全表，也就会锁表。

锁实践

基于上面的理论和总结，我们用实际SQL来证明一下，加深印象。

记录锁

创建一个表，并往里面塞一些数据，然后来验证

CREATE TABLE `xdx_lock` (
  `id` int NOT NULL COMMENT '主键索引',
  `conutry` varchar(255) NOT NULL COMMENT '唯一索引',
  `name` varchar(255) NOT NULL COMMENT '普通索引',
  `age` int NOT NULL COMMENT '不是索引',
  PRIMARY KEY (`id`),
  UNIQUE KEY `conutry_idx` (`conutry`),
  KEY `name_idx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='锁表测试'

锁没有索引

A事务B事务
– 开启事务 begin;
– 加排他锁（成功） select * from xdx_lock where age = 3 for update;
– 开启事务 begin;
– 加共享锁（失败） select * from xdx_lock where id = 7 lock in share mode;
– 加共享锁（失败） select * from xdx_lock where age = 1 lock in share mode;
– 加共享锁（失败） select * from xdx_lock where conutry = ‘c’ lock in share mode;

在这里插入图片描述

锁【普通索引】
A事务B事务
– 开启事务 begin
– 加排他锁（成功） select * from xdx_lock where name = ‘kklua’ for update;
– 开启事务 begin;
– 加排他锁（成功） select * from xdx_lock where conutry = ‘c’ for update;
– 加排他锁（成功） select * from xdx_lock where id = 7 for update;
– 加排他锁（成功） select * from xdx_lock where name = ‘kkrest’ for update;
– 加排他锁（失败）age没有索引 select * from xdx_lock where age = 1 for update;

在这里插入图片描述

锁 唯一索引
A事务B事物
– 开启事务 begin
– 加排他锁（成功） select * from xdx_lock where conutry = ‘c’ for update;
begin;
– 加排他锁（成功） select * from xdx_lock where id = 5 for update;
– 加排他锁（成功） select * from xdx_lock where name = ‘kkgo’ for update;
– 加排他锁（失败） select * from xdx_lock where name = ‘kktime’ for update;

在这里插入图片描述

锁 主键索引

和唯一索引一样的效果。

间隙锁

让我们再来回忆一下，间隙锁的目的是为了防止幻读，所以间隙锁的目的就是阻止一切会让当前事务数据量变化的操作。

MySQL的数据存储是B+Tree，以主键建立的B+Tree。我们假设表的数据有5条，它们的id分别是 5， 10,15， 20，25 （这种数据是有可能的对吧），虽然它是树结构，但是也是排好序的，并且就是以主键为排序的。

间隙锁的原理就是锁住我们查询数据的区间，可能不好理解。先举几个例子：
1， where id > 9 ，它锁住的区间就是 （5，+∞），开头是5不是9哦，因为9这个玩意不存在呀，基于索引加锁，没有索引无法加锁
2，where id > 9 and id < 18 它锁住的区间就是 (5,19]、 （10,15]、(15，20]
3，where id = 10 ，它只会锁住10 这一条数据
4，where id = 7， 因为7不存在，为了防止后续新增，所以也要锁住最近的一个区间 (5，10]

注：间隙锁 是左开右开的区间，但间隙锁和临键锁一起的，而临键锁是左开右闭。

CREATE TABLE `xdx_lock_new` (
  `id` INT   NOT NULL    COMMENT '主键' ,
  `name` VARCHAR(255)   NOT NULL    COMMENT '普通数据' ,
  PRIMARY KEY  (`id`)  
);


INSERT INTO `xdx_lock_new`(`id`, `name`) VALUES ('5', 'java');
INSERT INTO `xdx_lock_new`(`id`, `name`) VALUES ('10', 'c');
INSERT INTO `xdx_lock_new`(`id`, `name`) VALUES ('15', 'go');
INSERT INTO `xdx_lock_new`(`id`, `name`) VALUES ('20', 'lua');
INSERT INTO `xdx_lock_new`(`id`, `name`) VALUES ('25', 'js');
where id > 9
A事务B事务
begin;
– 加排他锁（成功） select * from xdx_lock_new where id = 10 for update;
begin;
– 加排他锁（成功）更新的时候自动加锁哈 update xdx_lock_new set name = ‘xxx’ where id = 5;
– 加排他锁（失败）插入的时候自动加锁哈 INSERT INTO xdx_lock_new(id, name) VALUES (‘6’, ‘c++’);

在这里插入图片描述

where id > 9 and id < 18
A事务B事务
begin;
– 加排他锁（成功） select * from xdx_lock_new where id > 9 and id < 18 for update;
begin;
– 加排他锁（失败）插入的时候自动加锁哈 INSERT INTO xdx_lock_new(id, name) VALUES (‘6’, ‘js’);
– 加排他锁（失败）插入的时候自动加锁哈 INSERT INTO xdx_lock_new(id, name) VALUES (‘11’, ‘js’);
– 加排他锁（失败）插入的时候自动加锁哈 INSERT INTO xdx_lock_new(id, name) VALUES (‘19’, ‘js’);
– 加排他锁（成功）插入的时候自动加锁哈 INSERT INTO xdx_lock_new(id, name) VALUES (‘21’, ‘js’);
– 加排他锁（失败） update xdx_lock_new set name = ‘xxx’ where id = 10;

在这里插入图片描述

where id = 10
A事务B事务
begin;
– 加排他锁（成功） select * from xdx_lock_new where id = 10 for update;
begin;
– 加排他锁（成功） select * from xdx_lock_new where id = 5 for update;
– 插入数据（成功） INSERT INTO xdx_lock_new(id, name) VALUES (‘9’, ‘c++’);
– 插入数据（成功） INSERT INTO xdx_lock_new(id, name) VALUES (‘11’, ‘rust’);

在这里插入图片描述

where id = 7
A事务B事务
begin;
– 加排他锁（成功） select * from xdx_lock_new where id = 7 for update;
begin;
– 加排他锁（失败）插入的时候自动加锁哈 INSERT INTO xdx_lock_new(id, name) VALUES (‘6’, ‘js’);
– 加排他锁（成功） update xdx_lock_new set name = ‘xxx’ where id = 5;
– 加排他锁（成功） update xdx_lock_new set name = ‘xxx’ where id = 10;
– 加排他锁（失败）插入的时候自动加锁哈 INSERT INTO xdx_lock_new(id, name) VALUES (‘7’, ‘js’);

在这里插入图片描述

锁非唯一索引

基于唯一索引或主键索引，间隙锁的范围很好理解但是基于非唯一索引进行数据锁的时候，SQL优化器最终执行的结果可能和我们想象的并不一样，比如下面这个案例、相同的SQL，但是表数据不一样的时候，执行结果页不一样。

CREATE TABLE `xdx_lock_normal_primary` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
结果一

在这里插入图片描述

结果二

在这里插入图片描述

总结

来看一下GPT的回答，我觉得应当是如此的，简单来说就是查询优化器觉得不需要间隙锁了，直接转成具体的记录锁。
在这里插入图片描述

注：在第一种情况下，的确是因为加了间隙锁导致的，因为间隙锁的目的是防止幻读，是在RR级别下的，当我把全局事务改成RC的时候，第一种数据集也不会锁 3、4 两行数据。

临键锁

MySQL默认的锁就是临键锁，当写一个SQL的时候就有可能触发临键锁，但临键锁并不是一个单独的锁，临键锁 = 记录锁 + 间隙锁，所以分析的时候我们可以单独的判断它当前加了那种记录锁，然后再单独看它是否被加了间隙锁，再结合起来判断。

表锁、页面锁、行锁、

在学习MySQL锁的时候这些锁给我带来了很大的困扰，其实它们只是表示我们当前这次操作锁了多少数据而已，比是一行数据还是整个表的数据。

MySQL官方文档里面并未对这几个锁做出解释。

死锁

死锁其上并不属于MySQL锁的范围，它只是基于锁而产生的一种现象。

A事务B事务
获取 one表的某些行数据的排他锁
获取 two表的某些行数据的排他锁
尝试获取 two表的某些行数据的锁（被B事务锁住了获取不到等待）
尝试获取 one表的某些行数据的锁（被A事务锁住了获取不到等待）
互相等待对方的锁，死锁了
昨日入城市
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
MYSQL锁机制详解
灯火觅阑珊
11-19 261
最近在学习MySQL的锁机制和隔离级别，发现跟Oracle还是有比较大的差异。例如Oracle默认隔离级别是read committed，而MySQL默认是repeatable read。Oracle除了串行化，无法避免幻读。而MySQL在repeatable read级别下，通过next-key锁机制，避免了幻读的产生。记录一下理论理解以及实验验证。 InnoDB锁类型有如下几种： 共享锁（S）...
MySQL锁机制详解
iva_brother的博客
03-07 462
一、概述 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统的计算资源的争用外，数据也是一种供许多用户共享的资源。为了保证数据并发访问的一致性和有效性，我们引入了锁。本文就是讨论MySQL锁机制的特点，常见的锁问题等。 针对不同的机制，从使用的方式，我们可以将锁分成乐观锁和悲观锁；如果按照粒度，我们可以分为页面锁、表级锁、行级锁。MySQL不...
MySQL的锁机制详解
“ 春风若有怜花意，能否许我再少年。”
02-29 1546
了解锁机制
MySQL锁机制详解：并发控制的秘密武器
[MySQL锁机制详解：并发控制的秘密武器](https://img-blog.csdnimg.cn/8b9f2412257a46adb75e5d43bbcc05bf.png) # 1. MySQL锁机制概述** MySQL锁机制是数据库系统中至关重要的功能，它通过控制对数据的并发访问，...
MySQL锁机制详解：并发控制与性能优化
[MySQL锁机制详解：并发控制与性能优化](https://img-blog.csdnimg.cn/8b9f2412257a46adb75e5d43bbcc05bf.png) # 1. MySQL锁机制概述** MySQL锁机制是并发控制和性能优化的核心。它通过对数据访问进行控制，确保...
MySQL锁机制详解：避免死锁与并发问题
[MySQL锁机制详解：避免死锁与并发问题](https://img-blog.csdnimg.cn/8b9f2412257a46adb75e5d43bbcc05bf.png) # 1. MySQL锁机制概述 MySQL数据库中的锁机制是保证数据并发访问一致性和完整性的关键技术。锁机制...
mysql-day02
m0_64370841的博客
08-29 1822
【代码】mysql-day02。
如何在 MySQL 中使用 Socket 身份验证 ？
最新发布
xiaochong0302的博客
08-31 313
在 MySQL 和 MariaDB 中，可以使用 socket 身份验证，而不是传统的用户名和密码组合。这种方法既安全又方便，因为它使用的是操作系统的用户凭证。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 945
MySQL——子查询（5）带比较运算符的子查询
阿里龙晰系统上将yum安装的myql_8.0.36升级到mysql_8.4.0的过程
老杜的博客
08-29 841
本章内容介绍了在阿里龙晰系统上修复 Oracle MySQL 安全漏洞CVE-2024-21176、CVE-2024-21177、CVE-2024-21179的过程。将Mysql从8.0.36升级到8.4.0
mysql的聚簇索引、非聚簇索引、回表
求知路上的同行者的博客
08-27 818
mysql聚簇索引
MySQL系列—6.权限管理
md54333的博客
08-27 615
MySQL权限控制主要是集中在5张表中,user,db,tables_priv,columns_priv,procs_priv每次启 动会直接加载到内存中。e.执行SQL语句时的检查，依次检查user表、db表、tables_priv, columns_priv, procs_priv表里查看是否具有相应权限。a. 用户连接时，MySQ server首先从user表里匹配host, user, password，匹配不到则拒绝 Mysql client的连接。
MySQL常见面试题总结
夏日 の blog
08-28 1119
ER 图全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。实体：通常是现实世界的业务对象，当然使用一些逻辑对象也可以，实体使用矩形框表示。属性：即某个实体拥有的属性，可以理解为字段，属性使用椭圆形表示。联系：即实体与实体之间的关系，用菱形表示，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。下图是一个学生选课的 ER 图，每个学生可以选若干门课程，同一门课程也可以被若干人选择，所以它们之间的关系是多对多（M: N）
前后端分离项目实战-通用管理系统搭建（前端Vue3+ElementPlus，后端Springboot+Mysql+Redis）第七篇：菜单和路由动态绑定
分享式获得也是一种学习的态度
08-26 1279
每个人都有惰性，但不断学习是好好生活的根本，共勉！绿竹入幽径，青萝拂行衣。——《下终南山过斛斯山人宿置酒》
慢sql问题解决，sql优化，数据库（mysql）
质量不太好的博客
08-27 1280
慢sql问题解决，sql优化，数据库（mysql）
MySQL：常用函数
盒马的博客
08-27 1196
本博客讲解MySQL中的常用函数
MYSQL多表连接一对多的理解
刘一帆的博客
08-28 312
解决方法二：只查询对应信息的时候并不需要多表连接，子查询也不错！函数，这样既简便也更加有效。解决方法一：建议使用。
MySQL锁机制详解：从表级锁到行级锁
"本文深入探讨了MySQL的锁机制，包括其分类、原理及使用技巧，主要关注MyISAM和InnoDB存储引擎的锁行为。文章指出，MySQL的锁机制因存储引擎的不同而有所差异，例如MyISAM使用表级锁，而InnoDB支持行级锁和表级锁。...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值