| Bug #102970 | trx reserves record-lock for failed insert statement | ||
|---|---|---|---|
| Submitted: | 15 Mar 2021 6:22 | Modified: | 15 Mar 2021 7:28 |
| Reporter: | Brian Yue (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | MySQL8.0.22, 8.0.23 | OS: | Any (rhel-7.4) |
| Assigned to: | CPU Architecture: | Any (intel x86-64) | |
[15 Mar 2021 7:28]
MySQL Verification Team
Hello Brian Yue, Thank you for the report and feedback. regards, Umesh
[16 Mar 2021 11:15]
Jakub Lopuszanski
I believe this is not a bug. The client which performed the failed INSERT has observed that the conflicting record existed in the database during its transaction (let's call the it T1). If we don't lock this record, another, concurrent transaction (let's call it T2) could delete the record, and commit, before T1. Then T1 could commit. Thus you'd get a serialization history in which T2 which deleted the record, happens-before T1 which saw the record.
Description: hello, While a insert statement fails with `Duplicate entry .. for key ..`, the record lock is not released, although there isn't any modified records. I suppose this is unnecessary. Reference to `How to repeat` for detail please. How to repeat: mysql> mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test Database changed mysql> create table t1 (id int primary key, age int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 values (1,1), (2,2), (3,3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values (2,3); ERROR 1062 (23000): Duplicate entry '2' for key 't1.PRIMARY' mysql> mysql> select * from performance_schema.data_locks where LOCK_TYPE = 'RECORD'\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140460297633176:37:4:3:140460194604992 ENGINE_TRANSACTION_ID: 10061 THREAD_ID: 113 EVENT_ID: 17 OBJECT_SCHEMA: test OBJECT_NAME: t1 PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140460194604992 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 1 row in set (0.00 sec) Suggested fix: unlock the record-lock while ER_DUP_ENTRY is returned.