MySQL5.7.11,tx_isolation为REPEATABLE-READ;
表格如下:
CREATE TABLE a (
id int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into a values(1);
在会话1中,执行如下:
begin;
select * from a where id=2 for update;
然后在会话2中,执行:
begin;
insert into a values(3);
session2被阻止了,我认为session2被阻塞是因为GAP锁定,但在information_schema.innodb_lock中,它显示了上确界伪记录和record锁;
*************************** 1. row ***************************
lock_id: 234076:115:3:1
lock_trx_id: 234076
lock_mode: X
lock_type: RECORD
lock_table: `test`.`a`
lock_index: PRIMARY
lock_space: 115
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: 234075:115:3:1
lock_trx_id: 234075
lock_mode: X
lock_type: RECORD
lock_table: `test`.`a`
lock_index: PRIMARY
lock_space: 115
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.00 sec)
MySQL_Doc描述如下:
For the last interval, the next-key lock locks the gap above the largest value in the index and the
“supremum” pseudo-record having a value higher than any value actually in the index. The supremum
is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index
value.
为什么Record Lock和Lock_data是上确界伪记录?
这个StackOverflow答案非常清楚地解释了这一点。
基本上,如果没有下一个键可用,比如当您锁定表末尾的一个范围时,MySQL将使用pseudo-record
,因为它不知道该范围的末尾。
在这种情况下,锁将阻止您添加超出SQL查询WHERE子句中使用的谓词锁边界的条目。
这篇文章对于理解如何获取这些锁也非常有用。