Bug #86812 Unnecessary deadlock with UNIQUE key

• Submitted: 23 Jun 2017 11:35
• Modified: 23 Jun 2017 12:22
• Reporter: Sveta Smirnova (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.5, 5.6, 5.7, 8.0, 5.7.18, 8.0.2
• OS: Any
• CPU Architecture: Any

[23 Jun 2017 11:35] Sveta Smirnova

Description:
InnoDB fails with deadlock if table has UNIQUE key and does not if only Primary Key exists.

Behavior is repeatable with both REAPEATABLE READ and READ COMMITTED transaction isolation levels.

How to repeat:
create table locker (pk int not null auto_increment, some_key varchar(10) not null, some_val varchar(25) default 'hi', primary key (pk), unique key uk_locker (some_key)); 
insert into locker values(1, 'key-one', 'some-value');

Now open second session.

session1> start transaction; 
session1> select * from locker where some_key='key-one' for update;

session2> start transaction; 
session2> select * from locker where some_key='key-one' for update;

While SELECT hangs run in session1:

session1> delete from locker where some_key='key-one'; 
session1> insert into locker values(1, 'key-one', 'some-value'); 

Session 2 will get deadlock.

Now rollback both transactions and drop unique key:

alter table locker drop key uk_locker;

Repeat test. You want see deadlock.

You may also run attached test case for MTR with 8.0

PostgreSQL does not have deadlock with table created as create table locker (pk serial not null, some_key varchar(10) not null, some_val varchar(25) default 'hi', primary key (pk), unique(some_key)); and same test case.

[23 Jun 2017 11:36] Sveta Smirnova

test case for MTR, requires 8.0

Attachment: bug86812.test (application/octet-stream, text), 2.15 KiB.

[23 Jun 2017 12:22] MySQL Verification Team

Hello Sveta,

Thank you for the report and test case.
Verified as described with 5.7.18/8.0.2.

Thanks,
Umesh

[23 Jun 2017 12:23] MySQL Verification Team

test results

Attachment: 86812.results (application/octet-stream, text), 21.94 KiB.