Bug #112838 INSERT ON DUPLICATE locks the first unequal record in unique secondary index

• Submitted: 26 Oct 2023 2:51
• Modified: 26 Oct 2023 9:46
• Reporter: Zarn Yao Tan
• Status: Not a Bug
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[26 Oct 2023 2:51] Zarn Yao Tan

Description:
If you execute INSERT ON DUPLICATE on a table with a unique secondary index that has a index record that is marked as deleted, and the new row has the same unique value as the marked deleted record, InnoDB will also lock the next record with an unequal value.

How to repeat:
1. Create table and prepare data

CREATE TABLE `t` (
  `pk` bigint unsigned NOT NULL,
  `uk` bigint unsigned NOT NULL,
  `value` bigint unsigned NOT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `idx_uk` (`uk`)
) ENGINE=InnoDB;

INSERT INTO `t` VALUES (1,1,1), (2,2,2);

2. Start a transaction that reads from the table

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t;
+----+----+-------+
| pk | uk | value |
+----+----+-------+
|  1 |  1 |     1 |
|  2 |  2 |     2 |
+----+----+-------+
2 rows in set (0.00 sec)

3. Delete a record from another transaction

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE pk=1;
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

4. Execute INSERT ON DUPLICATE in the transaction from Step 2 above

mysql> INSERT INTO `t` (`pk`,`uk`,`value`) VALUES (3,1,10) ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
Query OK, 1 row affected, 1 warning (0.00 sec)

5. Observe the locks

mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA, LOCK_STATUS FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+-----------+-----------+-------------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA | LOCK_STATUS |
+-----------------------+-------------+------------+-----------+-----------+-----------+-------------+
|                 43306 | t           | NULL       | TABLE     | IX        | NULL      | GRANTED     |
|                 43306 | t           | idx_uk     | RECORD    | X         | 1, 1      | GRANTED     |
|                 43306 | t           | idx_uk     | RECORD    | X         | 2, 2      | GRANTED     |
|                 43306 | t           | idx_uk     | RECORD    | X,GAP     | 1, 3      | GRANTED     |
+-----------------------+-------------+------------+-----------+-----------+-----------+-------------+
4 rows in set (0.00 sec)

We can see that the transaction is holding an exclusive next-key lock on the index record (2,2) which is the first unequal record after (1,3).

Suggested fix:
Similar to this fix (https://github.com/mysql/mysql-server/commit/506d587ae453e1917505a91f3089a6453c6662af), can InnoDB only acquire the gap lock before the first unequal record?

[26 Oct 2023 9:46] MySQL Verification Team

Hi Mr. Tan,

Thank you for your bug report.

However, this is not a bug, nor can it be a feature request.

Simply, when, in the transactional engine, you add a record that is to take the last spot, then the gap between the previous record and infimum has to be locked, so that space is reserved.

This is explained in our Reference Manual and in the famous Gray's book "Transaction Processing".

Not a bug.

[26 Oct 2023 9:46] MySQL Verification Team

Hi Mr. Tan,

Thank you for your bug report.

However, this is not a bug, nor can it be a feature request.

Simply, when, in the transactional engine, you add a record that is to take the last spot, then the gap between the previous record and infimum has to be locked, so that space is reserved.

This is explained in our Reference Manual and in the famous Gray's book "Transaction Processing".

Not a bug.

[26 Oct 2023 9:46] MySQL Verification Team

Hi Mr. Tan,

Thank you for your bug report.

However, this is not a bug, nor can it be a feature request.

Simply, when, in the transactional engine, you add a record that is to take the last spot, then the gap between the previous record and infimum has to be locked, so that space is reserved.

This is explained in our Reference Manual and in the famous Gray's book "Transaction Processing".

Not a bug.