Next-Key Lock浅析「实验一」

这是我参与8月更文挑战的第5天，活动详情查看：8月更文挑战

这是为 lock 加锁实现，设计的实验。

🌰

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `comment` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

预先插几个数据：

insert user select 20, 333, 333;
insert user select 25, 555, 555;
insert user select 20, 999, 999;

实验一

验证非唯一索引(二级索引)加锁情况

先看看如何检测到 lock 的存在？

-- mysql 8.0
SELECT * FROM sys.`innodb_lock_waits` \G

下面正式开始实验：

session 1 → 当前读查询

begin;
select * from user where name = '555' for update;

session 2 → 插入新的 record

begin;
insert user select 31,'556','556';
-- 开始阻塞
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

检测当前的 lock 情况：

***************************[ 1. row ]***************************
wait_started                 | 2021-08-13 14:32:25
wait_age                     | 0:00:27
wait_age_secs                | 27
locked_table                 | `test2`.`user`
locked_table_schema          | test2
locked_table_name            | user
locked_table_partition       | <null>
locked_table_subpartition    | <null>
locked_index                 | index_name
locked_type                  | RECORD
waiting_trx_id               | 11568
waiting_trx_started          | 2021-08-13 13:50:06
waiting_trx_age              | 0:42:46
waiting_trx_rows_locked      | 6
waiting_trx_rows_modified    | 1
waiting_pid                  | 30
waiting_query                | insert user select 31,'556','556'
waiting_lock_id              | 140068615962864:4:5:4:140068547280720
waiting_lock_mode            | X,GAP,INSERT_INTENTION
blocking_trx_id              | 11567
blocking_pid                 | 25
blocking_query               | <null>
blocking_lock_id             | 140068615962008:4:5:4:140068547273120
blocking_lock_mode           | X,GAP
blocking_trx_started         | 2021-08-13 13:49:38
blocking_trx_age             | 0:43:14
blocking_trx_rows_locked     | 3
blocking_trx_rows_modified   | 0
sql_kill_blocking_query      | KILL QUERY 25
sql_kill_blocking_connection | KILL 25

我们先明确几个加锁分析的规则：

1. 清楚当前事务隔离级别

2. SQL 当前执行使用的 index (聚簇索引，唯一二级索引，普通二级索引，还是没有index)

3. 是否精确匹配

   1. 直观来说：单点匹配区间
   2. 比如：where a = 1

4. 是否唯一性匹配

   1. 聚簇索引
   2. 唯一二级索引

然后我们明确几个加锁规则：

本文我们都在 RR 隔离级别讨论，这也是 Mysql 默认隔离级别

1. 锁定读 → next-key lock

2. 在回传 server 端时，如果其余搜索条件不匹配(除了 index 的搜索条件)，RR 隔离级别下是不会释放当前该记录的 next-key lock

3. 如果执行的是 二级索引 锁定读：

   1. 先对 二级索引 上的满足要求的记录，以及读过区域中的记录 → next-key lock
   2. 会有 index condition push 优化，如果在 ICP 判断下都没有满足，则不会进入回表阶段 → 也就不会有 聚簇索引 的加锁过程
   3. 回表到 聚簇索引 → record key