Bug #117637 InnoDB lock on supremum even though it is not the last data in the index.

• Submitted: 7 Mar 2:41
• Modified: 7 Mar 2:57
• Reporter: Donhun Shin
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.4
• OS: MacOS
• Assigned to: MySQL Verification Team
• CPU Architecture: Any
• Tags: gap lock, innodb, supremum, 다음 키 잠금

[7 Mar 2:41] Donhun Shin

Description:
According to mysql official documentation, when DML using range condition updates last record of data page, InnoDB storage engine also takes lock on supremum pseudo-record.

However, it is also setting a lock on lock on supremum pseudo-record even if it is not the last record on the data page. (See TEST 1).

But this doesn't happen in all cases, which confuses me even more.
See TEST 2 below for an example of the majority of cases where it doesn't happen.

How to repeat:
PREPARATION 1)
    CREATE TABLE lock_supremum (
                                id INT NOT NULL AUTO_INCREMENT,
                                fd1 CHAR(250) NOT NULL,
                                PRIMARY KEY (id)
    ) CHARSET=latin1 COLLATE=latin1_bin;

    INSERT INTO lock_supremum SELECT NULL, 'dummy'
    FROM information_schema.COLUMNS LIMIT 300;

TEST 1-1)
set autocommit=0;
SELECT * FROM lock_supremum WHERE id BETWEEN 0 AND 150 FOR UPDATE;
SELECT INDEX_NAME, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

RESULT 1) - locking for supremum pseudo-record. (Abnormal case)
+------------+-----------+-------------+------------------------+
| INDEX_NAME | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+------------+-----------+-------------+------------------------+
| NULL       | IX        | GRANTED     | NULL                   |
| PRIMARY    | X         | GRANTED     | supremum pseudo-record |
| PRIMARY    | X         | GRANTED     | 1                      |
| PRIMARY    | X         | GRANTED     | 2                      |
| PRIMARY    | X         | GRANTED     | 3                      |
| PRIMARY    | X         | GRANTED     | 4                      |
| PRIMARY    | X         | GRANTED     | 5                      |
| PRIMARY    | X         | GRANTED     | 6                      |
| PRIMARY    | X         | GRANTED     | 7                      |
| PRIMARY    | X         | GRANTED     | 8                      |
| PRIMARY    | X         | GRANTED     | 9                      |
| PRIMARY    | X         | GRANTED     | 10                     |
| PRIMARY    | X         | GRANTED     | 11                     |
| PRIMARY    | X         | GRANTED     | 12                     |
| PRIMARY    | X         | GRANTED     | 13                     |
| PRIMARY    | X         | GRANTED     | 14                     |
| PRIMARY    | X         | GRANTED     | 15                     |
| PRIMARY    | X         | GRANTED     | 16                     |
| PRIMARY    | X         | GRANTED     | 17                     |
| PRIMARY    | X         | GRANTED     | 18                     |
| PRIMARY    | X         | GRANTED     | 19                     |
| PRIMARY    | X         | GRANTED     | 20                     |
| PRIMARY    | X         | GRANTED     | 21                     |
| PRIMARY    | X         | GRANTED     | 22                     |
| PRIMARY    | X         | GRANTED     | 23                     |
| PRIMARY    | X         | GRANTED     | 24                     |
| PRIMARY    | X         | GRANTED     | 25                     |
| PRIMARY    | X         | GRANTED     | 26                     |
| PRIMARY    | X         | GRANTED     | 27                     |
| PRIMARY    | X         | GRANTED     | supremum pseudo-record |
| PRIMARY    | X         | GRANTED     | 28                     |
| PRIMARY    | X         | GRANTED     | 29                     |
| PRIMARY    | X         | GRANTED     | 30                     |
| PRIMARY    | X         | GRANTED     | 31                     |
| PRIMARY    | X         | GRANTED     | 32                     |
| PRIMARY    | X         | GRANTED     | 33                     |
| PRIMARY    | X         | GRANTED     | 34                     |
| PRIMARY    | X         | GRANTED     | 35                     |
| PRIMARY    | X         | GRANTED     | 36                     |
| PRIMARY    | X         | GRANTED     | 37                     |
| PRIMARY    | X         | GRANTED     | 38                     |
| PRIMARY    | X         | GRANTED     | 39                     |
| PRIMARY    | X         | GRANTED     | 40                     |
| PRIMARY    | X         | GRANTED     | 41                     |
| PRIMARY    | X         | GRANTED     | 42                     |
| PRIMARY    | X         | GRANTED     | 43                     |
| PRIMARY    | X         | GRANTED     | 44                     |
| PRIMARY    | X         | GRANTED     | 45                     |
| PRIMARY    | X         | GRANTED     | 46                     |
| PRIMARY    | X         | GRANTED     | 47                     |
| PRIMARY    | X         | GRANTED     | 48                     |
| PRIMARY    | X         | GRANTED     | 49                     |
| PRIMARY    | X         | GRANTED     | 50                     |
| PRIMARY    | X         | GRANTED     | 51                     |
| PRIMARY    | X         | GRANTED     | 52                     |
| PRIMARY    | X         | GRANTED     | 53                     |
| PRIMARY    | X         | GRANTED     | 54                     |
| PRIMARY    | X         | GRANTED     | 55                     |
| PRIMARY    | X         | GRANTED     | 56                     |
| PRIMARY    | X         | GRANTED     | 57                     |
| PRIMARY    | X         | GRANTED     | 58                     |
| PRIMARY    | X         | GRANTED     | 59                     |
| PRIMARY    | X         | GRANTED     | 60                     |
| PRIMARY    | X         | GRANTED     | 61                     |
| PRIMARY    | X         | GRANTED     | 62                     |
| PRIMARY    | X         | GRANTED     | 63                     |
| PRIMARY    | X         | GRANTED     | 64                     |
| PRIMARY    | X         | GRANTED     | 65                     |
| PRIMARY    | X         | GRANTED     | 66                     |
| PRIMARY    | X         | GRANTED     | 67                     |
| PRIMARY    | X         | GRANTED     | 68                     |
| PRIMARY    | X         | GRANTED     | 69                     |
| PRIMARY    | X         | GRANTED     | 70                     |
| PRIMARY    | X         | GRANTED     | 71                     |
| PRIMARY    | X         | GRANTED     | 72                     |
| PRIMARY    | X         | GRANTED     | 73                     |
| PRIMARY    | X         | GRANTED     | 74                     |
| PRIMARY    | X         | GRANTED     | 75                     |
| PRIMARY    | X         | GRANTED     | 76                     |
| PRIMARY    | X         | GRANTED     | 77                     |
| PRIMARY    | X         | GRANTED     | 78                     |
| PRIMARY    | X         | GRANTED     | 79                     |
| PRIMARY    | X         | GRANTED     | 80                     |
| PRIMARY    | X         | GRANTED     | 81                     |
| PRIMARY    | X         | GRANTED     | 82                     |
| PRIMARY    | X         | GRANTED     | supremum pseudo-record |
| PRIMARY    | X         | GRANTED     | 83                     |
| PRIMARY    | X         | GRANTED     | 84                     |
| PRIMARY    | X         | GRANTED     | 85                     |
| PRIMARY    | X         | GRANTED     | 86                     |
| PRIMARY    | X         | GRANTED     | 87                     |
| PRIMARY    | X         | GRANTED     | 88                     |
| PRIMARY    | X         | GRANTED     | 89                     |
| PRIMARY    | X         | GRANTED     | 90                     |
| PRIMARY    | X         | GRANTED     | 91                     |
| PRIMARY    | X         | GRANTED     | 92                     |
| PRIMARY    | X         | GRANTED     | 93                     |
| PRIMARY    | X         | GRANTED     | 94                     |
| PRIMARY    | X         | GRANTED     | 95                     |
| PRIMARY    | X         | GRANTED     | 96                     |
| PRIMARY    | X         | GRANTED     | 97                     |
| PRIMARY    | X         | GRANTED     | 98                     |
| PRIMARY    | X         | GRANTED     | 99                     |
| PRIMARY    | X         | GRANTED     | 100                    |
| PRIMARY    | X         | GRANTED     | 101                    |
| PRIMARY    | X         | GRANTED     | 102                    |
| PRIMARY    | X         | GRANTED     | 103                    |
| PRIMARY    | X         | GRANTED     | 104                    |
| PRIMARY    | X         | GRANTED     | 105                    |
| PRIMARY    | X         | GRANTED     | 106                    |
| PRIMARY    | X         | GRANTED     | 107                    |
| PRIMARY    | X         | GRANTED     | 108                    |
| PRIMARY    | X         | GRANTED     | 109                    |
| PRIMARY    | X         | GRANTED     | 110                    |
| PRIMARY    | X         | GRANTED     | 111                    |
| PRIMARY    | X         | GRANTED     | 112                    |
| PRIMARY    | X         | GRANTED     | 113                    |
| PRIMARY    | X         | GRANTED     | 114                    |
| PRIMARY    | X         | GRANTED     | 115                    |
| PRIMARY    | X         | GRANTED     | 116                    |
| PRIMARY    | X         | GRANTED     | 117                    |
| PRIMARY    | X         | GRANTED     | 118                    |
| PRIMARY    | X         | GRANTED     | 119                    |
| PRIMARY    | X         | GRANTED     | 120                    |
| PRIMARY    | X         | GRANTED     | 121                    |
| PRIMARY    | X         | GRANTED     | 122                    |
| PRIMARY    | X         | GRANTED     | 123                    |
| PRIMARY    | X         | GRANTED     | 124                    |
| PRIMARY    | X         | GRANTED     | 125                    |
| PRIMARY    | X         | GRANTED     | 126                    |
| PRIMARY    | X         | GRANTED     | 127                    |
| PRIMARY    | X         | GRANTED     | 128                    |
| PRIMARY    | X         | GRANTED     | 129                    |
| PRIMARY    | X         | GRANTED     | 130                    |
| PRIMARY    | X         | GRANTED     | 131                    |
| PRIMARY    | X         | GRANTED     | 132                    |
| PRIMARY    | X         | GRANTED     | 133                    |
| PRIMARY    | X         | GRANTED     | 134                    |
| PRIMARY    | X         | GRANTED     | 135                    |
| PRIMARY    | X         | GRANTED     | 136                    |
| PRIMARY    | X         | GRANTED     | 137                    |
| PRIMARY    | X         | GRANTED     | 138                    |
| PRIMARY    | X         | GRANTED     | 139                    |
| PRIMARY    | X         | GRANTED     | 140                    |
+------------+-----------+-------------+------------------------+
144 rows in set (0.00 sec)

TEST 1-2)
-- The case where supremum pseudo-record is not locked between 82 and 83.
set autocommit=0;
SELECT * FROM lock_supremum WHERE id BETWEEN 0 AND 85 FOR UPDATE;
SELECT INDEX_NAME, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

RESULT 1-2)
...(skip)
| PRIMARY    | X         | GRANTED     | 76                     |
| PRIMARY    | X         | GRANTED     | 77                     |
| PRIMARY    | X         | GRANTED     | 78                     |
| PRIMARY    | X         | GRANTED     | 79                     |
| PRIMARY    | X         | GRANTED     | 80                     |
| PRIMARY    | X         | GRANTED     | 81                     |
| PRIMARY    | X         | GRANTED     | 82                     |
| PRIMARY    | X         | GRANTED     | 83                     |
| PRIMARY    | X         | GRANTED     | 84                     |
| PRIMARY    | X         | GRANTED     | 85                     |
+------------+-----------+-------------+------------------------+
88 rows in set (0.01 sec)

PREPARATION 2)
    CREATE TABLE lock_supremum2 (
                                id INT NOT NULL AUTO_INCREMENT,
                                fd1 varchar(250) NOT NULL,
                                PRIMARY KEY (id)
    ) CHARSET=latin1 COLLATE=latin1_bin;

    INSERT INTO lock_supremum2 SELECT NULL, 'dummy'
    FROM information_schema.COLUMNS LIMIT 300;

TEST 2)
set autocommit=0;
SELECT * FROM lock_supremum2 WHERE id BETWEEN 0 AND 150 FOR UPDATE;
SELECT ENGINE_LOCK_ID, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

RESULT 2) - no locking for supremum pseudo-record. (Normal case)
mysql> SELECT INDEX_NAME, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+------------+-----------+-------------+-----------+
| INDEX_NAME | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+------------+-----------+-------------+-----------+
| NULL       | IX        | GRANTED     | NULL      |
| PRIMARY    | X         | GRANTED     | 1         |
| PRIMARY    | X         | GRANTED     | 2         |
| PRIMARY    | X         | GRANTED     | 3         |
| PRIMARY    | X         | GRANTED     | 4         |
| PRIMARY    | X         | GRANTED     | 5         |
| PRIMARY    | X         | GRANTED     | 6         |
| PRIMARY    | X         | GRANTED     | 7         |
| PRIMARY    | X         | GRANTED     | 8         |
| PRIMARY    | X         | GRANTED     | 9         |
| PRIMARY    | X         | GRANTED     | 10        |
| PRIMARY    | X         | GRANTED     | 11        |
| PRIMARY    | X         | GRANTED     | 12        |
| PRIMARY    | X         | GRANTED     | 13        |
| PRIMARY    | X         | GRANTED     | 14        |
| PRIMARY    | X         | GRANTED     | 15        |
| PRIMARY    | X         | GRANTED     | 16        |
| PRIMARY    | X         | GRANTED     | 17        |
| PRIMARY    | X         | GRANTED     | 18        |
| PRIMARY    | X         | GRANTED     | 19        |
| PRIMARY    | X         | GRANTED     | 20        |
| PRIMARY    | X         | GRANTED     | 21        |
| PRIMARY    | X         | GRANTED     | 22        |
| PRIMARY    | X         | GRANTED     | 23        |
| PRIMARY    | X         | GRANTED     | 24        |
| PRIMARY    | X         | GRANTED     | 25        |
| PRIMARY    | X         | GRANTED     | 26        |
| PRIMARY    | X         | GRANTED     | 27        |
| PRIMARY    | X         | GRANTED     | 28        |
| PRIMARY    | X         | GRANTED     | 29        |
| PRIMARY    | X         | GRANTED     | 30        |
| PRIMARY    | X         | GRANTED     | 31        |
| PRIMARY    | X         | GRANTED     | 32        |
| PRIMARY    | X         | GRANTED     | 33        |
| PRIMARY    | X         | GRANTED     | 34        |
| PRIMARY    | X         | GRANTED     | 35        |
| PRIMARY    | X         | GRANTED     | 36        |
| PRIMARY    | X         | GRANTED     | 37        |
| PRIMARY    | X         | GRANTED     | 38        |
| PRIMARY    | X         | GRANTED     | 39        |
| PRIMARY    | X         | GRANTED     | 40        |
| PRIMARY    | X         | GRANTED     | 41        |
| PRIMARY    | X         | GRANTED     | 42        |
| PRIMARY    | X         | GRANTED     | 43        |
| PRIMARY    | X         | GRANTED     | 44        |
| PRIMARY    | X         | GRANTED     | 45        |
| PRIMARY    | X         | GRANTED     | 46        |
| PRIMARY    | X         | GRANTED     | 47        |
| PRIMARY    | X         | GRANTED     | 48        |
| PRIMARY    | X         | GRANTED     | 49        |
| PRIMARY    | X         | GRANTED     | 50        |
| PRIMARY    | X         | GRANTED     | 51        |
| PRIMARY    | X         | GRANTED     | 52        |
| PRIMARY    | X         | GRANTED     | 53        |
| PRIMARY    | X         | GRANTED     | 54        |
| PRIMARY    | X         | GRANTED     | 55        |
| PRIMARY    | X         | GRANTED     | 56        |
| PRIMARY    | X         | GRANTED     | 57        |
| PRIMARY    | X         | GRANTED     | 58        |
| PRIMARY    | X         | GRANTED     | 59        |
| PRIMARY    | X         | GRANTED     | 60        |
| PRIMARY    | X         | GRANTED     | 61        |
| PRIMARY    | X         | GRANTED     | 62        |
| PRIMARY    | X         | GRANTED     | 63        |
| PRIMARY    | X         | GRANTED     | 64        |
| PRIMARY    | X         | GRANTED     | 65        |
| PRIMARY    | X         | GRANTED     | 66        |
| PRIMARY    | X         | GRANTED     | 67        |
| PRIMARY    | X         | GRANTED     | 68        |
| PRIMARY    | X         | GRANTED     | 69        |
| PRIMARY    | X         | GRANTED     | 70        |
| PRIMARY    | X         | GRANTED     | 71        |
| PRIMARY    | X         | GRANTED     | 72        |
| PRIMARY    | X         | GRANTED     | 73        |
| PRIMARY    | X         | GRANTED     | 74        |
| PRIMARY    | X         | GRANTED     | 75        |
| PRIMARY    | X         | GRANTED     | 76        |
| PRIMARY    | X         | GRANTED     | 77        |
| PRIMARY    | X         | GRANTED     | 78        |
| PRIMARY    | X         | GRANTED     | 79        |
| PRIMARY    | X         | GRANTED     | 80        |
| PRIMARY    | X         | GRANTED     | 81        |
| PRIMARY    | X         | GRANTED     | 82        |
| PRIMARY    | X         | GRANTED     | 83        |
| PRIMARY    | X         | GRANTED     | 84        |
| PRIMARY    | X         | GRANTED     | 85        |
| PRIMARY    | X         | GRANTED     | 86        |
| PRIMARY    | X         | GRANTED     | 87        |
| PRIMARY    | X         | GRANTED     | 88        |
| PRIMARY    | X         | GRANTED     | 89        |
| PRIMARY    | X         | GRANTED     | 90        |
| PRIMARY    | X         | GRANTED     | 91        |
| PRIMARY    | X         | GRANTED     | 92        |
| PRIMARY    | X         | GRANTED     | 93        |
| PRIMARY    | X         | GRANTED     | 94        |
| PRIMARY    | X         | GRANTED     | 95        |
| PRIMARY    | X         | GRANTED     | 96        |
| PRIMARY    | X         | GRANTED     | 97        |
| PRIMARY    | X         | GRANTED     | 98        |
| PRIMARY    | X         | GRANTED     | 99        |
| PRIMARY    | X         | GRANTED     | 100       |
| PRIMARY    | X         | GRANTED     | 101       |
| PRIMARY    | X         | GRANTED     | 102       |
| PRIMARY    | X         | GRANTED     | 103       |
| PRIMARY    | X         | GRANTED     | 104       |
| PRIMARY    | X         | GRANTED     | 105       |
| PRIMARY    | X         | GRANTED     | 106       |
| PRIMARY    | X         | GRANTED     | 107       |
| PRIMARY    | X         | GRANTED     | 108       |
| PRIMARY    | X         | GRANTED     | 109       |
| PRIMARY    | X         | GRANTED     | 110       |
| PRIMARY    | X         | GRANTED     | 111       |
| PRIMARY    | X         | GRANTED     | 112       |
| PRIMARY    | X         | GRANTED     | 113       |
| PRIMARY    | X         | GRANTED     | 114       |
| PRIMARY    | X         | GRANTED     | 115       |
| PRIMARY    | X         | GRANTED     | 116       |
| PRIMARY    | X         | GRANTED     | 117       |
| PRIMARY    | X         | GRANTED     | 118       |
| PRIMARY    | X         | GRANTED     | 119       |
| PRIMARY    | X         | GRANTED     | 120       |
| PRIMARY    | X         | GRANTED     | 121       |
| PRIMARY    | X         | GRANTED     | 122       |
| PRIMARY    | X         | GRANTED     | 123       |
| PRIMARY    | X         | GRANTED     | 124       |
| PRIMARY    | X         | GRANTED     | 125       |
| PRIMARY    | X         | GRANTED     | 126       |
| PRIMARY    | X         | GRANTED     | 127       |
| PRIMARY    | X         | GRANTED     | 128       |
| PRIMARY    | X         | GRANTED     | 129       |
| PRIMARY    | X         | GRANTED     | 130       |
| PRIMARY    | X         | GRANTED     | 131       |
| PRIMARY    | X         | GRANTED     | 132       |
| PRIMARY    | X         | GRANTED     | 133       |
| PRIMARY    | X         | GRANTED     | 134       |
| PRIMARY    | X         | GRANTED     | 135       |
| PRIMARY    | X         | GRANTED     | 136       |
| PRIMARY    | X         | GRANTED     | 137       |
| PRIMARY    | X         | GRANTED     | 138       |
| PRIMARY    | X         | GRANTED     | 139       |
| PRIMARY    | X         | GRANTED     | 140       |
| PRIMARY    | X         | GRANTED     | 141       |
| PRIMARY    | X         | GRANTED     | 142       |
| PRIMARY    | X         | GRANTED     | 143       |
| PRIMARY    | X         | GRANTED     | 144       |
| PRIMARY    | X         | GRANTED     | 145       |
| PRIMARY    | X         | GRANTED     | 146       |
| PRIMARY    | X         | GRANTED     | 147       |
| PRIMARY    | X         | GRANTED     | 148       |
| PRIMARY    | X         | GRANTED     | 149       |
| PRIMARY    | X         | GRANTED     | 150       |
+------------+-----------+-------------+-----------+
151 rows in set (0.00 sec)

Suggested fix:
Release supremum gap locks at unnecessary intermediate stages.

[7 Mar 2:44] Donhun Shin

Before)
However, it is also setting a lock on lock on supremum pseudo-record even if it is not the last record on the data page. (See TEST 1).

Fix)
However, InnoDB is also takes lock on supremum pseudo-record even if it is not the last record on the data page. (See TEST 1).

[7 Mar 2:57] Donhun Shin

And I want to know why InnoDB takes lock on supremum pseudo-record Before `id=1` index 

| NULL       | IX        | GRANTED     | NULL                   |
| PRIMARY    | X         | GRANTED     | supremum pseudo-record | <-- Why??
| PRIMARY    | X         | GRANTED     | 1                      |