MySQL数据库（八）☞ 我是不是锁神

目录

1 全局锁的应用

2 索引对行锁的影响

3 表锁（显式）--表级锁

4 元数据锁 MDL(隐式)--表级锁

5 意向锁(Intention)--IS锁 IX锁--表级锁（隐式）

6 记录锁-(Record)-S锁 X锁 -- 行级锁

7 如何理解select ... lock in share mode

8 记录锁/元数据锁/意向锁

9 如果意向排他锁由于第一行排他锁的出现而出现，但是此时我对第二行的数据执行update语句而出现排他锁会不会阻塞

10 间隙锁（Gap）-行级锁

11邻键锁（Next-Key）-行级锁

12 行锁之间的区分

1 全局锁的应用

锁->备份->解锁

• flush tables with read lock;
• mysqldump （-h ip地址linux中的数据库） -u root -p 123456 数据库名>路径.sql文件名.sql
• unlock tables;

特点：

2 索引对记录锁的影响

索引是行级锁的基础，直接影响锁的粒度和并发性能。

• 有索引时：记录锁基于索引定位到具体行
• 无索引时：记录锁退化为表锁。（锁住所有行）

3 表锁（显式）--表锁

语法

-- 加读锁（允许其他会话读，禁止写）
LOCK TABLES table_name READ;-- 加写锁（禁止其他会话读和写）
LOCK TABLES table_name WRITE;

示例

-- 会话1
LOCK TABLES users WRITE;
UPDATE users SET name = 'Alice' WHERE id = 1;  -- 独占操作
UNLOCK TABLES;

4 元数据锁 MDL(隐式)--表锁

 元数据加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。

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

作用对象主要是DML/DQL与 DDL之间  -俗称表结构的门卫

• DML DQL操作（如 SELECT, INSERT）：自动加 MDL 读锁（MDL_SHARED_READ）
• DDL 操作（如 ALTER TABLE）：自动加 MDL 写锁（MDL_EXCLUSIVE）。

示例

-- 会话1（未提交事务）
BEGIN;
SELECT * FROM users;  -- 持有 MDL 读锁-- 会话2（被阻塞）
ALTER TABLE users ADD COLUMN age INT;  -- 需要 MDL 写锁

结果：会话2 的 DDL 操作会等待会话1事务提交或超时（默认50秒）。

（需要手动验证，引擎默认自动提交事务）有事务存在不能对表结构进行操作，出现alter table 对表结构进行更改就会触发MDL读锁与写锁的冲突，将整张表锁住。

5 意向锁(Intention)--IS锁 IX锁--(隐式)--表锁

意向锁（Intention Locks）是为了优化表锁与行锁之间的协调而引入的机制，它允许数据库管理系统更高效地处理并发事务中的加锁请求。

在对表中某一行数据进行更新等操作时，表会对数据的这一行加上行锁，但是在此时加上表锁，表锁将会一行一行的排查，为了改进这种情况，引入了意向锁，在加上行锁时也会加上对应的意向锁，此时再加表锁时，意向锁会与表锁进行匹配判断是否兼容

简单理解是表中有行锁了，现在我们又想向这个表中添加表锁，但是在添加表锁之前往往会进行排查，因此引入了一个意向锁，添加的这个表锁会对表中的意向锁进行匹配，

（当一个事务打算对某些行加共享/排他锁时，InnoDB会在该表上自动加上意向共享/排他锁（相互绑定）。这是为了通知其他试图对该表加锁的事务，已经有行级别的共享锁存在或者即将存在，从而避免不必要的冲突检查）

如果一个事务打算对某些行加共享锁（S锁），那么在此之前，该事务会先在表上加上意向共享锁（IS锁）。这样做的目的是为了通知其他试图对整个表加锁的事务，已经有事务计划对表中的某些行进行读操作了。

6 记录锁-(Record)-S锁 X锁 -- 行锁

(Shared/Exclusive)  -俗称数据行的保镖

1 共享锁（Shared Locks）-并发读取

共享锁又称为读锁（S锁）它允许事务读取一行数据，但不允许修改或删改改行数据。

当一个事务对某行数据加上共享锁后，其他事务也可以对该行数据加上共享锁进行读取，但是不能加排他锁进行修改删除操作。这就意味着多个事务可以同时拥有一个资源的共享锁，从而实现并发读取数据而不会出现干扰。

select ... lock in share mode ;->对查询结果的行加上共享锁。

select ... for share ; -> 在MySQL 8.0 之后。

2 排他锁（Exclusive Locks）- 防止并发修改删除查询等

排他锁又称为写锁（X锁）（独占锁）他允许事务读取一行数据，还允许对其修改或者删除。

一旦某个事务获得了排他锁，其他的事务都不能再对该行数据加任何类型的锁，包括共享锁和排他锁，这样就确保了再任意时刻只有一个事务可以执行修改删除等操作，防止数据不一致的问题。

select ... for update ; -> 可以认为手动加上排他锁

insert ,update,delete等DML操作 -> 默认加上排他锁

7 如何理解select ... lock in share mode

执行这个语句时会施加两种类型的锁，一种是共享锁，另一种是MDL元数据共享锁

eg insert 语句会被默认加上排他锁以及MDL元数据共享锁

8 记录锁/元数据锁/意向锁

1 意向锁：协调表级锁与行级锁的关系，避免表级锁与行级锁发生冲突-伴随行锁出现

2 行级锁：保证事务对某一行或几行的独占访问（如防止脏读，不可重复读的出现）

3 元数据锁：保护表结构的一致性（如防止修改表结构时其他事务读取旧结构）-伴随SQL语句

9 如果意向排他锁由于第一行排他锁的出现而出现，但是此时我对第二行的数据执行update语句而出现排他锁会不会阻塞

1. InnoDB 的行级锁是精确到行的，事务 A 的锁仅作用于 id=1 的行。

2. 事务 B 的 UPDATE 需要获取 id=2 的行级 X 锁和表级 IX 锁，而 IX 锁是兼容的（允许多个事务同时持有 IX 锁）。

3. 两行数据互不冲突，因此并发操作正常。

1. 事务 A 的 SELECT ... FOR UPDATE 使用了范围查询（id BETWEEN 1 AND 10），InnoDB 会为符合条件的行加 X 锁，并添加间隙锁（Gap Lock），锁住 id=1~10 之间的间隙。

2. 事务 B 的 UPDATE 试图修改 id=2 的行，但该行已被事务 A 的间隙锁覆盖，因此被阻塞。

10 间隙锁（Gap）-行级锁

间隙锁锁的是记录之间的间隙，不包括记录本身，是为了那些不存在的索引记录加锁，

11邻键锁（Next-Key）-行级锁

间隙锁+行锁的组合，锁定对应区间的情况下，还会将当前记录也锁住

12 行锁之间的区分

对一个条件列进行等值匹配，如果该条件列存在，但是没有索引加的就是表锁，如果有索引加的就是记录锁。

如果对唯一列做等值查询该加锁的记录不存在的时候，那么此时使用的就是间隙锁。

如果对非唯一的普通索引做等值查询时，向右遍历的最后一个值，不满足查询条件时，则会退化为间隙锁。