长文-InnoDB中的各种锁

2022-07-14 19:50:31 浏览数 (2)

欢迎收藏作者个人网站

要在高并发的场景下，保证基于InnoDB的应用程序的可靠性、性能，理解InnoDB的锁机制是必不可少的。

InnoDB中的锁机制主要包括一下概念：

实验环境

本文会结合一些例子来介绍这些概念，如果没有特别说明，基于以下环境

MySQL：5.7 存储引擎: InnoDB 隔离级别： REPEATABLE-READ MySQL官方文档： https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

基础测试数据

代码语言：javascript复制
| t_user | CREATE TABLE `t_user` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  
mysql> select * from t_user;
 ---- -------- ----- 
| id | name   | age |
 ---- -------- ----- 
|  1 | user01 |  21 |
|  3 | user03 |  23 |
|  6 | user06 |  26 |
| 10 | user10 |  30 |

共享锁与排它锁

共享锁与排它锁

InnoDB实现了两种类型的行级锁。

  • 共享锁（读锁）-S：共享锁允许持有该锁的事务能够读取锁定行。
  • 排它锁（写锁）-X：排它锁允许持有该锁的事务能够更新和删除锁定行。

共享锁与排它锁的兼容性如下：

  • 当一个事务T1持有共享锁时，另一个事务T2可以被授予共享锁，不可以被授予排它锁。
  • 当一个事务T1持有排它锁时，另一个事务T2不可以被授予共享锁和排它锁。

意向锁

InnoDB中的意向锁是一种表级锁，用于指示稍后将对表中的行施加那种锁类型（共享锁或排它锁），意向锁是用于提升表级锁（共享锁、排它锁）的加锁效率的。

InnoDB支持多粒度锁（表级锁、行级锁），如果没有意向锁，当我们要加表级锁是，很可能需要扫描表中所有的行，检查这些行是否有行级别的与要施加的表级锁互斥的行级锁，如果表级锁的加锁效率就十分糟糕了。

我们看下意向锁如果提升表级锁的加锁效率。

意向锁可以分为两类

  • 共享意向锁-IS：指示事务将对表中的各行施加共享锁。
  • 排它意向锁-IX：指示事务将对表中的各行施加排它锁。

什么时候会设置意向锁

  • 当一个事务要对表中的行设置共享锁时，它需要先对这个表施加共享意向锁或者排它意向锁。
  • 当一个事务要对标中的行设置排它锁时，它需要先对这个表施加排它意向锁

意向锁和表级共享/排它锁的兼容性如下表所示

虽然意向锁是表级锁，但是因为施加行锁前需要先设置表级意向锁，因此意向锁不仅影响了表级锁的设置过程，也会影响行级锁的设置过程。

具体的影响可以小结为以下3点

  1. 对其它意向锁设置过程的影响：

意向锁之间都是互相兼容的。也就是说，一个事务对表施加了意向锁，并不会阻塞其他事务对该表施加任意一种意向锁。

  1. 对表级共享锁、排它锁设置过程的影响：

事务请求表级共享锁、排它锁时，需要先检测该表上是否被设置了与之互斥的意向锁。

举个例子，事务Trx02在要执行 LOCK TABLES db_windeal.t_user WRITE , （这条语句会请求 t_user 表的排它锁）。但是检测发现有另一个事务Trx01正在执行 SELECT * FROM db_windeal.t_user WHERE id=1 LOCK IN SHARE MODE； 对id=1的记录施加了行级锁，但是对表加了意向共享锁。 因为事务Trx02的表级排它锁与事务Trx01的意向共享锁冲突，事务Trx02只能阻塞。

  1. 对行级共享锁、排它锁设置过程的影响：

当需要表中的某一行设置行级锁时，需要先请求所在表对应的意向锁；而请求的意向锁时需要检测当前表中是否有与之互斥的表级意向锁或排他锁。比如某个事务Trx02执行 SELECT * FROM db_windeal.t_user WHERE id=1 LOCK IN SHARE MODE； 需要先对 db_windeal.t_user` `请求意向共享锁 。如果这时检测到另一个事务Trx01已经对db_windeal.t_user设置了排它锁，因为两者的互斥关系，Trx02只能阻塞。

锁的模式/算法

记录锁 Record Lock

记录锁是对索引记录加的锁。

当我们使用唯一索引的唯一行进行检索，并且检索到结果时，会对结果行设置记录锁。

举个例子：

代码语言：javascript复制
SELECT * FROM db_windeal.t_user WHERE id=3 FOR UPDATE

会对id=3这一条记录设置一个排它锁。防止其他事务对id=1的记录进行INSERT、UPDATE、DELETE等操作。

记录锁总是作用于索引记录。

记录锁在SHOW ENGINE INNODB STATUS 中展示如下

代码语言：javascript复制
SHOW ENGINE INNODB STATUS
TABLE LOCK table `db_windeal`.`t_user` trx id 1875 lock mode IX
RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `db_windeal`.`t_user` trx id 1875 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000003; asc     ;;
 1: len 6; hex 000000000739; asc 9;;
 2: len 7; hex aa0000011e011c; asc        ;;
 3: len 6; hex 757365723033; asc user03;;
 4: len 4; hex 80000017; asc     ;

可以看到添加了一个lock_mode X locks rec but not gap

InnoDB默认不会检测锁的状态，需要通过下列配置才能从SHOW ENGINE INNODB STATUS 中看到锁的状态

SET GLOBAL innodb_status_output=ON;

SET GLOBAL innodb_status_output_locks=ON;

间隙锁 Gap Lock

间隙锁是对索引记录的间隙的锁（包括第一条索引记录前的区间和最后一条索引记录之后的区间）。

间隙锁的唯一作用是防止其它事务向gap间隙插入记录（防止幻读）。被施加了间隙搜的间隙gap不允许插入新的记录。

间隙锁锁定的是基于索引记录的全开区间，前面的db_windeal.t_user表可以划分为四个gap：

（-∞, 1), (1,3), (3,6),(6,10), (10, ∞);

关于间隙锁和临键锁在什么时候设置，我这边的实验结果和mysql官方文档的介绍不太一致: 1 . 官方文档没有对比介绍间隙锁和临键锁，按官方文档的介绍，像SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;这样的语句会设置间隙锁。 但实际通过SHOW ENGINE INNODB STATUS 显示的结果是设置了临键锁Next-Key Lock。（不过也不能说他写错了，因为临建锁本就是记录锁加间隙锁；） 2. 官方文档说Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.  但是实际上如果查询条件没有检索到记录（比如我们的测试数据里使用了WHERE id=7），那么还是会设置间隙锁。

间隙锁示例：

代码语言：javascript复制
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t_user WHERE id=7 FOR UPDATE;
Empty set (0.04 sec)
mysql> SHOW ENGINE INNODB STATUS ;
...
SHOW ENGINE INNODB STATUS
TABLE LOCK table `db_windeal`.`t_user` trx id 1879 lock mode IX
RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `db_windeal`.`t_user` trx id 1879 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 0000000a; asc     ;;
 1: len 6; hex 000000000739; asc      9;;
 2: len 7; hex aa0000011e0134; asc       4;;
 3: len 6; hex 757365723130; asc user10;;
 4: len 4; hex 8000001e; asc     ;;
...

可以看到在记录id=10，name='user10'这一条记录前的一个间隙(6,10)添加了一个间隙锁 lock_mode X locks gap before rec

不需要区分共享间隙锁和排它间隙锁，间隙锁之间也不会产生冲突，甚至当删除某条索引记录时，间隙锁的gap还会发生合并。

间隙锁是对性能与并发进行权衡衍生的折衷的算法，并非所有隔离级别都会启用间隙锁。 

临键锁 Next-Key Lock

临键锁 Next-Key Lock 是索引记录的记录锁和索引记录之前的间隙锁的组合。

临键锁 = 记录锁 间隙锁

Next-Key Lock每次锁定的是一个基于索引记录左开右闭的区间（最后一个区间的右端点是一个supremum伪端点，表示为正无穷）， 前面的db_windeal.t_user表可以划分为四个临键锁区间：

代码语言：javascript复制
（-∞, 1], (1,3], (3,5], (5,  ∞);

InnoDB使用临键锁来防止幻读。施加了临键锁的左开右闭区间里，不允许插入新的记录。

什么时候产生间隙锁，什么时候产生临键锁

  1. 当查询的范围内不存在记录，就是产生间隙锁。（比如上述数据执行 SELECT * FROM db_windeal.t_user WHERE  id>1 and id < 3 FOR UPDATE；
  2. 当查询的范围内存在记录，就会产生记录锁和间隙锁，加起来就是临键锁。(（比如上述数据执行 SELECT * FROM db_windeal.t_user WHERE  id>1 and id <= 3 FOR UPDATE；

临键锁示例：

  • 示例01
代码语言：sql复制
mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t_user WHERE id>3 AND id<6 FOR UPDATE;

Empty set (13.22 sec)

mysql> SHOW ENGINE INNODB STATUS ;

...

SHOW ENGINE INNODB STATUS

TABLE LOCK table `db_windeal`.`t_user` trx id 1886 lock mode IX

RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `db_windeal`.`t_user` trx id 1886 lock_mode X

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 4; hex 00000006; asc     ;;

 1: len 6; hex 000000000739; asc      9;;

 2: len 7; hex aa0000011e0128; asc (;;

 3: len 6; hex 757365723036; asc user06;;

 4: len 4; hex 8000001a; asc     ;;

...

从输出可得出，（3，6]这个区间被施加了临键锁。

  • 示例02
代码语言：javascript复制
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM t_user WHERE id>3 AND ID<=6 FOR UPDATE;
 ---- -------- ----- 
| id | name  | age |
 ---- -------- ----- 
| 6 | user06 | 26 |
 ---- -------- ----- 
1 row in set (0.03 sec)
mysql> SHOW ENGINE INNODB STATUS ;
...
SHOW ENGINE INNODB STATUS
TABLE LOCK table db_windeal.t_user trx id 1887 lock mode IX
RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table db_windeal.t_user trx id 1887 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000006; asc    ;;
1: len 6; hex 000000000739; asc      9;;
2: len 7; hex aa0000011e0128; asc (;;
3: len 6; hex 757365723036; asc user06;;
4: len 4; hex 8000001a; asc    ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 0000000a; asc    ;;
1: len 6; hex 00000000075a; asc      Z;;
2: len 7; hex 46000001880110; asc F      ;;
3: len 6; hex 757365723130; asc user10;;
4: len 4; hex 8000001e; asc    ;;
...

从输出结果可以得到，SELECT * FROM t_user WHERE id>3 AND ID<=6 FOR UPDATE;对区间（3，6], (6,10]两个区间设置了临键锁。

当检索的结果包含记录时，该记录的下一个临键锁区间也会被设置临键锁。

插入意向（间隙）锁

插入意向锁一种用于在INSERT语句进行插入行操作时，对插入行对应的区间设置的一种间隙锁。

插入意向锁是间隙锁，注意和前面提到的意向锁（表级锁）进行区分。

为什么需要插入意向锁？

  1. 为了防止幻读，在执行插入时，需要有一种锁与临键锁/间隙锁进行互斥。
  2. 如果插入也使用间隙锁，加锁的区间一次就只能插入一条记录。并发性差

插入意向锁就是基于以上原因设计的，

  1. 插入意向锁之间相互兼容。（只要没有唯一键冲突）
  2. 插入意向锁和记录锁、间隙锁、临键锁互斥。

自增锁 AUTO-INC Locks

自增锁是一种特殊的表级锁，在向带有自增列的表进行INSERT动作时使用。

在深入理解自增锁之前，我们先了解下INSERT预计的三种类别（参考InnoDB AUTO_INCREMENT Lock Modes）

  • Simple inserts： 我们可以提前知道插入行数的INSERT语句，不带子查询和 ON DUPLICATE KEY UPDATE；并且由引擎对自增列进行赋值。
  • Bulk inserts： 无法事先计算插入行数的INSERT语句，一般是带有子查询语句；
  • Mixed-mode inserts： 两种场景，1.批量插入时指定部分记录行的自增列值。2. ON DUPLICATE KEY UPDATE这两种场景我们可以预测所需自增值的最大数量。

自增锁有三种工作模式，通过innodb_autoinc_lock_mode 参数设置。

  • 传统模式 innodb_autoinc_lock_mode=0：

基于带有自增列的表的所有INSERT-statements（非事务）都需要持有自增锁。

  • 连续模式 innodb_autoinc_lock_mode=1：

连续模式是自增锁的默认模式。此模式下，Bulk inserts需要持有自增锁直到语句执行结束；而Simple inserts只需要在分配自增值时使用轻量级互斥锁（不需要持有到语句结束，也不需要表级的自增锁）。对于可以预测所需自增值的最大数量的Mixed-mode inserts，会分配超出实际数量需求的连续自增值。

  • 交叉模式 innodb_autoinc_lock_mode=2：

任何INSERT语句都不需要持有自增锁。

低级锁对象（互斥锁、读写锁）与自旋锁

前面介绍的各种锁概念都是面向表、记录。是高级的InnoDB中的高级对象。

从操作系统层面而言，或者说对内部内存数据结构的访问控制上，主要是使用互斥锁、读写锁这两种锁对象。

互斥锁-mutexes

互斥锁是排它的，一旦互斥锁被获取，其他进程、线程等就无法再获取相同的锁。

读写锁-rw-lock

读写锁是InnoDB 用来表示和强制对内部内存数据结构遵循某些规则的共享访问锁的低级对象。

读写锁类型包含三种子对象，访问控制规则如下：

  • 共享锁 s-lock：允许对相同资源进行读访问。
  • 排它锁 x-lock：提供了对公共资源的写访问，同时不允许其他线程对公共资源的不一致读、写访问。
  • 共享/排它锁 sx-lock：提供了对公共资源的写访问，同时允许其他线程对公共资源进行读访问。

自旋锁

自旋锁跟互斥锁、读写锁不是一个维度的概念。互斥锁、读写锁在尝试获取锁失败后，立即返回失败，而是等待一段时间再继续尝试获取锁。

对于单核系统而言，获取锁失败后，就只能阻塞，释放CPU等系统资源。等待一段时间后再继续检查。因为涉及到上下文的切换，性能较差。

对于多核系统而言，获取锁失败后，不需要直接进入阻塞，它可以继续拥有系统资源，进行自旋轮询一段时间。

因此，自旋锁其实是在多核系统中，获取互斥锁、读写锁失败的场景下，通过自旋轮询而非直接阻塞（切换出上下文）来持续检查、尝试是否可以获取锁的一种算法。

死锁

MySQL 出现死锁的几个要素：

  1. 互斥条件：不同事务对某个资源而持有的锁是互斥的，及一个资源只能被一个事务持有锁。
  2. 请求与保持条件：事务持有锁后，在请求新的锁时，保持持有已持有的旧锁不释放。
  3. 不剥夺条件：事务已获得的资源（持有锁），在未使用完之前，不能强行剥夺。
  4. 循环等待条件：事务之间因为持有锁和申请锁导致彼此循环等待

如何预防死锁

  1. 合理的设计索引，缩小扫描范围，缩小加锁范围，减少锁竞争。
  2. 调整事务中SQL的顺序，将update/delete等需要还有锁的语句靠后执行。
  3. 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。
  4. 以固定的顺序访问表和行。
  5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。
  6. 优化查询语句，尽量用主键、索引等进行精确查找，减少锁定范围。
  7. 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL。
云数据库SQLServer 数据库InnoDB 锁 临键锁 间隙锁

0 人点赞