A batch insert deadlock has recently occurred on the line. Deadlocks are recorded as follows

2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: *** (1) TRANSACTION TRANSACTION 1202026765, ACTIVE 0 sec inserting mysql tablesin use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 8532863, OS thread handle 139858337453824, query id 16231472122 10.111.10.143 seewo update
INSERT IGNORE INTO xx_performance_type_label_relation(label_id, performance_type_id, type, create_time)
    VALUES
      
      ('bb0394e670644168a998a93a3ed521bc'.'06b96ee0bab84d71bb17bf9645d3aa54', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'27d82e2331b241e1a9c9c0a74ec21099', -1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'3100b5978fb24f56b327d25732a7d7a7', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'435a1e19ce6e4e5bbb84240b3b34cf03', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'447fe27199ca40e289ef2834469d9a78', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'87a52c4d00844b5bb9eb75e8fe34202a', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'c6a0e26983bd4fae837d5ee2f4efeef8', 1, now()) 2018-10-26T11:04:43.759635z 8530809 [Note] InnoDB: *** RECORD LOCKS space id 505 page no 9912 n bits 288 index uk_performance_type_id_label_id of table `masaike`.`xx_performance_type_label_relation` trx id 1202026765 lock_mode X locks gap before rec insert intention Waiting 2018-10-26T11:04:41.759674Z 8530809 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 1202026764, ACTIVE 0 sec inserting mysql tablesin use 1, locked 1
3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 8530809, OS thread handle 139858469242624, query id 16231472119 10.111.10.153 seewo update
INSERT IGNORE INTO xx_performance_type_label_relation(label_id, performance_type_id, type, create_time)
    VALUES
      
      ('bb0394e670644168a998a93a3ed521bc'.'06b96ee0bab84d71bb17bf9645d3aa54', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'27d82e2331b241e1a9c9c0a74ec21099', -1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'3100b5978fb24f56b327d25732a7d7a7', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'435a1e19ce6e4e5bbb84240b3b34cf03', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'447fe27199ca40e289ef2834469d9a78', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'87a52c4d00844b5bb9eb75e8fe34202a', 1, now())
     , 
      ('bb0394e670644168a998a93a3ed521bc'.'c6a0e26983bd4fae837d5ee2f4efeef8', 1, now()) 2018-10-26T11:04:41.759713z 8530809 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 505 page no 9912 n bits 288 index uk_performance_type_id_label_id of table 'xx_performance_type_label_relation' TRX ID 1202026764 lock mode S 2018-10-26T11:04:41.759753z 8530809 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 505 page no 9912 n bits 288 index uk_performance_type_id_label_id of table `masaike`.`xx_performance_type_label_relation` trx id 1202026764 lock_mode X locks gap before rec insert intention Waiting 2018-10-26T11:04:41.759784z 8530809 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)Copy the code

The first reaction is to batch insert, insert order is not the same cause of deadlock. But that doesn’t work here. There are two reasons

  1. Implicit lock = explicit X lock; implicit lock = explicit X lock; implicit lock = explicit X lock
  2. If the deadlock log is caused by inconsistent batch insert order, the printed result is not insert intention waiting, as shown in the following experiment

Now let’s do an experiment using a simplified table

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(5)  NOT NULL DEFAULT ' ',
  `b` varchar(5)  NOT NULL DEFAULT ' ',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`a`,`b`)
) ENGINE=InnoDB;
Copy the code

The experimental 01

In the absence of records, two batch inserts in the same order are executed at the same time, with the second in lockwait state

First of all, truncate t1;

t1 t2
begin; begin;
insert ignore into t1(a, b)values(“1”, “1”); successful
insert ignore into t1(a, b)values(“1”, “1”); Lock wait state

You can see the current lock status

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'| +-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+----- ------+Copy the code

When we perform transaction T1 insert, there are no lock breakpoints. This is related to the principle of MySQL insert

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

When a record is inserted at T1, it is unlocked. At this time, when transaction T1 is not committed, transaction T2 tries to insert and finds this record. T2 tries to obtain S lock, which will determine whether the transaction ID on the record is active. If it is active, it indicates that the transaction is not finished and will help T1 to upgrade its implicit lock to explicit lock (X lock).

The source code is as follows

T2 :DB_LOCK_WAIT

The experiment.

Deadlock logs caused by inconsistent batch insert sequences are not waiting to insert intent locks

t1 t2
begin
insert into t1(a, b)values(“1”, “1”); successful
insert into t1(a, b)values(“2”, “2”); successful
insert into t1(a, b)values(“2”, “2”); DB_LOCK_WAIT t1 attempts to acquire the S lock and t2 promotes the implicit lock to the explicit X lock
insert into t1(a, b)values(“1”, “1”); T2 attempts to acquire the S lock, upgrading t1’s implicit lock to an explicit X lock, resulting in a 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)
Copy the code

So far, we have reached an impasse, and there is no way to reproduce the deadlock situation. Insert (delete); insert (delete); insert (delete); insert (delete) If a delete is excluded at the beginning of a transaction, it will have an impact on the future.

I wrote a code to simulate, with a high probability of recurrence

fun test(a) {
    dao.delete() // Corresponding to delete from
    // sleep for 10ms
    dao.insert() // Corresponds to insert ignore
}
Copy the code

The corresponding SQL is as follows, note that there are two transactions

begin;
delete from t1 where a = '25'
commit;

begin;
INSERT ignore INTO `t1` (`a`, `b`) VALUES('25'.'1')
commit;
Copy the code

This code is very prone to deadlocks when two threads are called simultaneously.

After scouring the Internet for deadlocks, there’s a story about the purge process that might have something to do with it.

If marked as delete, the transaction has been committed and has not yet been purge, and subsequent transactions are locked and waiting.

Print some logs in the source code. 1. In the storage/innobase/row/row0ins c row_ins_set_shared_rec_lock increase log, you can see the process of unique index increase S lock

if (dict_index_is_clust(index)) {
    err = lock_clust_rec_read_check_and_lock(
        0, block, rec, index, offsets, LOCK_S, type, thr);
} else {
    err = lock_sec_rec_read_check_and_lock(
        0, block, rec, index, offsets, LOCK_S, type, thr); Fprintf (stderr,"row_ins_set_shared_rec_lock %s %lu %d\n" , index->name, type, err);
}
Copy the code

2. Add a log to lock_rec_enqueue_waiting. You can view the lock waiting status

static
enum db_err
lock_rec_enqueue_waiting(
{
	fprintf(stderr, "lock_rec_enqueue_waiting::::: %s %lu\n" , index->name, type_mode);
}
Copy the code

The log might look like this

Row_ins_set_shared_rec_lock UK_name 0 9 (T1 obtaining S lock successfully) row_ins_set_shareD_rec_lock UK_name 0 9 (T2 obtaining S lock successfully) Lock_rec_enqueue_waiting ::::: uk_name 2563 (T1 X locked like lock waiting) lock_rec_enqueue_waiting::::: uk_name 2563 (T2 X locked like lock waiting)Copy the code

2563 = 2048 + 512 + 3 = LOCK_INSERT_INTENTION + LOCK_GAP + LOCK_X

This process is exactly the same as the classic three simultaneous insert transactions, one rollback, and the remaining two transactions, one successful and one deadlocked.

Experiment 3

Three INSERT ignore, one rollback deadlock

Insert ignore into T1 (a, b)values(“1”, “1”); The following is omitted

t1 t2 t3 note
begin begin begin
insert successful
insert T1’s implicit lock is promoted to X lock, t2 enters S lock wait
insert T3 enters S lock and waits
rollback; After t1 is rolled back, the X lock is released, and T2 and T3 both acquire the S lock
ok deadlock Both T2 and T3 want to insert the intent lock X lock, causing a deadlock condition

Deadlock logs, exactly like in our case

------------------------
LATEST DETECTED DEADLOCK
------------------------
181101 23:22:59
*** (1) TRANSACTION:
TRANSACTION 5032, ACTIVE 11 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 0x70000d736000, query id 125 localhost root update
insert ignore into t1(a, b)values("1"."1")
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 4 n bits 584 index `uk_name` of table `d1`.`t1` trx id 5032 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 139 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 3; hex 313031; asc 101;;
 1: len 3; hex 313031; asc 101;;
 2: len 4; hex 800007b1; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 5033, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 0x70000d779000, query id 126 localhost root update
insert ignore into t1(a, b)values("1"."1")
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 56 page no 4 n bits 584 index `uk_name` of table `d1`.`t1` trx id 5033 lock mode S locks gap before rec
Record lock, heap no 139 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 3; hex 313031; asc 101;;
 1: len 3; hex 313031; asc 101;;
 2: len 4; hex 800007b1; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 56 page no 4 n bits 584 index `uk_name` of table `d1`.`t1` trx id 5033 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 139 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 3; hex 313031; asc 101;;
 1: len 3; hex 313031; asc 101;;
 2: len 4; hex 800007b1; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
Copy the code

So far, the conclusions are as follows:

A committed record that has not been purge causes subsequent inserts to acquire the S shared lock, and two transactions simultaneously acquire the S lock and then attempt to acquire the insert intent lock, resulting in a deadlock

Online god combed the insert process

  • Insert Intension Locks first.

    • If the GAP has been added GAP lock or next-key lock, the lock fails to enter the wait;
    • If not, the lock is successful, indicating that the disk can be inserted.

  • It then determines whether the inserted record has a unique key, and if so, performs a uniqueness constraint check

    • If the same key value does not exist, the insert is completed
    • If the same key value exists, the system checks whether the key value is locked
      • If there is no lock, determine whether the record is marked for deletion
      • If marked as delete, the transaction has been committed and has not yet been purge.
      • If no label is deleted, a 1062 duplicate key error is reported.
    • If there is a lock, it indicates that the record is being processed (added, deleted, or updated), and the transaction has not been committed, add S lock and wait.

  • Insert record and add X record lock to record;

Here are some of the conclusions I got from the debug source code, please let me know in the comments if I get them wrong

The following is a reference document

  • www.aneasystone.com/archives/20…
  • www.aneasystone.com/archives/20…
  • Hedengcheng.com/?p=844#_Toc…