深度整理总结MySQL——锁类型

最新推荐文章于 2025-02-15 01:22:43 发布

左灯右行的爱情

最新推荐文章于 2025-02-15 01:22:43 发布

阅读量1k

点赞数 21

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_45852626/article/details/145524102

版权

前言

MySQL的锁是比较难的(个人认为).本章简单介绍一下锁的类型,帮助我们有个大致的了解.

全局锁

全局锁是 MySQL 级别最高的锁，它作用于整个数据库实例，一旦加上全局锁，整个数据库都会进入只读模式，所有写操作（DML、DDL）都会被阻塞，直到锁被释放。
全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

语法

加锁:

FLUSH TABLES WITH READ LOCK;

FLUSH TABLES：先将所有表的修改数据写入磁盘，并关闭所有已打开的表。
WITH READ LOCK：对整个数据库加上全局读锁，防止其他线程进行写操作。

释放锁:

UNLOCK TABLES;

释放全局锁后，数据库恢复正常读写。

使用场景

逻辑备份（mysqldump 备份）：

如果不加 --single-transaction 选项，mysqldump 会使用全局锁来保证数据一致性。
–single-transaction 适用于 InnoDB，不会加全局锁，而是基于 MVCC 进行快照读，避免影响业务。会先创建 Read View，然后整个事务执行期间都在用这个 Read View.

数据库维护：
需要短时间冻结数据写入时，可以使用全局锁。

表级锁

表级锁就比较多一些了,有几种类型:表锁,元数据锁(MDL),意向锁,AUTO-INC 锁；

表锁

表锁是一种锁定整个表的机制，它会阻止其他线程对该表进行并发操作。MyISAM 存储引擎主要使用表锁，而 InnoDB 由于支持行锁，很少使用表锁。

语法

LOCK TABLES table_name READ;  -- 加表锁（只读）
LOCK TABLES table_name WRITE; -- 加表锁（读写）
UNLOCK TABLES;                -- 释放表锁
或者当事务提交/连接断开时，表锁会自动释放。

表锁的类型

锁类型	语法	影响
读锁（READ）	`LOCK TABLES table_name READ;`	允许多个线程读取，但不允许写入
写锁（WRITE）	`LOCK TABLES table_name WRITE;`	仅允许当前线程读写，其他线程不能访问

使用示例

读锁示例

读锁允许其他线程同时读取该表，但禁止写入。

LOCK TABLES users READ;
SELECT * FROM users; -- 允许查询
INSERT INTO users (name) VALUES ('Alice'); -- 阻塞（不能写）
UNLOCK TABLES;

写锁示例

写锁会阻塞所有其他线程，直到锁释放。

LOCK TABLES users WRITE;
SELECT * FROM users; -- 允许查询
INSERT INTO users (name) VALUES ('Alice'); -- 允许写入
-- 其他线程的读写操作都会被阻塞！
UNLOCK TABLES;

MyISAM vs InnoDB 的表锁

特性	MyISAM	InnoDB
默认锁机制	表锁	行锁
适用场景	读多写少	高并发
是否支持事务	❌ 不支持	✅ 支持
是否支持 MVCC	❌ 不支持	✅ 支持
是否会锁整张表	✅ 是	❌ 默认行锁（但有例外）
适合批量写入	❌ 不适合	✅ 更高效
加锁粒度	整张表	单行（默认）
是否支持外键	❌ 不支持	✅ 支持
执行 `ALTER TABLE` 是否锁表	✅ 锁表	✅ 锁表（某些操作）

元数据锁(MDL)

元数据锁（MDL, Metadata Lock）是 MySQL 5.5 引入的一种锁机制，用于保护表结构的并发访问。它在访问表时自动加锁，防止DDL（数据定义语言）操作和DML（数据操作语言）操作发生冲突，保证数据一致性。

特点

自动加锁：不需要手动 LOCK TABLES，MySQL 在访问表时自动加锁。
保证一致性：确保在 DDL 变更表结构时，不会影响正在执行的 SQL 语句。
可导致死锁：如果 DML 语句运行时间过长，DDL 可能会长时间等待，甚至导致死锁。

MDL类型

MDL 类型	锁名	作用	触发操作	是否允许并发
MDL 读锁（共享锁）	`MDL_SHARED`	保护表的元数据，防止其他事务修改表结构	`SELECT`、`INSERT`、`UPDATE`、`DELETE`	✅ 允许其他事务读写（但不能变更结构）
MDL 写锁（排他锁）	`MDL_EXCLUSIVE`	变更表结构，如 `ALTER TABLE`、`DROP TABLE`	执行 DDL 语句	❌ 阻塞其他事务的所有访问（包括查询）

读锁示例:
只要查询表，就会自动加MDL 读锁，防止表结构被修改。

SELECT * FROM users;  -- 自动加 MDL 读锁
-- 其他事务可以继续查询或修改 users 表，但不能执行 ALTER TABLE

写锁示例:
ALTER TABLE 会加MDL 写锁，阻塞所有对 users 表的访问，直到 DDL 语句执行完毕。

ALTER TABLE users ADD COLUMN age INT;  -- 自动加 MDL 写锁

MDL死锁问题

MDL不会主动释放,当表上有长时间运行的查询(如大事务、慢 SQL),DDL语句会一直等待,导致死锁或阻塞所有查询.
举例

-- 事务 1（长时间查询，持有 MDL 读锁）
BEGIN;
SELECT * FROM users;  -- 事务未提交，MDL 读锁一直持有

-- 事务 2（试图修改表结构）
ALTER TABLE users ADD COLUMN age INT;  -- 等待 事务 1 释放 MDL 读锁

后果：

事务 1 没有提交，MDL 读锁一直占用。
事务 2 执行 ALTER TABLE，等待 MDL 读锁释放。
其他事务（甚至 SELECT）都可能被阻塞。

申请MDL锁的操作会形成一个队列,队列中写锁优先级高于读锁.
一旦出现写锁等待,会阻塞后续该表的所有CRUD操作.
在对表结构变更前，先要看看数据库中的长，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

总结

特性	MDL 读锁（共享锁）	MDL 写锁（排他锁）
作用	保护表结构不被修改	变更表结构
触发操作	`SELECT`、`INSERT`、`UPDATE`、`DELETE`	`ALTER TABLE`、`DROP TABLE`
能否并发执行	✅ 允许其他事务读写	❌ 阻塞所有事务
是否自动释放	❌ 事务未提交前不会释放	❌ DDL 语句执行完才释放

意向锁(Intention Lock)

意向锁（Intention Lock，简称 IX 和 IS）是一种用于行锁和表锁之间的协调机制.
他不会直接阻塞表的操作,而是通过标记意向来表明某个事务希望对某一表或其某些行加锁,从而在更高层级(如表级)协调不同锁类型的使用.

意向共享锁(IS,Intention Shared Lock): 表示事务打算对某些行加共享锁(行级读锁).
意向排他锁(IX,Intention Exclusive Lock):表示事务打算对某些行加排它锁(行级写锁）.

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。
普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

不过，select 也是可以对记录加共享锁和独占锁的,具体方式如下:

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突.
而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。
表锁和行锁是满足读读共享、读写互斥、写写互斥的。

意向锁是表级锁，但它可以配合行锁来预告未来的锁请求，并避免行锁和表锁之间的冲突。

作用

协调行锁与表锁: 为了避免表级锁与行级锁冲突,使用意向锁来标记一个事务在行级锁操作时的意图.
提高并发性: 意向锁使得多个事务可以并发地对同一表执行不冲突的操作,从而避免了每个事务都需要申请表级锁.
如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。
那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁,如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

意向锁的目的是为了快速判断表里是否有记录被加锁。

类型

锁类型	锁名	作用	适用场景
意向共享锁（IS）	`IS`	表示事务打算对某些行加共享锁（读锁）	允许多个事务同时读取行，但不允许写
意向排他锁（IX）	`IX`	表示事务打算对某些行加排他锁（写锁）	允许事务对表的某些行进行写操作

示例

意向共享锁（IS）示例:

-- 事务 1
SELECT * FROM users WHERE age > 20;  -- 自动在表级加 IS 锁

事务 1 正在对 users 表中的某些行加共享锁（行级锁），但会在表上自动加意向共享锁（IS）。

意向排它锁(IX)示例:
当一个事务打算对表中的某些行加排他锁时，MySQL 会自动在表级别加上意向排他锁（IX）。

-- 事务 2
UPDATE users SET name = 'John' WHERE age > 30;  -- 自动在表级加 IX 锁

事务 2 正在对 users 表中的某些行加排他锁（行级锁），并自动在表上加意向排他锁（IX）。

锁类型	可兼容的锁类型	不可兼容的锁类型
意向共享锁（IS）	IS	IX、S、X
意向排他锁（IX）	IS、IX	S、X
共享锁（S）	IS、IX、S	X
排他锁（X）	无	任何其他锁

意向共享锁（IS）可以和其他 IS 锁并发，但与 IX、S 和 X 锁冲突。
意向排他锁（IX）可以和其他 IS 或 IX 锁并发，但与 S 和 X 锁冲突。

AUTO-INC锁(自增锁)

MySQL处理**自增字段(AUTO_INCREMENT)**时使用的专用锁.
自增字段是数据库中常见的一种字段类型,用于自动生成唯一的整数值(通常用于主键).
之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

目的：确保自增列的值是唯一的，避免多个事务插入数据时自增值重复。
作用：当多个事务同时进行插入时，AUTO-INC 锁确保自增值的生成不会发生冲突。

类型

InnoDB 存储引擎中的 AUTO-INC 锁只会在插入操作时使用，并且是表级锁（表锁）。它与行锁是独立的。

插入锁：当插入数据时，自增值会被锁定，防止多个事务同时插入数据时产生冲突。
自增字段的“获取”锁：事务会获取这个锁直到自增值生成完成。

工作原理

每个事务插入数据时，InnoDB 会获得一个自增锁，确保每次插入数据时自增值的生成是顺序的，没有竞争条件。
当有多个事务并发插入数据时，InnoDB 会通过加锁机制来控制自增字段的生成，避免不同事务插入的行使用相同的自增值。
具体流程：

事务 1 开始执行插入操作，MySQL 获取自增锁并分配自增值。
事务 2 在事务 1 执行插入时尝试插入数据，但会被自增锁阻塞，直到事务 1 提交。
事务 1 提交后，自增锁释放，事务 2 才能获得自增值并插入数据。

优化

在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。
一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁,然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

行级锁

行级锁是数据库的一种锁机制，用于在并发环境下确保对数据库表中某一行数据的访问是互斥的。
与表级锁不同，行级锁只锁定被访问的行，不会影响表中的其他行。行级锁能显著提高并发性能，尤其是在多用户同时对不同数据行进行操作的场景下。
行级锁主要应用于 InnoDB 存储引擎，它支持在事务中使用不同类型的行级锁来管理并发控制。

类型

行级锁的类型主要有两类：Record Lock,Gap Lock

Record Lock锁

记录锁是锁定表中的一行或一条记录的锁机制.
它专门用来锁定某一特定记录，从而防止其他事务在事务未提交之前对该记录进行修改或读取。记录锁能够实现更高的并发性，因为它锁定的是数据行，而不是整张表。

类型

主要有两种常见类型:

共享锁(S Lock)

也叫读锁，允许其他事务对同一记录加共享锁，但不允许对其进行修改,适用于事务进行读取操作时，保证数据不会被修改。

排他锁(X Lock)

也叫写锁，禁止其他事务读取或修改该记录,适用于事务进行写操作时，确保数据的唯一性和一致性。

应用场景

事务隔离：在并发的事务环境下，Record Lock 用于防止多个事务对同一条记录同时进行修改，避免了数据不一致的情况。
防止重复插入：在插入操作中，尤其是当插入的数据包含唯一索引时，Record Lock 确保同一记录不会被多个事务重复插入。
高并发场景：在并发环境下，通过记录锁可以在多个事务之间最大限度地共享资源，同时保持数据一致性。

案例

select * from t_test where id = 1 for update;

在这里插入图片描述
当事务执行 commit 后，事务过程中生成的锁都会被释放。

Gap Lock

它用于锁定索引中记录之间的间隙，而不是锁定实际存在的记录。Gap Lock 是一种 范围锁，通常与记录锁一起使用，用于防止其他事务在当前事务锁定的范围内插入新记录。
Gap Lock 主要用于处理幻读问题，在高并发的事务环境下，避免多个事务在相同的范围内插入重复数据或产生不一致的数据。

类型

本身是通过特定的查询和索引操作实现的，但其行为可通过以下几种不同的方式体现:纯间隙锁,间隙与记录锁结合.

纯间隙锁

锁定两条记录之间的空隙。
例如，查询条件是 (value > 30 AND value < 40)，那么 Gap Lock 锁定的是 30 和 40 之间的空隙。
例如,表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了.
在这里插入图片描述
，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

间隙与记录锁结合- 临键锁

当查询条件包含某一特定记录时，Gap Lock 会与记录锁（例如，X 锁）结合使用，锁定某个范围的记录和记录之间的空隙。例如，查询条件是 (value >= 30 AND value <= 40)，此时会锁定 [30, 40] 范围内的记录，并且锁定 30 和 40 之间的空隙。
例如,表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。
在这里插入图片描述
next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。