| Bug #116503 | duplicate-key error will hold gap lock when there are multi unique keys | ||
|---|---|---|---|
| Submitted: | 30 Oct 2024 6:55 | Modified: | 31 Oct 2024 14:11 |
| Reporter: | Xiaolong Wang | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[30 Oct 2024 10:14]
MySQL Verification Team
Hi Mr. Wang, Thank you for your bug report. We truly do not understand what do you mean by primary key not existing for some row in InnoDB ??? For each record or gap lock there is a corresponding primary key. If user does not define a primary key for InnoDB, InnoDB will create an invisible primary key. There can be no InnoDB table without a primary key. Can you clarify that ????
[30 Oct 2024 10:17]
MySQL Verification Team
Mr Wang, Gap locks are VERY much necessary for the INSERT operation, whenever a new row has to be inserted between two existing rows. Hence, gap locks are always necessary.
[31 Oct 2024 14:11]
Xiaolong Wang
Hi, Just like my first case shows, when INSERT (2,1), there is Duplicate entry error for `1`, and `2` does not exist in Primary Key. There are gap lock between Primary Key 1 to 5. However this gap lock does not match the document description: "If a duplicate-key error occurs, a shared lock on the duplicate index record is set." I totally understand gap locks are very much necessary, but in this special duplicate-key error case, why this gap lock is needed? Or in another words, if there is no gap lock in the above case, what's the impact? What kind of issue will it cause? Would you give an example?
[31 Oct 2024 14:18]
MySQL Verification Team
Hi Mr. Wang, This is all done in accordance with the current standards that are set for the transactional processing. You have two columns. For the first one, you want to insert a new row in the gap between values 1 and 5. However, after that lock is secured, you go to check for second column and there you find a duplicate. That is simply expected behaviour.
[31 Oct 2024 14:25]
MySQL Verification Team
Hi, One short additional comment ..... The burden of checking the errors in on the users side. In most cases, it is something that should be built into the application. Whenever a transaction is not in auto-commit mode, an error is returned the the application and the application is the one that is responsible for issuing the ROLLBACK command. That is why that command exists ..... Standard does not allow that engine itself issues a ROLLBACK, except in the cases like lock wait timeout, timeout on waiting on mutex, deadlocks and similar situations. Last, but not least, in auto-commit mode, there should be no lingering locks ...... Not a bug.
Description: MySQL document explain that "If a duplicate-key error occurs, a shared lock on the duplicate index record is set." However, when there are multi unique keys, duplicate-key error occurs on one unique key and corresponding Primary Key dose not exist, there will be gap lock. This gap lock will affect INSERT concurrency performance, I'd like to know why MySQL implement gap lock in such situation, and what's the purpose of this gap lock? Thanks. How to repeat: CREATE TABLE `test` ( `id` int NOT NULL, `a` int NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_a` (`a`) ) ENGINE=InnoDB; mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.38 | +-----------+ mysql> insert into test values(1,1),(5,5),(10,10); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from test; +----+----+ | id | a | +----+----+ | 1 | 1 | | 5 | 5 | | 10 | 10 | +----+----+ 3 rows in set (0.00 sec) case 1) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(2,1); ERROR 1062 (23000): Duplicate entry '1' for key 'test.uk_a' mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473153168168:1080:281473030619936 ENGINE_TRANSACTION_ID: 7163 THREAD_ID: 57 EVENT_ID: 39 OBJECT_SCHEMA: test OBJECT_NAME: test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 281473030619936 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473153168168:14:5:2:281473030617024 ENGINE_TRANSACTION_ID: 7163 THREAD_ID: 57 EVENT_ID: 39 OBJECT_SCHEMA: test OBJECT_NAME: test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: uk_a OBJECT_INSTANCE_BEGIN: 281473030617024 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: 1, 1 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473153168168:14:4:3:281473030617712 ENGINE_TRANSACTION_ID: 7163 THREAD_ID: 57 EVENT_ID: 39 OBJECT_SCHEMA: test OBJECT_NAME: test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 281473030617712 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 5 3 rows in set (0.01 sec) After duplicate-key error, there is gap lock between Primary Key 1 to 5, any new INSERT transaction within this gap will be blocked: -- start another transaction mysql> insert into test values(4,4); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction case 2) mysql> select * from test; +----+----+ | id | a | +----+----+ | 1 | 1 | | 5 | 5 | | 10 | 10 | +----+----+ 3 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values(11,1); ERROR 1062 (23000): Duplicate entry '1' for key 'test.uk_a' mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473153166552:1080:281473030607792 ENGINE_TRANSACTION_ID: 7166 THREAD_ID: 58 EVENT_ID: 24 OBJECT_SCHEMA: test OBJECT_NAME: test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 281473030607792 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473153166552:14:5:2:281473030604800 ENGINE_TRANSACTION_ID: 7166 THREAD_ID: 58 EVENT_ID: 24 OBJECT_SCHEMA: test OBJECT_NAME: test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: uk_a OBJECT_INSTANCE_BEGIN: 281473030604800 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: 1, 1 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 281473153166552:14:4:1:281473030605488 ENGINE_TRANSACTION_ID: 7166 THREAD_ID: 58 EVENT_ID: 24 OBJECT_SCHEMA: test OBJECT_NAME: test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 281473030605488 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: supremum pseudo-record 3 rows in set (0.00 sec) After duplicate-key error, there is gap lock for Primary Key supremum, any new INSERT transaction greater than max Primary Key value will be blocked: -- start another transaction mysql> insert into test values(100,100); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Suggested fix: Check the necessity for gap lock, and if not, remove gap lock to improve concurrent throughput.