mysql有关data_locks表说明
前言
最近重新看mysql行锁、表锁相关的内容时,发现关于什么情况加锁、加什么锁、什么时候事务阻塞等等有很多的规则,要分析的第一步就是要看锁相关的信息,performance_schema库的data_locks表就提供了包括持有锁、请求锁及请求的锁被谁持有等信息,借助官方文档系统看下。
文中演示均使用Mysql8.0.31,InnoDB引擎,使用8.0版本是因为它的锁信息查看替换为performance_schema.data_locks表,可以看持有锁信息。
data_locks表
初始化测试表
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
加锁并查询锁表信息:
begin;
update t set d = d+1 where id = 5;
-- 查看锁信息
select * from performance_schema.data_locks;
输出如图所示:
ENGINE
使用的存储引擎,如图InnoDb引擎。
ENGINE_LOCK_ID
存储引擎生成的锁ID,在对应存储引擎中是唯一的。但它有可能随时改变,应用不应该进行依赖。
ENGINE_TRANSACTION_ID
请求或持有锁的事务id。注意,并不表示该事务持有锁,如果锁状态是WAITING,表示只是在等待请求。
这里有个细节:
如果是只读事务,那么事务id是内部生成的比较长的随机id;
如果是写操作事务,事务id则是较短的id,读操作后跟写操作,则事务id会变。
如下:
begin;
select * from t where id = 20 lock in share mode;
select * from t where id = 20 for update;
事务id 从 281480634016296 变成了 162316。
select * from information_schema.innodb_trx;
一个扩展:
mysql对只读事务会省略生成事务id,来减少开销,识别只读事务包括:
- 显示开启read-only事务;
- autocommit为true时,单个select语句;(也会有事务日志记录,可以通过performance_schema的events_transactions_history表查看)
- 非read-only事务开启,没有update等更新语句时,也会认为是只读事务。
THREAD_ID
当前session的线程id
EVENT_ID
记录了导致锁的事件id,(THREAD_ID, EVENT_ID)可以定位到1个唯一的父事件。
OBJECT_SCHEMA
指明了加锁或请求锁的库。
OBJECT_NAME
指明了加锁或请求锁的表。
PARTITION_NAME
锁名字的分区,一般为NULL
SUBPARTITION_NAME
锁名字子分区
INDEX_NAME
指明了锁的索引名称,如上面图示例,用的主键索引。
InnoDb存储引擎下,不会为null,都是基于索引存储的,记录都是基于索引搜索的。
OBJECT_INSTANCE_BEGIN
锁在内存中的地址
LOCK_TYPE
加/申请锁的维度
InnoDb存储引擎中有 RECORD、TABLE两种,分别表示行锁和表锁。
LOCK_MODE
锁的模式,即哪种类型的锁。
InnoDb存储引擎中,包括了S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, and UNKNOWN。
([]代表了有括号内的GAP出现时表示为间隙锁,没有的时候为行锁加间隙锁)
加锁的基本单位是Next-Key锁。
举例如:
- “S/X, REC_NOT_GAP” 表示行锁;
- “S/X” 表示行锁+间隙锁 即Next-Key;
- “S/X, GAP” 表示行间隙锁;
至于是S还是X表示是共享锁还是排它锁。
LOCK_STATUS
锁的状态。
InnoDb存储引擎,包括两种:GRANTED表示持有锁、WAITING表示等待锁。
LOCK_DATA
展示了锁的数据。
InnoDb存储引擎,如果是RECORD锁会显示对应数据,否则会展示NULL。
- 如果是主键索引上,那么此列展示主键值;
- 如果是二级索引,那么此列展示二级索引值(组合索引也会全展示)和其关联的主键id
一些例子
为了看核心几个列:INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA,把查询锁的sql精简如下:
select ENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
我们先再插入1条数据,使用不同的主键值和二级索引值。
-- 2主键、3二级索引值
insert into t values(2, 3, 4);
案例一:只锁行,即行锁
主键或唯一索引的等值查询,sql如下:
begin;
select * from t where id = 1 for update;
结果如下:
可以看到:
1、在表上增加了IX(意向排它锁,关于意向锁可以看系列文章-表锁相关文章)
2、在行上增加了 “X,REC_NOT_GAP”锁,表示只加主键id=2这一行的排他锁,而不包括2之前的间隙。
案例二:间隙锁
二级索引c的等值查询,sql如下:
begin;
select * from t where c = 3 for update;
结果如下:
可以看到:
1、在索引c上加了间隙锁,索引值为5,对应主键值也为5,也即3-5的间隙。
加个这个间隙,会导致不能插入间隙数据 values(3, 4, X),因为INSERT_INTENTION。
案例三:行 + 间隙锁 = Next-Key锁
同案例二,如下圈出来的地方
可以看出:
1、给c=3,id=2这行X锁,即加上了行锁,同时c索引0-3加上间隙锁。两者合起来即 X型的 Next-Key锁。
此时新事务无法插 values(X, 2, Y);
案例四:二级索引锁不锁主键?
根据案例二可知,是会锁的,如果是覆盖索引查询呢,即只差id字段。sql如下:
begin;
select id from t where c = 3 lock in share mode;
结果发现此时并不会锁主键上的行,因为使用for update,系统会认为接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁;而lock in share mode被认为是读则不会锁主键,前提是查询覆盖索引。
总结
文章主要讲了performance_schema库的data_locks表的各个列表示的具体含义,并结合示例进行了演示。
主要的加锁类型包括,如下:
- “S/X, REC_NOT_GAP” 表示行锁;
- “S/X” 表示行锁+间隙锁 即Next-key;
- “S/X, GAP” 表示行间隙锁;
Mysql InnoDb是基于索引存储的,所以会在索引上进行加锁。