mysql lock

发布时间:2022-11-13 08:30

intention lock 意向锁,表明有事务正在或者打算锁住一行记录
record lock行所,分为排他锁和共享锁
gap lock锁住索引之间的间隙
next key lock :record lock + gap lock

for example
id :0,5,10,15,20,25
unique,a
index,b
select * from performance_schema.data_locks;

id=10
index_name lock_type lock_mode lock_status lock_data
NULL TABLE IX GRANTED NULL
PRIMARY RECORD X,REC_NOT_GAP GRANTED 10
a=10
index_name lock_type lock_mode lock_status lock_data
NULL TABLE IX GRANTED NULL
test_a_uindex RECORD X,REC_NOT_GAP GRANTED 10, 10
PRIMARY RECORD X,REC_NOT_GAP GRANTED 10
b=10
index_name lock_type lock_mode lock_status lock_data
NULL TABLE IX GRANTED NULL
test_t_index RECORD X GRANTED 10, 10
PRIMARY RECORD X,REC_NOT_GAP GRANTED 10
test_t_index RECORD X,GAP GRANTED 15, 15
id=11
index_name lock_type lock_mode lock_status lock_data
NULL TABLE IX GRANTED NULL
PRIMARY RECORD X,GAP GRANTED 15
a=11
index_name lock_type lock_mode lock_status lock_data
NULL TABLE IX GRANTED NULL
test_a_uindex RECORD X,GAP GRANTED 15, 15
b=11
index_name lock_type lock_mode lock_status lock_data
NULL TABLE IX GRANTED NULL
test_t_index RECORD X,GAP GRANTED 15, 15
id>11
index_name lock_type lock_mode lock_status lock_data
NULL TABLE IX GRANTED NULL
PRIMARY RECORD X GRANTED 0
PRIMARY RECORD X GRANTED 5
PRIMARY RECORD X GRANTED 10
PRIMARY RECORD X,GAP GRANTED 15
id<=11
index_name lock_type lock_mode lock_status lock_data
NULL TABLE IX GRANTED NULL
PRIMARY RECORD X GRANTED 0
PRIMARY RECORD X GRANTED 5
PRIMARY RECORD X GRANTED 10
PRIMARY RECORD X,GAP GRANTED 15

结论:
X:next key lock,也就是recordlock+ previous gap
x gap:previous gap
x rec not gap: rec

ItVuer - 免责声明 - 关于我们 - 联系我们

本网站信息来源于互联网,如有侵权请联系:561261067@qq.com

桂ICP备16001015号