MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows:

    CREATE TABLE test1 (
      id int(11) NOT NULL AUTO_INCREMENT,
      name varchar(100),
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;


Transaction1:

    mysql&gt; SET autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql&gt; 
    mysql&gt; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    Query OK, 0 rows affected (0.00 sec)

    mysql&gt; insert into test1(name) values ('shit1') ;
    Query OK, 1 row affected (0.00 sec)


Transaction2:

    mysql&gt; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    Query OK, 0 rows affected (0.00 sec)

    mysql&gt; SET autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

    mysql&gt; insert into test1(name) values ('shit2') ;
    Query OK, 1 row affected (0.00 sec)


Transaction1:

    mysql&gt; delete from test1 where name = 'shit1' ;


The transaction1 now trying to apply exclusive lock on non-committed row from transaction2. How can it be possible ? "shit2" row is not committed, mysql use next-key lock in the delete ? why ?

Transaction2:

    mysql&gt; delete from test1 where name='shit2' ;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


Deadlock details: where name = 'shit1' tried to lock "shit2"

    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2019-05-21 19:24:16 0x7f6d16db5700
    *** (1) TRANSACTION:
    TRANSACTION 24153, ACTIVE 61 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
    MySQL thread id 5009, OS thread handle 140106507769600, query id 111381 localhost root updating
    delete from test1 where name = 'shit1'
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `db1`.`test1` trx id 24153 lock_mode X locks rec but not gap waiting
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 4; hex 80000002; asc     ;;
     1: len 6; hex 000000005e5a; asc     ^Z;;
     2: len 7; hex 81000000b60110; asc        ;;
     3: len 5; hex 7368697432; asc shit2;;

    *** (2) TRANSACTION:
    TRANSACTION 24154, ACTIVE 28 sec starting index read
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
    MySQL thread id 5008, OS thread handle 140106511636224, query id 111382 localhost root updating
    delete from test1 where name='shit2'
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `db1`.`test1` trx id 24154 lock_mode X locks rec but not gap
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 4; hex 80000002; asc     ;;
     1: len 6; hex 000000005e5a; asc     ^Z;;
     2: len 7; hex 81000000b60110; asc        ;;
     3: len 5; hex 7368697432; asc shit2;;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 15 page no 4 n bits 72 index PRIMARY of table `db1`.`test1` trx id 24154 lock_mode X locks rec but not gap waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
     0: len 4; hex 80000001; asc     ;;
     1: len 6; hex 000000005e59; asc     ^Y;;
     2: len 7; hex 02000000f10301; asc        ;;
     3: len 5; hex 7368697431; asc shit1;;

    *** WE ROLL BACK TRANSACTION (2)
Here, the MySQL is not trying to lock uncommitted rows, but it locks all rows from table upto auto-incremented value if index on specific column is not defined ( using which we are deleting row).

Reproduce : 

Transaction 1 :

mysql&gt; SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; insert into test1(name) values ('shit1') ;
Query OK, 1 row affected (0.01 sec)

mysql&gt; select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  1 | shit1 |
+----+-------+
1 row in set (0.00 sec)

mysql&gt; delete from test1 where name = 'shit1' ;
Query OK, 1 row affected (10.97 sec)



Now Let's try to delete record from Transaction 2 using column Name as well as Primary key.

Transaction 2 : 

mysql&gt; SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; insert into test1(name) values ('shit2') ;
Query OK, 1 row affected (0.01 sec)

mysql&gt;  select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  2 | shit2 |
+----+-------+
1 row in set (0.00 sec)

mysql&gt; delete from test1 where name = 'shit2' ;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

mysql&gt; delete from test1 where name = 'shit2' ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql&gt; delete from test1 where id=2 ;
Query OK, 0 rows affected (0.01 sec)



Now We have created Index on name column : 

Transaction 1 : 

mysql&gt; create index IDX_NAME on test1(name);
Query OK, 0 rows affected (10.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql&gt; select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  3 | shit2 |
+----+-------+
1 row in set (0.00 sec)

mysql&gt; insert into test1(name) values ('shit1') ;
Query OK, 1 row affected (0.01 sec)

mysql&gt; delete from test1 where name = 'shit1' ;
Query OK, 1 row affected (0.00 sec)



Transaction 2 : 

mysql&gt; SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt;  select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  3 | shit2 |
+----+-------+
1 row in set (0.00 sec)

mysql&gt; insert into test1(name) values ('shit2') ;
Query OK, 1 row affected (0.00 sec)

mysql&gt;  select * from test1;
+----+-------+
| id | name  |
+----+-------+
|  3 | shit2 |
|  5 | shit2 |
+----+-------+
2 rows in set (0.00 sec)

mysql&gt; delete from test1 where name = 'shit2' ;
Query OK, 2 rows affected (0.00 sec)