上篇文章中对 InnoDB 存储引擎中的锁进行学习, 本文是实践部分, 根据索引和查询范围, 探究加锁范围的情况.
在本实例中, 创建简单表如下:
- MySQL> select * from t;
- +------+
- | id |
- +------+
- | 1 |
- | 3 |
- | 5 |
- | 9 |
- | 11 |
- +------+
测试结果
根据唯一索引, 非唯一索引和无索引, 以及数据库中是否存在查询边界, 进行测试. 结果如下, 其中边界 5 存在索引中, 边界 6 不在.
结论:
1. 凡是涉及范围锁, 都会在行记录上加 Next-Key Lock(X 和 GAP 锁).
2. 小于时, 唯一索引和非唯一索引加锁最大范围, 都是索引中最接近边界的下一个索引, 并且包含该索引 (Next-Key), 如索引 5, 和离边界 6 最近的索引 9;
如果边界值超过索引中最大值, 则锁定包含该边界值的所有范围.
3. 大于时, 唯一索引和非唯一索引加锁最小范围, 都是索引中最接近边界的前一个索引, 但不包含该索引, 如索引 5, 和离 6 最近的索引 5;
如果插入的值超过索引中最大值, 锁类型变为 X 锁, 被锁的最大行为 supremum pseudo-record, 它是索引中的伪记录, 表示索引中可能存在的最大值. 此时, 锁的范围扩大到正无穷.
4. 等于时,
1 唯一索引, 在索引中存在该值, 锁由 Next-Key Lock 降级为 Record 锁, 只锁住该行; 索引中不存在边界值, 锁的范围为离边界值最近的两个索引, 但不包含这两个索引, 如 5 和 9;
2 非唯一索引, 无论索引中存在该边界值与否, 都会找离边界值最近的两个索引 (边界值在索引中, 也会去前边一个索引, 如 5, 会取 3)
5. 无索引情况下, 对全表行加锁.
6. InnoDB 存储引擎中底层锁定的实际范围会根据插入值而变化, 给用户感觉锁情况为上表.
7. 如果删除范围锁中的上下边界索引 (当然是未被锁定的索引), 范围锁会动态地将锁的范围扩大到下一个索引位置.
测试过程
涉及的 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);
小结:1 加锁都是 X,GAP;2 lock_data 字段 (范围锁最大的边界), 根据插入值而缩小范围;3 lock_rec 字段为锁的行数, 插入 - 1 时会锁住 1 和 - 1, 即 2 行; 没搞清插入 2 和 4, 为何分别为 5 和 9.
锁定: 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:1 lock_data 字段根据插入值, 会缩小锁定范围;
2 当插入值超过记录的最大值, 即超过所有记录, 锁则转变为 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)
小结:1 加锁为 X 锁;2 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 存储引擎会为行记录自动生成一个隐藏的主键. 在 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. 无索引
无索引情况下, 无论大于, 小于还是等于, 都是全表行锁, 以小于举例:
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); 同上
小结:1 无索引, 锁为 X 锁;2 lock_data 为 supremum pseudo-record, 表示全表行锁;3 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. |
来源: http://www.bubuko.com/infodetail-3168690.html