疫情期间在家工作时, 同事使用了 insert into on duplicate key update 语句进行插入去重, 但是在测试过程中发现了死锁现象:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
由于开发任务紧急, 只是暂时规避了一下, 但是对触发死锁的原因和相关原理不甚了解, 于是这几天一直在查阅相关资料, 总结出一个系列文章供大家参考. 本篇是上篇, 主要介绍 MySQL 加锁原理和锁的不同模式或类型的基本知识. 后续会讲解常见语句的加锁情况和通过 MySQL 死锁日志分析死锁原因.
由于本篇文章涉及很多 MySQL 的基础知识, 大家可以自行阅读我之前的 MySQL 系列文章 《MySQL 探秘》 http://remcarpediem.net/categories/MySQL/ 中的对应章节.
表锁和行锁
我们首先来了解一下表锁和行锁: 表锁是指对一整张表加锁, 一般是 DDL 处理时使用; 而行锁则是锁定某一行或者某几行, 或者行与行之间的间隙.
表锁由 MySQL Server 实现, 行锁则是存储引擎实现, 不同的引擎实现的不同. 在 MySQL 的常用引擎中 InnoDB 支持行锁, 而 MyISAM 则只能使用 MySQL Server 提供的表锁.
表锁
表锁由 MySQL Server 实现, 一般在执行 DDL 语句时会对整个表进行加锁, 比如说 ALTER TABLE 等操作. 在执行 SQL 语句时, 也可以明确指定对某个表进行加锁.
- MySQL> lock table user read(write); # 分为读锁和写锁
- Query OK, 0 rows affected (0.00 sec)
- MySQL> select * from user where id = 100; # 成功
- MySQL> select * from role where id = 100; # 失败, 未提前获取该 role 的读表锁
- MySQL> update user set name = 'Tom' where id = 100; # 失败, 未提前获得 user 的写表锁
- MySQL> unlock tables; # 显示释放表锁
- 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 索引》一文.
下面以两条 SQL 的执行为例, 讲解一下 InnoDB 对于单行数据的加锁原理.
- update user set age = 10 where id = 49;
- update user set age = 10 where name = 'Tom';
第一条 SQL 使用主键索引来查询, 则只需要在 id = 49 这个主键索引上加上写锁; 第二条 SQL 则使用二级索引来查询, 则首先在 name = Tom 这个索引上加写锁, 然后由于使用 InnoDB 二级索引还需再次根据主键索引查询, 所以还需要在 id = 49 这个主键索引上加写锁, 如上图所示.
也就是说使用主键索引需要加一把锁, 使用二级索引需要在二级索引和主键索引上各加一把锁.
根据索引对单行数据进行更新的加锁原理了解了, 那如果更新操作涉及多个行呢, 比如下面 SQL 的执行场景.
update user set age = 10 where id> 49;
上述 SQL 的执行过程如下图所示. MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录, 然后 InnoDB 引擎会将第一条记录返回并加锁, 接着 MySQL Server 发起更新改行记录的 UPDATE 请求, 更新这条记录. 一条记录操作完成, 再读取下一条记录, 直至没有匹配的记录为止.
这种场景下的锁的释放较为复杂, 有多种的优化方式, 我对这块暂时还没有了解, 还请知道的小伙伴在下方留言解释.
下面主要依次介绍 InnoDB 中锁的模式和类型, 锁的类型是指锁的粒度或者锁具体加在什么地方; 而锁模式描述的是锁的兼容性, 也就是加的是什么锁, 比如写锁或者读锁.
来源: http://www.tuicool.com/articles/zYNZfeQ