MySQL-InnoDB锁（二）

上篇文章中对InnoDB存储引擎中的锁进行学习，本文是实践部分，根据索引和查询范围，探究加锁范围的情况。

在本实例中，创建简单表如下：

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    3 |
|    5 |
|    9 |
|   11 |
+------+

 测试结果

 根据唯一索引、非唯一索引和无索引，以及数据库中是否存在查询边界，进行测试。结果如下，其中边界5存在索引中，边界6不在。

结论：

1. 凡是涉及范围锁，都会在行记录上加Next-Key Lock（X和GAP锁）。其中，GAP间隙锁，防止幻读现象（一个事务中后边读的行比前一次多）

2. 小于时，唯一索引和非唯一索引加锁最大范围，都是索引中最接近边界的下一个索引，并且包含该索引（Next-Key），如索引5，和离边界6最近的索引9；

3. 大于时，唯一索引和非唯一索引加锁最小范围，都是索引中最接近边界的前一个索引，但不包含该索引，如索引5，和离6最近的索引5；

　　如果插入的值超过索引中最大值，锁类型变为X锁，被锁的最大行为supremum pseudo-record，它具有大于所有索引的值，不是真正的索引记录。此时，锁的范围扩大到正无穷。

4. 等于时，

　　① 唯一索引，在索引中存在该值，锁由Next-Key Lock降级为Record锁，只锁住该行；索引中不存在边界值，锁的范围为离边界值最近的两个索引，但不包含这两个索引，如5和9；

　　② 非唯一索引，无论索引中存在该边界值与否，都会找离边界值最近的两个索引（边界值在索引中，也会去前边一个索引，如5，会取3）

5. 无索引情况下，对全表行加锁。

6. InnoDB存储引擎中底层锁定的实际范围会根据插入值而变化，给用户感觉锁情况为上表。

7. 如果删除范围锁中的上下边界索引（当然是未被锁定的索引），范围锁会动态地将锁的范围扩大到下一个索引位置。

8. 在唯一索引或非唯一索引上加锁时，还会在对应聚集索引上的主键加锁，防止通过索引修改记录和通过主键修改记录冲突。

 测试过程

测试环境：

• MySQL版本5.7.27
• 事务隔离级别：Repeatable Read

涉及的sql：

select * from t where XXX  lock in share mode;

select * from information_schema.INNODB_LOCKS；

 

其中information.INNODB_LOCKS为MySQL自身携带的表，记录锁信息，具体表结构文章末尾。

1. 唯一索引

（1）索引中存在边界值

　　锁定：select * from t where id < 5 lock in share mode;

　　锁定范围：锁<=5，根据插入值不同，实际锁定范围不同

insert into t VALUES(4);

insert into t VALUES(2);

insert into t VALUES(-1);

小结：① 加锁都是X,GAP；② lock_data字段表示被锁定的主键（唯一索引被判定为主键），根据插入值而缩小范围；③ lock_rec字段为被锁的行号。

　　锁定：select * from t where id > 5 lock in share mode;

　　锁定范围：锁>5，实际范围不断变化； 

　　删除索引5，范围锁变为：锁 > 3，即锁会动态变化找到下一个索引的位置。

insert into t VALUES(6); 

insert into t VALUES(10);

insert into t VALUES(12);

小结1：① lock_data字段根据插入值，会缩小锁定范围；

② 当插入值超过记录的最大值，即超过所有记录，锁则转变为X锁，并且lock_data为supremum pseudo-record，锁的范围将变成（5～无穷大）。

delete from t where id in (5)  成功
insert into t VALUES(5);

小结2：删除边界索引后，范围锁会动态地找到下一个索引位置代替被删掉的索引，从而扩大锁的范围，此时再插入删除的索引，则会阻塞。

　　锁定：select * from t where id = 5 lock in share mode;

　　锁定范围：锁=5，锁定该行 

delete from t where id in (5)

小结：① 加锁为X锁；② lock_data表示锁定5。

 

（2）索引中不存在边界值

　　锁定：select * from t where id < 6 lock in share mode;

　　锁定范围：锁<=9，锁到下一个索引（即Next-Key）

insert into t VALUES(7);

　　锁定：select * from t where id > 6 lock in share mode;

　　锁定范围：锁>5

 　　具体过程和结果与上边类似。

　　锁定：select * from t where id = 6 lock in share mode;

　　锁定范围：5<锁<9

删除5，再插入5，阻塞
insert into t VALUES(5);  

删除4，再插入4，阻塞
删除3，再插入3，阻塞

 小结：上下边界索引删除，锁会动态扩展范围。

 2. 非唯一索引

具体情况与唯一索引类似，此处不再重复赘述。唯一的区别是lock_data字段不同，由于表中没有主键或唯一性索引，InnoDB存储引擎会为行记录自动生成一个隐藏的主键（6字节）。在lock_data中既包含索引值，也包含隐藏的主键，如（9, 0x000000000304） 。

　　select * from t where id < 7 lock in share mode;

　　范围：锁<=9

insert into t VALUES(6);

insert into t VALUES(4);

3. 无索引

由于id上没有索引，因此只能走聚簇索引进行全部扫描，故无论大于、小于还是等于，都是全表行锁，以小于举例：

　　select * from t where id < 5 lock in share mode;

　　范围：全表行锁

insert into t VALUES(4);

insert into t VALUES(3);        同上
insert into t VALUES(5);     同上
insert into t VALUES(100);     同上

小结：① 无索引，锁为X锁；② lock_data为 supremum pseudo-record，表示全表行锁；③ lock_index为GEN_CLUST_INDEX ，表示表的主键为存储引擎自动生成的。

 

INNODB_LOCKS表 

Column name	Description
LOCK_ID	Unique lock ID number, internal to InnoDB. Treat it as an opaque string. Although LOCK_ID currently contains TRX_ID, the format of the data in LOCK_ID is not guaranteed to remain the same in future releases. Do not write programs that parse the LOCK_ID value.
LOCK_TRX_ID	ID of the transaction holding this lock. Details about the transaction can be found by joining with INNODB_TRX on TRX_ID.
LOCK_MODE	Mode of the lock. One of S, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INC for shared, exclusive, intention shared, intention exclusive row locks, shared and exclusive gap locks, intention shared and intention exclusive gap locks, and auto-increment table level lock, respectively. Refer to the sections Section 14.5.3, “InnoDB Lock Modes” and Section 14.5.2, “The InnoDB Transaction Model and Locking” for information on InnoDB locking.
LOCK_TYPE	Type of the lock. One of RECORD or TABLE for record (row) level or table level locks, respectively.
LOCK_TABLE	Name of the table that has been locked or contains locked records.
LOCK_INDEX	Name of the index if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_SPACE	Tablespace ID of the locked record if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_PAGE	Page number of the locked record if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_REC	Heap number of the locked record within the page if LOCK_TYPE='RECORD', otherwise NULL.
LOCK_DATA	Primary key value(s) of the locked record if LOCK_TYPE='RECORD', otherwise NULL. This column contains the value(s) of the primary key column(s) in the locked row, formatted as a valid SQL string (ready to be copied to SQL commands). If there is no primary key then the InnoDB internal unique row ID number is used. If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports “supremum pseudo-record”. When the page containing the locked record is not in the buffer pool (in the case that it was paged out to disk while the lock was held), InnoDB does not fetch the page from disk, to avoid unnecessary disk operations. Instead, LOCK_DATA is set to NULL.