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

2023-11-28 13:12
文章标签 数据 mysql 实践 详解 全面 解析 指南 机制 database 71 表级 意向锁

本文主要是介绍第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 TABLES4.客户端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: 04.客户端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: 0mysql> 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: 0mysql> 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

这篇关于第71讲：MySQL锁机制详解：表级锁、元数据锁和意向锁的全面解析与实践指南的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/429760。

相关文章

详解Vue如何使用xlsx库导出Excel文件

详解Vue如何使用xlsx库导出Excel文件

《详解Vue如何使用xlsx库导出Excel文件》第三方库xlsx提供了强大的功能来处理Excel文件,它可以简化导出Excel文件这个过程,本文将为大家详细介绍一下它的具体使用,需要的小伙伴可以了解... 目录1. 安装依赖2. 创建vue组件3. 解释代码在Vue.js项目中导出Excel文件，使用第三
阅读更多...
Linux中shell解析脚本的通配符、元字符、转义符说明

Linux中shell解析脚本的通配符、元字符、转义符说明

《Linux中shell解析脚本的通配符、元字符、转义符说明》：本文主要介绍shell通配符、元字符、转义符以及shell解析脚本的过程,通配符用于路径扩展,元字符用于多命令分割,转义符用于将特殊... 目录一、linux shell通配符(wildcard)二、shell元字符（特殊字符 Meta）三、s
阅读更多...
SQL注入漏洞扫描之sqlmap详解

SQL注入漏洞扫描之sqlmap详解

《SQL注入漏洞扫描之sqlmap详解》SQLMap是一款自动执行SQL注入的审计工具,支持多种SQL注入技术,包括布尔型盲注、时间型盲注、报错型注入、联合查询注入和堆叠查询注入... 目录what支持类型how---less-1为例1.检测网站是否存在sql注入漏洞的注入点2.列举可用数据库3.列举数据库
阅读更多...
Linux之软件包管理器yum详解

Linux之软件包管理器yum详解

《Linux之软件包管理器yum详解》文章介绍了现代类Unix操作系统中软件包管理和包存储库的工作原理,以及如何使用包管理器如yum来安装、更新和卸载软件,文章还介绍了如何配置yum源,更新系统软件包... 目录软件包yumyum语法yum常用命令yum源配置文件介绍更新yum源查看已经安装软件的方法总结软
阅读更多...
Oracle查询优化之高效实现仅查询前10条记录的方法与实践

Oracle查询优化之高效实现仅查询前10条记录的方法与实践

《Oracle查询优化之高效实现仅查询前10条记录的方法与实践》：本文主要介绍Oracle查询优化之高效实现仅查询前10条记录的相关资料,包括使用ROWNUM、ROW_NUMBER()函数、FET... 目录1. 使用 ROWNUM 查询2. 使用 ROW_NUMBER() 函数3. 使用 FETCH FI
阅读更多...
java图像识别工具类(ImageRecognitionUtils)使用实例详解

java图像识别工具类(ImageRecognitionUtils)使用实例详解

《java图像识别工具类(ImageRecognitionUtils)使用实例详解》：本文主要介绍如何在Java中使用OpenCV进行图像识别,包括图像加载、预处理、分类、人脸检测和特征提取等步骤... 目录前言1. 图像识别的背景与作用2. 设计目标3. 项目依赖4. 设计与实现 ImageRecogni
阅读更多...
Java访问修饰符public、private、protected及默认访问权限详解

Java访问修饰符public、private、protected及默认访问权限详解

《Java访问修饰符public、private、protected及默认访问权限详解》：本文主要介绍Java访问修饰符public、private、protected及默认访问权限的相关资料,每... 目录前言1. public 访问修饰符特点：示例：适用场景：2. private 访问修饰符特点：示例：
阅读更多...
Python将大量遥感数据的值缩放指定倍数的方法(推荐)

Python将大量遥感数据的值缩放指定倍数的方法(推荐)

《Python将大量遥感数据的值缩放指定倍数的方法(推荐)》本文介绍基于Python中的gdal模块,批量读取大量多波段遥感影像文件,分别对各波段数据加以数值处理,并将所得处理后数据保存为新的遥感影像... 本文介绍基于python中的gdal模块，批量读取大量多波段遥感影像文件，分别对各波段数据加以数值处
阅读更多...
python管理工具之conda安装部署及使用详解

python管理工具之conda安装部署及使用详解

《python管理工具之conda安装部署及使用详解》这篇文章详细介绍了如何安装和使用conda来管理Python环境,它涵盖了从安装部署、镜像源配置到具体的conda使用方法,包括创建、激活、安装包... 目录pytpshheraerUhon管理工具：conda部署+使用一、安装部署1、 下载2、 安装3
阅读更多...
Mysql虚拟列的使用场景

Mysql虚拟列的使用场景

《Mysql虚拟列的使用场景》MySQL虚拟列是一种在查询时动态生成的特殊列,它不占用存储空间,可以提高查询效率和数据处理便利性,本文给大家介绍Mysql虚拟列的相关知识,感兴趣的朋友一起看看吧... 目录1. 介绍mysql虚拟列1.1 定义和作用1.2 虚拟列与普通列的区别2. MySQL虚拟列的类型2
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2