【MySQL（锁篇）】深入MySQL锁机制：从全局到行级，解锁数据库性能瓶颈（上：MySQL锁介绍、表级锁、行级锁）

标签：mylock 锁篇行级 -- t1 simple 意向锁 MySQL id

文章目录

MySQL（锁篇）- 全局锁、表锁、行锁（记录锁、间隙锁、临键锁、插入意向锁）、意向锁、SQL加锁分析、死锁产生原因与排查
- 一条Update语句
- MySQL锁介绍
- - 1 锁分类
  - 2 全局锁
- 表级锁
- - 1 什么是表级锁？
  - 2 表读锁、写锁
  - - 2.1 表锁相关命令
    - 2.2 表锁演示
  - 3 元数据锁
  - - 3.1 元数据锁介绍
    - 3.2 元数据锁演示
  - 4 自增锁（AUTO-INC LOCK）
- 行级锁【重点！！！】
- - 1 什么是行级锁？
  - 2 行锁四兄弟：记录、间隙、临键、插入意向
  - - 2.1 记录锁
    - 2.2 间隙锁
    - 2.3 临键锁
    - 2.4 插入意向锁
  - 3 加锁规则【非常重要】
  - 4 意向锁
  - - 4.1 什么是意向锁？
    - 4.2 作用
    - 4.3 意向锁和读锁、写锁的兼容关系
  - 5 锁相关的参数

MySQL（锁篇）- 全局锁、表锁、行锁（记录锁、间隙锁、临键锁、插入意向锁）、意向锁、SQL加锁分析、死锁产生原因与排查

一条Update语句

update tab_user set name='曹操' where id=1;

执行流程：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TgkwF3LS-1722312270345)(https://i-blog.csdnimg.cn/direct/8ce85c5a161540c6a603fda1f266c3dd.png)]

MySQL锁介绍

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改一部分数据的时候，MySQL会通过锁定防止其他用户读取同一条数据。
在处理并发读或者写的时候，通过实现一个由两种类型的锁组成的锁系统来解决问题。两种锁通常被称为共享锁（shared lock）和排它锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。

读锁是共享的，是互相不阻塞的。多个客户端在同一时刻可以同时读取同一个资源，而不互相干扰。写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样才能确保在给定的时间内，只有一个用户能执行写入，并防止其他用户读取正在写入的同一个资源。

1 锁分类

按照颗粒度分：

全局锁：锁整个database，由MySQL的SQL Layer层（核心服务层）实现。
表级锁：锁某个table，由MySQL的SQL Layer层实现。
行级锁：锁某Row的索引，也可锁定行索引之间的间隙，由存储引擎实现【InnoDB】

[外链图片转存中…(img-80Wvbigd-1722312270347)]
按锁功能分

共享锁Shared Lock（S锁，也叫做读锁）
- 加了读锁的记录，允许其他事务再加读锁
- 加锁方式：select … lock in share mode
排它锁Exclusive Lock（X锁，也叫写锁）
- 加了写锁的记录，不允许其他事务再加读锁或者写锁
- 加锁方式：select … for update

2 全局锁

全局锁是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续DML的写语句，DDL语句，已经更新操作的事务提交语句都会被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

加全局锁的命令为：
flush tables with read lock;

释放全局锁的命令为：
unlock tables;
或者断开加锁session的连接，自动释放全局锁。

说到全局备份的事情，还是很危险的。因为如果主库加上全局锁，则整个数据库将不能写入，备份期间影响业务运行，如果在从库上加全局锁，则会导致不能执行主库同步过来的操作，造成主从延迟。

对于innoDb这种支持事务的存储引擎，使用mysqldump备份时可以使用-single-transction参数，利用mvcc提供一致性视图，而不是用全局锁，不影响业务的正常运行。而对于有MyISAM这种不支持事务的表，就只能通过全局锁获得一致性视图，对应的mysqldump参数为-lock-all-tables;

举个例子：

# 提交请求锁定所有数据库的所有表，以保障数据的一致性，全局锁【LBCC】
mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables > /root/db.sql

# 一致性视图【MVCC】
mysqldump -uroot -p --host=localhost --all-databases --single-transaction > /root/db.sql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UAmoKhjG-1722312270349)(https://i-blog.csdnimg.cn/direct/c104b08a6dde41eebd7687008bc0a9ea.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MxypHkfD-1722312270349)(https://i-blog.csdnimg.cn/direct/637018466783470287ce497fe5aef1cc.png)]

表级锁

1 什么是表级锁？

表读锁（Table Read Lock）：阻塞对当前表的写，但不阻塞读
表写锁（Table Write Lock）：阻塞对当前表的读和写
元数据锁（meta data lock, MDL）：不需要显式指定，在访问表时会被自动加上，作用保证读写的正确性
- 当对表做增删改查操作时加元数据读锁
- 当对表做结构变更操作的时候加元数据写锁
自增锁（ATUO-INC Lock）：一种特殊的表级锁，自增列事务性插入操作时产生

2 表读锁、写锁

2.1 表锁相关命令

Mysql实现的表级锁定的争用状态变量；

# 查看表锁定状态
mysql > show status like 'table%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vYM5I7D3-1722312270350)(https://i-blog.csdnimg.cn/direct/a0a11c310e3c43ecb1f8fb635e000e3d.png)]

table_locks_immediate: 产生表级锁定的次数；
table_locks_waited: 出现表级锁定争用而发生等待的次数;

表锁有两种表现形式：

表读锁（Table Read Lock）
表写锁（Table Write Lock）

手动添加表锁：

lock table 表名称 read(write), 表名称2 read(write), 其他；
# 举例
lock table t read; #为表t加读锁
lock table t weite; #为表t加写锁

查看表锁情况：

show open tables;

删除表锁：

unlock tables;

2.2 表锁演示

环境准备

CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
);

INSERT INTO mylock (id,NAME) VALUES (1, 'a');
INSERT INTO mylock (id,NAME) VALUES (2, 'b');
INSERT INTO mylock (id,NAME) VALUES (3, 'c');
INSERT INTO mylock (id,NAME) VALUES (4, 'd');

读锁演示：mylock表加read锁【读阻塞写】

时间	session01	session02
T1	连接数据库
T2	获得mylock的Read Lock锁定：lock table mylock read;	连接数据库
T3	当前session可以查询该表记录：select * from mylock;	其他session也可以查询该表记录： select * from mylock;
T4	当前session不能查询其他没有锁定表的记录：select * from t;	其他session可以查询或更新未锁定的表： update t set c = ‘张飞’ where id =1;
T5	当前session插入或更新锁定的表会提示错误：insert into mylock(name) values(‘e’);	其他session插入或更新锁定的表会一直等待获取锁： insert into mylock(name) values(‘e’);
T6	释放锁：unlock tables;	插入成功

-- Session01
# 获得表mylock的Read Lock锁定：
lock table mylock read;
# 当前Session可以查询该表记录：
select * from mylock;
# 当前Session不能查询其他没有锁定的表：
select * from t;
# 当前Session插入或更新锁定的表会提示错误：
insert into mylock (name) values('e');
# 释放锁：
unlock tables;


-- Session02
# 其他Session也可以查询该表的记录：
select * from mylock;
# 其他Session可以查询或更新未锁定的表：
update t set name='张飞' where id=1;
# 其他Session插入或更新锁定表会一直等待获取锁：
insert into mylock (name) values('e');

写锁演示：mylock表加write锁【写阻塞读】

时间	session01	session02
T1	连接数据库	待session01开启锁之后，session02再获取连接
T2	获得mylock的Write Lock锁定：lock table mylock write;
T3	当前session对锁定表的查询+更新+插入操作都可以执行：select * from mylock where id =1; insert into mylock(name) values(‘e’);	连接数据库
T4		其他session对锁定的表查询被阻塞，需要等待锁释放：select * from mylock where id=1;
T5	释放锁：unlock tables;	获得锁，返回查询结果

-- Session01
# 获得表mylock的write锁：
lock table mylock write;
# 当前session对锁定表的查询+更新+插入操作都可以执行：
select * from mylock where id=1;
insert into mylock (name) values('e');
# 释放锁：
unlock tables;
-- Session02
# 注意：待session1开启锁后，session2再获取连接
# 其他session对锁定表的查询被阻塞，需要等待锁被释放
select * from mylock where id=1;
# 获得锁，返回查询结果：

查询操作在客户端可以正常查询，navcat会阻塞
[外链图片转存中…(img-nh2nFUMA-1722312270350)]

3 元数据锁

3.1 元数据锁介绍

元数据锁不需要显式指定，在访问一个表的时候会被自动加上，锁的作用是保证读写的正确定。

可以想像一下：如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表的表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，Mysql5.5版本中引入了元数据锁，当一个表做增删改查的时候，加元数据读锁；当要对表结构做变更操作的时候，加元数据写锁。

读锁是共享的，是互相不阻塞的：因此你可以有多个线程同时对一张表加读锁，保证数据在读取的时候不会被其他线程修改。
写锁则是排他的：也就是说一个写锁会阻塞其他的写锁和读锁，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

3.2 元数据锁演示

时间	session01	session02
T1	开始事务：begin
T2	加元数据读锁 select * from mylock;	修改表结构：alter table mylock add f int;
T3	提交/回滚事务：commit/rollback释放锁
T4		获取锁，修改完成

-- Session01
# 开启事务：
begin;
# 加元数据读锁：
select * from mylock;
# 提交/回滚事务：
commit;
# 释放锁

-- Session02
# 修改表结构：
alter table mylock add f int;
# 获取锁，修改完成

4 自增锁（AUTO-INC LOCK）

自增锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

行级锁【重点！！！】

1 什么是行级锁？

Mysql的行级锁，是由存储引擎来实现的，这里我们主要讲解innoDb的行级锁。InnoDb行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点：

只有通过索引条件检索的数据，Inn oDb才能使用行级锁，否则，InnoDB将使用表锁！

InnoDB的行级锁，按照锁定范围来说，分为4种：
- 记录锁（Record Locks）：锁定索引中的一条记录
- 间隙锁（Gap Locks）：要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
- 临键锁（Next-Key Locks）：是索引记录上的记录锁和在索引记录之间的间隙锁的组合（间隙锁+记录锁）
- 插入意向锁（Insert Intention Locks）：做insert操作时添加的对记录ID的锁
InnoDB的行级锁，按照功能来说，分为两种：
- 读锁：允许一个事务去读一行，阻止其他事务更新目标行数据。同时阻止其他事务加写锁，但不允许其他事务加读锁。
- 写锁：允许获得排他锁的事务更新数据，阻止其他事务获取或修改数据。同时阻止其他事务加读锁和写锁。

如果加行级锁？

对于Update、delete、insert语句InnoDB会自动给涉及数据集加写锁
对于普通的select语句，InnoDb不会加任何锁
事务可以通过以下语句手动给记录集加读锁或写锁

案例：

CREATE TABLE `t1_simple` (
`id` int(11) NOT NULL,
`pubtime` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_pu`(`pubtime`) USING BTREE
) ENGINE = InnoDB;

INSERT INTO `t1_simple` VALUES (1, 10);
INSERT INTO `t1_simple` VALUES (4, 3);
INSERT INTO `t1_simple` VALUES (6, 100);
INSERT INTO `t1_simple` VALUES (8, 5);
INSERT INTO `t1_simple` VALUES (10, 1);
INSERT INTO `t1_simple` VALUES (100, 20);

添加读锁

select * from t1_simple WHERE id = 1 lock in share mode;

添加写锁

select * from t1_simple WHERE id = 1 for update;

2 行锁四兄弟：记录、间隙、临键、插入意向

2.1 记录锁

记录锁仅仅锁住索引记录的一行，在单条索引记录上加锁。记录锁锁住的永远是索引，而非数据本身，即使该表上没有任何显式索引，那么innodb会在后台创建一个隐藏的聚簇索引，那么锁住的就是这个隐藏的聚簇索引。
举个例子：

sql 代码解读复制代码-- 加记录读锁
select * from t1_simple where id = 1 lock in share mode;
-- 加记录写锁
select * from t1_simple where id = 1 for update;
-- 新增，修改，删除加记录写锁
insert into t1_simple values (1, 22);
update t1_simple set pubtime=33 where id =1;
delete from t1_simple where id =1;

2.2 间隙锁

间隙锁（Gap Locks），仅仅锁住一个索引区间（开区间，不包含双端端点）
在索引记录之间的间隙中加锁，或者是在某个索引记录之前或者之后加锁，并不包含该索引记录本身。
间隙锁可用于防止幻读，保证索引间隙不会被插入数据
在可重复读（REPETABLE READ）这个隔离级别下生效。

主键ID索引的行锁区间划分图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VkREnkXa-1722312270351)(https://i-blog.csdnimg.cn/direct/4d0a1699b4db4209b07194fbc6296752.png)]
session01执行：

begin;
select * from t1_simple where id > 4 for update; -- 加间隙锁
-- 间隙锁区间(4,100+)
commit;

session02执行：

begin;
insert into t1_simple values (7,100); -- 阻塞
insert into t1_simple values (3,100); -- 成功
commit;

2.3 临键锁

临键锁（Next—Key Locks）相当于记录锁+间隙锁【左开右闭区间】，例如(5,8]
默认情况下，innodb使用临键锁来锁定记录。但是在不同场景中会退化
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2kStzNNP-1722312270352)(https://i-blog.csdnimg.cn/direct/14533b07ec734e6b931dcbdced820883.png)]默- 默认情况下，InnoDB使用临键锁来锁定记录，但会在不同场景中退化

场景01-唯一性字段等值（=）且记录存在，退化为记录锁
场景02-唯一性字段等值（=）且记录不存在，退化为间隙锁
场景03-唯一性字段范围（< >），还是临键锁
场景04-非唯一性字段，默认是临键锁

session1执行：

begin;
select * from t1_simple where pubtime = 20 for update;
-- 临键锁区间(10,20],(20,100]
commit;

session2执行：

begin;
insert into t1_simple values (16, 19); -- 阻塞
select * from t1_simple where pubtime = 20 for update; -- 阻塞
insert into t1_simple values (16, 50); -- 阻塞
insert into t1_simple values (16, 101); -- 成功
commit;

2.4 插入意向锁

间隙锁可以帮助我们在一定程度上解决幻读的问题，但是间隙锁就是最佳的解决思路了吗，还有没有优化空间？
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7TlmMYCl-1722312270352)(https://i-blog.csdnimg.cn/direct/8837b41f872d43e49e9315ad9dc4d590.png)]

insert into t1_simple values (60, 200); -- 阻塞
insert into t1_simple values (70, 300); -- 阻塞

按照之前关于间隙锁的知识分析，此时间隙锁的范围是(11,99)，意思是这个范围的id都不可以插入。如果是这样的话，数据插入效率太低，
锁范围比较大，很容易发生锁冲突怎么办？

插入意向锁就是解决这个问题的。

什么是插入意向锁？

插入意向锁是一种在INSERT操作之前设置的一种特殊的间隙锁。
插入意向锁表示了一种插入意图，即当多个不同的事务，同时往同一个索引的同一个间隙插入数据的时候，他们互相之间无需等待，即不会阻塞。
插入意向锁不会阻止插入意向锁，但是插入意向锁会阻止其他间隙写锁（排他锁）、记录锁

session01执行

begin;
insert into t1_simple values (60, 200);
-- 插入意向锁区间(10,100)
commit;
begin;
select * from t1_simple where id > 10 for update;
-- 临键锁（区间）写锁区间(10,100+)
commit;

session02执行

begin;
insert into t1_simple values (70, 300); -- 没有发生阻塞
-- 插入意向锁区间(10,100)
commit;
-- 说明两个插入意向锁之间是兼容的，可以共存!
begin;
insert into t1_simple values (90, 300); -- 被阻塞，阻塞的原因在于，插入意向锁和其他写锁之间是互斥的！
commit;

趁着阻塞，在新会话中，通过 show engine innodb status\G 指令，可以看到加锁日志信息，重点看 TRANSACTION：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zhVVbFTl-1722312270353)(https://i-blog.csdnimg.cn/direct/e69d658ce6814e46a58cf9022f6b272a.png)]
在输出的内容中，框选中的地方，清楚的表明了插入意向锁（insert intention）的存在。

3 加锁规则【非常重要】

主键索引

等值条件：
- 命中：加记录锁
- 未命中，加间隙锁
范围条件：
- 命中，包含where条件的临键区间，加临键锁
- 未命中，加间隙锁

辅助索引

等值条件：
- 命中：命中记录的辅助索引项，回表主键索引项加记录锁，辅助索引项两侧加间隙锁
- 未命中：加间隙锁
范围条件
- 命中：包含where条件的临键区间加临键锁。命中记录回表主键索引项加记录锁
- 未命中：加间隙锁

4 意向锁

4.1 什么是意向锁？

相当于存储引擎级别的表锁。

InnoDB也实现了表锁，也就是意向锁。意向锁是Mysql内部使用的，不需要用户干预。意向锁和行锁可以共存，意向锁的主要作用是为了全表更新数据时提升性能。否则在全表更新数据的时候，需要先检索该改为是否某些记录上有行锁，那么将是一件非常繁琐且耗时的操作。

举个例子：
事务A修改user表的记录r, 会给记录r上一把行级的写锁，同时会给user表上一把意向写锁（IX），这时事务B要给user表上一个表级的写锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存，且满足事务隔离性的要求。
当我们需要加一个写锁时，需要根据意向锁区判断表里有没有数据行被锁定；

如果行锁，则需要遍历每一行去确认。
如果表锁，则只需要判断一次即可知道没有有数据行被锁定，提升性能。

4.2 作用

表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”
意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁和行锁）的锁共存

4.3 意向锁和读锁、写锁的兼容关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UKOZXA9v-1722312270354)(https://i-blog.csdnimg.cn/direct/8f1970f7c6dd46e2b337594a57a3576e.png)]

意向锁相互兼容：因为IX、IS只是表明申请更低层次级别元素（page、记录）的X、S操作。
表级S锁和X、IX锁不兼容：因为上了表级S锁后，不允许其他事务再加X锁。
表级X锁和IS、IX、S、X不兼容：因为上了表级X锁之后，会修改数据。

注意：上了行级写锁后，行级写锁不会因为有别的事务上了意向写锁而阻塞，一个Mysql是允许多个行级写锁同时存在的，只要他们不是针对相同的数据。

5 锁相关的参数

InnoDB所使用的行级锁定争用状态查看：

show status like 'innodb_row_lock%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0pTs1gZv-1722312270354)(https://i-blog.csdnimg.cn/direct/a6421cb466794784a94052392ab8bf74.png)]

Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg：每次等待所花费的平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花费的时间
Innodb_row_lock_waits：系统启动到现在总共等待的次数

比较重要的是：

Innodb_row_lock_time_avg（等待平均时长）
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手执行优化计划。

查看事务、锁的sql:

# 查看锁的SQL
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;

# 查看事务SQL
select * from information_schema.innodb_trx;

# 查看未关闭的事务详情
SELECT
a.trx_id,a.trx_state,a.trx_started,a.trx_query,
b.ID,b.USER,b.DB,b.COMMAND,b.TIME,b.STATE,b.INFO,
c.PROCESSLIST_USER,c.PROCESSLIST_HOST,c.PROCESSLIST_DB,d.SQL_TEXT
FROM
information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
AND b.COMMAND = 'Sleep'
LEFT JOIN performance_schema.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current d ON d.THREAD_ID =
c.THREAD_ID;

标签：mylock,锁篇,行级,--,t1,simple,意向锁,MySQL,id
From： https://blog.csdn.net/weixin_68020300/article/details/140769220