1

MySQL5.7.11, tx_isolation is REPEATABLE-READ;

Table like this:

CREATE TABLE a (
  id int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into a values(1);

in session1, execute like this:

begin;
select * from a where id=2 for update;

then in session2, execute:

begin;
insert into a values(3);

session2 is blocked, and I think session2 is blocked because the GAP lock, but in information_schema.innodb_lock, it shows supremum pseudo-record and RECORD Lock;

*************************** 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 describes it as follow:

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.

why is the Record Lock and the lock_data is supremum pseudo-record?

Wang Wen'an
  • 312
  • 2
  • 8

1 Answers1

0

This StackOverflow answer explains it very clearly.

Basically, if there is no next key available, like when you are locking a range that's at the end of your table, then MySQL will use the pseudo-record since it doesn't know where the range ends.

In this case, the lock will prevent you from adding entries beyond the boundaries of the predicate lock you used in the SQL query WHERE clause.

This article is also very useful to understand how these locks are taken.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • Yeah, `supremum pseudo-record` is clear; and another question is why the `RECORD X` prevent the insert in session2, not the `GAP` between the first row and `supremum pseudo-record`; – Wang Wen'an Mar 04 '17 at 14:37
  • hi @Vlad Mihalcea excellent, +1. one question, When MySQL knows that there will be gap locking, is it always going to look for the next key? thanks in advance –  Jan 05 '23 at 16:59
  • @Daniel Check out [this article](https://vincepergolizzi.com/programming/2020/09/02/mysql-locking-reads.html) for more details. – Vlad Mihalcea Jan 05 '23 at 17:56