| Bug #112106 | InnoDB locks supremum when last record of page is updated | ||
|---|---|---|---|
| Submitted: | 18 Aug 2023 5:20 | Modified: | 21 Aug 2023 22:41 |
| Reporter: | Seunguck Lee | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
| Version: | 8.0.33 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | supremum | ||
[18 Aug 2023 13:22]
MySQL Verification Team
HI Mr. Lee, Thank you very much for your bug report. However, this is not a bug. This is expected behaviour and is fully described in our Reference Manual, and even more in the official standards for the transaction processing storage engines, which InnoDB upholds strictly. Any data-changing DML that affects last row, MUST lock supremum record. The same holds true for the first record and infimum record. Not a bug.
[19 Aug 2023 5:45]
Seunguck Lee
Thank you. > This is expected behaviour and is fully described in our Reference Manual, > and even more in the official standards for the transaction processing storage > engines, which InnoDB upholds strictly. > Any data-changing DML that affects last row, MUST lock supremum record. Would you please tell me the Reference Manual URL(link) which mentions this behavior ? I have tried but could not. > The same holds true for the first record and infimum record. I have done range DELETE, and done INSERT less than minimum and greater than maximum primary key values. But looks only supremum gap is blocked not infimum gap. SESSION-1> SET transaction_isolation="repeatable-read"; SESSION-1> begin; SESSION-1> delete from lock_supremum where id between 5 and 7; Query OK, 3 rows affected (0.01 sec) SESSION-2> SET transaction_isolation="repeatable-read"; SESSION-2> begin; SESSION-2> insert into lock_supremum values (1); Query OK, 1 row affected (0.00 sec) SESSION-2> insert into lock_supremum values (9); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
[21 Aug 2023 12:20]
MySQL Verification Team
Hi, You have not attempted any operation that would demand locking of the infimum.
[21 Aug 2023 22:41]
Seunguck Lee
I think, I have not attempted any operation that would demand locking of the supremum either, Because query condition requires lock from id=5 to id=7, not supremum gap. I don't think supremum gap lock is necessary for this "UPDATE .. WHERE id BETWEEN 5 AND 7" statement. > This is expected behaviour and is fully described in our Reference Manual, > and even more in the official standards for the transaction processing storage > engines, which InnoDB upholds strictly. > Any data-changing DML that affects last row, MUST lock supremum record. Again, Would you please tell me the Reference Manual URL(link) which mentions this behavior ?
[22 Aug 2023 12:29]
MySQL Verification Team
Hi, Actually, 7 is the last row in the table, so supremum is very much affected. There is a difference between infimum and supremum, in that case. When you INSERT row, it is usually supremum that has to be locked, including in the cases when the gap between last row and supremum needs to be locked. You have not executed any DML that would affect infimum. This is all described in our Reference Manual. Not a bug.
[22 Aug 2023 12:48]
Tsubasa Tanaka
I wonder why you strictly refuse to tell the reference.
[22 Aug 2023 12:50]
MySQL Verification Team
Hi, The reference is our Reference Manual, where there are many chapters devoted to InnoDB locking. If that is not sufficient , we recommend the book "Transaction Processing" by Mr. Gray.
Description: When DML using range condition updates last record of data page, InnoDB storage engine also takes lock on supremum pseudo-record. This issue also produced when DML updates last PRIMARY KEY record of table, because it's the last record of last page. Locking supremum record of intermediate pages of B-tree is not a big issue, But locking supremum of last page of B-tree can be a big issue when table use auto-increment primary key. And we usually use auto-increment primary key. When deleting(or updating) last record using range-condition, nobody can insert new record with bigger than last record's primary key value. And this might be a nontrivial performance impact. How to repeat: PREPARATION) CREATE TABLE lock_supremum ( id INT PRIMARY KEY ); INSERT INTO lock_supremum VALUES (5), (6), (7); TEST) SESSION-1) SET transaction_isolation='REPEATABLE-READ'; BEGIN; DELETE FROM lock_supremum WHERE id BETWEEN 6 AND 7; SELECT * FROM performance_schema.data_locks; +--------+----------------------------------------+-----------------------+-----------+----------+---------------+---------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+----------------------------------------+-----------------------+-----------+----------+---------------+---------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+ | INNODB | 140278598110344:1229:140279355036304 | 36703 | 48 | 22429 | test | lock_supremum | NULL | NULL | NULL | 140279355036304 | TABLE | IX | GRANTED | NULL | | INNODB | 140278598110344:63:4:3:140279361359904 | 36703 | 48 | 22434 | test | lock_supremum | NULL | NULL | PRIMARY | 140279361359904 | RECORD | X,REC_NOT_GAP | GRANTED | 6 | | INNODB | 140278598110344:63:4:1:140279361360248 | 36703 | 48 | 22451 | test | lock_supremum | NULL | NULL | PRIMARY | 140279361360248 | RECORD | X | GRANTED | supremum pseudo-record | | INNODB | 140278598110344:63:4:4:140279361360248 | 36703 | 48 | 22451 | test | lock_supremum | NULL | NULL | PRIMARY | 140279361360248 | RECORD | X | GRANTED | 7 | +--------+----------------------------------------+-----------------------+-----------+----------+---------------+---------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+ SESSION-2) INSERT INTO lock_supremum VALUES (8); ## Will wait for SESSION-1's trx Suggested fix: Release unnecessary supremum gap lock (Especially it belongs to last page of B-Tree)