【MYSQL锁】透彻地理解MYSQL锁

🔥作者主页：小林同学的学习笔录

🔥mysql专栏：小林同学的专栏

目录

1.锁

1.1  概述 

1.2  全局锁

1.2.1  语法

1.2.1.1   加全局锁

1.2.1.2   数据备份

1.2.1.3   释放锁

1.2.1.4   特点

1.2.1.5   演示

1.3   表级锁

1.3.1  介绍

1.3.2  表锁

1.3.2.1  语法

1.3.2.2  特点

1.3.2.3  结论

1.3.3  元数据锁

1.3.4  意向锁

1.3.4.1  介绍

1.3.4.2  分类

1.3.4.3  演示

1.4  行级锁

1.4.1  介绍

1.4.2  行锁

1.4.3  演示

1.4.4  间隙锁&临键锁

1.4.4.1  示例演示

1.锁

1.1  概述 

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源

（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据

库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

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

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

在介绍锁之前先回顾一下DML、DQL、DDL分别代表什么?

数据操作语言（DML）：DML 用于对数据库中的数据执行操作，例如插入、更新、删除、查询数

据，简称增删改查，尽管 SELECT 语句通常被归类为 DQL，但它也可以在某种程度上被认为是

DML，因为它允许检索数据。

数据查询语言（DQL）：DQL 用于从数据库中检索数据。它专门用于执行查询操作

数据定义语言（DDL）：DDL 用于定义数据库的结构，包括创建、修改和删除数据库对象，

例如（表、索引、视图等）。DDL 的操作影响数据库的整体结构。

1.2  全局锁

全局锁（Global Lock）是数据库管理系统中的一种锁定机制，通常用于锁定整个数据库实例，而

不是单个表或行。全局锁可以阻止对整个数据库的写入操作，但通常不会阻止读取操作。这种锁定

机制在某些情况下可以用于数据库备份、恢复或维护操作。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的

完整性.

为什么全库逻辑备份，就需要加全就锁呢？

①.先来分析一下不加全局锁，可能存在的问题。

假设在数据库中存在这样三张表: tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日志表。

在进行数据备份时，先备份了tb_stock库存表。

然后接下来，在业务系统中，执行了下单操作，扣减库存，生成订单（更新tb_stock表，插入

tb_order表）。

然后再执行备份 tb_order表的逻辑。

业务中执行插入订单日志操作。

最后，又备份了tb_orderlog表。

此时备份出来的数据，是存在问题的。因为备份出来的数据，tb_stock表与tb_order表的数据不一

致(有最新操作的订单信息,但是库存数没减)。

②.再来分析一下加了全局锁后的情况

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的

DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就

是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保

证了数据的一致性和完整性。

1.2.1  语法

1.2.1.1   加全局锁

flush tables with read lock ;

1.2.1.2   数据备份

mysqldump -uroot –p1234 itcast > itcast.sql

mysqldump -uXxx –pXxx 数据库名 > 磁盘地址  +  数据库名.sql

例如：mysqldump -uXxx –pXxx db_01 > D:/db_01

1.2.1.3   释放锁

unlock tables ;

1.2.1.4   特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致

主从延迟。

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

据备份。

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

1.2.1.5   演示

开启两个命令行

第一个命令行

第二个命令行

会处于阻塞状态，光标一直在闪，只有全局锁被释放才可以执行相应的操作

1.3   表级锁

1.3.1  介绍

表级锁，每次操作锁住整张表。锁定力度大，发生锁冲突的概率最高，并发度最低。应用在

MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：

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

1.3.2  表锁

对于表锁，分为两类：

①.表共享读锁（read lock）

②.表独占写锁（write lock）

1.3.2.1  语法

加锁：lock tables 表名... read/write。

释放锁：unlock tables /  客户端断开连接 。

1.3.2.2  特点

①.读锁

左侧为客户端一，对指定表加了读锁，不会影响右侧客户端二的读，但是会阻塞右侧客户端的写。

②.写锁

左侧为客户端一，对指定表加了写锁，会阻塞右侧客户端的读和写。

1.3.2.3  结论

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户

端的写。

1.3.3  元数据锁

MDL(meta data lock)加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加

上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进

行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

注意：有元数据锁只有在事务开启，才有的锁，当事务提交之后相应的锁会被释放.

这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的

事务时，是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进

行变更操作的时候，加MDL写锁(排他)，另外共享锁是兼容的。

常见的SQL操作时，所添加的元数据锁：

1.3.3.1  演示

①.当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁

（SHARED_READ / SHARED_WRITE），之间是兼容的，所以不会出现阻塞状态。

②.当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁

（EXCLUSIVE），之间是互斥的，所以出现了阻塞状态。

我们可以通过下面的SQL，来查看数据库中的元数据锁的情况：

select object_type,object_schema,object_name,lock_type,lock_duration from

performance_schema.metadata_locks ;

1.3.4  意向锁

1.3.4.1  介绍

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查

每行数据是否加行锁，使用意向锁来减少表锁的检查。

假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢?

来通过示意图简单分析一下：

首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加锁。

当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此

时就会从第一行数据，检查到最后一行数据，效率较低。

有了意向锁之后 :

客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加上表

锁，而不用逐行判断行锁情况了。

1.3.4.2  分类

①.意向共享锁(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;

1.3.4.3  演示

①.意向共享锁与表读锁是兼容的

由于select语句不会自动加行锁，需要手动加行锁

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

②.意向排他锁与表读锁、写锁都是互斥的

insert、update、delete语句会自动加行锁

1.4  行级锁

1.4.1  介绍

行级锁，每次操作锁住对应的行数据。锁定力度最小，发生锁冲突的概率最低，并发度最高。应用

在InnoDB存储引擎中。

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

的锁。对于行级锁，主要分为以下三类：

①.行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。

在RC、RR隔离级别下都支持。

②.间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他

事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

③.临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。

在RR隔离级别下支持。

1.4.2  行锁

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁.就是允许一个事务

select，另外事务可以获得相同数据的共享锁，但是不能获得相同数据集的排他锁。

排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他

锁。

两种行锁的兼容情况如下:

常见的SQL语句，在执行时，所加的行锁如下：

1.4.3  演示

①.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

②.InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from

performance_schema.data_locks;

• 加共享锁，共享锁与共享锁之间兼容。
• 共享锁与排他锁之间互斥。

• 排它锁与排他锁之间互斥

• 无索引行锁升级为表锁

注意：这里面经常会手动开启事务的原因是为了演示效果，如果是自动开启事务会自动提交事务，

会把锁给释放，因此看不出效果，但是原理还是一样的.

1.4.4  间隙锁&临键锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜

索和索引扫描，以防止幻读。

①.索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

②.索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock

退化为间隙锁。

③.索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻

止另一个事务在同一间隙上采用间隙锁。

1.4.4.1  示例演示

①.索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁

②.索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock

退化为间隙锁。

介绍分析一下：

我们知道InnoDB的B+树索引，叶子节点是有序的双向链表。 假如，我们要根据这个二级索引查询

值为18的数据，并加上共享锁，我们是只锁定18这一行就可以了吗？ 

并不是，因为是非唯一索引，这个结构中可能有多个18的存在，所以在加锁时会继续往后找，找

到一个不满足条件的值（当前案例中也就是29）。此时会对18加临键锁，并对29之前的间隙加锁

这里的临键锁(行锁+间隙锁)，还会锁住age=3的行，并且还会锁住主键为1-3之间的间隙

③.索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止

所以数据库数据在加锁是，就是将19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷

的临键锁(正无穷及之前的间隙)。

麒麟而非淇淋，不是干货不制作https://blog.csdn.net/2301_77358195