Mysql之锁机制

2021 年 3 月 30 日
筆記
MySQL, 搞懂Mysql

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法FTWRL

Flush tables with read lock

全局锁的典型使用场景是，做全库逻辑备份，也就是把整库每个表都 select 出来存成文本。在备份过程中整个库完全处于只读状态，存在以下问题：

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟

可使用官方自带的逻辑备份工具mysqldump，配合参数–single-transaction，导数据之前启动一个事务，来确保拿到一致性视图。由于 MVCC 的支持，这个过程中数据是可以正常更新的。但需要注意的是：single-transaction 方法只适用于所有的表使用事务引擎的库(InnoDB )

表级锁

表锁

-- 给指定表加上表级读锁或写锁
lock tables … read/write

-- 查看表锁定情况
-- In_use：表上锁及请求锁的数量(表锁时其他会话写请求堵塞)
-- Name_locked：表名是否被锁定，用于删除表和表重命名
show open tables where in_use >=1;
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | t     |      1 |           0 |

-- 释放被当前会话持有的任何锁
unlock tables

元数据锁

MDL（metadata lock)，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

上图中如果session A事务未及时提交，就会一直占用MDL锁，session C中MDL写锁堵塞，后续的读请求因为MDL读锁堵塞，造成整个表不可读写。如果刚好是一张热点表，就有可能造成数据库线程爆满，从而整个库不可用。因此，对于长事务或者热点表的结构调整要慎重。

意向锁

意向锁是一种不与行级锁冲突的表级锁，分为两种：
- 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）
```
-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;
```
- 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）
```
-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;
```
意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在数据表对应意向锁。

其存在的意义在于：对同一张表加表锁时，只需要检测是否存在意向排他锁即可，不用检测表中行上的排他锁存在。

意向共享锁（IS）意向排他锁（IX）

共享锁（S）兼容互斥

排他锁（X）互斥互斥

注意：这里的排他 / 共享锁指的都是表锁！！！意向锁不会与行级的共享 / 排他锁互斥！！！意向锁之间是互相兼容的！！！

	意向共享锁（IS）	意向排他锁（IX）
共享锁（S）	兼容	互斥
排他锁（X）	互斥	互斥

行锁

行锁又称记录锁，记为LOCK_REC_NOT_GAP

-- 加共享锁(Shared Locks：S锁)
select…lock in share mode

-- 加排他锁(Exclusive Locks：X锁)
select…for update

两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。因此，如果事务中涉及多个行锁，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

在读已提交隔离级别下有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。也就是说，读提交隔离级别下，锁的范围更小，锁的时间更短，这也是不少业务都默认使用读已提交隔离级别的原因。

死锁检测

直接进入等待，直到超时，可以通过参数 innodb_lock_wait_timeout 来设置，默认50s
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑

由于第一种策略，时间无法预知，太短可能误伤。正常情况下采用第二种策略，即主动死锁检测，但又会消耗CPU资源。对于热点行的更新可能导致性能问题，解决思路：

对于不会出现死锁的业务，可以关掉死锁检测，存在风险
控制并发度
- 客户端并发控制，但需要考虑分布式问题
- 数据库端并发控制：数据库中间件实现或者修改Mysql源码(大神玩家)
业务设计上拆分，单行数据拆分为多行，减小并发度，例如：1一个账户拆分为多个子账户

思考题

如果删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。

哪一种方法更好？为什么？

长事务、锁冲突

next-key lock

next-key lock由间隙锁(Gap Lock)和行锁组成，每个 next-key lock 是前开后闭区间，解决了幻读的问题。锁类型记为：LOCK_ORDINARY

间隙锁之间不存在冲突关系，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。

举例说明

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

session A	session B
begin;
select * from t where id=9 for update;
	begin;
	select * from t where id=9 for update;
	insert into t values(9,9,9);
insert into t values(9,9,9);

上述语句执行结果如何？出现了死锁，为什么呢？

session A、session B中select for update由于id=9不存在，均加上了(5,10)的间隙锁，这也证明了间隙锁之间不存在冲突。接下来A、B都向这个间隙里插入数据，互相和对方持有的间隙锁冲突，相互等待形成死锁。如果开启了死锁检测，InnoDB会马上发现死锁关系，让A中插入报错返回。

从以上例子也可以看出，由于间隙锁的引入，虽然解决了幻读，可也影响了数据库的并发度。如果实际业务场景不需要保证可重复读，就可以考虑使用读已提交，同时binlog_format=row，保证主从同步的一致性。

加锁规则：两个原则、两个优化、一个bug

原则 1：加锁的基本单位是 next-key lock，前开后闭区间
原则 2：查找过程中访问到的对象才会加锁
优化 1：索引上的等值查询，给唯一索引加锁的时候，匹配上数据，next-key lock 退化为行锁
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

以上规则，其实可以理解为数据查找过程中，扫描到的对象应该加锁，排除逻辑上明显不需要加锁的对象，即为加锁范围

重点：

加锁是分步进行的，例如：c>=10 and c<=11，分解为c=10、c>10 and c<11、c=11依次进行锁申请
间隙由右边的间隙记录，这也导致了不同方向上扫描加锁范围不一样
从扫描到的第一个记录上作为起点，例如：普通索引c取值为[0,5,10,15,20]，c>10和c>=10其分别第一个扫描到的数为15、10，因此第一个间隙锁为(10,15]、(5,10]

读已提交下的应用

在外键场景下有间隙锁，场景待确认

insert intention lock

插入意向锁，仅用于insert语句，表明将在某间隙插入记录，与间隙锁互斥关系如下：

	X,GAP	S,GAP	intention-insert
X,GAP	兼容	兼容	互斥
S,GAP	兼容	兼容	互斥
intention-insert	兼容	兼容	唯一键冲突可能互斥

间隙锁之间不存在互斥关系（X、S表示是什么语句导致的间隙锁）
间隙锁可以堵塞区间内的插入意向锁，但插入意向锁不会堵塞后续的间隙锁
唯一键冲突，如果是主键加记录锁，如果是唯一索引加next-key lock

插入意向锁实验验证

mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;

mysql> insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

-- 开启事务A
mysql> begin;

/**
在事务A中执行修改语句，id=7不存在，添加(5,10)的间隙锁，LOCK_MODE=X,GAP LOCK_DATA=10可以验证两个观点：
1、间隙锁是加在右边间隙上的
2、此处X并不代表10上加行锁，仅代表什么语句造成的，若改为select * from t where id=7 lock in share mode，LOCK_MODE就变为S,GAP
**/
mysql> update t set d=d+1 where id=7;

-- 在事务B中插入id=6的数据，需要申请插入意向锁，进入堵塞状态
mysql> insert into t values(6,6,6);

/**
事务A中已经添加了间隙锁，相同间隙的插入意向锁堵塞，LOCK_MODE=X,GAP,INSERT_INTENTION，LOCK_STATUS=WAITING
v8.0.11时，LOCK_MODE=X,GAP，INSERT_INTENTION标识是高版本新加的(此处使用的是8.0.21)，插入意向锁是一种特殊的间隙锁
**/
mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+-----------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE              | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+
|        54 | demo          | t           | NULL       | TABLE     | IX                     | GRANTED     | NULL      |
|        54 | demo          | t           | PRIMARY    | RECORD    | X,GAP                  | GRANTED     | 10        |
|        53 | demo          | t           | NULL       | TABLE     | IX                     | GRANTED     | NULL      |
|        53 | demo          | t           | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | WAITING     | 10        |
+-----------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+

-- 开启事务C
mysql> begin;

-- 在事务C中插入id=16的数据，由于该间隙上没有间隙锁，申请插入意向锁成功
mysql> insert into t values(16,16,16);

/** 
查询当前加锁情况，并没有发现插入意向锁，为什么？
插入意向锁是为了配合间隙锁解决幻读问题，在有间隙锁的情况下进行堵塞。此时没有间隙锁，不需要堵塞，所以就不用加插入意向锁吗？
但其他事务中相同行插入会产生冲突，说明这里还是有其他约束的，只是不用堵塞的插入意向锁转换成另外一种约束了
**/
mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
|        53 | demo          | t           | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
/**
证明其他约束的存在，新启一个事务，同样执行insert into t values(16,16,16)，可以看到申请S锁堵塞，正常上一个事务中的插入有其他约束
这里需要进行唯一约束验证，获取id=16的读锁
**/
mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
|        53 | demo          | t           | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|        56 | demo          | t           | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|        56 | demo          | t           | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 16        |
|        56 | demo          | t           | PRIMARY    | RECORD    | S,REC_NOT_GAP | WAITING     | 16        |
+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+


-- 开启事务D
mysql> begin;

-- 在事务D中插入id=10，
mysql> insert into t values(10,10,10);
1062 - Duplicate entry '10' for key 'PRIMARY'

-- 在事务E中插入id=9
mysql> insert into t values(9,9,9); 
(blocked)

/**V8.0.11
查看当前加锁情况，事务D插入语句检测到唯一冲突后在id=10上加了一个S锁
事务E中插入id=9，等待插入意向锁，没有间隙锁冲突，为什么会堵塞呢？
唯一键冲突加的应该不是一个记录S锁，应该是一个next-key lock (5,10]，因为已经存在间隙锁，所以插入意向锁才会堵塞
这是MySQL的一个bug，在V8.0.16已经修复，事务E中插入不会堵塞（主键唯一冲突就是一个单纯的记录锁）
//bugs.mysql.com/bug.php?id=93806
**/
mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t           | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       109 | demo          | t           | PRIMARY    | RECORD    | S         | GRANTED     | 10        |
| INNODB |       108 | demo          | t           | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t           | PRIMARY    | RECORD    | X,GAP     | WAITING     | 10        |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

加锁检测

等MDL锁

-- 事务A
lock table test_data write;

-- 由于事务A加了表锁，事务B堵塞
select * from test_data;

等flush

-- 关闭表t
flush tables t with read lock; 
-- 关闭所有打开的表
flush tables with read lock;

-- 事务A
select sleep(1) from t;

-- 事务B：事务A中表t已打开，需要等待其结束
flush tables t;

-- 事务C：等待事务B中flush结束
select * from t where id=1;

等行锁

-- 事务C
begin
update t set a=1 where id=1;

-- 由于事务C行锁未提交，事务D相同行被堵塞
update t set a=1 where id=1;

锁及堵塞查询

-- 查看表阻塞的process id(MySQL启动时需要设置performance_schema=on，相比于设置为off会有10%左右的性能损失)
select blocking_pid from sys.schema_table_lock_waits;

-- 查看行锁等待情况
select * from sys.innodb_lock_waits;

-- MySQL5.7及之前查看事务锁情况
select * from performance_schema.innodb_locks;
-- MySQL8.0及之后查看事务锁情况
select * from performance_schema.data_locks;
-- 查看元数据加锁情况
select * from performance_schema.metadata_locks;

-- 查看当前进程及状态
show processlist;

-- 查看innodb引擎状态，可以获取一些关键信息点，例如：最近事务及加锁情况，对分析定位问题有帮助
show engine innodb status;

Tags: MySQL 搞懂Mysql