Bug #99736 | Unique index lock after INSERT, DELETE, INSERT with same values | ||
---|---|---|---|
Submitted: | 29 May 2020 7:34 | Modified: | 29 May 2020 14:23 |
Reporter: | Niklas Deutschmann | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 8.0.20 | OS: | Windows (Windows 10 (1909)) |
Assigned to: | CPU Architecture: | x86 |
[29 May 2020 7:34]
Niklas Deutschmann
[29 May 2020 7:36]
Niklas Deutschmann
SQL (session 1)
Attachment: session1.sql (application/octet-stream, text), 314 bytes.
[29 May 2020 7:36]
Niklas Deutschmann
SQL (session 2)
Attachment: session2.sql (application/octet-stream, text), 137 bytes.
[29 May 2020 7:37]
Niklas Deutschmann
Output from "performance_schema" tables (MySQL 8.0.19)
Attachment: performance_schema.log (application/octet-stream, text), 2.29 KiB.
[29 May 2020 9:56]
Frederic Descamps
Just some extra info using MySQL Shell Plugin to see locks ;) MySQL localhost:33060+ 2020-05-29 11:54:05 JS ext.check.getLocks() +-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+ | mysql_thread_id | trx_duration | row_locks_held | row_locks_pending | tables_with_locks | current_statement | +-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+ | 8 | 7.79 s | 6 | 0 | test.foo | select sleep(20) | | 9 | 3.00 s | 1 | 0 | test.foo | insert into foo (bar_id) values (3) | | 7 | 41.07 ms | 0 | 0 | NULL | NULL | | 12 | 904.01 us | 0 | 0 | NULL | SELECT thr.processlist_id AS m ... IT DESC LIMIT 10 | +-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+ For which thread_id do you want to see locks ? (8) 8 Metadata Locks: --------------- GRANTED SHARED_WRITE on test.foo Data Locks: ----------- GRANTED TABLE (IX) LOCK on test.foo (None) GRANTED RECORD (X,REC_NOT_GAP) LOCK on test.foo (PRIMARY) GRANTED RECORD (X,REC_NOT_GAP) LOCK on test.foo (bar_id_idx) GRANTED RECORD (S,GAP) LOCK on test.foo (bar_id_idx) GRANTED RECORD (S,GAP) LOCK on test.foo (bar_id_idx) GRANTED RECORD (S) LOCK on test.foo (bar_id_idx) MySQL localhost:33060+ 2020-05-29 11:54:59 JS ext.check.getLocks() +-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+ | mysql_thread_id | trx_duration | row_locks_held | row_locks_pending | tables_with_locks | current_statement | +-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+ | 8 | 9.88 s | 6 | 0 | test.foo | select sleep(20) | | 9 | 5.09 s | 1 | 0 | test.foo | insert into foo (bar_id) values (3) | | 7 | 41.07 ms | 0 | 0 | NULL | NULL | | 12 | 418.56 us | 0 | 0 | NULL | SELECT thr.processlist_id AS m ... IT DESC LIMIT 10 | +-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+ For which thread_id do you want to see locks ? (8) 9 Metadata Locks: --------------- GRANTED SHARED_WRITE on test.foo Data Locks: ----------- GRANTED TABLE (IX) LOCK on test.foo (None) WAITING RECORD (X,INSERT_INTENTION) LOCK on test.foo (bar_id_idx)
[29 May 2020 12:49]
MySQL Verification Team
Hi Mr. Deutschmann, Thank you for your bug report. However, this is not a bug. The INSERT that you are trying to execute, in the second transaction, has to grab the gap lock between the last record and the infimum. However, it can not do that, since first transaction is holding a no-gap lock on the last existing record. Hence, what you get is a lock that waits and due to your settings, you get a lock wait timeout. Lock waits and deadlock are expected occurrence with ACID engines. This is not a bug, but expected behaviour with all transactional engines, based on the pessimistic locking approach. This is all fully described in our Reference Manual. Not a bug.
[29 May 2020 13:01]
Niklas Deutschmann
But when I add a single insert of any other value between the delete and the reinsert in session1.sql... create table foo(foo_id int auto_increment primary key, bar_id int); create unique index bar_id_idx on foo (bar_id); set autocommit=0; start transaction; insert into foo (bar_id) values (1); delete from foo where foo_id = 1; insert into foo (bar_id) values (2); insert into foo (bar_id) values (1); select sleep(20); commit; set autocommit=1; ...there won't be a lock. Why? This is quite mind-blowing to me.
[29 May 2020 13:06]
MySQL Verification Team
Hi, The answer is actually quite easy to understand if you have read our Reference Manual. DELETE takes exclusive write lock, while you can have several gap locks on the same gap. Not a bug.