数据库的锁 - 全局锁、表锁、行锁

目录

一、全局锁

1.1 介绍

1.2 语法

1). 加全局锁

2). 数据备份

3). 释放锁

1.3 特点

二、表级锁

2.1 表锁

2.2 元数据锁（MDL）

2.3 意向锁

三、行级锁

3.1 行锁​

3.2 间隙锁 & 临键锁​

---

一、全局锁

1.1 介绍

全局锁是对整个数据库实例施加的锁，一旦加锁，整个数据库实例将进入只读状态。在此状态下，后续的 DML（数据操作语言，如 INSERT、UPDATE、DELETE）写语句、DDL（数据定义语言，如 CREATE、ALTER、DROP）语句，以及涉及更新操作的事务提交语句都会被阻塞。

其典型使用场景是全库的逻辑备份。在进行全库逻辑备份时，通过对所有表加锁，获取一致性视图，从而保证备份数据的完整性。

加全局锁后，在进行逻辑备份前，先对整个数据库加上全局锁。加锁后，其他的 DDL、DML 操作全部处于阻塞状态，仅允许执行 DQL（数据查询语言，如 SELECT）语句，即数据库处于只读状态。由于数据备份本质上是查询操作，所以在备份过程中，数据库中的数据不会发生变化，从而保证了数据的一致性和完整性。

1.2 语法

1). 加全局锁

在 MySQL 中，可以使用 FLUSH TABLES WITH READ LOCK; 语句来添加全局锁。执行该语句后，数据库进入只读状态，其他客户端的写操作和结构修改操作都会被阻塞。

2). 数据备份

数据备份可使用 mysqldump 等工具例如，使用 mysqldump 进行全库备份的基本命令格式为：

mysqldump -u[用户名] -p[密码] --all-databases > backup_file.sql

执行命令后，会提示输入密码，输入正确密码后即可开始备份。

3). 释放锁

当数据备份完成后，使用 UNLOCK TABLES; 语句释放全局锁，使数据库恢复正常读写状态。

1.3 特点

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

1. 主库备份影响业务：如果在主库上进行备份，那么在备份期间，所有更新操作都无法执行，业务基本上处于停摆状态，严重影响业务的连续性。
2. 从库备份导致主从延迟：如果在从库上备份，备份期间从库不能执行主库同步过来的二进制日志（binlog）。由于主库的更新操作持续进行，从库无法及时同步，就会导致主从延迟不断增大，影响数据库集群的可用性。

不过，在 InnoDB 引擎中，我们可以在备份时加上 --single-transaction 参数来完成不加锁的一致性数据备份。该参数利用 InnoDB 的 MVCC（多版本并发控制）机制，在一个事务内获取一致性快照，从而实现备份过程中不阻塞其他读写操作，极大地减少了备份对业务的影响。

二、表级锁

表级锁是 MySQL 中的一种锁机制，每次操作会锁住整张表。它的锁定粒度较大，发生锁冲突的概率较高，因此并发度较低。表级锁在多种存储引擎中都有应用，包括 MyISAM、InnoDB 和 BDB。

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

1. 表锁

2. 元数据锁（MDL）

3. 意向锁

2.1 表锁

表锁分为两类：

• 表共享读锁（Read Lock）：允许其他事务读取表中的数据，但会阻塞写操作。

• 表独占写锁（Write Lock）：允许事务读取和修改表中的数据，但会阻塞其他事务的读取和写入操作。

语法：

• 加锁

  LOCK TABLES 表名 READ/WRITE;

• 释放锁

  UNLOCK TABLES;

  或客户端断开连接。

特点：

• 读锁不会阻塞其他客户端的读操作，但会阻塞写操作。

• 写锁会阻塞其他客户端的读取和写入操作。

2.2 元数据锁（MDL）

元数据锁（MDL）是 MySQL 5.5 引入的一种锁机制，用于维护表元数据的数据一致性。MDL 锁是系统自动控制的，无需显式使用。它的主要作用是防止在表上有活动事务时对表结构进行修改，从而避免 DML 和 DDL 之间的冲突。

锁类型：

• MDL 读锁（SHARED_READ）：在对表进行增删改查操作时添加。

• MDL 写锁（EXCLUSIVE）：在对表结构进行变更操作时添加。

常见 SQL 操作对应的 MDL 锁：

• LOCK TABLES xxx READ/WRITE：SHARED_READ_ONLY 或 SHARED_NO_READ_WRITE

• SELECT、SELECT ... LOCK IN SHARE MODE：SHARED_READ

• INSERT、UPDATE、DELETE、SELECT ... FOR UPDATE：SHARED_WRITE

• ALTER TABLE：EXCLUSIVE

查看 MDL 锁情况： 可以通过以下 SQL 查询当前数据库中的 MDL 锁情况：

SELECT object_type, object_schema, object_name, lock_type, lock_duration
FROM performance_schema.metadata_locks;

2.3 意向锁

意向锁是 InnoDB 存储引擎中的一种锁机制，用于减少表锁与行锁之间的冲突。它允许表锁在不检查每行数据是否加锁的情况下，直接判断是否可以成功加锁。

假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。

当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就 会从第一行数据，检查到最后一行数据，效率较低。

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

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而 不用逐行判断行锁情况了。

分类：

• 意向共享锁（IS）：由 SELECT ... LOCK IN SHARE MODE 添加。与表锁共享锁（READ）兼容，但与表锁排他锁（WRITE）互斥。

• 意向排他锁（IX）：由 INSERT、UPDATE、DELETE、SELECT ... FOR UPDATE 添加。与表锁共享锁（READ）和排他锁（WRITE）都互斥，但意向锁之间不会互斥。

特点：

• 意向锁在事务提交后自动释放。

• 意向锁的存在使得表锁在判断是否可以加锁时更加高效，无需逐行检查行锁情况。

查看意向锁情况： 可以通过以下 SQL 查询当前数据库中的意向锁和行锁情况：

SELECT * FROM information_schema.innodb_locks;

意向共享锁与表读锁兼容：意向共享锁不会阻塞表读锁。

意向排他锁与表读锁和写锁互斥：意向排他锁会阻塞表读锁和写锁

三、行级锁

行级锁是 MySQL 中锁定粒度最小的锁机制，每次操作仅锁定对应的行数据。其特点是锁定粒度小，锁冲突概率低，并发度最高，仅应用于 InnoDB 存储引擎。​

InnoDB 的行级锁通过对索引项加锁实现（而非直接锁定记录），根据锁的类型和功能，可分为以下三类：​

1. 行锁（Record Lock）：锁定单个行记录，阻止其他事务对该行进行UPDATE和DELETE，支持RC和RR隔离级别。​
2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含记录本身），防止其他事务在间隙插入数据（避免幻读），仅支持RR隔离级别。​
3. 临键锁（Next-Key Lock）：行锁与间隙锁的组合，锁定数据本身及其前面的间隙，仅支持RR隔离级别。​

3.1 行锁​

1). 类型与兼容性​

InnoDB 支持两种行锁：​

• 共享锁（S 锁）：允许事务读取数据，阻止其他事务获取同记录的排他锁。​

• 排他锁（X 锁）：允许事务修改数据，阻止其他事务获取同记录的共享锁和排他锁。​

兼容性表：​

锁类型​	共享锁（S）​	排他锁（X）​
共享锁（S）​	兼容​	互斥​
排他锁（X）​	互斥​	互斥​

常见 SQL 加锁规则：​

SQL 操作​	行锁类型​	说明​
INSERT/UPDATE/DELETE​	排他锁（X）​	自动加锁​
SELECT ... LOCK IN SHARE MODE​	共享锁（S）​	手动加锁，需显式指定​
SELECT ... FOR UPDATE​	排他锁（X）​	手动加锁，需显式指定​
SELECT（普通查询）​	无锁​	默认不加锁​

2). 演示与特性​

默认隔离级别与加锁机制​

InnoDB 默认使用REPEATABLE READ（RR）隔离级别，采用 ** 临键锁（Next-Key Lock）** 防止幻读，仅在通过唯一索引等值查询时优化为行锁

行锁依赖索引的特性​

• 通过索引检索：行锁仅锁定命中的索引记录。​

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

示例演示​

A. 普通查询不加锁​

-- 客户端一​SELECT * FROM stu WHERE id=1; -- 不加锁，可正常查询​

B. 共享锁（S 锁）演示​

-- 客户端一（开启事务）​

BEGIN;​SELECT * FROM stu WHERE id=1 LOCK IN SHARE MODE; -- 对id=1加共享锁​

-- 客户端二（开启事务）​

SELECT * FROM stu WHERE id=3 LOCK IN SHARE MODE; -- 可正常加锁（不同行兼容）​UPDATE stu SET age=4 WHERE id=3; -- 阻塞（S锁与X锁互斥）​

C. 排他锁（X 锁）演示​

-- 客户端一（开启事务）​

BEGIN;​UPDATE stu SET name='Tom' WHERE id=1; -- 对id=1加排他锁​

-- 客户端二（开启事务）​

UPDATE stu SET name='Cat' WHERE id=1; -- 阻塞（X锁互斥）​UPDATE stu SET name='Jetty' WHERE id=11; -- 可正常执行（不同行不冲突）​

D. 无索引导致行锁升级为表锁​

-- 客户端一（无索引场景，name字段未建索引）​

BEGIN;​UPDATE stu SET age=20 WHERE name='lily'; -- 全表扫描，行锁升级为表锁​

-- 客户端二（尝试更新其他行）​

UPDATE stu SET age=4 WHERE id=3; -- 阻塞（表锁锁定全表）​

-- 优化：为name字段添加索引​

ALTER TABLE stu ADD INDEX idx_name(name);​

-- 客户端一（重建索引后）​

BEGIN;​UPDATE stu SET age=20 WHERE name='lily'; -- 通过索引锁定id=19的行，不阻塞其他行操作​
​

3.2 间隙锁 & 临键锁​

临键锁（Next-Key Lock） = 行锁（Record Lock） + 间隙锁（Gap Lock），锁定数据本身及其左侧间隙。​

间隙锁（Gap Lock）：锁定索引记录之间的间隙，防止其他事务插入数据，避免幻读。​

间隙锁兼容性：多个事务可共存相同间隙锁，仅阻止插入操作。​

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

• 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
• 索引上的等值查询(非唯一普通索引)，使用临键锁，向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁。
• 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

典型场景演示​

A. 唯一索引等值查询（不存在的记录）​

-- 场景：查询id=2（表中不存在），加排他锁​

BEGIN;​SELECT * FROM stu WHERE id=2 FOR UPDATE; -- 锁定间隙 (1,3)，即间隙锁​

B. 非唯一索引等值查询（普通索引）​

假设age为非唯一索引，表中age值为：1,3,8,11,19,25​

-- 场景：查询age=18（不存在），加共享锁​

BEGIN;​SELECT * FROM stu WHERE age=18 LOCK IN SHARE MODE; ​

-- 锁定间隙 (11,19) 和 (19,25)（临键锁退化为间隙锁，向右遍历至不满足条件的值25）​

C. 唯一索引范围查询​

-- 场景：查询id>=19，加共享锁​

BEGIN;​SELECT * FROM stu WHERE id>=19 LOCK IN SHARE MODE; ​

-- 锁定范围：​

-- 1. id=19（行锁）​

-- 2. 间隙 (19,25]（临键锁，含25）​

-- 3. 间隙 (25, +∞)（临键锁，防止插入大于25的值）​

间隙锁的作用​

• 防止幻读：在 RR 隔离级别下，通过锁定间隙避免其他事务插入新数据，确保范围查询结果的一致性。​

• 性能影响：间隙锁可能导致锁范围扩大，影响并发性能，需谨慎设计索引和查询条件。​

查看行锁状态​

SELECT OBJECT_SCHEMA AS `数据库`,OBJECT_NAME AS `表名`,INDEX_NAME AS `索引名`,LOCK_TYPE AS `锁类型`,LOCK_MODE AS `锁模式`,LOCK_DATA AS `锁定数据`
FROM performance_schema.data_locks;