MySQL/InnoDB 锁诊断：主键记录锁

InnoDB 的锁诊断是一个比较困难的事情。首先，锁机制是一种较为复杂的资源竞争管理机制，如果涉及的资源类型比较多，锁类型又比较多，那么锁机制就会看起来比较复杂。具体到，MySQL/InnoDB 上，确实也就非常复杂了。这里主要关注 InnoDB 层面的锁，涉及的内容则包括了记录锁(record)、间隙锁(gap)、索引缝隙锁(next-key)，而为什么要加锁，则又涉及到隔离级别、MVCC的实现，而锁的实现，则又与 InnoDB 底层的数据存储结构有有一定的关系，总得来说涉及的面比较多，如果对于这些概念没有了解，则比较难理解 InnoDB 的锁机制，也就比较难去排查 InnoDB 锁出现的问题。

另一个层面是排查手段。MySQL/InnoDB在早期的版本中，对于锁问题的排查手段是比较有限的，而且与很多的配置参数有关，所以了解这些参数，熟悉MySQL/InnoDB锁信息查看的一些方法，则是另一个需要了解的。

所以，关于 InnoDB 锁问题的也并不是一两个话题能够说清楚的。本文可能是一个系列（给自己挖坑），一个自己学习以及锁问题排查经验的分享。

构造主键锁竞争

记录锁，应该是 InnoDB 锁类型中较为常见，也是在READ-COMMITTED事务级别下，比较容易遇到的死锁类型（如果有死锁的话）。这里通过观察主键死锁、唯一键死锁，初步了解 InnoDB 锁信息内容，以及结构。

查看当前的隔离级别

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

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

准备表结构

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 ( 
  id int unsigned, 
  nick varchar(32),
  age int,
  primary key (id)
)

mysql> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |       |
| nick  | varchar(32)  | YES  |     | NULL    |       |
| age   | int          | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

构建锁等待

在两个会话中，按从上到下，执行下述的SQL：

时间顺序	`Session A`	`Session B`
1	`START TRANSACTION;`
2	`INSERT INTO t1 VALUES ( 1, "a",12 );`
3		`START TRANSACTION;`
4		`INSERT INTO t1 VALUES ( 1, "x",23 );`

这时候，Session B会陷入等待。

观察锁信息

通过 SHOW INNODB STATUS 观察

此时查看 InnoDB 锁信息，则有如下数据：

---TRANSACTION 10094, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 84, OS thread handle 140453961758272, query id 9262 10.88.0.1 sysb update
INSERT INTO t1 VALUES ( 1, "x",23 )
------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:
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     ;;

------------------
---TRANSACTION 10093, ACTIVE 23 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 83, OS thread handle 140454159017536, query id 9260 10.88.0.1 sysb

在 SHOW ENGINE INNODB STATUS\G中仅打印了处于等待授予状态的锁信息，即这里仅打印了事务10094的等待的锁详情。

详解锁信息

这里详细看看其中的内容：

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 LOCKS”	这是一个记录锁
`space id 27 page no 4`	该记录处于物理位置，包括页面编号，以及页面所处的物理文件编号
`n bits 72`	该页面有72个记录对应标记位
`index PRIMARY of table sysbenchdb.t1`	对应表
`trx id 10094`	所在的事务 ID
`lock mode S`	锁类型，为 `S` ，即共享锁
`locks rec but not gap`	这是一个简单记录锁，无需对记录前的“间隙”加锁

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     ;;

`Record lock`	（等待的）记录锁
`heap no 2`	在页面中，记录以堆的方式存放，该记录的堆编号
`PHYSICAL RECORD: n_fields 5;`	记录共五个字段
`0: len 4; hex 00000001; asc ;;`	`id` 字段值，为1
`1: len 6; hex 00000000276d; asc 'm;;`	该记录的`DB_TRX_ID`,即为`0x276d`，`10093`
`2: len 7; hex 810000008d0110; asc ;;`	`DB_ROLL_PTR`
`3: len 1; hex 61; asc a;;`	字段`nick`取值 `a`
`4: len 4; hex 8000000c; asc ;;`	字段`age`取值 `0x8000000c`，即`12`

这里需要注意的是，这里一共有五个字段（n_fields 5; ）。那实际这个表，只有三个字段，为什么这里会有五个字段？原因在于，InnoDB 在存储数据信息的时候，会额外的存储两个信息：DB_TRX_ID、DB_ROLL_PTR。这是两个InnoDB的较为底层的概念，具体的可以参考该文档：17.3 InnoDB Multi-Versioning。

注意到，这里的 DB_TRX_ID 取值为 0x276d，转化为10进制则为：10093。即，该条记录最后一次被修改是被事务10093所修改，即上述表格中的Session A所执行的SQL所修改。

在内置视图中查看锁信息

mysql> SELECT
    ->     ENGINE_TRANSACTION_ID AS TRX_ID,
    ->     OBJECT_NAME,
    ->     INDEX_NAME,
    ->     LOCK_TYPE,
    ->     LOCK_MODE,
    ->     LOCK_STATUS,
    ->     LOCK_DATA
    ->   FROM performance_schema.data_locks;
+--------+-------------+------------+-----------+---------------+-------------+-----------+
| TRX_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+-------------+------------+-----------+---------------+-------------+-----------+
|  10224 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|  10225 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|  10224 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
|  10225 | t1          | PRIMARY    | RECORD    | S,REC_NOT_GAP | WAITING     | 1         |
+--------+-------------+------------+-----------+---------------+-------------+-----------+

（注：这里的TRX_ID与上述不同，因为这是重新执行了上述的冲突事务）

补充说明

在本示例中，写入的数据仅为主键（也是一种唯一键），故无论是READ-COMMITTED、还是REPEATABLE-READ，其所需要的锁都相同的。

orczhou.com