MySQL2️⃣锁

锁：计算机协调多个进程或线程并发访问某一资源的机制。

按照锁的粒度分，MySQL 锁的分类如下：

全局锁：锁住当前数据库实例（数据库中的所有表）。
表级锁：锁住当前表。
行级锁：锁住当前行记录。

1、全局锁

锁住当前数据库实例（数据库中的所有表）

加锁后整个实例处于只读状态，期间的 DML、DDL 语句都会被阻塞。
场景：数据库逻辑备份。此时锁定所有的表，获取一致性视图，以保证数据的完整性。

1.1、语法

加锁
```
flush tables with read lock ;
```

数据备份

mysqldump -uroot –p1234 itcast > itcast.sql

释放
```
unlock tables;
```

1.2、数据库备份

有加锁和不加锁两种方式

加全局锁：
- 主库备份：备份期间都不能执行更新，基本上业务无法进行。
- 从库备份：备份期间从库不能执行主库同步过来的二进制日志（binlog），导致主从延迟。

不加锁：使用参数 --single-transaction

# 语法
mysqldump --single-transaction -u用户名 –p密码 数据库 > SQL文件

# 示例：将student数据库备份到student.sql文件中
mysqldump --single-transaction -uroot –p123456 student > student.sql

2、表级锁

锁住当前表。

（MyISAM、InnoDB、BDB 等存储引擎都支持）

特点：锁定粒度大，发生锁冲突的概率最高，并发度最低。
类型：表锁、元数据锁、意向锁

2.1、表锁

分类

表共享读锁（read lock）

表独占写锁（write lock）

# 加锁
LOCK TABLES 表名... READ;
LOCK TABLES 表名... WRITE;
# 释放锁
UNLOCK TABLES;
# 客户端断开连接时，锁也会释放

示例

读锁：对一个表加读锁，则任意事务对该表只读（包括当前事务）
写锁：对一个表加写锁，则阻塞其它事务的读写。

2.2、元数据锁（MDL）

meta data（元数据）

可理解为表的结构。

当表上有活动事务时，此时不应该对元数据进行写操作。

也就是说，当前表存在未提交的事务时（DML），不应该修改表结构（DDL）。

meta data lock（MySQL 5.5 引入）

作用：维护表元数据的数据一致性，避免 DML 和 DDL 冲突。
类型：在访问一张表的时候，自动根据操作控制 MDL 加锁。
- 增删改查：MDL 读锁（共享）
- 更改表结构：MDL 写锁（排他）

理解

事务 A 执行 UPDATE 语句：对记录添加行锁，并且自动为该表添加一个 MDL 读锁
```
SELECT * FROM tb_user WHERE id = 7;
```
事务 B 对该表添加表锁时，会阻塞
```
LOCK TABLES tb_user READ;
```
事务 A 提交事务，释放行锁后，MDL 读锁也随之释放。
事务 B 才能获得 MDL 写锁。

常见元数据锁

在访问数据库表时，系统会根据操作自动加上相应的元数据锁。

	对应元数据锁	互斥性
`加表锁`	`SHARED_READ_ONLY` `SHARED_NO_READ_WRITE`
`SELECT` `SELECT IN SHARE MODE`	`SHARED_READ`	只与 `EXCLUSIVE` 互斥
`增删改` `SELECT FOR UPDATE`	`SHARED_WRITE`	只与 `EXCLUSIVE` 互斥
`修改表结构`	`EXCLUSIVE`	与其它 `MDL` 互斥

查看元数据锁情况

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

2.3、意向锁

没有意向锁的场景

假设事务 A 对表中记录添加了行锁。
假设事务 B 要添加表锁，需要遍历全表（效率低），确定每行记录都没有行锁才能加表锁。

Intent lock

针对添加表锁的优化机制，

避免在 DML 执行期间，加的行锁与表锁冲突。

机制：
1. 执行 DML 时添加行锁，此时系统自动添加对应的意向锁。
2. 事务提交后，意向锁会自动释放。
类型：意向锁之间相互兼容，不会互斥。

对应意向锁互斥性

SELECT LOCK IN SHARE MODE 意向共享锁（IS）与表独占写锁互斥

增删改、SELECT FOR UPDATE 意向排它锁（IX）与表锁互斥

	对应意向锁	互斥性
`SELECT LOCK IN SHARE MODE`	意向共享锁（IS）	与表独占写锁互斥
`增删改`、`SELECT FOR UPDATE`	意向排它锁（IX）	与表锁互斥

有意向锁的场景

事务 A 对表中记录添加了行锁，此时自动添加一个意向锁。
假设事务 B 要添加表锁，只需判断该表是否有意向锁，无需遍历全表。

查看意向锁、行锁的加锁情况

SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
FROM performance_schema.data_locks;

3、行级锁（❗）

锁住当前行记录。

（InnoDB 支持）

特点：锁定粒度小，发生锁冲突的概率最低，并发度最高。
注意：
- 针对索引项加锁，而不是对记录本身加锁。
- 如果对应列没有索引或索引失效，则行级锁会升级表锁。

3.1、行锁

Record Lock：锁定单个行记录，阻塞其它事务的删改。

隔离级别支持：RC、RR
InnoDB 行锁类型：
1. 共享锁（S）：允许获取共享锁的事务执行读操作，阻止其它事务获得相同数据集的排它锁。
2. 排它锁（X）：允许获取排它锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排它锁。

常见 SQL 语句的行锁

	类型	说明
`增删改`	排它锁	自动加锁
`SELECT`	不加锁
`SELECT ... LOCK IN SHARE MODE`	共享锁	手动添加 `LOCK IN SHARE MODE`
`SELECT ... FOR UPDATE`	排它锁	手动添加 `FOR UPDATE`

3.2、间隙锁、临键锁

隔离级别支持：RR

作用：阻塞其它事务的 INSERT，防止幻读。

Gap Lock：针对目标记录行，在其上一条记录和目标记录本身之间的间隙加锁。
- 假设表中有 id 为 1，3，7，8 的记录，执行 UPDATE tb_user WHERE id = 5
- 此时在 3 和 7 之间添加间隙锁。
Next-Key Lock：针对存在的记录，锁定当前记录+之后所有记录的间隙。
- 假设表中有 id 为 1，3，7，8 的记录，执行 UPDATE tb_user WHERE id = 3
- 此时对 3 添加行锁，在 3和 7 之间、7 和 8 之间添加间隙锁。

4、不同场景的使用情况

InnoDB 默认在 RR 隔离级别运行，

且使用临键锁进行搜索和索引扫描，以防止幻读。

4.1、没有索引

当记录没有索引或索引失效时，无法添加行锁，使用表锁。

4.2、唯一索引

等值查询：
1. 对于存在的记录，使用临键锁。
2. 对于不存在的记录，使用间隙锁。
范围查询：使用临键锁。

4.3、普通索引

针对等值查询：临键锁 + 间隙锁

说明：
- InnoDB 使用 B+tree 索引，叶子节点是有序的双向链表。
- 由于普通索引非唯一，可能存在多个相同值的记录。
加锁步骤：
- 向右遍历叶子节点，直到找到不满足查询条件的值，加临键锁。
- 在添加临键锁的记录之后加间隙锁。

示例：假设等值查询条件为 age = 16

MySQL 匹配到第一个 16，向右遍历直到不满足查询条件的值 38
对 B 位置的 16 加临键锁，在 B 和 C 之间加间隙锁。

posted @ 2022-10-13 11:09 Jaywee 阅读(41) 评论(0) 编辑收藏举报

刷新页面返回顶部

Jaywee