关于 MySQL 中的各种锁

前言

本篇文章已收录到 GitHub 仓库 https://github.com/logerJava/loger‍

关于 MySQL 中的锁, 如果是在小型项目开发中, 或在学习阶段很可能会忽略锁的问题, 也很少在主动去加锁, 这种情况一般都是被业务场景所限制的, 如果有做过库存方面对数据库数量要求十分严格的业务场景的话, 就应该对这些锁并不陌生 . 然而事实上一般情况下确实不需要太去了解 MySQL 锁方面的知识程序也会跑的很好, 这是因为数据库已经隐式的帮我们加好了锁, 但是这只是一般情况, 不论是为了以后业务场景的扩展, 还是自己知识的深度, 我们都有必要去学习 MySQL 中锁相关的知识 .

MySQL 中锁的分类

我们在前面的 MySQL - 基础概念 中有简单提到过锁的分类, 因为不同的存储引擎支持的锁机制是不同的, 我们这里主要以 MyISAM 和 InnoDB 进行解析

需要注意的是 MyISAM 存储引擎仅支持表锁, InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁

1

MyISAM 中的表锁

MyISAM 中的表锁可以分为两种 :

• 表共享读锁 （Table Read Lock）

• 不会阻塞其他用户对同一张表的读请求，但是会阻塞对同一张表的写请求

• 表独占写锁 （Table Write Lock）

• 会阻塞其他用户对同一张表的读写操作

MyISAM 表的读写是串行操作的, 也就是说当一个线程获得一个表的写锁后, 只有持有锁的线程才可以对表进行更新操作, 其他线程的读写操作都会阻塞, 知道锁被释放

在默认的情况下, 写锁的优先级别是高于读锁的, 也就是说, 如果存在锁争抢情况, 在上一个锁被释放时, 会优先给写锁队列中等待的请求, 然后再分配给读锁队列等待的请求

上面提到的也正是 MyISAM 存储引擎的表不适合大量更新, 查询操作的原因, 在大量更新操作时, 会导致查询操作难以获取读锁, 有可能出现一致阻塞的情况, 并且时间较长的查询会导致写操作线程 "饿死", 所以应用程序中应避免出现运行时间较长的查询操作

在 MyISAM 存储引擎中我们可以通过参数配置修改读写锁的优先级 :

• low-priority-updates : 配置默认给读请求优先权

• 执行 set low-priority-updates = 1 命令, 让该连接发出的更新请求优先级降低

• 指定 insert、update、delete 语句的 low_priority 属性, 降低执行语句的优先级

• 设置系统参数 max_write_lock_count 的值, 当表的读锁达到这个值后, MySQL 会暂时性的降低写请求的优先级, 从而使读进程获取锁

2

MyISAM 加表锁方法

事实上 MyISAM 在执行查询语句之前会自动的将涉及的表加读锁, 在执行更新操作前, 会自动给涉及的表加写锁, 整个过程并不需要用户的干预, 所以用户在一般场景下并不需要使用 lock table 命令显示的加锁

而在这种自动加锁的场景下, MyISAM 总是一次获得执行 SQL 语句需要的全部锁, 所以 MyISAM 表并不会出现死锁的情况

MyISAM 是支持并发插入的, 目的是减少指定表读写操作之间锁的争用, 如果 MyISAM 表在数据文件中不存在空闲块, 那么行数据始终都会插入数据文件的末尾部分, 此这种场景下, 就可以自由混合并发 MyISAM 表中的 insert 和 select 不需要加锁操作, 也就是说你可以在其他线程进行读操作的同时将行插入到 MyISAM 表中, 文件中的空闲块就有可能是从表中删除或更新的时候产生出来的, 如果文件中存在空闲块, 那么并发插入就会被禁止, 但是当所有的空闲块都填充有新数据的时候, 它又会重新启动, 我们可以通过 concurrent_insert 去控制系统变量

• concurrent_insert

• 设置为 0 时, 禁止并发插入

• 设置为 1 时, 如果 MyISAM 表中间没有被删除行, 那么 MyISAM 会允许在一个线程读表时, 另一个线程从尾行插入, 此为默认设置

• 设置为 2 时, 无论 MyISAM 表中间是否存在被删除行, 均允许在尾行并发插入

如果你在语句中使用 lock tables 显示锁定, 那么可以请求 read local 获取锁, 而非 read 锁, 这样在锁定表的时候, 其他会话操作可以并发插入

3

查询表级锁争用情况

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 34800596 |
| Table_locks_waited | 0 |
+-----------------------+---------+

如上我们可以通过 table_locks_waited 和 table_locks_immediate 来分析表锁的争用情况, 如果 table_locks_immediate 数值较高, 则可以认为存在严重的锁竞争情况

4

InnoDB 中的锁

在 InnoDB 中行锁和表锁是共存的, 它实现了如下两种行锁 :

• 共享锁 (S) : 允许一个事务去读一行, 阻止其他事务获得相同数据集的排他锁

• 排他锁 (X) : 允许获得排他锁的事务更新数据, 阻止其他事务取得相同数据集的共享读锁和排他写锁

而为了兼容表锁和行锁, 实现多粒度的锁, InnoDB 中还存在两种意向锁, 这两种都是表锁 :

• 意向共享锁 (IS) : 事务打算给数据行加行共享锁, 事务在给一个数据行加共享锁前必须先取得该表的意向共享锁

• 意向排他锁 (IX) : 事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的意向排他锁

通过下面的表格我们可以看一下锁兼容的情况 :

若一个事务请求的锁与当前的锁是兼容的, InnoDB 就会把请求的锁给到该事务; 相反, 若两者不兼容, 那么该事务就要等待锁的释放

5

InnoDB 加锁方法

加锁情况 :

• 对于意向锁是 InnoDB 自动加的, 不需用户干预

• 对于 insert, update, delete 语句, InnoDB 会自动给涉及数据加上排他锁

• 对于 select 语句, InnoDB 不会进行加锁操作

我们也可以通过如下语句显示的加排他锁和共享锁 :

• select ... from 表名 where ... lock in share mode

• 其他会话仍然可以查询本条记录, 并且可以加 share mode 的共享锁, 但是如果需要对该记录进行更新, 那么就会有可能造成死锁

• select ... from 表名 where ... for update

• 其他会话可以查询该记录, 但是不能对此记录加排他锁, 共享锁, 而是阻塞等待获取锁

6

隐式锁定与显示锁定

1

隐式锁定

InnoDB 在事务执行时, 采用两阶段锁协议 :

• 在任何时间都可以执行锁定, InnoDB 会根据隔离级别自动加锁

• 锁只有在提交和回滚的时候才会在同一时间释放

2

显示锁定

上面有提到两种显示锁定, 这里详细介绍一下 :

-- 共享锁
select ... lock in share mode
-- 排他锁
select ... for update

select ... for update :

我们一般在确保查询的是最新数据时会用到 for update, 在执行加了 for update 的查询语句时, 会将对查询行加排他锁, 也就是说只允许自己进行修改

select ... lock in share mode :

在使用 lock in share mode 时, 会对查询数据加共享锁, 同样是为了确保最新数据, 不允许其他用户进行修改, 但同样的自己也不一定能修改这条数据, 因为有可能其他事务也存在对相同数据添加 lock in share mode 的情况

select ... for update 与 select ... lock in share mode 的区别 :

• for update 为排他锁, 事务一旦获取此锁, 其他数据无法再在同样数据上添加 for update

• lock in share mode 是共享锁, 多个事务可以同时对同样数据添加 lock in share mode

3

性能影响

select ... for update 语句实际上相当于一个 update 语句, 若事务没有及时提交或回滚的情况下, 有可能造成其他事务长时间等待的问题, 影响数据库的并发效率

select ... lock in share mode 允许同时对数据上共享锁, 但是不能对数据进行更新操作, 同理, 如果不及时提交和回滚也可能造成大量事务等待问题

8

InnoDB 行锁的实现方式

首先我们都知道 InnoDB 索引的概念, 如果不了解可以看一下前面的文章 MySQL - 索引机制, 而 InnoDB 的行锁就是通过给索引上的索引项加锁来实现的, 这也就意味着不论是主键索引, 普通索引还是唯一索引, 只有在通过索引条件检索数据的情况下, InnoDB 才会启用行锁, 否则将会使用表锁

而这里需要注意的是, 只有真正启用索引的情况下, 才会使用行锁, 为什么要强调真正使用呢 ? 这是因为就算你加了索引字段, 但是是否真正的启用索引是由 MySQL 的判断执行代价来决定的, 如果 MySQL 判断执行全表扫描的效率会更好一点, 那么就不会使用索引, 比如一些数据量很小的表, 这种情况下 InnoDB 就会使用表锁, 所以在工作中进行分析锁冲突时, 一定要检查 explain 检查 SQL 的执行计划, 确认是否真正的启用了索引

另外, 因为 InnoDB 的行锁是针对索引加的锁, 而非记录, 所以虽然多个会话是访问的不同行记录, 如果行使用的相同的索引键, 还是会出现锁冲突的情况, 在后面使用这些索引的会话需要等待先一步使用索引的会话释放锁之后才能获取锁

9

InnoDB 的间隙锁

什么叫做间隙锁呢 ?

在我们用范围查询而非等值查询并请求锁时, InnoDB 会将符合的已存在数据记录的索引项加锁, 对于键值在条件范围内但并不存在的记录, 叫做间隙 (GAP), InnoDB 同样会对这个 "间隙" 加锁, 这种锁机制就是所谓的间隙锁, 间隙锁只会在 Repeatableread (可重复读) 隔离级别下使用

我们用学生表举例, 如果 student 表存在 101 条记录, 其中 studentId 分别是 1 - 101 :

SELECT * FROM student WHERE studentId > 100 for update;

在上面的范围查询中, InnoDB 不仅会对符合条件的 101 进行加锁, 还会对 studentId 大于 101 的 "间隙" 加锁, 即便这些记录不存在

10

InnoDB 使用间隙锁的目的

InnoDB 使用间隙锁的目的有两个 :

• 防止幻读, 在可重复读的隔离级别下, 通过 GAP 锁是可以避免幻读的

• 满足恢复和复制的需求

在之前讲解 MySQL - log 解析 的时候有讲解, MySQL 是通过 binlog 回放执行成功的增删改 SQL 语句来进行主从复制和数据恢复的吗, 我们可以根据其恢复特点来分析恢复和复制的需求 :

• binlog 恢复是重放 SQL 语句

• binlog 按照事务提交的先后顺序记录, 回放也是根据这个顺序回放

那么就可以看出, 在一个事务没有提交之前, 其他并发事务不能插入满足其锁定条件的任何记录, 通俗来讲就是不允许出现幻读

11

获取 InnoDB 行锁的争用情况

我们可以通过 innodb_row_lock 变量来分析行锁的争用情况 :

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |  -- 当前正在等待锁定的数量；
| InnoDB_row_lock_time | 6345955 |  -- 从系统启动到现在锁定总时间长度；
| InnoDB_row_lock_time_avg | 287 |  -- 每次等待所花平均时间；
| InnoDB_row_lock_time_max | 51094 |  -- 从系统启动到现在等待最长的一次所花的时间；
| InnoDB_row_lock_waits | 22069 |  -- 系统启动后到现在总共等待的次数；
+-------------------------------+-------+
5 rows in set (0.01 sec)

死锁

1

死锁的产生

说到并发, 就一定要谈一下死锁问题, 那么死锁是怎么产生的呢 ?

在 MySQL 中我们可以这么定义死锁 : 指两个或多个事务在同一资源上的互相占用, 并请求锁定对方占用的资源, 导致的恶性循环

死锁在数据库中产生的常见场景 :

• 当事务试图以不同顺序锁定资源时

• 多个事务锁定同一个资源时

然而死锁在某些存储引擎中是不会出现的, 以同样的顺序执行语句, 一些存储引擎会导致死锁, 而一些则不会, 那么我们就可以总结出死锁的原因 :

• 数据的冲突

• 存储引擎

2

避免死锁

一般来说 MySQL 通过回滚可以帮助我们解决很多死锁问题, 但是死锁问题没有办法完全杜绝, 既然无法杜绝我们就要尽可能的避免 :

• 以固定顺序访问表和行, 避免交叉等待锁的情况出现

• 将两个不同事务的 SQL 顺序调整一致

• 拆分事务, 一般大事务更容易出现死锁情况, 在业务允许的情况下尽量将大事务拆分为小事务

• 在同一事务中, 尽量一次锁定需要的所有资源

• 降低隔离级别

• 合理添加索引 (上面提到的不走索引的 InnDB )

参考

• MySQL 死锁问题分析

  https://www.cnblogs.com/LBSer/p/5183300.html

• MySQL 锁的总结

  https://zhuanlan.zhihu.com/p/29150809

结尾

因为主要介绍的是 MySQL 数据库的锁机制, 所以涉及到乐观锁, 悲观锁的地方没有讲解, 因为并不属于 MySQL 锁的范畴, 这些会在处理读写冲突的时候进行讲解

我是 loger, 扫描下方二维码关注公众号, 更多知识分享等你来看, 兄弟们别忘了点赞哦, 这真的对我很重要 👍