第71讲:MySQL锁机制详解:表级锁、元数据锁和意向锁的全面解析与实践指南

MySQL中的表级锁

文章目录

  • MySQL中的表级锁
    • 1.MySQL中表级锁的概念
    • 2.表锁的概念以及基本使用
      • 2.1.表锁的分类以及概念
      • 2.2.表锁的使用语法
      • 2.3.表共享读锁的基本使用
      • 2.4.表独占写锁的基本使用
    • 3.元数据锁的概念以及基本使用
      • 3.1.元数据锁的概念
      • 3.2.常见的SQL操作所对应的元数据锁
      • 3.3.元数据锁演示
    • 4.意向锁的概念以及基本使用
      • 4.1.意向锁的概念
      • 4.2.意向锁的种类
      • 4.3.意向共享锁与表读锁兼容的演示
      • 4.3.意向排他锁与表锁不兼容的演示

1.MySQL中表级锁的概念

表级锁显而易见就是对于数据库表的一种锁,通过表锁可以锁定表,避免并发事务下,导致表中数据不一致性。

表级锁的力度大,针对表上锁后,表中的数据可能都无法操作,只能读,影响的范围大,并且发送锁的冲突概率是最高的,因为后面还会有行级锁,如果通过事务对某一条数据进行了行级锁,那么再创建一个表级锁,就会导致事务无法提交的情况。

对于MySQL中的表级锁分为以下三种类型:

  • 表锁
    • 表锁是针对某张表设置一个lock锁。
  • 元数据锁
    • 元数据锁就是针对表结构的一种锁。
  • 意向锁
    • 对表设置锁时,会检查表中每一条数据是否有行级锁,可以通过意向锁避免设置表锁时去检查每一条数据是否有行级锁。

2.表锁的概念以及基本使用

2.1.表锁的分类以及概念

表级锁中的表锁又分为两类:

  • 表共享读锁(read lock)
  • 表独占写锁(write lock)

什么是表共享读锁?

如下图所示,在多个并发客户端的情况下,第一个客户端开启了表共享读锁,那么DQL查询操作在多个客户端上都可以查询数据,不受影响,但是当有DDL/DML类的修改表数据的操作时,第一个客户端直接会提示表中存在锁,第二个客户端的写操作会处于阻塞状态,当第一个客户端释放锁之后,第二个客户端的写操作才能正常执行。

image-20220621095644576

共享读锁不会影响其他客户端的读,但是会阻塞其他客户端的写。

什么是表独占写锁?

如下图所示,在多个并发客户端的情况下,第一个客户端开启了表独占写锁,此时第一个客户端上执行DQL查询或者DML修改数据都是不受任何影响,但是当第二个客户端要执行查询或者修改数据的操作时,就会一直处于阻塞状态,只有当第一个客户端释放锁之后,第二个客户端才能操作。

image-20220621102327484

独占写锁会阻塞其他客户端的读以及写。

2.2.表锁的使用语法

1)创建锁

lock tables 表名 read/write

2)解锁

unlock tables
或者断开客户端链接

2.3.表共享读锁的基本使用

准备两个客户端,第一个客户端设置表共享读锁,然后分别在所有的客户端上进行读操作,观察是否有影响,然后分别再所有客户端上执行写操作,观察是否有影响。

1.客户端1创建一个读锁
mysql> lock tables xscjb read;

2.客户端1查询数据
mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   45 |   75 |   93 |
+----+--------+------+------+------+

3.客户端1写入数据异常
mysql> update xscjb set ywcj = '99' where xh = 1;
ERROR 1100 (HY000): Table 'xscjb_logs' was not locked with LOCK TABLES

4.客户端2查询数据
mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   45 |   75 |   93 |
+----+--------+------+------+------+

5.客户端2写入数据
mysql> update xscjb set ywcj = '99' where xh = 1;
一直处于阻塞中

6.客户端1解锁
mysql> unlock tables;

7.客户端2写入数据成功

image-20220621104458867

2.4.表独占写锁的基本使用

准备两个客户端,第一个客户端设置表独占写锁,然后分别在所有的客户端上进行读操作,观察是否有影响,然后分别再所有客户端上执行写操作,观察是否有影响。

1.客户端1创建一个读锁
mysql> lock tables xscjb read;

2.客户端1查询数据
mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   45 |   75 |   93 |
+----+--------+------+------+------+

3.客户端1写入数据正常
mysql> update xscjb set ywcj = '99' where xh = 1;
Query OK, 0 rows affected (0.13 sec)
Rows matched: 1  Changed: 0  Warnings: 0

4.客户端2查询数据
mysql> select * from xscjb limit 1;
处于阻塞

5.客户端2写入数据
mysql> update xscjb set ywcj = '99' where xh = 1;
处于阻塞中

6.客户端1解锁
mysql> unlock tables;

7.客户端2写入数据成功

image-20220621105127209

3.元数据锁的概念以及基本使用

3.1.元数据锁的概念

元数据锁(metadata lock)是MySQL系统中自动控制的一种锁,无需手动创建,默认就存在的一种锁,在访问一张数据表时都会自动增加一个元数据锁。

元数据锁又称为MDL锁,元数据锁的作用是维护表中元数据与实际数据的一致性,元数据可以理解为是表结构,当表中存在活动的事务时,不可以对元数据进行写入操作。为了避免表中还存在活动事务时,元数据被修改的情况,从而出现了元数据锁。

通俗一点来说,元数据锁就是为了避免DML(数据库操作语音)与DDL(数据库定义语音)语句有冲突,从而保证数据读写的正确性。

我们可以想一想,正在操作表中的数据呢,突然表中的元数据发生了改变,此时拿到的数据可能就会有异常。

当一张表涉及到未提交事务时,这张表的表结构不允许修改。

在MySQL5.5版本中引入了MDL元数据锁的概念,当对一张表进行增删改查操作时,会默认加一个MDL元数据共享锁,当对表结构进行变更操作时,会添加一个MDL排他锁。

3.2.常见的SQL操作所对应的元数据锁

SQL锁类型说明
lock tables xxx read/writeSHARED_READ_ONLY、SHARED_NO_READ_WRITE
select、select … lock in share modeSHARED_READ与SHARED_READ和SHARED_WRITE类型的锁兼容,与EXCLUSIVE类型的锁互斥
insert、update、deleet、select … fro updateSHARED_WRITE与SHARED_READ和SHARED_WRITE类型的锁兼容,与EXCLUSIVE类型的锁互斥
alter table …EXCLUSIVE与任何类型的锁都互斥

当有互斥的操作,就说明不兼容,此时就会处于阻塞状态。

在元数据锁中对DQL和DML语句又进行了细分,DQL类型的语句都是SHARED_READ类型的元数据锁,DML类型的语句都是SHARED_WRITE类型的锁,这两种类型的锁互相兼容。

3.3.元数据锁演示

当多个事务执行增删改查语句时,添加的都是元数据共享锁(SHARED_READ/SHARED_WRITE),他们之间是相互兼容,互不影响的。

#事务一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   99 |   75 |   93 |
+----+--------+------+------+------+
1 row in set (0.00 sec)

mysql> update xscjb set ywcj = '99' where xh = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.03 sec)


#事务二
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   99 |   75 |   93 |
+----+--------+------+------+------+
1 row in set (0.00 sec)

mysql> update xscjb set ywcj = '99' where xh = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

只要两个事务修改的不是同一条数据,都是互不影响的。

image-20220621113032795

当时当有未提交的事务,此时我们去修改表结构,修改表结构会产生排他锁,排他锁与共享锁不兼容互相排斥,此时修改表结构的语句就会一直处于阻塞状态,只有当事务全部提交完成后,修改表结构的语法才能正常执行。

1.开启一个事务查询xscjb的数据,不提交事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from xscjb limit 1;
+----+--------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj |
+----+--------+------+------+------+
|  1 | 小明   |   99 |   75 |   93 |
+----+--------+------+------+------+

2.此时去修改xscjb表的表结构
mysql> alter table xscjb add column pjcj int;
会一直处于阻塞状态,修改元数据会产生排它锁,排它锁与共享锁互斥,此时会处于阻塞状态

3.只有当事务全部提交后,修改元数据的语句才能成功执行

image-20220621113731234

我们还可以通过以下SQL语句看到当前数据库实例中的元数据锁,如上图所示,select产生的是共享读锁,alert产生了排它锁。

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

4.意向锁的概念以及基本使用

4.1.意向锁的概念

意向锁是为了避免DML数据库操作语句在执行过程中,出现行锁与表锁的冲突,通过意向锁可以在创建表锁时不需要去检查每行数据是否有行锁,从而减少表锁检查所带来的性能消耗。

没有使用意向锁之前,客户端一给表中加了行锁,客户端二要给表加表锁,过程如下:

首先客户端一开启一个事务,在事务中对表中的数据进行增删改查,在执行增删改时,会对表中的这些数据加一个行锁,这时客户端二想在表中添加一个表锁,客户端二在添加表锁时,就会去检查当前表中每一行数据是否有行锁,如果没有则会去添加表锁。

检查每一行数据是否有行锁,当表数据量很大时,效率很低。

当有了意向锁之后,客户端一给表中加了行锁,客户端二要给表加表锁,过程如下:

首先客户端一开启一个事务,在事务中对表的数据进行增删改查,对数据添加行锁的同时,又会给表添加一个意向锁,这时客户端二想要在表中添加一个表锁,客户端二就会根据客户端一加的意向锁来判断是否可以成功添加表锁,并且不会再逐行检查数据是否有行锁,效率得到大幅度提升。

简单来说,意向锁相当于一个声明,根据这个声明,添加表锁时就有了参考依据,因此不需要逐行判断是否可以添加表锁,从而避免了行锁与表锁产生的冲突。

4.2.意向锁的种类

根据意向锁的不同种类,可以判断出是否允许对表添加表锁,从而避免行锁与表锁的冲突,如果不去管这个冲突,那么一条数据正在修改,突然对表上了锁,那么就会产生数据异常的现象。

意向锁的分类:

  • 意向共享锁(IS)
    • 平常的select语句不会自定添加意向共享锁,需要在select后面加上lock in share mode参数,才能为表建立意向共享锁。
  • 意向排它锁(IX)
    • insert、update、delete、select…for update等语句,都会字段添加一个意向排它锁。

意向共享锁可以与表锁中表共享读锁(read)兼容,但是与表锁中的表独占写锁(write)互斥,当表存意向共享锁时,我们可以为表设置一个读锁,但是不能设置为写锁,一旦表中有意向共享锁时,当为表设置写锁时,就会处于阻塞状态,只有当事务提交后,写锁才能正常执行。

意向排它锁既与表共享读锁排斥又与表独占写锁排斥,当表中有意向排它锁时,都不能为表设置表锁,否则就会处于阻塞状态。

当事务提交后,意向共享锁、意向排它锁都会自定是否,被阻塞的操作此时才能被执行。

表共享读锁可以称为表共享锁,表独占写锁也可以称之为表排它锁

4.3.意向共享锁与表读锁兼容的演示

意向共享锁与表锁中的读锁兼容,但是与写锁互斥。

首先开启一个事务,然后执行select语句产生意向共享锁,然后再另一个会话中为表设置表锁,观察效果。

1.开启一个事务并执行select产生意向共享锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from xscjb where xh = 1 lock in share mode;
+----+--------+------+------+------+------+
| xh | xm     | ywcj | sxcj | yycj | pjcj |
+----+--------+------+------+------+------+
|  1 | 小明   |   99 |   75 |   93 | NULL |
+----+--------+------+------+------+------+
1 row in set (0.00 sec)


2.查询产生的意向共享锁,lock_mode为IS就表示为意向共享锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db_1          | xscjb       | NULL       | TABLE     | IS            | NULL      |
| db_1          | xscjb       | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+

3.为表设置读锁没有任何问题
mysql> lock tables xscjb read;
mysql> unlock tables;

4.由于意向共享锁与写锁互斥,因此写锁一直处于阻塞中,只有当事务提交后释放意向锁,写锁才能正常执行
mysql> lock tables xscjb write;

image-20220621170245440

4.3.意向排他锁与表锁不兼容的演示

意向排它锁与表读锁和写锁都互斥,都会处于阻塞中。

1.开启一个事务并执行update产生意向排它锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update yexxb set ye = '10000' where id = 1;


2.查询产生的意向共享锁,lock_mode为IX就表示为意向排它锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| db_1          | yexxb       | NULL       | TABLE     | IX            | NULL      |
| db_1          | yexxb       | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+


4.由于意向排它锁与读锁、写锁互斥,因此都会一直处于阻塞中,只有当事务提交后释放意向锁,读锁、写锁才能正常执行
mysql> lock tables yexxb read;
mysql> lock tables yexxb write;

image-20220621171225935

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

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

相关文章

婴儿专用洗衣机哪个牌子比较好?好用迷你洗衣机品牌推荐

当婴儿的到来,确实会给家庭带来许多变化,就好比如对于宝宝相关衣物的清洗需求。对于新生儿及婴幼儿的衣服,一般都要给予特殊的照顾与清洗,以保证不含细菌及过敏原。尤其是刚刚出生的婴儿,这时候宝宝们的皮肤很是幼嫩。…

APP端-阻止ios 默认全屏模式显示

问题描述: ios 默认全屏模式显示&#xff0c;该加的参数都加了&#xff0c;但是还是会自动默认全屏模式 代码如下: <video autoPlay loop playsInline muted{true} poster{UPIPreload}><source src{"video/your.mp4"} /></video>于是乎跟我们的A…

机器人制作开源方案 | 网球自动拾取机

作者&#xff1a;柳文浩、李浩杰、苏伟男、贾思萌、张天芸 单位&#xff1a;西安外事学院 指导老师&#xff1a;胡宝权、陈小虎 1. 产品说明 1.1 设计目的 近年来&#xff0c;网球运动越来越受到老百姓的欢迎&#xff0c;各种规模的比赛层出不穷。然而由于网球运动极为激烈…

使用jenkins和tomcat创建并部署maven项目

准备三台服务器&#xff1a; 192.168.58.139 部署tomcat 详细参照&#xff1a;http://t.csdnimg.cn/Yp2z2 192.168.58.140 部署gitlab 详细参照&#xff1a;http://t.csdnimg.cn/Sb1uz 192.168.58.153 部署Jenkins 详细参照…

代码随想录训练营第30天 | 332.重新安排行程、51. N皇后、37. 解数独

332.重新安排行程 题目链接&#xff1a;重新安排行程 解法&#xff1a; 这个题&#xff0c;卡哥的思路会超时。辛辛苦苦看懂了卡哥的思路&#xff0c;结果超时了&#xff0c;直接崩溃。 看了leetcode官方的思路&#xff0c;非常简洁&#xff0c;但是里面的深意还是不太懂。 由…

excel对号怎么打

对号无论是老师批改作业&#xff0c;还是在标注某些数据的时候都会用到&#xff0c;但这个符号在键盘上是没有的&#xff0c;那么excel对号怎么打出来呢&#xff0c;其实只要使用插入符号功能就可以了。 excel对号怎么打&#xff1a; 第一步&#xff0c;选中想要打出对号的单…

OpenCV快速入门:移动物体检测和目标跟踪

文章目录 前言一、移动物体检测和目标跟踪简介1.1 移动物体检测的基本概念1.2 移动物体检测算法的类型1.3 目标跟踪的基本概念1.4 目标跟踪算法的类型 二、差值法检测移动物体2.1 差值法原理2.2 差值法公式2.3 代码实现2.3.1 视频或摄像头检测移动物体2.3.2 随机动画生成的移动…

利用kibana 快照备份es数据库

环境 主机名ip地址组件ambari-hadoop1192.168.10.101ambari-hadoop2192.168.10.102kibanaambari-hadoop3192.168.10.103es 这里我们利用共享文件系统&#xff0c;存储快照&#xff0c;所以需要利用到nfs&#xff08;NFS&#xff08;Network File System&#xff09;是一种分布…

AI超级个体:ChatGPT与AIGC实战指南

目录 前言 一、ChatGPT在日常工作中的应用场景 1. 客户服务与支持 2. 内部沟通与协作 3. 创新与问题解决 二、巧用ChatGPT提升工作效率 1. 自动化工作流程 2. 信息整合与共享 3. 提高决策效率 三、巧用ChatGPT创造价值 1. 优化产品和服务 2. 提高员工满意度和留任率…

锂电行业废水及母液除铊解决方案,除铊树脂技术

锂电池原材料和生产设备的制造、电池回收和处理等&#xff0c;产业的发展会带来铊排放问题。除了锂电池生产过 程中存在的铊污染外&#xff0c;企业的生活污水或者初期雨水也含有铊&#xff0c;因为铊是一种广泛存在于自然环境中的 元素&#xff0c;存在于饮用水、土壤和食物中…

【Linux】初识重定向(输入输出)

一切皆文件 这是Linux的设计理念&#xff0c;因为这个理念的存在我们可以使用统一的方法对待不同的东西&#xff0c;&#xff0c;这也是为什么嵌入式之类的会需要Linux&#xff0c;因为用LInux来操纵硬件真的很方便 另外我们下文也会都基于这个理念来命名&#xff0c; 比如&am…

【前端开发】Remix与Next.js

很容易&#xff0c;我们被问到的最大问题是&#xff1a; Remix与Next.js有何不同&#xff1f; 看来我们必须回答这个问题&#xff01;我们想直接而不带戏剧性地解决这个问题。如果你是Remix的粉丝&#xff0c;并且想开始在推特上对这篇文章做出沾沾自喜的反应&#xff0c;我们恳…

构建沉浸式 AI 文本编辑器:开源 3B 编辑器的设计原则与思路

借助于在 AutoDev 与 IDE 上的 AI 沉浸式体验设计&#xff0c;我们开始构建一个 AI 原生的文本编辑器&#xff0c;以探索沉浸式创作体验。其适用于需求编写、架构文档等等文档场景&#xff0c;以加速软件开发中的多种角色的日常工作。 GitHub&#xff1a;https://github.com/un…

Android问题笔记四十九:ViewPager 嵌套 Fragment 扩大滑动响应区域,避免左右滑动过于灵敏问题

Unity3D特效百例案例项目实战源码Android-Unity实战问题汇总游戏脚本-辅助自动化Android控件全解手册再战Android系列Scratch编程案例软考全系列Unity3D学习专栏蓝桥系列ChatGPT和AIGC &#x1f449;关于作者 专注于Android/Unity和各种游戏开发技巧&#xff0c;以及各种资源分…

WPS Office JS宏实现批量处理Word中的表格样式

由于本职工作原因&#xff0c;经常会用到office办公软件&#xff0c;经常很多内容审批后&#xff0c;需要统一修改内容或样式&#xff0c;如果Word文档中有上百页或上千页&#xff0c;则一个一个修改太麻烦了。 在接触到WPSJS宏后&#xff0c;发现工作效率大大提升&#xff1b;…

ETL+BI结合的数据集成工具

在当今信息化时代&#xff0c;企业积累了大量的数据资产&#xff0c;如何高效地提取、转换和加载&#xff08;ETL&#xff09;这些数据&#xff0c;并将其转化为有用的洞察力成为了企业取得竞争优势的关键。同时&#xff0c;商业智能&#xff08;BI&#xff09;作为一种数据驱动…

ChatGPT等模型:到2026年,将消耗尽高质量训练数据

《麻省理工技术评论》曾在官网发表文章表示&#xff0c;随着ChatGPT等大模型的持续火热&#xff0c;对训练数据的需求越来越大。大模型就像是一个“网络黑洞”不断地吸收&#xff0c;最终会导致没有足够的数据进行训练。 而知名AI研究机构Epochai直接针对数据训练问题发表了一…

不受平台限制,Sketch 网页版震撼登场

Sketch 是一种基于 Mac 的矢量图形编辑器&#xff0c;可用于数字设计。其主要功能包括无损矢量编辑、完美像素精度和数百个插件同步功能&#xff0c;可导出预设和代码。它是目前流行的页面交互协作设计工具。但是 Sketch 最大的缺点是对 Windows/PC 用户不友好。严格来说&#…

CentOS添加开机启动

1.编写项目启动脚本&#xff08;run.sh&#xff09; #!/bin/bash-切换到程序所在路径 cd /home/cavs_install/app/cavs-admin/target/ # 等待其他组件启动完毕后再启动本项目&#xff08;如果不需要等待&#xff0c;本步骤可省略&#xff09; sleep 300 # 实际启动命令 nohup …

01:编译lua及C调用

我们今天在windows平台编译lua&#xff0c;生成 lua动态库,lua.exe&#xff0c;luac.exe 我把这个目录上传到giee&#xff0c;使用下面命令获取它: git clone gitgitee.com:jameschenbo/lua_c_application.git 或者直接访问:访问网页 目录结构如下&#xff1a; build.cmd 是…