Bug #86812 Unnecessary deadlock with UNIQUE key

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.

test case for MTR, requires 8.0

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

Hello Sveta,

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

Thanks,
Umesh

test results

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