0401锁详解-MySQL-数据库

news/2024/11/17 17:46:49/

1 概述

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

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

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

2 全局锁

  • 介绍

全局锁就是对整个数据库实例加锁，加锁后整个数据库实例处于只读状态，后续DML语句、DDL语句以及更新操作的事务提交语句都将被阻塞。

其典型应用场景就是做全库的逻辑备份，多所有表锁定，从而获得一致性视图，保证数据的完整性。

  • 演示

在这里插入图片描述

-- 加全局锁
flush tables with read lock;
-- 备份
mysqldump -u用户名 -p密码 数据库名 > 备份地址;
-- 解锁
unlock tables;

备份示例，窗口1 ：

-- 链接数据库
mysql -h 127.0.0.1 -uroot -p
-- 加全局锁
flush tables with read lock;

窗口2 ，备份数据库

mysqldump -h 127.0.0.1 -uroot -proot1234 nacos> /Users/gaogzhen/Downloads/nacos1.sql
  • mysqldump是mysql提供的备份工具，不是sql命令

窗口3 ，链接数据库执行插入操作

use nacos;
insert into nacos values('gaogzhen', '123456', 1);
  • 未释放全局锁之前，该操作一直阻塞

如下图所示：

在这里插入图片描述

窗口1执行释放全局锁命令：

unlock tables;

释放锁之后，如下图所示：

在这里插入图片描述

  • 特点：数据库中加全局锁，是一个比较重的操作，存在以下问题：
    • 如果在主库上备份，那么在备份期间不能执行更新，业务停摆；
    • 如果在从库上备份，那么备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDb引擎中，我们可以在备份时加上参数--single-transaction来完成不加锁的一致性备份数据。

命令如下：

-- 不加锁备份
mysqldump -h host --single-transaction -u用户名 -p密码 数据库名 > 备份地址;

3 表锁

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

表级锁分类：

  • 表锁
  • 元数据锁（meta data lock，MDL）
  • 意向锁

3.1 表锁

表锁分类：

  • 表共享读锁（read lock）
  • 表独占写锁（write lock）

加锁、解锁语法：

-- 加锁
lock tables 表名 read/write;
-- 解锁
unlock tables / 客户端断开连接。

  • 读锁特点：客户端A加read lock,客户端A只能执行读操作，不能执行写操作；其他连接能执行读操作，写操作会被阻塞，直至库客户端A释放锁。如下图所示

    在这里插入图片描述

示例如下：

步骤1：开启2个客户端，目标表score。

步骤2：客户端A对表A加读锁

 -- 表score加读锁lock tables score read;

步骤3：客户端A执行读取操作

select * from score;
| id | name | math | english | chinese |
+----+------+------+---------+---------+
|  1 | Tom  |   67 |      88 |      95 |
|  2 | Rose |   23 |      66 |      90 |
|  3 | Jimi |   98 |      77 |      80 |

步骤4：客户端A执行写操作

update score set english=100 where id=1;
ERROR 1099 (HY000): Table 'score' was locked with a READ lock and can't be updated

步骤5：客户端B执行读取操作

select * from score;

步骤6：客户端B执行写操作

update score set

  • 阻塞

  • 表write特点：客户端A加写锁，客户端可以执行读写操作；其他客户端读写操作会被阻塞，直至客户端释放锁.

    在这里插入图片描述

示例：

-- 客户端加写锁
lock tables score write;
select * from score;
update score set math=60 where id=1;
-- 客户端B查询
select * from score;
-- 客户端A释放锁
unlock tables;

阻塞如下图所示：

在这里插入图片描述

释放锁如下图所示：

在这里插入图片描述

3.2 元数据锁

MDL加锁过程是系统自动控制，无需显示使用，在访问一张表的时候自动加上。MDL锁的主要作用是维护表元数据的数据一致性。在表上有活动事务时，不能对元数据执行写操作。为了避免DML与DDL冲突，保证读写的正确性。

元数据是描述数据库结构和对象的信息，如表结构、索引、触发器等。

在MySQL5.5加入了MDL， 当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排它锁）。如下表所示：

SQL类型锁类型说明
lock tables xxx read/writeSHARED_READ_ONLY/SHARED_NO_READ_WRITE
select\select … lock in share modeSHARED_READ与SAHRED_READ,SHARED_WRITE兼容，与EXCLUSIVE互斥
Insert、update、delete、select … for updateSHARED_WRITE元数据是描述数据库结构和对象的信息，如表结构、索引、触发器等。
alter table …EXCLUSIVE与其他MDL互斥

示例：

-- 窗口1开启事务，执行查询
select * from score;

-- 窗口2 开启事务，执行修改表结构
alter table add column java int;

如下图所示：3.2-1所示：

在这里插入图片描述

修改表结构语句，加exclusive排它MDL锁，阻塞；当窗口1提交事务后，继续执行。

  • 查看元数据锁

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

示例：

-窗口1 开启事务
begin;
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
≈
object_typeobject_schemaobject_namelock_typelock_duration
TABLEperformance_schemametadata_locksSHARED_READTRANSACTION
-- 窗口1执行查询
select * from score;
-- 窗口2 开启事务
begin;
update score set math=28 where id=1;
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

如下表所示：

object_typeobject_schemaobject_namelock_typelock_duration
TABLEperformance_schemametadata_locksSHARED_READTRANSACTION
TABLEperformance_schemametadata_locksSHARED_READTRANSACTION
TABLEgaogzhenscoreSHARED_READTRANSACTION
TABLEgaogzhenscoreSHARED_WRITETRANSACTION
-- 现在窗口2 执行删除java列操作
alter table score drop column java;
-- 窗口1 查看元数据锁
  • 窗口2 阻塞，如下图所示

在这里插入图片描述

窗口1，提交事务，窗口2正常执行。

3.3 意向锁

  • 概述

InnoDB意向锁是为了支持多粒度锁共存而设计的，意向锁是一种特殊的行锁。在取得行锁之前需要先获取表的意向锁。
意向锁分为两类：意向共享锁和意向排他锁：

  • 意向共享锁IS：表示事务想要对表中的行设置共享锁，有语句select ... in share mode添加；
  • 意向排他锁IX：表示事务想要对表中行设置排他锁，由insert\update\delete ... 添加。

意向锁主要是辅助表级和行级锁冲突的判断，因为InnoDB支持行级锁，如果没有意向锁，那么判断表级锁和行级锁冲突就需要遍历所有行的行锁，有了意向锁就可以直接判断意向锁是否存在就可以判断是否有行锁了。

  • 与表锁兼容性

    • 意向共享锁（IS）： 与表锁共享锁（read）兼容，与表锁排它锁（write）互斥；
    • 意向排它锁（IX）：与表锁共享锁（read）及表示排它锁（write）都互斥；
    • 意向锁之间不互斥。

  • 查看意向锁与行锁加锁

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

  • 意向锁测试

第一步：窗口1 开启事务，添加意向共享锁IS

begin;
select * from score where id=1 lock in share mode;

第二步：窗口2查看锁记录

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
object_schemaobject_nameindex_namelock_typelock_modelock_data
gaogzhenscoreNULLTABLEISNULL
gaogzhenscorePRIMARYRECORDS,REC_NOT_GAP1

第三步：窗口2 加表共享锁（read）

mysql> lock tables score read;
Query OK, 0 rows affected (0.01 sec)mysql> unlock tables-> ;
Query OK, 0 rows affected (0.01 sec)

第四步：窗口2 加表排它锁（write）,阻塞。

在这里插入图片描述

第五步：窗口1提交事务，窗口2阻塞解除。

在这里插入图片描述

第六步：窗口1开启事务，添加意向排它锁（IX）

begin;
update score set math=88 where id=1;
object_schemaobject_nameindex_namelock_typelock_modelock_data
gaogzhenscoreNULLTABLEIXNULL
gaogzhenscorePRIMARYRECORDX,REC_NOT_GAP1

第七部：窗口2执行加表锁操作及及意向锁操作

lock tables score read;
-- 阻塞，窗口1重新开启事务添加意向排它锁IX
lock tables score write;
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
-- 阻塞，窗口1重新开启事务添加意向排它锁IX
update score set math=88 where id=2;
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
object_schemaobject_nameindex_namelock_typelock_modelock_data
gaogzhenscoreNULLTABLEIXNULL
gaogzhenscorePRIMARYRECORDX,REC_NOT_GAP2
gaogzhenscoreNULLTABLEIXNULL
gaogzhenscorePRIMARYRECORDX,REC_NOT_GAP1

​

4 行锁

4.1 概述

行级锁，每次操作锁住对应的行数据。锁粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB引擎中。

InnoDB引擎的数据是基于索引组织的，行锁通过对索引上加锁来实现的，而不是对记录加的锁。

  • 行锁分类：
    • 行锁（Record Lock）：锁定单个行记录，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持；
    • 间隙锁（Gap Lock）： 锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他记录在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
    • 临建锁（Next-Key Lock）：行锁和间隙锁的结合，同时锁住行数据，并锁住当前行数据前面的间隙。在RR隔离级别下支持。

在这里插入图片描述

4.2 行锁

InnoDB实现了一些两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁；
  • 排它锁（X）：允许获取排它锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排它锁。
请求锁类型S（共享锁）X（排它锁）
S（共享锁）兼容冲突
X（排它锁）冲突冲突
  • 加锁
SQL行锁类型说明
insert \update\deleteX（排它锁）自动加锁
select …不加任何锁
select … lock in share modeS(共享锁)手动在select之后加lock in share mode
select … for updateX(排它锁)手动在select之后加for update
  • 说明

默认情况下，InnoDB在REPEATABLE READ 事务隔离级别下运行，InnoDB使用Next-Key Lock锁（临建锁）进行搜索和索引扫描，以防止幻读。

  1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，自动优化为行锁；
  2. InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB引擎将对表中的所有记录加锁，此时升级为表锁。

  • 查看意向锁及行锁加锁

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

  • 演示

初始化：窗口1开启事务，执行普通的select ,窗口2查看加锁情况

select * from score where id=1;
-- 无锁
Empty set (0.01 sec)

第一步：窗口1加共享行锁，再次查看

select * from score where id=1 lock in share mode;

查询结果如下表4.2-1所示：

object_schemaobject_nameindex_namelock_typelock_modelock_data
gaogzhenscoreNULLTABLEISNULL
gaogzhenscorePRIMARYRECORDS,REC_NOT_GAP1

第二步：窗口2开启事务，也对相同行加共享行锁，查看锁情况

select * from score where id=1 lock in share mode;
object_schemaobject_nameindex_namelock_typelock_modelock_data
gaogzhenscoreNULLTABLEISNULL
gaogzhenscorePRIMARYRECORDS,REC_NOT_GAP1
gaogzhenscoreNULLTABLEISNULL
gaogzhenscorePRIMARYRECORDS,REC_NOT_GAP1

第三步：窗口2 提交事务，重新开启事务对id=3的数据行执行修改操作

update score name='Java' where id=3;
  • 不阻塞，正常更新，不同数据集之间行锁不会冲突。
object_schemaobject_nameindex_namelock_typelock_modelock_data
gaogzhenscoreNULLTABLEIXNULL
gaogzhenscorePRIMARYRECORDX,REC_NOT_GAP3
gaogzhenscoreNULLTABLEISNULL
gaogzhenscorePRIMARYRECORDS,REC_NOT_GAP1

第四步：窗口2对id=1的数据行执行更新操作

update score set name='Java' where id=1;
-- 窗口2事务行锁gaogzhen      | score       | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1
  • 窗口2阻塞

其他情况不在测试，有兴趣自行测试

第五步：测试无索引更新与添加索引后的更新

-- 重新开启事务
begin;
-- 窗口1 通过name字段更新分数
update score set math=88 where name='Tom';

查看加锁情况，对所有记录加锁

object_schemaobject_nameindex_namelock_typelock_modelock_data
gaogzhenscoreNULLTABLEIXNULL
gaogzhenscorePRIMARYRECORDXsupremum pseudo-record
gaogzhenscorePRIMARYRECORDX1
gaogzhenscorePRIMARYRECORDX2
gaogzhenscorePRIMARYRECORDX3

窗口2执行更新操作，阻塞

给name字段添加索引

create index idx_score_name on score(`name`);

再次执行上述操作，窗口1根据name字段跟新数据

4.3 间隙锁&临建锁

默认情况下，InnoDB在REPEATABLE READ 事务隔离级别下运行，InnoDB使用Next-Key Lock锁（临建锁）进行搜索和索引扫描，以防止幻读。

  1. 索引上的等值查询（唯一索引）

    • 记录存在时，对应索引添加行锁
    • 给不存在记录加锁时，优化为间隙锁，即向右边遍历第一个不满足条件索引加间隙锁；

  2. 索引上的等值查询（普通索引）

    • 记录存在时，对应的主键索引添加行锁；普通索引对应的记录前后加临建锁；
    • 记录不存在时向右遍历至第一个不满足查询需求时next-key lock 退化为间隙锁；

  3. 索引上的范围查询

    • 唯一索引：范围内添加临建锁

    • 普通索引：对应普通索引范围内添加临建锁；对应存在主键索引添加行锁。

说明：对于表中最大值之后的临建锁怎么加的呢

  • 示例：| gaogzhen | stu | idx_stu_age | RECORD | S | supremum pseudo-record |

  • supremum pseudo-record：上界伪记录即无穷大到记录对应查询最大值之间的间隙

测试表stu如下所示：

idagename
12tom
34cat
89rose
1112jetty
1920lily
2526luci

  • 测试更新id=5的行数据

    update stu set age=20 where id=5;

sss

加锁情况如下表所示：

object_schemaobject_nameindex_namelock_typelock_modelock_data
gaogzhenstuNULLTABLEIXNULL
gaogzhenstuPRIMARYRECORDX,GAP8
  • 索引：主键索引
  • 锁类型：行锁
  • 锁模式：排它锁，间隙锁。
  • 锁的间隙：id 3~8之间的间隙，不包含id为3和8的间隙

验证一下，插入一条id=7的数据

insert into stu values(7, 20, 'Lucy');

  • 窗口2阻塞

  • 测试普通索引等值查询

在age字段创建普通索引，窗口1开启事务，执行查询

create index idx_stu_age on stu(`age`);
begin;
select * from stu where age=4 lock in share mode;

查看加锁情况：

object_schemaobject_nameindex_namelock_typelock_modelock_data
gaogzhenstuNULLTABLEISNULL
gaogzhenstuidex_stu_ageRECORDS4, 3
gaogzhenstuPRIMARYRECORDS,REC_NOT_GAP3
gaogzhenstuidx_stu_ageRECORDS,GAP9, 8
  • 第二条记录，索引类型idx_stu_age,锁模式S临建锁，lock_date age=4对应主键3
    • 锁住age=4及之前的间隙即age=2记录到age=4之间间隙及age=4记录对应的索引
  • 第三条记录，锁住id=3对应的主键索引
  • 第四条，锁住age=4之后的间隙，即age 4~9之间的间隙对应的

其他情况自行测试。lock_mode标志如下表所示

行锁间隙锁临建锁
共享锁S,REC_NOT_GAPS,GAPS
排它锁X,REC_NOT_GAPX,GAPX

5 小结

5.1 概述

  • 在并发访问数据库时，解决数据的一致性、有效性问题；
  • 分类：全局锁、表级锁、行级锁。

5.2 全局锁

  • 对整个数据库加锁，加锁后整个数据库实例处于只读状态；
  • 性能较差，数据逻辑备份时使用。

5.3 表锁

  • 操作时锁住整张表，锁粒度大，发生锁冲突概率高；
  • 分类：表锁、元数据锁、意向锁。

5.4 行锁

  • 操作锁住对应的行数据，锁粒度最小，发生锁冲突的概率最低；
  • 分类：行锁、间隙锁、临建锁。

结语

如果小伙伴什么问题或者指教，欢迎交流。

❓QQ:806797785

参考链接:

[1]MySQL数据库视频[CP/OL].2020-04-16.p122-132.


http://www.ppmy.cn/news/694695.html

相关文章

基于圆弧搜索和最小似然圆估计的圆查找算法

基于圆弧搜索和最小似然圆估计的圆查找算法

在这里提出一种算法，其实是将两篇papers的想法的结合。没有什么创新点，只是做一下算法原理的记录，防止后面重新看代码的时候一头雾水。 算法的目的在于查找特定半径范围的圆，而不致力于查找所有的圆，当然它也支持查找所…
阅读更多...
有什么外观漂亮的蓝牙耳机？高颜值真无线蓝牙耳机推荐

有什么外观漂亮的蓝牙耳机？高颜值真无线蓝牙耳机推荐

在当今的生活中，不管是开车，还是运动，都离不开无线蓝牙耳机，可以在开车不方便用手接听电话的时候，也可以在跑步过程中听听音乐，释放汗水，如今市面上各种各样的无线蓝牙耳机，各种牌子…
阅读更多...
【WSN定位】基于蜣螂优化算法DBO的Dvhop定位算法 不同锚节点比例和不同通信半径情况下对比【Matlab代码#45】

【WSN定位】基于蜣螂优化算法DBO的Dvhop定位算法 不同锚节点比例和不同通信半径情况下对比【Matlab代码#45】

文章目录 【可更换其他算法，获取资源请见文章第5节：资源获取】1. Dvhop定位算法2. 蜣螂优化算法3. 部分代码展示4. 仿真结果展示5. 资源获取 【可更换其他算法，获取资源请见文章第5节：资源获取】 1. Dvhop定位算法 根据距离矢量和…
阅读更多...
Java -- XXL-JOB分布式任务调度平台

Java -- XXL-JOB分布式任务调度平台

XXL-JOB是一个分布式任务调度平台，其核心设计目标是开发迅速、学习简单、轻量级、易扩展。现已开放源代码并接入多家公司线上产品线，开箱即用 xxl是xxl-job的开发者大众点评的【许雪里】名称的拼音开头 官网地址 分布式任务调度平台XXL-JOB 文档地址…
阅读更多...
Matlab遗传算法和KSW熵法实现灰度图像阈值分割（附上完整源码+图片）

Matlab遗传算法和KSW熵法实现灰度图像阈值分割（附上完整源码+图片）

灰度图像阈值分割是一种常用的图像分割方法，可以将图像中的物体和背景分开，便于后续的图像处理和分析。本文将介绍如何使用Matlab编程实现灰度图像阈值分割的方法，其中使用了遗传算法和KSW熵法。 文章目录，完整源码数据见文末下载…
阅读更多...
OSChina 周三乱弹 ——一首诗解释程序人生

OSChina 周三乱弹 ——一首诗解释程序人生

2019独角兽企业重金招聘Python工程师标准>>> Osc乱弹歌单（2017）请戳（这里） 【今日歌曲】 莱布妮子 ：分享Bullet for My Valentine的单曲《Tears Dont Fall (Acoustic)》 《Tears Dont Fall (Acoustic)》- …
阅读更多...
【概率论】独立性作业（二）

【概率论】独立性作业（二）

文章目录 填空题填空题 设 A₁,A₂,A₃相互独立,且P(A_{i})=2 \sqrt {3},i=1,2,3.试求A₁,A₂,A₃中 (1) 至少出现一个的概率=____ 【正确答案:26/27】; (2) 恰好出现一个的概率=____ 【正确答案:2/9】; (3) 最多出现一个的概率=____ 【正确答案:7/27】; 解： (1) 所求概率为 P…
阅读更多...
Linux--运行指令的本质

Linux--运行指令的本质

本质： ①找到它 which的作用就是找到它 ②运行它 示例： ①告诉系统要运行的指令，然后系统去查找它的路径并运行它 ②自己告诉系统自己要运行的路径，然后系统运行它 注意：a.out不能运行，而./a.out能运行…
阅读更多...
最新文章

Copyright @ 2022~2023