关于java:mysql-lock

jiezi

2 年前

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

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

退出移动版