mysql lock

站长

2022年09月16日 10:51 · 阅读数 34

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

for exampleid :0,5,10,15,20,25unique,aindex,bselect * 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 gapx rec not gap: rec