orczhou.com

MySQL/InnoDB 锁诊断：间隙锁

为什么需要间隙锁

关于为什么需要 “Gap Locks” 或者 “Next-key Locks” ，在MySQL的文档“17.7.1 InnoDB Locking”的小节中有较为详细的介绍，这里不再赘述。这里使用一个具体的示例，以便开发者方便构造与观察间隙锁。

在Repeatable-Read隔离级别下，在整个事务的过程中，数据需要保持一致，经常需要使用间隙锁对数据或资源进行保护。例如，在如下的事务中：

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id int,
  nick varchar(32),
  age int,
  KEY ind_n (nick),
  PRIMARY KEY (id)
);

INSERT INTO t1 VALUES ( 1, "a", 27 );
INSERT INTO t1 VALUES ( 11, "k" ,23 );
INSERT INTO t1 VALUES ( 24, "x" ,22 );

START TRANSACTION;
SELECT * FROM t1 WHERE nick >= "k" for update;
+----+------+------+
| id | nick | age  |
+----+------+------+
| 11 | k    |   23 |
| 24 | x    |   22 |
+----+------+------+
2 rows in set (0.00 sec)  Warnings: 0

在上述事务中，为了实现将 SELECT 涉及的“相关记录”全部加上“排它锁”，从而阻止其他事务对该部分数据进行修改。即，如果有任何其他的事务，尝试修改该事务中的“相关记录”，都需要被阻塞。这里的“相关记录”，具体是指：WHERE nick >= "k" 查询扫描到的索引入口（二级索引），以及对应的数据（即主键入口）。

试想，如果有其他事务尝试写入一条 nick = 'm'的记录，那么上述的...for update语句则也会返回该记录。为了阻止上述的不一致，上述事务不仅要对单个记录或索引入口进行加锁，还需要对索引入口之间的间隙进行加锁。

具体的，该案例中详细的锁信息如下：

mysql> SELECT
    ENGINE_TRANSACTION_ID AS TRX_ID,     OBJECT_NAME,     
    INDEX_NAME, LOCK_MODE,     LOCK_STATUS,     
    LOCK_DATA    FROM performance_schema.data_locks 
  WHERE LOCK_TYPE="RECORD";
+--------+-------------+------------+---------------+-------------+------------------------+
| TRX_ID | OBJECT_NAME | INDEX_NAME | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |
+--------+-------------+------------+---------------+-------------+------------------------+
|  10703 | t1          | ind_n      | X             | GRANTED     | supremum pseudo-record |
|  10703 | t1          | ind_n      | X             | GRANTED     | 'k', 11                |
|  10703 | t1          | ind_n      | X             | GRANTED     | 'x', 24                |
|  10703 | t1          | PRIMARY    | X,REC_NOT_GAP | GRANTED     | 11                     |
|  10703 | t1          | PRIMARY    | X,REC_NOT_GAP | GRANTED     | 24                     |
+--------+-------------+------------+---------------+-------------+------------------------+

上述的锁信息显示，该事务获得了索引 (a,supremum)间隙的排它锁，以及对应的数据记录锁。

如果说上述案例中的 SELECT ... FOR UPDATE在实际中不那么常见的话，类似的直接使用UPDATE语句也需要类似的加锁信息，这里使用SELECT ... FOR UPDATE展示则更为直接。

InnoDB 中“间隙锁”的表示

理解 InnoDB 锁的困难有很多，其中一个非常大的困难就是理解在 InnoDB 间隙锁的表示，因为 InnoDB 使用了一个比较反直觉的模式去实现间隙锁。具体的：InnoDB 在表示间隙锁的时候，并没有把 GAP或Next-Key当做一个“资源”。而是依旧把Record当做资源，然后，以“锁类型的”（Lock Mode）不同的取值表示记录锁或间隙锁。例如，

  • Lock Mode取值为S,GAP时，则表示间隙锁，锁类型为S
  • Lock Mode取值为S,REC_NOT_GAP时，则表示记录锁，锁类型为S

锁的对象，均为索引入口或主键入口。

关于该实现，在InnoDB Data Locking – Part 2 “Locks”中有着较为详细的描述：

“Even without knowing too much about how databases like InnoDB operate, we can guess, that sometimes the operation involves just the record, sometimes the gap before a record, and at yet another times we need to access both, the record and a gap. One way to model that, would be to consider records and gaps to be two different kinds of resources which you can lock independently. Current InnoDB implementation takes a different approach: there is just one resource for each point, but there are multiple kinds of access right you can request for it, and the access right specifies if you need the row, the gap or both parts. One benefit of this is that it is optimized for the most common case where you need both.”

InnoDB Data Locking – Part 2 “Locks”

锁类型与加锁模式

这里使用如下表格表示了InnoDB在进行加锁时常见的加锁对象与类型。这里Dm表示具体的加锁数据，例如索引入口数据或主键数据。详细的表格如下：

LOCK_DATA
加锁目标/资源		LOCK_MODE
加锁模式		加锁对象/
资源		加锁类型
DmS,REC_NOT_GAPDmS
DmX,REC_NOT_GAPDmX
DmS,GAP(D(m-1),Dm)S
DmX,GAP(D(m-1),Dm)X
DmS(D(m-1),Dm]S
DmX(D(m-1),Dm]X

通常的，

  • 在performance_schema中看到的Dm取值可能是这样：0x000000000207
  • 在show engine innodb status\G中看到的Dm会是：0: len 4; hex 00000001; asc ;;
  • D(m-1)则表示在索引中，Dm前面的一个索引入口或主键取值
  • (...)表示开区间；(...]表示半开半闭区间

在 show engine innodb status\G 输出示例：

RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `sysbenchdb`.`t1` trx id 10094 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 00000000276d; asc     'm;;
 2: len 7; hex 810000008d0110; asc        ;;
 3: len 1; hex 61; asc a;;
 4: len 4; hex 8000000c; asc     ;;

UPDATE 语句的间隙锁

环境说明

mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

场景构造

Session ASession B
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id int,
nick varchar(32),
age int,
KEY ind_n (nick),
PRIMARY KEY (id)
);

INSERT INTO t1 VALUES ( 1, "a", 27 );
INSERT INTO t1 VALUES ( 11, "k" ,23 );
INSERT INTO t1 VALUES ( 24, "x" ,22 );
START TRANSACTION;
UPDATE t1 SET age = 127 WHERE nick = "k";

观测间隙锁

mysql> SELECT      
  ENGINE_TRANSACTION_ID AS TRX_ID,     
  OBJECT_NAME,  INDEX_NAME, LOCK_MODE,     
  LOCK_STATUS,  LOCK_DATA
FROM performance_schema.data_locks WHERE LOCK_TYPE="RECORD";
+--------+-------------+------------+---------------+-------------+-----------+
| TRX_ID | OBJECT_NAME | INDEX_NAME | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+-------------+------------+---------------+-------------+-----------+
|  10644 | t1          | ind_n      | X,GAP         | GRANTED     | 'x', 24   |
|  10644 | t1          | ind_n      | X             | GRANTED     | 'k', 11   |
|  10644 | t1          | PRIMARY    | X,REC_NOT_GAP | GRANTED     | 11        |
+--------+-------------+------------+---------------+-------------+-----------+

可以看到，在索引上（KEY ind_n (nick)）的锁范围为：(a,x)。这个范围由两个锁构成，分别是：

  • X,GAP : 'x', 24 对应的范围为 (k,x)
  • X : 'k', 11 对应的范围为 (a,k]

故，上述两个范围，共同构成的范围为：(a,x),锁类型都是排它锁（X）。

测试验证

可以尝试写入一个在 (a,k)或(k,x)范围为的记录，观察阻塞情况。继续上述的两个Session，并执行如下的SQL 语句进行观察：

Session ASession B
START TRANSACTION;
INSERT INTO t1 VALUES (2,"c",32);
-- blocking / waiting
ROLLBACK
START TRANSACTION;
INSERT INTO t1 VALUES (2,"m",32);
-- blocking / waiting
ROLLBACK

参考链接

Leave a Reply

Your email address will not be published. Required fields are marked *