Bug #86812 Unnecessary deadlock with UNIQUE key
Submitted: 23 Jun 2017 11:35 Modified: 23 Jun 2017 12:22
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
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
Assigned to: 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] Umesh Shastry
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] Umesh Shastry
test results

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