MySQL Lock PartI|ZklMao-Space

这篇我们主要分享的是MySQL锁，我们需要回答下面几个问题？

MySQL 有哪些锁？
对表结构修改会加什么锁？
MyISAM 支持行级锁吗？
行级锁有那些？
InnoDB 怎么加行级锁的？
在线上增加索引会发生什么？
如何在线上安全加索引？
死锁发生条件有那些？
死锁怎么发生的？
怎么排查死锁？
怎么避免死锁？
MySQL 乐观锁和悲观锁

MySQL 有那些锁？

这里主要将InnoDB 中的锁(按颗粒度分类)：

库全局锁：用于数据库备份

表锁
- 独占读写表锁 : 这个给锁在会话中开启后，此会话就相当于和锁定的表绑定，只有绑定是指定的权限，不可以访问其他表、库，并排斥其他线程对绑定的表做任何操作，包括读！绑定的会话退出，锁释放
```
lock tables table_name_2 read/write, table_name_2 read/write ...;
```
- 意向锁 -- 意向锁存在意义是快速判断表中是否有记录被加锁。相当于一个行锁标记位, 意向锁也数据间隙锁的一种，官方说的....
  - S 共享意向锁，事务在对某行数据加S共享锁前会生成S 共享意向表锁
  - X 排他意向锁，事务在某行数据加X独占锁前会生成X 独占意向表锁
- MDL 锁: 表元数据锁。
- Lock_auto_inc 自增锁
  - Lock_auto_inc: 自增主键的具体实现。一中特殊的表锁，有三种配置模式：0 : 整条插入语句完成才释放，1: 主键自增后释放 2：二者折中，只有一条插入语句时，工作模式是0号模式，批量插入时1号模式
  - 在使用1号模式或者2号模式时，搭配binlog statement 日志格式主从模式，在做主从复制时会出现数据不一致的情况，情况见下：

偷个小林哥的图

会话A null 列从0 开始自增，到 insert into t values(null,4,4) 采用自增模式2或者1时，此时自增ID 值 id = 3，此时会话B 同步主库数据，直到当前自增主键，从三开始，B会话插入一条新数据，此时从库自增ID 分配 ID = 4 加入数据 *********** ---- > 前后ID不连续，数据不一致出现。

如何解决上面这个问题?

设置bing log format格式为 row 即可。

总结：当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。

行记录锁
- Record 记录锁 (按锁属性分类):
  - S (shared 共享) 记录锁
  - X 独占记录锁
- Gap 间隙锁
- next key lock （组合锁）= Record + Gap

对表结构修改会加什么锁？

对表做DDL (增删改类，增删改索引) 会加MDL(metadata lock) 独占写锁
对一张表进行 CRUD 操作时，加的是 MDL 共享读锁；

Tip: MDL 写锁优先级高于 MDL 共享读锁，并发的时候会每个并发事务都会生成一把MDL锁，按生成时间链接成一个锁链，MDL 写锁会阻塞队列后面所有写锁。只有等前方写独占锁事务完成释放MDL锁后面的事务才能继续进行

所以如果在大并发量的情况下贸然修改表结构会引发业务会有业务阻塞/停止的危险！

MyISAM 支持行级锁吗？

MyISAM 不支持行级锁，原因在于设计之初就决定使用非聚簇索引，加堆表的形式，各索引之间相互独立，想要实现行级锁成本巨大。

行级锁有那些？

Record 记录锁 (按锁属性分类):
- S (shared 共享) 记录锁
- X 独占记录锁
Gap 间隙锁
next key lock （组合锁）= Record + Gap

行级锁的特性

S：读读共享，读写互斥

X：独占，读写排斥，写写排斥

下面介绍行Record lock记录锁和 Gap 锁:

Gap 锁：Gap 锁可以同时在同以区间存在多把锁，锁的区间可以完全一样或者部分重叠。某个区间的锁

Gap 间隙锁存在的意义是：锁定一个没有记录空区间，防止在事务进行过程中，有新数据插入到锁定区间中，产生幻读。

Record 记录锁: 锁定一行数据

Record 记录存在的意义说，锁定这行记录，防止在当前事务进行时有其他事务进行数据篡改，导致不可重复读。

下面介绍next key lock 组合锁，引入下面事务：

begain;
select * from t_test where id > 3 and id <= 5  for update;

next key lock 是 record锁 + gap锁的组合锁，用来保证 RR 事务隔离特性,如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

使用X 排他记录锁定 5号记录，使用 gap 间隙锁锁定 (3,5] 的整数空闲区间，防止有新记录 id = 4 插入进来产生幻读.

InnoDB 怎么加行级锁的？

这篇主要讲规律，网上一大堆讲 next-key lock 怎么退化的，这里统统不讲，因为所谓的键合锁next-key lock 其实就是record lock + gap lock 的组合使用，是一个上层抽象概念，并不需要去绕这么多，可以说，锁怎么上的，是由事务隔离级别去驱动的，next-key lock、gap lock 都是再RR 下特有产物，目的就是保证在已有RC Record lock 的基础上加入区间锁定解决绝大部分幻读的情况，所以才引入了Gap lock。所以你和别人讲RR 下的加锁机制的时候可以直接口胡：这个，那个，内个，上的是next-key lock 键合锁！因为RR 也是一言不和就上 next-key 键合锁。但归根结底，上的都是Record/Gap 锁。无非就是组合着用了还是单独用了。毕竟，在查性能表的时候输出也没见着人家日志信息写next-key lock这个玩意的。人都是写 X、X_GAP、X_NOT_GAP 这么各个玩意。

吐槽完毕，书归正传，我们开始分享Record 锁、Gap 锁是怎么上的。我们这里按查询种类做总，再按索引是否由唯一约束（主键、唯一、普通）做分。

Tip1. 再分享前，我们先做区间查询规律总结：

RR下，区间查询会给区间内所有记录上记录锁

RR下，查询的区间如果相邻的两索引键之间(比如唯一索引设定的唯一约束键、主键查询的主键)不连续，则会在这个不连续区间中插入间隙锁，以保证事务途中其他事务无法插入数据，保证幻读不出现。

RR下，如果是等值查询，会锁住临界值，给临界值加记录锁(where id >= 2 [2,sup) id = 2会加记录锁)

特别的，RR下，普通索引(非唯一约束索引)会把全表符合条件的记录都上锁，在最左和最右记录之间的记录如果右间隙则加间隙锁，最左最右之外的记录也上锁，但是可以修改，若修改的值为查询条件内的值回阻塞，之外的值可以修改。

上面规律适用于唯一约束索引，普通索引

在全索引失效导致全表扫描时也适用，只不过区间变为 [表的第一个数据，表最后一个数据]

Tip2. 上是区间查询的总结，这里是等值区间查询的的总结（因为有唯一约束的索引等值查询不同于普通索引等值查询，为了保障幻读不发生普通索引要比等值查询多加一把间隙锁，所以也就有网上说的普通查询next-key锁不会退化...）:

【唯一约束索引】RR 下等值查询在扫面到匹配项后会对匹配项加记录锁，如果是扫描索引有唯一约束，则扫描会立即停止，并返回数据给服务层。(因为有唯一约束，索引不怕从重复插入。所以可以不加间隙锁)

【普通索引】RR 下引擎扫描到匹配项后会给匹配项加记录所，继续向下扫描，在扫到下个匹配值后上记录锁，再在二者之间间隙插入间隙锁，防止幻读。第一个匹配项前有间隙加间隙锁，最后一个匹配项后有间隙加间隙锁

我们来看一下环境:

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`,`b`),
  KEY `aa` (`c`),
  KEY `f` (`d`,`e`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4

准备数据如下:

等值查询 =

【唯一索引】

select * from a where a=5 for update

【普通索引】

select * from a where a=6 lock in share mode;

等值区间查询 >= 、<=

【有唯一约束索引】

select * from user where id >= 15 for update;

【普通索引】

select * from user where age >= 22  for update; # age NOT NULL

不等值区间查询 >、<

【有唯一约束索引】

select * from user where id > 15 for update; # primarykey(id)

事务1

begin;
select * from info where age > 21 for update; # age 普通索引

# do something ...
commit;

事务2

begin;
insert into info values(2,'magua','Pine Street',31,65);
insert into info values(4,'zhangsan','Pine Street',20,65);
insert into info values(7,'goudan','Pine Street',33,65);
insert into info values(9,'goudan','Pine Street',33,65);

update info set age = 52 where id= 1;
update info set age = 52 where id= 10;
update info set age = 22 where id= 15; #成功
update info set age = 31 where id= 15; #失败

# do something ...
commit;

为什么会加这么多锁(差不多别锁表了)？因为普通索引没有唯一约束，RR下只能把把这些记录和间隙都锁了，防止出现幻读🤡

在线上增加索引会发生什么？

FBI Warring: 这是各危险的行为,请务必认真效仿此操作。（后果自负

MySQL 5.5 只后引入了（metadata lock）MDL 锁。
- 在对表中数据CURD 的时候加MDL 读共享锁
- 修改表结构（加索引、增删改类名...）加MDL 写独占锁， MDL 写锁优先级比读锁高，并发下会阻塞写锁之后的其他事务上锁。
线上修改表结构会给表加MDL 写独占锁，回阻塞后面并发过来的事务，导致业务停滞。

如何在线上安全加索引？

有两种方案:

online-DDL
- MySQL 5.6 之后开始支持 Online DDL 用法见下

alter table table table_name add index index_name(列名1,列名2...), algorithem=inplace,lock=none; #添加索引

pt-osc

死锁发生条件有那些？

互斥、互相等待

死锁怎么发生的？

MySQL InnoDB 发生死锁的情况有很多，下面列举几个常见的场景，隔离级别可重复读:

同一区间加入同时被多把next-key lock 加锁，导致互相等待

这里偷个小林哥的图

此时的加锁情况是这样的:

事务A、B为了防止区间失效，所以给行记录 1009 加 X独占锁
事务A、B为了防止区间被其他事务插入数据导致幻读，所以给记录区间 (1005, 1009] 加间隙锁

S 共享锁读读兼容，读写互斥，索引不会引发互斥阻塞

X 独占锁读写互斥，写写互斥

行记录1009加的是X独占锁 1005 - 1009 区间加的是Gap 间隙锁

A、B两事务在向区间 1005 - 1009 插入数据时都碰到了对象加的X Gap 间隙锁，导致阻塞等待对方解锁，因为没有设置事务超时和死锁检测，所以两事务会一直阻塞等待对方解锁，一获取区间写入权限资源。

2.全表扫描导致锁表，全表扫描期间，其他事务无法进行写操作

还是偷小林哥的图

此时区间 (2，1009] 上锁情况:

因为要做全表扫描，所以区间(2,1009] 会被加next-key lock，区间中有的数据都会被加上行记录X独占锁，数据之间的间隙会加上间隙锁

事务B 会在生成一把插入意向锁，转台为等待中... (拿到资源，上锁后会变为activing 活跃中)

怎么排查死锁？

MySQL 通过查询系统数据库performance_schema 表data_locks 获得当前系统中所有活跃事务的加锁情况:

select * from performance_schema.data_locks;

主要观察一下几个主要字段:

OBJECT_NAME: 上锁的表

LOCK_MODE: 上锁模式
- X: next-key lock
- X, REC_NOT_GAP: X 独占锁
- X, GAP: GAP 间隙锁
LOCK_TYPE: 锁的颗粒度
- TABLE 表级锁
- RECORD 行锁
LOCK_DATA: 上锁的数据、区间
- 表中的某行的值：表中行数据主键 -- 锁住的是表中的一行数据
- suprenum pseudo-record: 间隙/区间 -- 锁住的是间隙

怎么避免死锁？

打破死锁任一成立条件即可:

设置事务超时时间
- 设置 innodb_lock_wait_timeout 参数，默认事件50s

开启死锁检测
- 设置 innodb_deadlock_detect 参数为ON

怎么排查死锁？

开启MySQL 数据库监控，MySQL 提供了 innodb_status_output/innodb_status_output_locks 系列开关来监控MySQL 运行状况:
innodb_status_output_locks : 开启事务锁监控
innodb_print_all_deadlocks: 死锁专门日志
innodb_status_output: 标准监控

使用方法见下：

set global innodb_status_output_locks/innodb_print_all_deadlocks/innodb_status_output = on; 开启
set global innodb_status_output_locks/innodb_print_all_deadlocks/innodb_status_output = off; 关闭

死锁日志分析

日志配置

set global innodb_status_output_locks = on;
set global innodb_print_all_deadlocks = on;
SET GLOBAL log_error_verbosity = 3;
SHOW GLOBAL VARIABLES LIKE 'innodb_status_%';

事务A

begin;
select * from info where id > 2 for update ;

# do something ...

commit;

事务B

begin;
select * from info where  id > 2 for update;

#do something ...

commit;

执行事务A

执行事务B

可以看到事务B被阻塞。

直到事务B超时自动终止

查看死锁日志

show engine innodb status;

一下是死锁日志日志输出，MySQL 版本8.0.40


=====================================
2024-12-18 15:28:13 13xxxxxxxxxxxxxxxx INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 23 seconds
-----------------
BACKGROUND THREAD
-----------------
。。。
----------
SEMAPHORES
----------
。。。
------------
TRANSACTIONS
------------
Trx id counter 18484
Purge done for trx's n:o < 18466 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
。。。

---TRANSACTION 18483, ACTIVE 5 sec starting index read  #事务B
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 12, OS thread handle 139685983655488, query id 1222 192.168.1.1 admin executing
/* ApplicationName=DataGrip 2023.3.4 */ select * from info where  id > 2 for update
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 4 n bits 80 index PRIMARY of table `MySQLTest`.`info` trx id 18483 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000004292; asc     B ;;
 2: len 7; hex 820000009e012a; asc       *;;
 3: len 5; hex 73616e6469; asc sandi;;
 4: len 11; hex 4d61706c65205374726565; asc Maple Stree;;
 5: len 1; hex 20; asc  ;;
 6: len 1; hex 3f; asc ?;;

------------------
TABLE LOCK table `MySQLTest`.`info` trx id 18483 lock mode IX
RECORD LOCKS space id 12 page no 4 n bits 80 index PRIMARY of table `MySQLTest`.`info` trx id 18483 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000004292; asc     B ;;
 2: len 7; hex 820000009e012a; asc       *;;
 3: len 5; hex 73616e6469; asc sandi;;
 4: len 11; hex 4d61706c65205374726565; asc Maple Stree;;
 5: len 1; hex 20; asc  ;;
 6: len 1; hex 3f; asc ?;;


---TRANSACTION 18482, ACTIVE 14 sec  # 事务A
2 lock struct(s), heap size 1128, 6 row lock(s)
MySQL thread id 9, OS thread handle 139686497502784, query id 1232 192.168.1.1 admin
TABLE LOCK table `MySQLTest`.`info` trx id 18482 lock mode IX
RECORD LOCKS space id 12 page no 4 n bits 80 index PRIMARY of table `MySQLTest`.`info` trx id 18482 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000004292; asc     B ;;
 2: len 7; hex 820000009e012a; asc       *;;
 3: len 5; hex 73616e6469; asc sandi;;
 4: len 11; hex 4d61706c65205374726565; asc Maple Stree;;
 5: len 1; hex 20; asc  ;;
 6: len 1; hex 3f; asc ?;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 000000004292; asc     B ;;
 2: len 7; hex 820000009e0137; asc       7;;
 3: len 3; hex 73756e; asc sun;;
 4: len 10; hex 4f616b20537472656574; asc Oak Street;;
 5: len 1; hex 24; asc $;;
 6: len 1; hex 52; asc R;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000004292; asc     B ;;
 2: len 7; hex 820000009e0144; asc       D;;
 3: len 4; hex 73616e6d; asc sanm;;
 4: len 11; hex 50696e6520537472656574; asc Pine Street;;
 5: len 1; hex 14; asc  ;;
 6: len 1; hex 41; asc A;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 00000000481c; asc     H ;;
 2: len 7; hex 010000010c0727; asc       ';;
 3: len 4; hex 6f6d6172; asc omar;;
 4: len 10; hex 456c6d20537472656574; asc Elm Street;;
 5: len 1; hex 20; asc  ;;
 6: len 1; hex 4c; asc L;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 6; hex 00000000480f; asc     H ;;
 2: len 7; hex 82000001050110; asc        ;;
 3: len 5; hex 6a6f6e6875; asc jonhu;;
 4: len 10; hex 4d61696e205374726565; asc Main Stree;;
 5: len 1; hex 15; asc  ;;
 6: len 1; hex 3c; asc <;;

--------
FILE I/O
--------
。。。
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
。。。
---
LOG
---
。。。
----------------------
BUFFER POOL AND MEMORY
----------------------
。。。
--------------
ROW OPERATIONS
--------------
。。。
----------------------------
END OF INNODB MONITOR OUTPUT
============================

日志分析(一下是死锁日志的节选)：

---TRANSACTION 18483, ACTIVE 5 sec starting index read   #事务B 
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 12, OS thread handle 139685983655488, query id 1222 192.168.1.1 admin executing    # 会话地址 用户名
/* ApplicationName=DataGrip 2023.3.4 */ select * from info where  id > 2 for update    # 阻塞的事务语句
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:                        # 已阻塞等待时间 一定阻塞了 5 sec
RECORD LOCKS space id 12 page no 4 n bits 80 index PRIMARY of table `MySQLTest`.`info` trx id 18483 lock_mode X waiting   #等待主索引上的X独占锁释放
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000004292; asc     B ;;
 2: len 7; hex 820000009e012a; asc       *;;
 3: len 5; hex 73616e6469; asc sandi;;
 4: len 11; hex 4d61706c65205374726565; asc Maple Stree;;
 5: len 1; hex 20; asc  ;;
 6: len 1; hex 3f; asc ?;;