Batch insertion order is inconsistent under the unique index of MySQL deadlock routine

Time:2019-8-4

Preface

The essence of deadlock is resource competition. Batch insertion can easily lead to deadlock if the order is inconsistent. Let’s analyze this situation. To facilitate demonstration, batch inserts are rewritten to multiple inserts.

First, do a few small experiments. The simplified table structure is as follows.


CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` varchar(5),
 `b` varchar(5),
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_name` (`a`,`b`)
);

Experiment 1:

In the absence of a record, two batch inserts in the same order are executed simultaneously, and the second is locked waiting state.

t1 t2
begin; begin;
insert ignore into t1(a, b)values(“1”, “1”); Success
insert ignore into t1(a, b)values(“1”, “1”); Lock Waiting State

You can see the status of the current lock.


mysql> select * from information_schema.innodb_locks;
+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id  | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 31AE:54:4:2 | 31AE  | S   | RECORD | `d1`.`t1` | `uk_name` |   54 |   4 |  2 | '1', '1' |
| 31AD:54:4:2 | 31AD  | X   | RECORD | `d1`.`t1` | `uk_name` |   54 |   4 |  2 | '1', '1' |
+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+

When we insert transaction t1, there is no breakpoint for any lock, which is related to the principle of MySQL insert.

Insert adds an implicit lock. What is an implicit lock? Implicit lock means no lock.

When T1 inserts a record, it is unlocked. When transaction T1 is not committed at this time, transaction T2 tries to insert this record. When T2 tries to acquire the S lock, it decides whether the transaction ID on the record is active or not. If active, it indicates that the transaction is not finished, it will help T1 to elevate its implicit lock to an explicit lock (X lock).

The source code is as follows

T2 Gets the result of the S lock: DB_LOCK_WAIT

Experiment 2:

Deadlock caused by inconsistent batch insertion sequence

t1 t2
begin
insert into t1(a, b)values(“1”, “1”); Success
insert into t1(a, b)values(“2”, “2”); Success
insert into t1(a, b)values(“2”, “2”); T1 tries to acquire the S lock, elevates the implicit lock of T2 to an explicit X lock, and enters DB_LOCK_WAIT.
insert into t1(a, b)values(“1”, “1”); T2 tries to acquire the S lock, elevating the implicit lock of T1 to the explicit X lock, resulting in deadlock

------------------------
LATEST DETECTED DEADLOCK
------------------------
181101 9:48:36
*** (1) TRANSACTION:
TRANSACTION 3309, ACTIVE 215 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0x70000a845000, query id 58 localhost root update
insert into t1(a, b)values("2", "2")
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 3309 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 1; hex 32; asc 2;;
 1: len 1; hex 32; asc 2;;
 2: len 4; hex 80000002; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 330A, ACTIVE 163 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 0x70000a888000, query id 59 localhost root update
insert into t1(a, b)values("1", "1")
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 330A lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 1; hex 32; asc 2;;
 1: len 1; hex 32; asc 2;;
 2: len 4; hex 80000002; asc  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 330A lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 1; hex 31; asc 1;;
 1: len 1; hex 31; asc 1;;
 2: len 4; hex 80000001; asc  ;;

*** WE ROLL BACK TRANSACTION (2)

How to solve such a problem?

One possible way to do this is to sort the application layer before inserting it.

summary

Above is the whole content of this article. I hope the content of this article has some reference value for your study or work. Thank you for your support to developpaer.

Recommended Today

Redis (1)

Redis About redis Remote dictionary server (redis) is a key value storage system. Redis is an open source log type, key value database written in ANSI C language, complying with BSD protocol, supporting network, memory based and persistent, and providing API in multiple languages. It is often referred to as a data structure server, because […]