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)
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP