探秘 MySQL 锁：原理与实践

写在文章开头

在当今数字化的时代，数据库管理系统的重要性不言而喻，而 MySQL 作为广泛应用的数据库之一，更是有着举足轻重的地位。在 MySQL 的复杂世界中，锁机制宛如一把关键的钥匙，它既是保障数据一致性和完整性的坚实卫士，也是影响数据库性能和并发处理能力的重要因素。当我们深入探索 MySQL 的领域时，不得不将目光聚焦于这看似神秘却又至关重要的锁。它究竟是如何运作的？有哪些不同的类型和特点？又如何在各种业务场景中发挥着关键作用？让我们一同开启这场关于 MySQL 锁的探索之旅，逐步揭开它的神秘面纱，去洞悉它背后的深刻原理和广泛应用，为我们更好地驾驭 MySQL 数据库奠定坚实基础。

注意:本文所有知识点都是从MySQL8.0版本进行讨论，针对5.7版本笔者会在特定知识点中点出区别。

Hi，我是 sharkChili ，是个不断在硬核技术上作死的技术人，是 CSDN的博客专家 ，也是开源项目 Java Guide 的维护者之一，熟悉 Java 也会一点 Go ，偶尔也会在 C源码 边缘徘徊。写过很多有意思的技术博客，也还在研究并输出技术的路上，希望我的文章对你有帮助，非常欢迎你关注我的公众号： 写代码的SharkChili 。

同时也非常欢迎你star我的开源项目mini-redis:https://github.com/shark-ctrl/mini-redis

因为近期收到很多读者的私信，所以也专门创建了一个交流群，感兴趣的读者可以通过上方的公众号获取笔者的联系方式完成好友添加，点击备注  “加群”  即可和笔者和笔者的朋友们进行深入交流。

详解MySQL锁原理

详解共享锁和排他锁

共享锁(Share Lock,S 锁)
:又称读锁，进行读取操作时会上的锁，上读锁之后的锁其他事务上读锁也没问题。

排他锁(Exclusive Lock,X 锁)
:又称写锁，进行修改操作前就需要上一把X锁，上了写锁的数据，其他事务则无法上读锁或者写锁。对于已经上了读锁的数据，写锁自然也是不能上锁的。

有了上述基础，我们针对x锁对应delete
和update
操作进行一定的拓展，当事务进行delete的操作时，对应的步骤为：

1. 定位要删除的数据的B+树。
2. 对其上X锁
   。
3. 上锁成功后将该位置标志位delete mark
   。
4. 提交事务。

可以看到删除操作本质就是定位、标记、删除对应3个步骤，而且删除也并非实际意义上的删除，而是标记删除。

针对update
操作，我们可以从以下3个维度探讨：

1. 普通更新，即更新的字段物理空间不会变化，例如update tb set age=19 where id=1
   ，该操作操作和上述操作差不多，定位到B+树上的数据后上X锁，并进行修改操作：

1. 更新可变字段，假设我们基于主键更新某个varchar
   字段，例如update tb set name='aaaaaaaaaaaaaa' where id=1
   ，将name
   由varchar(1)
   更新为varchar(14)
   ，那么对应的操作就是定位到数据后上X锁，将数据删除(这里的删除就是将记录移动到垃圾链表)，然后执行insert
   操作。
2. 更新主键，该操作因为会修改b+树，在定位到数据上X锁之后，先按照delete
   的方式将数据删除，然后再执行insert
   操作即可。

需要了解的是MySQL
在进行读写操作为保证并发性能并非一定会用到读写锁，在可重复读和读已提交两个级别下，由于mvcc
机制的存在，所以MySQL
事务的读操作都是基于readview
进行数据查询的。

深入解读MySQL的MVCC与事务隔离级别:https://mp.weixin.qq.com/s/30KsjO00FWUfHNLHyB1nRA

详解意向锁

假如我们要上全表锁，我们就必须知道这张表有没有上过读锁或者写锁的行级锁，要想做到这点，常规做法是一行一行遍历过去看看，针对大表而言，这种遍历的效率是非常低的。

意向锁就是用于解决这个问题的，它是表级锁，在事务需要上读锁(S锁)
或者写锁(X锁)
前，首先必须取得意向锁
，这样某些事务需要上全表锁时，只需要看看有没有事务持有意向锁即可：

需要补充的是，意向锁由数据引擎自行维护，用户是无法操作的。

说完意向锁的作用，我们就可以再来聊聊意向锁的种类，意向锁分为意向共享锁和意向排他锁：

1. 意向共享锁(Intention Shared Lock，IS 锁)
   ：当事务需要上S锁时，就需要先尝试获取IS锁。
2. 意向排他锁(Intention Exclusive Lock，IX 锁)
   ：当事务需要针对某条数据上X锁的时候，就需要先上一把IX锁。 而IS
   和IX
   之间互相兼容，彼此不互斥，例如：一个事务上了IS，其他事务同样可以上IS和IX，因为IS本质就是一个共享读锁，某个事务持有这个IS锁之后上的可能是id为1的读锁。不影响其他事务上IS或IX后对id为2的数据上X锁：

本质上来说IS和IX锁都是表级锁，它们主要是解决在事务上S锁或者X锁前意向询问来避免扫表的开销，所以彼此之间是兼容的。

接下来我们从表记锁的角度探讨以下意向锁和读写锁之间的关系，假设我们的事务需要上IS锁之后针对表上了S表级锁，那么就需要查看是否有事务上了IX锁，如果有则说明有事务正在进行写操作，此时我们的S表级锁操作就会阻塞：

同理假设我们的尝试IX锁进行修改操作，需要针对全表上了X锁，此时我们就需要检查是否有其他事务上了IS或者IX以确定是否存在数据操作，如果有则阻塞等待，这就是IS、IX锁对于数据读写操作的巧妙设计：

详解表记锁

表级锁(table-level locking)
即可每一次操作时，锁的是整张表，锁的粒度大，上锁速度快，开销低，一旦某个事务上了表级锁，那么其他事务就无法再上行级锁或者表锁，这也就意味着高并发场景下性能非常差:

如下所示，这条SQL语句查询上的就是表级锁：

SELECT * FROM s1   for update;

这一点我们可以通过查看performance_schema.data_locks
表印证这一点，可以看到这个事务上的是IX意向写锁，并将表中的所有的记录即都上了record
写锁：

详解行级锁

行级锁(table-level locking)
锁的粒度相对小一些，是针对索引字段加锁，锁的是选定的行数据，相比前者上锁速度会慢一些，因为需要定位到当前行才能锁定，并且因为粒度和逻辑设计问题有可能会导致死锁，但是对于高并发多事务会相对友好一些：

对应我们给出行级锁的使用示例：

SELECT * FROM s1 WHERE id=1 for update;

通过performance_schema.data_locks
表，可以看到这条该事务上X锁前上了一把IX锁之后，针对这条记录上了X,REC_NOT_GAP
即上了一把行级锁，但是不锁住间隙：

注意：我们上文所讨论的都是针对innodb这个存储引擎，如果是MyISAM
这个存储引擎仅仅支持表级锁，而InnoDB
支持行级锁。

行级锁实际使用示例

我们先给出行锁的使用示例：首先创建一张测试表，注意id被设置为主键是自带索引的。

drop  table user;

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;

插入模拟数据：

insert into `user`(username, age)values
 ('tom',23),
('joey',22),
('James',21),
('William',20),
('David',24);

关闭事务自动提交，并确认：

-- 关闭事务自动提交
set  autocommit = 0;

-- 查看自动提交是否生效
show VARIABLES like 'autocommit';

此时，在窗口键入一下SQL
进行查询，可以看到笔者用了for update
对id
为1
的数据上了排他锁。

begin;
select * from `user` u where id=1 for update ;

此时我们再开一个排他锁查询，可以看到事务阻塞：

begin;
select * from `user` u where id=1 for update ;

由此可知，使用排他锁对索引列上锁时，其他排他锁是无法操作对应数据行的:

补充一个联合索引
的行级锁使用示例，基于上述表格我们创建一个表级锁：

create index index_username_age on user(username,age);

开启一个事务进行查询

begin;
select * from user where username ='tom' and age=23  for update ;

此时另一个事务进行相同查询会阻塞，走不相同的查询条件不会阻塞，说明联合索引
也是走行级锁：

begin;
select * from user where username ='tom' and age=23  for update ;

我们通过performance_schema.data_locks表可以看到，第一个事务针对tom数据上了一把行级写锁：

表级锁示例

上文提到，只要不走索引的查询锁定的都是整张表，所以我们使用age
对应22的SQL查询语句上排他锁：

begin;
select * from user where age=20 for update;

再打开另一个窗口就会发现，窗口被阻塞

begin;
select * from user where age=22 for update ;

很明显因为没有命中索引所以无法通过索引方式进行定位，所以上了表级锁，这一点我们也可以通过information_schema.INNODB_TRX表查看，可以看到第一个事务将所有数据行都上了锁：

行级锁使用的注意事项

我们都知道行级锁锁的是索引字段
，而表级锁锁的是非索引字
段，这就意味着如果我们进行update
或者delete
操作 (这两个操作会上写锁互斥的，后文会说明)
时where
条件没有命中唯一索引或者索引失效的话，就会上表级锁，进而出现性能问题。

当然了，有时候MySQL
优化器也会不走索引，例如范围索引检索范围区间超过30%，优化器就会走全表扫描，那就无能为力了。

InnoDB有哪几类锁

1. 记录锁(Record Lock)
   :这个锁锁定的是单个记录行上的锁。
2. 间隙锁(Gap Lock)
   :这个锁锁定的是一个范围，例如查找id>21
   的用户，那么间隙锁锁定的就是大于21
   的记录，不包括21
   本身。
3. 临建锁(Next-key Lock)
   :这个锁我们可以理解为是记录锁和间隙锁的综合，它可以保证当前锁定的记录及其间隙都上锁，这个锁可以保证可重复读场景下事务读避免幻读问题，例如我们数据正在读取id为8的数据，针对该锁上一把Next-key Lock
   可以阻塞当前数据及其前后区间被其他事务操作导致幻读：

可重复读是基于上面那种锁避免幻读的呢？

可重读读避免幻读的方式有两种:

1. 快照读(一次性非锁定读)
   :这种方式就是通过mvcc
   的方式仅仅在启动时创建一个readView
   确保不会出现幻读。
2. 当前读(一次性锁定读)
   :这种方式就是通过临建锁，避免新的记录插入从而避免幻读的情况发生。

具体可以参考笔者的这篇文章

深入解读MySQL的MVCC与事务隔离级别:https://mp.weixin.qq.com/s/30KsjO00FWUfHNLHyB1nRA

详解间隙锁

上文说过，间隙锁就是在范围查询时对索引项上锁，但不包括范围本身的一种锁，这种机制在RR这个隔离级别可以一定程度上避免幻读，注意是一定程度上。 对此我们不妨举个例子:

首先我们创建一个用户表，并对age加个索引：

drop  table user;

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;


create index age_index on user(age);

然后插入数据

insert into `user`(username, age)values ('tom',23),('joey',22),('James',21),('William',20),('David',24);

由于MySQL
默认隔离级别为RR
，所以我们开启一个窗口设置关闭自动提交，并查看其是否生效

-- 关闭事务自动提交
set  autocommit = 0;

-- 查看自动提交是否生效
show VARIABLES like 'autocommit';

若生效，我们则输入一个begin
，并进行一个范围查询，搜索大于24
范围的数据

begin;
select * from user where age >24 for update;

当我们在开启一个新的窗口并进行插入操作时可以发现，操作被阻塞了，

begin;
insert into `user`(username, age)values ('tom',26);

因为年龄上了索引，所以查询时走了范围索引，从performance_schema.data_locks可以看出该操作针对id为5、10这两条年龄为24的数据都上了行级锁和间隙锁，导致数据插入失败：

详解悲观锁和乐观锁

悲观锁就我们上面所说的互斥锁，它认为自己每次拿到的数据都很可能被人修改，通过上锁确保互斥性避免数据一致性问题。在MySQL
中select...for update
，insert
，update
、delete
语句用的都是用排他锁实现悲观锁。 而乐观锁则是一种业务锁，通过用户手动对表增加一个版本号的字段来解决并发数据正确性问题，如下图所示，某个时间点两个相同的事务读取到相同版本号的当行数据，彼此都执行更新逻辑，为了保证并发更新保证数据准确性，我们就需要通过版本号确保自己更新的数据是最新数据，如下图所示，左边的更新SQL
最先执行，这就使得右边的更新失败了，这样右边的事务就知道自己更新条件数据已过期，就会修改版本号再次进行更新。

当前读和快照读有什么区别？

答:  先说说当前读(一致性非锁定读)
吧，当前读发生在读已提交(RC)
或者可重复读(RR)
这两个隔离级别下，我们使用的select
使用的就是快照读:

1. 若在RC
   这个隔离级别下，用户每次进行读操作时，都会创建一个readView
   ，然后通过这个readView
   获取数据。
2. 若在RR
   这个隔离级别下，仅仅在启动事务时创建一个readVew
   ，后续无论其他事务无论修改用户读取的数据，用户都只会读取当前readView
   的数据，这就是为什么RR
   可以保证可重入读。

而当前读(一致性锁定读)
则基于S锁或者X锁实现的一种读取最新数据，快照读的select
语句如下:

SELECT ... FOR UPDATE
SELECT ... LOCK IN SHARE MODE

当然，我们的insert
、update
、delete
语句也是使用当前读。

具体可以参考笔者的这篇文章

深入解读MySQL的MVCC与事务隔离级别:https://mp.weixin.qq.com/s/30KsjO00FWUfHNLHyB1nRA

详解MySQL中死锁问题

详解定位事务各种锁的几张表

在正式演示死锁定位与排查思路之前，我们先简单介绍几张比较重要的表，首先是INNODB_TRX 这张表，它会记录当前活跃事务所持有的锁的情况：

SELECT * FROM information_schema.INNODB_TRX it ;

如下图，可以看到我们567584这个事务，这里我们着重查看trx_tables_locked
、trx_lock_structs
、trx_rows_locked三个字段，其含义分别是：

1. trx_tables_locked
   ：当前事务对几张表上锁，以本条数据为例就上了一把锁。
2. trx_rows_locked
   ：标识当前事务锁定几行数据，下图表示当前事务锁定了一行数据。
3. trx_lock_structs
   ：当前事务生成几个锁的结构体，显示为2，即生成两个锁的结构体。

我们再来看看data_locks
表(对应MySQL5.7
版本表明为innodb_locks
)，这张表在MySQL
中活跃事务的上锁情况：

 select * from performance_schema.data_locks;

如下图，可以看到567834
事务的线程号、事件id以及这个事务在tb_1表上了一把IX意向读锁：

最后再来看看 data_lock_waits
表（对应MySQL5.7是innodb_waits表），这张表就比较重要了，它代表了当前事务中处于等待锁状态的事务情况：

select * from performance_schema.data_lock_waits;

如下图，可以看到本文的567585
事务正在等待567584
的事务的锁：

(实践)线上定位MySQL死锁与解决思路

接下来我们就基于一个简单的例子来掩饰一下如何定位死锁问题，我们都知道for update
上的是写锁，这意味着一旦上了X锁的数据，其他事务就无法针对该数据上S锁或者X锁：

如下图这个说明，假设的我们的事务1先针对id为1的值上了一把写锁，对应事务的SQL如下：

begin;
SELECT * FROM  tb_1 t WHERE id =1 for UPDATE ;
SELECT * FROM  tb_1 t WHERE id =2 for UPDATE ;

同理第二个事务现针对id为2的数据上写锁，在针对id为1的数据上写锁，由此双方循环等待，造成死锁

begin;
SELECT * FROM  tb_1 t WHERE id =2 for UPDATE ;
SELECT * FROM  tb_1 t WHERE id =1 for UPDATE ;

接下来我们就基于上述所说的3张表进行死锁的问题的定位，首先我们查看INNODB_TRX
可以看到我们本次SQL的食物号为567585它处于锁等待状态，可以看到它正在执行的SQL语句以及上锁的信息

然后我们到data_locks
表查看当前数据库的锁情况，这个事务正在等待X
锁和REC_NOT_GAP
锁，说明这个事务存在死锁：

最后再到data_lock_waits
可以看到567585
的事务等待567584
的事务

明确定位了两个事务的，查看innodb 状态信息定位到这两个事务号的执行执行语句：

show engine innodb status;

最终，可以看到处于等待的事务567585
锁等待的事务567584
所执行的SQL语句，很明显是因为上了同一个行级锁造成事务567585
等待造成死锁：

针对死锁问题这个问题，我们先得说说造成死锁的4个条件:

1. 互斥
2. 不可剥夺
3. 请求和保持条件
4. 锁之间构成环路

所以MySQL
解决死锁的方式大抵有以下几种:

1. 每个事务按照顺序到表中上锁(破坏环路条件)
   。
2. 将大事务拆小。
3. 逻辑上要求事务必须一次性取得两张表的锁才能操作数据。
4. 降低隔离级别，例如将RR
   级别降低为RC
   避免上间隙锁确保降低发生死锁的概率。

我是 sharkchili ，CSDN Java 领域博客专家，mini-redis的作者，我想写一些有意思的东西，希望对你有帮助，如果你想实时收到我写的硬核的文章也欢迎你关注我的公众号： 写代码的SharkChili 。

同时也非常欢迎你star我的开源项目mini-redis:https://github.com/shark-ctrl/mini-redis

因为近期收到很多读者的私信，所以也专门创建了一个交流群，感兴趣的读者可以通过上方的公众号获取笔者的联系方式完成好友添加，点击备注  “加群”  即可和笔者和笔者的朋友们进行深入交流。

参考

MySQL常见面试题总结:https://javaguide.cn/database/mysql/mysql-questions-01.html#mysql-如何存储-ip-地址

MySQL锁相关:https://juejin.cn/post/6844903821546618888#heading-18

Mysql 8查看并修改事务隔离级别:https://blog.csdn.net/q283614346/article/details/103938542

MySQL间隙锁、Next-Key Lock主要知识点:https://www.jianshu.com/p/d5c2613cbb81

《MySQL是怎样运行的：从根儿上理解MySQL》

MySQL的数据锁表（data_locks 表）:https://blog.csdn.net/jkzyx123/article/details/135848317