Kevin's blog
kevin
2025-10-30
目录

Mysql锁详解

# 锁详解

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

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

# 锁分类

  • 从性能上分为乐观锁(用版本对比来实现)和悲观锁

  • 从对数据操作的粒度分，分为表锁和行锁

  • 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)，还有意向锁

    • 读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响，

      比如：select * from T where id=1 lock in share mode

    • 写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁，数据修改操作都会加写锁，查询也可以通过for update加写锁，

      比如：select * from T where id=1 for update

    • 意向锁（Intention Lock）：又称I锁，针对**表锁**，主要是为了提高加表锁的效率，是mysql数据库自己加的。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

      • 意向锁主要分为：

        • 意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。

        • 意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

# 表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

CREATE TABLE `mylock` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR (20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');
1
2
3
4
5
6
7
8
9
10

  • 手动增加表锁：lock table 表名称 read(write),表名称2 read(write);

  • 查看表上加过的锁：show open tables;

  • 删除表锁：unlock tables;

案例分析(加读锁）

image-20220720204135709

当前session和其他session都可以读该表

当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待

案例分析(加写锁）

​ image-20220720204142515

当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

案例结论

1、对MyISAM表的读操作(加读锁) ,不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

# 页锁

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

# 行锁 唯一索引 + 等值命中（= 或 IN 的每个值）

如果命中唯一索引等值条件，每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

InnoDB与MYISAM的最大不同有两点：

  • InnoDB支持事务（TRANSACTION）MYISSAM不支持事务
  • InnoDB支持行级锁

注意，InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁)

比如我们在RR级别执行如下sql

select * from account where name = 'lilei' for update; --where条件里的name字段无索引

则其它Session对该表任意一行记录做修改操作都会被阻塞住。

PS：关于RR级别行锁升级为表锁的原因分析

因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录。

# 行锁演示

一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

# 总结

  • MyISAM存储引擎在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。

  • InnoDB存储引擎在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

    简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

# 间隙锁(Gap Lock) 间隙锁 = 纯空档（范围内没命中记录时出现）

间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁在某些情况下可以解决幻读问题。

假设account表里数据如下：

​ image-20220720204730578

那么间隙就有 id 为 (3,10)、(10,20)、(20,+∞) 这三个区间。

在 Session_1 下执行：UPDATE account SET name = 'yangguo' WHERE id > 8 AND id < 18;

此时 InnoDB（在 Repeatable Read 隔离级别下）会加如下锁：

  • 对命中行 id=10 加 记录锁 [10]（在 RR 隔离级别下，InnoDB 对“命中记录”会加记录锁（record lock））；
  • 对其前后相邻的区间加 间隙锁 (3,10] 与 (10,20)；

也就是说，其他 Session 无法在这些锁定范围内插入或修改数据：

  • 不能插入或更新 id 在 (3,20) 区间内的记录（例如 4、9、11、19 等）；
  • 但 id=20 本身不在本次更新范围内，因此不会被加记录锁。

⚠️ 注意：右边界 20 不包含在内，锁区间是 (3,20) 而不是 (3,20]。

间隙锁仅在可重复读（Repeatable Read）隔离级别下才会生效。

# 临键锁（Next-Key Lock）临键锁 = 记录锁 + 左侧间隙（命中记录才出现）

Next-Key Lock 是记录锁（Record Lock）与间隙锁（Gap Lock）的组合。

例如上面的例子：UPDATE account SET name = 'yangguo' WHERE id > 8 AND id < 18;

在 InnoDB 的 Repeatable Read 隔离级别下，会锁住：

  • 命中记录：[10]（记录锁）
  • 以及前后的间隙：(3,10] 与 (10,20)（间隙锁）

把这些锁区间合并起来，即可以视为对 (3,20) 区间的 Next-Key Lock（临键锁）。

⚠️ 注意：

  • Next-Key Lock 的右边界是否“包含”取决于是否命中该行。
  • 在本例中右端 20 不包含，因为 id=20 不满足条件； 如果条件是 id <= 20，则右端才会包含（形成 (3,20]）。

# 无索引更新与“表锁”现象

InnoDB 的 行锁是加在索引记录上的锁，而不是直接加在数据行上。

  • 如果 WHERE 条件中 能命中索引，则只锁定相应索引项（行锁 / Next-Key 锁）。
  • 如果 没有可用索引，InnoDB 会对主键索引执行全表扫描，为了保证逻辑一致性，会锁定整张表所有行对应的索引项，表现上就像“表锁”。

例如：

-- name 列没有索引
session1:
UPDATE account SET balance = 800 WHERE name = 'lilei';
1
2
3

此时：

  • InnoDB 必须全表扫描才能找出 name='lilei'；
  • 扫描过程中会锁住所有索引记录；
  • 所以 session2 对该表任意行的操作都会被阻塞；
  • 这不是传统意义上的 “表锁”，而是行级锁覆盖了整张表索引范围（效果等同于表锁）。

✅ 注意：

  • 在 RR（Repeatable Read） 隔离级别下，这种全表范围锁最常见；
  • 在 RC（Read Committed） 下，间隙锁通常关闭，锁粒度会小一些；
  • 无论隔离级别如何，本质上都是“索引锁”，只是因为没有可用索引，导致锁定范围极大。

# 显式加锁语句

除了自动加锁外，还可以使用 显示锁语句：

  • 共享锁（S锁）：SELECT * FROM account WHERE id = 2 LOCK IN SHARE MODE;
  • 排他锁（X锁）：SELECT * FROM account WHERE id = 2 FOR UPDATE;

二者区别：

  • 共享锁允许其他事务读取该行（共享读），但不能修改；
  • 排他锁会阻塞其他事务的读写（除非使用 MVCC 的一致性读）。

# ✅ 总结

  • 行锁加在索引项上，不在数据行本身。
  • Next-Key Lock 临键锁 = 记录锁 + 间隙锁，用于防幻读。
  • 没有可用索引时，锁范围会扩大到整表，表现如“表锁”。
  • RC 级别一般不开间隙锁，RR 级别会启用 Next-Key Lock。

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

# 行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

对各个状态量的说明如下：

Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg: 每次等待所花平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间

Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：

Innodb_row_lock_time_avg （等待平均时长）

Innodb_row_lock_waits （等待总次数）

Innodb_row_lock_time（等待总时长）

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

# 快速定位“谁在挡路”

在 MySQL 8.0 建议用 performance_schema + sys 视图。

  1. 当前/最近的锁等待链
-- 需要开启 performance_schema，8.0 默认开
SELECT * FROM sys.innodb_lock_waits
1
2

关注：waiting_trx_id / blocking_trx_id / waiting_query / blocking_pid。

2.看阻塞者在干嘛、来自哪个应用

-- 等待链（谁等谁）
SELECT
  r.trx_id              AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_id              AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  r.trx_query           AS waiting_query,
  b.trx_query           AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r
  ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b
  ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;


-- 也可直接：
SELECT * FROM sys.innodb_lock_waits;  -- 需 sys/performance_schema 读权限
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3如需“立刻解堵”

-- 谨慎操作：只 KILL 明确确认是“卡住且可回滚”的会话
KILL <blocking_pid>;
1
2

（更稳妥做法是通知持锁方先提交/回滚）

# 查看INFORMATION_SCHEMA系统库锁相关数据表

  • -- 查看事务 select * from INFORMATION_SCHEMA.INNODB_TRX;
  • -- 查看锁 select * from INFORMATION_SCHEMA.INNODB_LOCKS;
  • -- 查看锁等待 select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
  • -- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到 kill trx_mysql_thread_id
  • -- 查看锁等待详细信息 show engine innodb status\G;

# 死锁

set tx_isolation='repeatable-read';

Session_1执行：select * from account where id=1 for update;

Session_2执行：select * from account where id=2 for update;

Session_1执行：select * from account where id=2 for update;

Session_2执行：select * from account where id=1 for update;

查看近期死锁日志信息：show engine innodb status\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

# 锁优化建议

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少检索条件范围，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
  • 尽可能低级别事务隔离
上次更新: 2025/10/29, 17:38:08
Mysql事务隔离级别和锁
MVCC与BufferPool缓存机制

← MVCC与BufferPool缓存机制→

最近更新
01
AI是如何学习的
10-30
02
提示词工程实践指南
10-30
03
chatGpt提示原则
10-30
更多文章>
| Copyright © 2022-2025 Kevin | MIT License