TransWikia.com

MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result

Database Administrators Asked by Triffids on December 31, 2021

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> SET autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)

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

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

Transaction2:

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

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

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

Transaction1:

    mysql> 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> 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)

One Answer

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> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

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

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

mysql> 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> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

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

mysql> 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> create index IDX_NAME on test1(name);
Query OK, 0 rows affected (10.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

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

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

Transaction 2 :

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

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

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

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

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

Answered by JYOTI RAJAI on December 31, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP