when exactly does a lock pseudo-record supremum occur?

Question

I need a example, please

What do you mean by applying the lock to a pseudo-record?

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks

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.

Here is an example, but:

Lock-data:supremum pseudo-record in MySQL5.7.11 with REPEATABLE-READ

I don't understand the answer to this question, it confuses me because it is a single search condition with UNIQUE INDEX

Answer 1

The supremum lock is created when the gap lock is at the end of the table. That is, your transaction locks a gap, and there is no existing record greater than the range you requested.

In the example you linked to:

Session 1:

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

insert into a values(1);

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

In the default transaction isolation level of REPEATABLE-READ, this locks a gap from id=2 to infinity, which is the supremum.

Then in session 2:

begin;
insert into a values(3);
...waits...

The gap lock held by session 1 covers everything from 2 to infinity, so it blocks an insert of id 3.

Whereas if session 1 had a transaction isolation level of READ-COMMITTED, then it doesn't acquire the gap lock.

Session 1:

set transaction_isolation='READ-COMMITTED';

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

Session 2:

mysql> insert into a values(3);
Query OK, 1 row affected (0.00 sec)