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

• Submitted: 7 Mar 2:41
• Modified: 24 Mar 18:02
• Reporter: Donhun Shin
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.4
• OS: MacOS
• 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                      |

[11 Mar 23:02] MySQL Verification Team

Hi,

What part of this behavior you believe is a bug? Do you think our documentation should explain this better or you believe the behavior needs to be different, and if so, why?

Thanks.

[12 Mar 7:25] Yichang SONG

Each leaf page in InnoDB contains a supremum pseudo-record. When a range query (SELECT ... FOR UPDATE) spans multiple pages, InnoDB locks all accessed pages' supremum records to prevent phantom inserts, as dictated by its Next-Key Locking mechanism

[24 Mar 11:33] Jakub Lopuszanski

I am sorry, but I don't see the presented behaviour as a bug.
There seems to be some confusion.

Each page has its own supremum pseudo-record.

The results of querying performance_schema.data_locks, are not guaranteed to be sorted in any particular way. That one row appears before another, doesn't mean much. Conceptually, supremum is after all of the other records on the page, but technically, it has "heap_no==1", which means the bit in the bitmap which corresponds to it is before the bits for user records, so when performance_schema's logic iterates over a bitmap, it will first notice and report the lock request for supremum, and only later those for other records on a given page. But, this is a low-level implementation detail, which you shouldn't care much about. Anyway, if you are curious you can:
- take a look at ENGINE_LOCK_ID which encodes among other things the space_id:page_no:heap_no
- take a look at OBJECT_INSTANCE_BEGIN to see the identifier of the bitmap object
```
SELECT ENGINE_LOCK_ID, OBJECT_INSTANCE_BEGIN, INDEX_NAME, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks LIMIT 3;
+---------------------------------------+-----------------------+------------+-----------+------------------------+
| ENGINE_LOCK_ID                        | OBJECT_INSTANCE_BEGIN | INDEX_NAME | LOCK_MODE | LOCK_DATA              |
+---------------------------------------+-----------------------+------------+-----------+------------------------+
| 2073949831216:84:1072:2073899994408   |         2073899994408 | NULL       | IX        | NULL                   |
| 2073949831216:84:10:5:1:2073899991496 |         2073899991496 | PRIMARY    | X         | supremum pseudo-record |
| 2073949831216:84:10:5:2:2073899991496 |         2073899991496 | PRIMARY    | X         | 1                      |
+---------------------------------------+-----------------------+------------+-----------+------------------------+
```
See: both of them are in the same bitmap, and one is at heap_no 1, and the other at heap_no 2, that's why we've chosen to report them in this order, but it is up to us, unless you specify an ORDER BY clause.

As for why there are locks supremum when you use CHAR(250), but there are none for VARCHAR(250) the answer is simple:
CHAR(250) always takes a lot of space, even if the string is just "dummy", so you can only fit around 56 or so rows on a single page (also, there's no rule that every page has to have same number of rows, and as you can see the distribution is quite uneven).
VARCHAR(250) only uses what it has to, so all of your data has fit in one page.

And we only take a supremum lock if it is part of the scanned range, i.e. if the range span across more than one page.
Notice, that even in the case of CHAR(250) we are *not* locking the supremum after the last record:
```
| 2073949831216:84:10:8:14:2073899992552 | PRIMARY    | X         | GRANTED     | 150                    |
| 2073949831216:84:10:7:1:2073899992200  | PRIMARY    | X         | GRANTED     | supremum pseudo-record |
| 2073949831216:84:10:7:2:2073899992200  | PRIMARY    | X         | GRANTED     | 83                     |
```
the supremum locked is from page 7, while the last record (150) is on page 8.

[24 Mar 18:02] Donhun Shin

Thank you so much for your kind and helpful response :)