0

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)
  • See my comments on the other task- by doing alter table test1 add index(name) and adding some extra rows, the locking is avoided. – jynus May 22 at 16:14
0

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)

  • My first problem - there are no committed rows in table in my sample. In deadlock log I see that transaction1 tried lock "shit2" row, which was never committed. Second problem - index doesn't solve the issue in real environment. dba.stackexchange.com/questions/238187/… – Triffids May 22 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.