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:
None 
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
Description:
When you have an InnoDB table with a unique index constraint, and you insert, delete and insert again a record that is equal in the index constraint columns (in immediate succession) the table is locked completely, and any other transaction doing modifications on this table will receive Error 1205 (Lock wait timeout)

This bug has been reported as #68021 years ago and closed, but I still have this problem with 8.0.20 and 5.7.26 (running on Windows 10)

How to repeat:
Run this in one session:

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 (1);
select sleep(20);
commit;
set autocommit=1;

And this in the other session:

set innodb_lock_wait_timeout=10;
set autocommit=0;
start transaction;
insert into foo (bar_id) values (3);
commit;
set autocommit=1;
[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.