MySQL锁

概述

介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算资源（CPU、IO）的争用除外，数据也是一种供许多用户共享的资源。保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也会影响数据库并发访问性能的重要因素。从这个角度来说，锁对数据库而言，尤为重要，也更加复杂。

分类

MySQL中的锁分类，按照锁粒度分，分以下三类：

  • 全局锁：锁定数据库中的所有表
  • 表级锁：每次操作锁整张表
  • 行级锁：每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例加锁，锁的是所有的库和表，加锁之后就是处于只读状态，只能读不能写，DML语句都会处于阻塞状态。

使用场景

典型的使用场景就是做全库备份，对所有的表进行锁定，保证一致性视图，保证数据完整性。
为什么做数据库备份就需要加全局锁，不加行不行？

不加锁

会出现数据不一致问题
image.png

加锁

image.png
从开始备份到备份结束，其他客户端只允许读数据，不允许写数据，从而保证备份数据的一致性。

操作

加锁

flush tables with read lock;

备份（下面这个不是SQL语句，可以直接在windows的终端执行）

mysqldump -uroot -p1234 xxx>xx.sql

释放全局锁

unlock tables;

特点

数据库加全局锁，是一个比较重的操作：

  • 如果在主库备份，备份期间都不能执行更新操作，业务基本就得停摆。
  • 如果在从库备份，备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟

在InnoDB引擎中，我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份，底层其实是通过快照读来实现的。

mysqldump --single-transaction -uroot -p1234 xxx>xx.sql

表级锁

介绍

表级锁，每次操作锁住整张表，锁粒度大，发生冲突概率高，并发度低。
应用在MyISAM、InnoDB、BDB存储引擎中。

分类

表锁

锁的就是整张表

表共享读锁（read lock）：简称读锁

image.png

举例：
1、客户端1加表读锁；
2、客户端1执行DQL，正常执行；
3、客户端2执行DQL正常执行；
4、客户端1执行DDL/DML操作，直接报错；
5、客户端2执行DDL/DML操作，阻塞；等到表读锁被释放会结束阻塞；
6、客户端1释放读锁，客户端2结束阻塞，执行语句。

客户端1和客户端2可以同时持有表读锁，写操作会被阻塞，直到两个事务释放了读锁。当客户端1释放读锁后，其他写操作才有可能执行，但如果客户端2仍然持有读锁，其他写操作仍然会被阻塞，直到客户端2也释放了读锁。
表级读锁可以多个客户端同时持有，只要有一把读锁，就会阻塞写操作，直到所有的读锁被释放。

表独占写锁（write lock）：简称写锁image.png

举例：
1、客户端1加表写锁；
2、客户端1能执行读、写，正常执行；
3、客户端2既不能执行读也不能执行写；
4、客户端1释放读锁，客户端2结束阻塞，执行语句。

客户端1添加了表级写锁，其他客户端既不能读也不能写，但是客户端1既能读又能写。
读锁不会阻塞其他客户端的读，但是会阻塞写。
写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。
表级别的写锁（排他锁）是互斥的，即一次只能有一个事务持有写锁。如果客户端1已经持有了表的写锁，那么客户端2在这个时候尝试加表写锁会被阻塞，直到客户端1释放了写锁。
写锁是排他的，它会阻止其他事务同时获取写锁。这确保了在任何给定时刻只有一个事务可以对表执行写操作，以保证数据的一致性。
因此，在有一个事务持有表写锁的情况下，其他事务想要加表写锁会被阻塞，直到当前事务释放写锁。

语法

1、加锁：lock table 表名 read/write
2、释放锁：unlock tables 或者客户端断开连接

元数据锁（meta data lock，MDL）

MDL加锁过程是系统自动控制的，不需要我们通过lock这样的关键字去显示加锁，当我们去访问一张表的时候，MDL元数据锁会自动加。
MDL锁住要作用是维护表元数据的数据一致性，当表中还存在未提交的事务的时候，此时不能修改表结构。为了避免DML和DDL的冲突，保证读写的正确性。
在MySQL5.5之后引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）；
读锁与读锁之间是兼容的，写锁和写锁之间是互斥的。写锁和读锁之间也是互斥的。
应用场景
image.png

1、SQL加表锁或写锁的时候，会加对应的元数据锁；
2、执行select、select… lock in share mode时会自动加元数据锁的读锁，
3、执行insert、update、delete、select… for update时，会自动加共享写锁，其实SHARED_WRITE也是MDL读锁
4、alter table…修改表结构 ，加的是排他锁。EXCLUSIVE与其他所有的MDL都是互斥的。

在事务中执行select和insert、update、delete操作时都会加元数据锁。
这两类的元数据锁都是共享锁，共享读锁，共享写锁。共享锁之间是兼容的，无论怎么操作都行。
举例说明：
举例1:
1、客户端1开启一个事务；
2、客户端1执行查询操作（共享读锁），但是并未提交事务；
3、客户端2开启一个事务；
4、客户端2执行查询（共享读锁）或修改操作（共享写锁），可以执行；
5、两个事务提交，这两个事务内加的都是共享锁，互不影响。

SHARED_WRITE也是MDL读锁：
共享读锁（SHARED_READ)与SHARED_READ和SHARED_WRITE都是兼容的；
共享写锁（SHARED_WRITE)与SHARED_READ和SHARED_WRITE都是兼容的；

举例2:
1、客户端1开启一个事务；
2、客户端1执行查询操作（共享读锁），但是并未提交事务；
3、客户端2开启一个事务；
4、客户端2要修改表结构，执行alter table语句（EXCLUSIVE），此时会出现阻塞，直到客户端1提交了事务；
5、客户端1提交事务，客户端2结束阻塞，执行SQL语句。
问：怎么知道是元数据锁？

select object_type,object_schema,object_name,lock_type,lock_duration 
from performance_schema.metadata_locks;

测试：
查询元数据锁；image.png
客户端1开启了事务
客户端1执行查询操作：

begin;
SELECT * FROM payment;

image.png
客户端2开启事务并执行修改操作：

begin;
update payment set serial = 'xxx' WHERE id = 1;

image.png
元数据锁就是为了避免DML和DDL冲突，保证读写的正确性。

意向锁

为了避免DML只执行时，加的行锁和表锁的冲突，在InnoDB中引入意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表的检查。

没加意向锁之前

image.png
线程A：开启了一个事务，执行update，默认的MySQL事务隔离级别，会添加行锁。
线程B：要lock table，加表锁，此时行锁和表锁就会冲突，线程B要加锁的话，会检查表中每行数据是否有行锁，性能很差。

加意向锁之后

image.png
线程A：加对应数据的行锁，以及加表的意向锁；
线程B：加表锁的时候，检查表中意向锁的情况，要加的表锁和意向锁是兼容的，那么就加锁成功，否则阻塞。阻塞到A线程，提交事务，释放行锁和意向锁之后。
image.png

意向锁分类

意向共享锁（IS）

  • 由语句select…lock in share mode添加。
  • 意向共享锁和表锁共享锁（read）是兼容的，与表锁排他锁（write）互斥。

意向排他锁（IX）

  • 由语句insert、update、delete、select…for update添加。
  • 意向共享锁和表锁共享锁（read）表锁和排他锁（write）都互斥，意向锁之间是不会互斥的。

通过以下SQL查看意向锁和行锁的情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
from performance_schema.data_locks;

举例：

begin;
-- 意向锁维度：这条SQL语句加的是这条数据的行锁以及这张表的意向共享锁。
select * from payment where id = 1 lock in share mode;

begin;
-- 意向锁维度：这条语句并不会加任何锁；
select * from payment where id = 1;

查看意向锁和行锁的情况：
image.png
此时，客户端2对这张表加表锁：

-- 加表读锁，成功：因为意向共享锁和表读锁是兼容的
lock tables payment read;

-- 加表写锁，阻塞：因为意向共享锁和表写锁是互斥的
lock tables payment write;

举例2:
客户端1:

begin;
-- 意向锁维度：这条SQL语句加的是这条数据的行锁以及这张表的意向排他锁。
update payment set serial = 'xxx' where id = 1;

这个行锁也是行锁排他锁；表锁意向排他锁。
image.png
此时，客户端2对这张表加表锁：

-- 加表读锁，阻塞：因为意向排他锁和表读锁是互斥的
lock tables payment read;

-- 加表写锁，阻塞：因为意向排他锁和表写锁是互斥的
lock tables payment write;

行级锁

行级锁，每次操作锁住对应的行数据。锁粒度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDB引擎中。InnoDB支持行级锁，MyISM不支持行级锁；
MySQL中的MyISM与InnoDB存储引擎当中，有三大区别： 事务、外键、行级锁。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加锁。也就是说，行锁锁的是索引而不是数据。
对于行级锁，主要分为以下三类：

  • 行锁：锁单个行记录的锁，防止其他事务对这行数据进行delete或者update。在RC、RR都支持；

image.png

  • 间隙锁：间隙锁，只锁间隙，不包含该记录。确保索引记录记录间隙不变，防止其他事务在这个间隙进行insert防止幻堵问题。在RR隔离级别下都支持。

image.png

  • 临键锁：行锁和间隙锁的组合，同时锁住数据，并锁住数据前面的间隙。在RR隔离级别下支持。

image.png

分类

InnoDB引擎下实现了以下两种类型行锁：

  • 共享锁（S）：共享锁和共享锁之间是兼容的，但是共享锁和排他锁之间是互斥的；
  • 排他锁（X）：获取到这条数据排他锁的事务可以执行更新，其他事务就不能再获取到这行数据的共享锁和排他锁。

image.png
常见的增删改查的SQL加的都是什么锁？
image.png
影认情况下，InnoDB在RR事务隔离级别运行，Innodb使用next-key锁进行搜索和察引扫描，以防止幻读。
1、针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2、Innodb的行锁是针对于索引加的貌，不通过索引条件检索数据，那么innodb将对表中的所有记录加锁，此时就会开级为表锁。
可以通过以下SQL，查看意向锁及行锁的情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
from performance_schema.data_locks;

演示：
验证共享锁与共享锁：
客户端1:

-- 开启事务
begin;

-- 查询
SELECT * FROM payment WHERE id = 1;

此时的没有任何的行锁，查看行锁情况：无任何锁（只针对行级锁）
image.png
客户端2:

-- 开启事务
begin;

-- 查询
SELECT * FROM payment WHERE id = 1;

此时客户端2仍然可以查到这条数据。
如果想对这条SQL语句加一把锁

SELECT * FROM payment WHERE id = 1 lock in share mode;

再次查询意向锁及行锁情况：
image.png
此时会有一把行锁，S：共享锁；X：排他锁。
S,REC_NOT_GAP：表示的是行锁共享锁；没有间隙
其他客户端也执行查询：
image.png
此时会有两把共享锁，共享锁与共享锁之间是相互兼容的，所以，这条语句依旧可以执行成功。
提交事务1后，共享锁还剩一把。
image.png
验证共享锁与排他锁：
客户端2:

update payment set serial = 'xxx' where id = 3;

此时，持有的是id为3的这行数据的行锁排他锁，所以可以正常执行，因为另一个事务持有的是id为1的是数据的行锁共享锁，并不是同一把行锁。

update payment set serial = 'xxx' where id = 1;

此时，这条语句会被阻塞，因为id为1的这行数据的行锁共享锁被另一个事务持有，需要等另一个事务提交事务之后才可以。
说明：共享锁与排他锁之间是互斥的；
验证：排他锁与排他锁之间
客户端1:

update payment set serial = 'xxx' where id = 1;

客户端2:

update payment set serial = 'xxx' where id = 1;

此时客户端2的语句会被阻塞，因为事务1持有id为1的行数据的行锁排他锁。
等待事务1释放排他锁后，事务2就可以获取到这把行数据的排他锁。
说明：行锁的排他锁与排他锁之间是互斥的；
验证：
Innodb的行锁是针对于索引加的貌，不通过索引条件检索数据，那么innodb将对表中的所有记录加锁，此时就会开级为表锁。
客户端1:

begin;
update payment set serial = 'xxx' where serial = 'yyy';

客户端2:

begin;
update payment set serial = 'xxx' where id = 2;

此时，客户端2会处于阻塞状态。
因为，事务1并不是根据索引去做数据更新，此时锁的是表，事务2不能获取到行锁，处于阻塞状态。
等待事务1提交后，事务2就可以获取到行锁。
我们可以针对更新的serial字段建立一个索引。

create index idx_payment_serial on payment(serial);

此时，再去更新就不会锁整张表，而是加行锁。

间隙锁/临键锁

默认情况下，InnoDB在RR事务隔离级别运行，InnoDB使用next-key 锁进行搜索和察引扫描，以防止幻读。

  • 索引上的等值查询（唯一素引）,给不存在的记录加锁时，优化为间隙锁。

验证：
当前表中的数据为：1、2、3、4、8。
此时，我们更新数据为6的数据；

update payment set serial = 'xxx' where id = 6;

image.png
但是，此时6这行数据并不存在，此时会对4-8之间的间隙加锁，锁的是4-8之间，不包含4和8。
image.png
X,GAP：X表示排他锁，GAP表示间隙锁。8表示锁的是8之前的那一段间隙；
此时，如果，我们在这个间隙中插入一行数据，比如插入7，发现无法插入：

insert into payment values (7,'7777');

因为中间的间隙被锁了，所以现在间隙中间不能写入数据，为了防止幻读。
当另一个事务提交后，释放掉锁，就会结束阻塞。

  • 索引上的等值查询（普通索引）,向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁。

  • 索引上的范围查询（唯一索引）–会访问到不满足条件的第一个值为止。

唯一索引进行范围查询时，加的是临键锁。
image.png
解释：
dbcloud payment PRIMARY RECORD S,REC_NOT_GAP 4
是对4这条记录加一个行锁。
dbcloud payment PRIMARY RECORD S 8
锁的是8之前的那段间隙。
dbcloud payment PRIMARY RECORD S supremum pseudo-record
锁的是8之后到正无穷大的临键锁。

注意：间隙锁唯一目的是防止其他事务插入间隙，间隙锁可以共存，一个事务采用的同隙锁不会阻止另一个事务在同一间隙上采用间隙锁；
间隙锁：锁间隙不包含数据记录。
临键锁：锁数据之前的这部分间隙和当前的数据记录。

小结

image.png

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/164318.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Halcon (4):如何开始自学

Halcon (4):如何开始自学

文章目录 文章专栏前言Halcon文档Halcon基础案例文档英语阅读建议 结论 文章专栏 Halcon开发 前言 在我完成上一篇代码，halcon基础窗口事件写完了之后，我已经基本掌握了如何写一个简单的halcon程序。后面我学习新的知识的时候感觉遇到了瓶颈。因为网上没…
阅读更多...
pom.xml格式化快捷键

pom.xml格式化快捷键

在软件开发和编程领域，"格式化"通常指的是将代码按照一定的规范和风格进行排列，以提高代码的可读性和维护性。格式化代码有助于使代码结构清晰、统一，并符合特定的编码规范。 格式化可以包括以下方面： 缩进&#xff1a…
阅读更多...
直流电机干扰的产生-EMC和EMI

直流电机干扰的产生-EMC和EMI

直流电机干扰的产生-EMC和EMI 干扰的产生电路滤波处理EMC处理措施 干扰的产生 带电刷的电动机，由于在电刷切换时，电动机线圈中的电流不能突变，当一路线圈通电断开时，会在该线圈的两端产生较高的反电动势，这个电动势会…
阅读更多...
MongoDB随记

MongoDB随记

MongoDB 1、简单介绍2、基本术语3、shard分片概述背景架构路由功能chunk（数据分片）shard key（分片键值） 4、常用命令 1、简单介绍 MongoDB是一个分布式文件存储的数据库，介于关系数据库和非关系数据库之间&#xff0c…
阅读更多...
『亚马逊云科技产品测评』活动征文｜借助AWS EC2搭建服务器群组运维系统Zabbix+spug

『亚马逊云科技产品测评』活动征文｜借助AWS EC2搭建服务器群组运维系统Zabbix+spug

授权声明：本篇文章授权活动官方亚马逊云科技文章转发、改写权，包括不限于在 Developer Centre, 知乎，自媒体平台，第三方开发者媒体等亚马逊云科技官方渠道。 本文基于以下软硬件工具： aws ec2 frp-0.52.3 zabbix 6…
阅读更多...
Typecho框架漏洞

Typecho框架漏洞

这里说的框架漏洞只适用于1.2.0版本及以下的版本 这里说的漏洞是xss漏洞，学过渗透的应该都学过，我在这里就不过多阐述了，下面我们直接进入正题 直接在这个地方插入网址，后面再接上html代码即可，代码如下： …
阅读更多...
『力扣刷题本』:二叉树的中序遍历

『力扣刷题本』:二叉树的中序遍历

一、题目 给定一个二叉树的根节点 root ，返回 它的 中序 遍历 。 示例 1： 输入：root [1,null,2,3] 输出：[1,3,2]示例 2： 输入：root [] 输出：[]示例 3： 输入：root [1…
阅读更多...
MySQL 的执行原理（三）

MySQL 的执行原理（三）

5.4. InnoDB 中的统计数据 我们前边唠叨查询成本的时候经常用到一些统计数据，比如通过 SHOW TABLE STATUS 可以看到关于表的统计数据，通过 SHOW INDEX 可以看到关于索引 的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方…
阅读更多...
Scalable Exact Inference in Multi-Output Gaussian Processes

Scalable Exact Inference in Multi-Output Gaussian Processes

Orthogonal Instantaneous Linear Mixing Model TY are m-dimensional summaries，ILMM means ‘Instantaneous Linear Mixing Model’，OILMM means ‘Orthogonal Instantaneous Linear Mixing Model’ 辅助信息 作者未提供代码
阅读更多...
年货FPS大作，艾尔莎EA B450M-E和你玩转《使命召唤20》

年货FPS大作，艾尔莎EA B450M-E和你玩转《使命召唤20》

说到动视旗下的《使命召唤》系列，相信大家都不陌生，它以出色爽快的游戏体验以及精良的画面著称，而且每年一部的更新节奏也是如今为数不多的“年货”游戏之一了。时至今日，该系列已经来到了第20部作品，也就是《使命召唤…
阅读更多...
Argo Rollouts结合Service进行Blue-Green部署

Argo Rollouts结合Service进行Blue-Green部署

删除03 部署04 rootk8s-master01:~/learning-jenkins-cicd/09-argocd-and-rollout/rollout-demos# kubectl delete -f 03-rollouts-with-prometheus-analysis.yaml rootk8s-master01:~/learning-jenkins-cicd/09-argocd-and-rollout/rollout-demos# kubectl apply -f 04-rol…
阅读更多...
OceanBase：Zone管理

OceanBase：Zone管理

OceanBase 集群由若干个 Zone 组成。从物理层面来讲，一个 Zone 通常是一个独立的物理部署单元，可以是一个数据中心（IDC）或者云上的一个 Zone（可用区），也可以是一个单独的机架（Rack&a…
阅读更多...
AI工具合集

AI工具合集

网站：未来百科 | 为发现全球优质AI工具产品而生 (6aiq.com) 如今，AI技术涉及到了很多领域，比如去水印、一键抠图、图像处理、AI图像生成等等。站长之家之前也分享过一些，但是在网上要搜索找到它们还是费一些功夫。 今天发现了一…
阅读更多...
第 372 场 LeetCode 周赛题解

第 372 场 LeetCode 周赛题解

A 使三个字符串相等 求三个串的最长公共前缀 class Solution { public:int findMinimumOperations(string s1, string s2, string s3) {int n1 s1.size(), n2 s2.size(), n3 s3.size();int i 0;for (; i < min({n1, n2, n3}); i)if (!(s1[i] s2[i] && s2[i] s…
阅读更多...
系列十、你说你做过JVM调优和参数配置，请问如何盘点JVM系统的默认值？

系列十、你说你做过JVM调优和参数配置，请问如何盘点JVM系统的默认值？

一、JVM的参数类型 1.1、标配参数 java -versionjava -help 1.2、XX参数 1.2.1、Boolean类型 公式&#xff1a;-XX:或者- 某个属性值 表示开启、-表示关闭 # 是否打印GC收集细节 -XX:PrintGCDetails -XX:-PrintGCDetails# 是否使用串行垃圾收集器 -XX:UseSerialGC -XX:-UseS…
阅读更多...
Java Web——Web开发介绍

Java Web——Web开发介绍

什么是Web开发 Web开发是一种创建和维护全球广域网&#xff08;World Wide Web&#xff09;上的网站和应用的技术。全球广域网也称为万维网(www World Wide Web)&#xff0c;是一个能够通过浏览器访问的互联网上的巨大信息库。 Web开发的目标是创建功能齐全、易于使用和安全的…
阅读更多...
C++多线程编程（2）：四种线程管理方法

C++多线程编程（2）：四种线程管理方法

文章首发于我的个人博客&#xff1a;欢迎大佬们来逛逛 文章目录 线程管理get_idsleep_forsleep_untilyield 线程管理 有一个this_thread的名称空间中定义了许多的线程管理方法&#xff1a; get_id&#xff1a;获取当前线程idsleep_for&#xff1a;当前线程休眠一段时间sleep_…
阅读更多...
开源更安全? yum源配置/rpm 什么是SSH?

开源更安全? yum源配置/rpm 什么是SSH?

文章目录 1.开放源码有利于系统安全2.yum源配置&#xff0c;这一篇就够了&#xff01;(包括本地&#xff0c;网络&#xff0c;本地共享yum源)3.rpm包是什么4.SSH是什么意思&#xff1f;有什么功能&#xff1f; 1.开放源码有利于系统安全 开放源码有利于系统安全 2.yum源配置…
阅读更多...
数据挖掘复盘——apriori

数据挖掘复盘——apriori

read_csv函数返回的数据类型是Dataframe类型 对于Dataframe类型使用条件表达式 dfdf.loc[df.loc[:,0]2]df: 这是一个DataFrame对象的变量名&#xff0c;表示一个二维的表格型数据结构&#xff0c;类似于电子表格或SQL表。 df.loc[:, 0]: 这是使用DataFrame的.loc属性来进行…
阅读更多...
Java调用com组件之jacob

Java调用com组件之jacob

一、背景介绍 现有标准的 win32 com组件&#xff0c;有如下的参数&#xff1a; 属性 值 说明Program IDyinhai.yh_hb_sctrCOM ClassIDCOM ClassName COClass_yh_hb_sctr Interface TypeDual InterfaceInterface NameIyh_hb_sctr 具有一个方法&#xff1a; yh_hb_call( string…
阅读更多...
最新文章

Copyright @ 2022~2023