的锁的类型_MySQL的死锁系列锁的类型以及加锁原理

今天药忘吃喽~ 2023-01-07 03:48 163阅读 0赞

**点击上方****“****码农沉思录****”****，选择“设为星标”**

**优质文章，及时送达**

疫情期间在家工作时，同事使用了 insert into on duplicate key update 语句进行插入去重，但是在测试过程中发生了死锁现象:

1.  ERROR 1213 (40001): Deadlock found when trying to get lock;
2.  try restarting transaction

由于开发任务紧急，只是暂时规避了一下，但是对触发死锁的原因和相关原理不甚了解，于是这几天一直在查阅相关资料，总结出一个系列文章供大家参考，本篇是上篇，主要介绍 MySQL 加锁原理和锁的不同模式或类型的基本知识。后续会讲解常见语句的加锁情况和通过 MySQL 死锁日志分析死锁原因。

由于本篇文章涉及很多 MySQL 的基础知识，大家可以自行阅读我之前的 MySQL系列文章 《MySQL探秘》(公众号菜单处可进入系列文章)中的对应章节。

**表锁和行锁**

我们首先来了解一下表锁和行锁：表锁是指对一整张表加锁，一般是 DDL 处理时使用；而行锁则是锁定某一行或者某几行，或者行与行之间的间隙。

表锁由 MySQL Server 实现，行锁则是存储引擎实现，不同的引擎实现的不同。在 MySQL 的常用引擎中 InnoDB 支持行锁，而 MyISAM 则只能使用 MySQL Server 提供的表锁。

![9f2ae19c8030b25944bf53a8417c0f76.png][]

**表锁**

表锁由 MySQL Server 实现，一般在执行 DDL 语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作。在执行 SQL 语句时，也可以明确指定对某个表进行加锁。

1.  mysql> lock table user read(write); \# 分为读锁和写锁
2.  Query OK, 0 rows affected (0.00 sec)
3.  
4.  mysql> select \* from user where id = 100; \# 成功
5.  mysql> select \* from role where id = 100; \# 失败，未提前获取该 role的读表锁
6.  mysql> update user  set name = 'Tom' where id = 100; \# 失败，未提前获得user的写表锁
7.  
8.  mysql> unlock tables; \# 显示释放表锁
9.  Query OK, 0 rows affected (0.00 sec)

表锁使用的是一次性锁技术，也就是说，在会话开始的地方使用 lock 命令将后续需要用到的表都加上锁，在表释放前，只能访问这些加锁的表，不能访问其他表，直到最后通过 unlock tables 释放所有表锁。

除了使用 unlock tables 显示释放锁之外，会话持有其他表锁时执行lock table 语句会释放会话之前持有的锁；会话持有其他表锁时执行 start transaction 或者 begin 开启事务时，也会释放之前持有的锁。

**行锁**

不同存储引擎的行锁实现不同，后续没有特别说明，则行锁特指 InnoDB 实现的行锁。

在了解 InnoDB 的加锁原理前，需要对其存储结构有一定的了解。InnoDB 是聚簇索引，也就是 B+树的叶节点既存储了主键索引也存储了数据行。而 InnoDB 的二级索引的叶节点存储的则是主键值，所以通过二级索引查询数据时，还需要拿对应的主键去聚簇索引中再次进行查询。关于 InnoDB 和 MyISAM 的索引的详细知识可以阅读《Mysql探索(一):B+Tree索引》一文。

![fcbe5a8184092bd8b5231b76d6851bd9.png][]

下面以两条 SQL 的执行为例，讲解一下 InnoDB 对于单行数据的加锁原理。

1.  update user set age = 10 where id = 49;
2.  update user set age = 10 where name = 'Tom';

第一条 SQL 使用主键索引来查询，则只需要在 id = 49 这个主键索引上加上写锁；第二条 SQL 则使用二级索引来查询，则首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加写锁，如上图所示。

也就是说使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁。

根据索引对单行数据进行更新的加锁原理了解了，那如果更新操作涉及多个行呢，比如下面 SQL 的执行场景。

1.  update user set age = 10 where id > 49;

上述 SQL 的执行过程如下图所示。MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁，接着 MySQL Server 发起更新改行记录的 UPDATE 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止。

![28142b9c6222a2760b68f13a795a6cef.png][]

这种场景下的锁的释放较为复杂，有多种的优化方式，我对这块暂时还没有了解，还请知道的小伙伴在下方留言解释。

下面主要依次介绍 InnoDB 中锁的模式和类型，锁的类型是指锁的粒度或者锁具体加在什么地方；而锁模式描述的是锁的兼容性，也就是加的是什么锁，比如写锁或者读锁。

[9f2ae19c8030b25944bf53a8417c0f76.png]: /images/20221119/b8b4c86cb0ed46f0bb43d6d3485547ad.png
[fcbe5a8184092bd8b5231b76d6851bd9.png]: /images/20221119/99fdb747d21a4b54b7e0eade738e51ca.png
[28142b9c6222a2760b68f13a795a6cef.png]: https://img-blog.csdnimg.cn/img_convert/28142b9c6222a2760b68f13a795a6cef.png