Question 80204 in Mysql Question posted in
The official MySQL documentation can be found here.

Why locks PRIMARY supremum pseudo-record? – Mysql

mysql version is 8.0.26 and use REPEATABLE-READ.

table:

create table test
(
    id          int auto_increment primary key,
    a           varchar(256) null,
    b           int null,
    c           int null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    constraint uni_key unique (a, b, c)
);

init data: insert into test(a,b,c) values ('a',1,1);

SessionA SessionB
begin; begin;
insert into test(a,b,c) values (‘a’,1,1);
ERROR 1062 (23000): Duplicate entry insert into test(a,b,c) values (‘b’,2,3);
(blocked)

Session B will be blocked until Session A commit.

See locks info below

> SELECT ENGINE_LOCK_ID as Lock_Id,
       ENGINE_TRANSACTION_ID as Trx_id, 
       OBJECT_NAME as `Table`,
       INDEX_NAME as `Index`, 
       LOCK_DATA as Data, 
       LOCK_MODE as Mode,
       LOCK_STATUS as Status,
       LOCK_TYPE as Type         
FROM performance_schema.data_locks;

+-----------------------------------------+--------+-------+---------+------------------------+------+---------+--------+
| Lock_Id                                 | Trx_id | Table | Index   | Data                   | Mode | Status  | Type   |
+-----------------------------------------+--------+-------+---------+------------------------+------+---------+--------+
| 140135522398208:1088:140135454819312    |  15656 | test  | NULL    | NULL                   | IX   | GRANTED | TABLE  |
| 140135522398208:23:5:27:140135454816400 |  15656 | test  | uni_key | 'a', 1, 1, 64          | S    | GRANTED | RECORD |
| 140135522398208:23:4:1:140135454817088  |  15656 | test  | PRIMARY | supremum pseudo-record | X    | GRANTED | RECORD |
+-----------------------------------------+--------+-------+---------+------------------------+------+---------+--------+
3 rows in set (0.01 sec)

My doubt is why a lock to infinity is inserted on the primary key after the union index is duplicate-key.

It should be noted that if there is no duplicate key in SessionA, then SessionB can be executed successfully, so when the error of duplicate key is thrown, what does mysql do and why

Tags:

2

Answers


  1. 0

    supremum pseudo-record is locked for the next key’s generation.

    Reference: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

    Search for ‘supremum’ on this page for details.

    Login or Signup to reply.
  2. 0

    The reason for the gap lock during REPEATABLE-READ is for to prevent transactions from being replayed out of order during replication.

    If both sessions insert rows that have side-effects like auto-increment, or ON DUPLICATE KEY UPDATE, or they run triggers, then they must be replayed on a replica in the order they were executed. But the binary log is written in commit order. It’s possible the sessions are committed in a different order than the INSERTs were executed. This could result in different effects on a replica (if you use statement-based binary logs).

    The solution is to acquire gap locks during INSERT that prevent other sessions from doing concurrent updates. But these gap locks are acquired before the INSERT statement is known to succeed or fail. For example in your SessionA, the INSERT fails because of a duplicate key, but the thread has already acquired the gap lock just in case. It doesn’t release the gap lock until the end of the transaction.

    If you run the transaction for SessionA in READ-COMMITTED mode, it does not need to acquire the gap lock, because it’s assumed that replaying transactions out of order on the replica is not a problem.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search