The deadlock caused by two identical INSERT statements is a distortion of human nature, or a loss of morality, which makes us say: Oh my God! This can also be deadlocked, and then with tears in my eyes I have to change the business code.

Okay, before diving into why two identical INSERT statements can cause deadlocks, let’s cover some basics.

Prepare the environment

To keep the story going, let’s create a new hero table that uses it countless times:

CREATE TABLE hero (
    number INT AUTO_INCREMENT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    UNIQUE KEY uk_name (name)
) Engine=InnoDB CHARSET=utf8;
Copy the code

Then insert a few entries into the table:

INSERT INTO hero VALUES (1, 'l, liu bei, "shu"), (3,' z zhuge liang, "shu"), (8, 'cao cao c', 'w'), (15, xun yu 'x', 'w') and (20 's sun quan', 'wu');Copy the code

The hero table now has two indexes (a unique secondary index and a clustered index), as shown below:

How do INSERT statements lock

INSERT statements do not generate lock structures during normal execution. Instead, they use the trx_ID column as an implicit lock to protect records.

However, in some special cases, INSERT statements can still generate locks. Here are some examples:

1. The next record to be inserted has been locked by another transaction with a GAP lock

Each time a new record is inserted, it is necessary to see if the next record to be inserted has a gap lock. If a gap lock has been inserted, the INSERT statement should block and generate an INSERT intent lock.

For example, in the hero table, transaction T1 ran in REPEATABLE READ (RR) isolation level and executed the following statement:

Mysql > BEGIN; Query OK, 0 rows affected (0.00 SEC) mysql> SELECT * FROM hero WHERE number < 8 FOR UPDATE; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | number | name | country | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | | | | l liu bei shu 1 | 3 | Zhuge liang | | shu z + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.02 SEC)Copy the code

Select * from INNODB where number = 8; select * from INNODB where number = 8; select * from INNODB where number = 8;

Tip: As for how SELECT, DELETE, and UPDATE statements are locked, we have already analyzed it in previous articles and won’t go into details here.

At this time, transaction T2 wants to insert a cluster index record with primary key value of 4. Then, before inserting the record, T2 needs to locate the cluster index record with primary key value of 4 in the page. It finds that the primary key value of the next record with primary key value of 4 is 8. If a gap lock has been added to the clustered index record whose primary key is 8, transaction T2 needs to enter the blocking state and generate a lock structure of type insert intent.

Let’s verify this by executing an INSERT statement in transaction T2:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO hero VALUES(4, 'g关羽', '蜀');

Copy the code

If T2 is blocked, run the SHOW ENGINE INNODB STATUS command to check whether INNODB is locked.

T2 has an insert intention lock (lock_mode X locks gap before REc insert intention) and is in waiting state.

Ok, now that we’ve verified this, let’s look at how it works in the code:

The lock_rec_insert_check_and_lock function is used to see if another transaction blocks the INSERT. If so, this transaction generates an INSERT intent lock for the record where the gap lock was added by another transaction.

Tip: The lock_rec_other_has_conflicting function is used to check whether a lock is conflicting with an existing one on the log. If you’re interested, check this out.

2. Duplicate keys are encountered

If the primary key or unique secondary index column of an existing record on the page is found to be the same as the primary key or unique secondary index column of the record to be inserted (although the value of the unique secondary index column of multiple records can be NULL at the same time, this situation is not considered here), The transaction that inserts the new record at this point acquires the lock of the record with the same key value that already exists on the page.

If the primary key is duplicate, then:

  • When the isolation level is not greater than RC, a transaction that inserts a new record adds an S-type serious record lock to an existing clustered index record with duplicate primary key values.
  • When the isolation level is greater than or equal to RR, a transaction inserting a new record adds an S-type next-key lock to an existing clustered index record with duplicate primary key values.

If the only secondary index column is duplicate, the transaction that inserts a new record adds an S-type next-key lock to the secondary index record that already has the same secondary index column value, regardless of the isolation level. It’s a next-key lock! It’s a next-key lock! This is one of the few scenarios in the RC isolation level where a gap lock is added to a record.

Note: InnoDB does not want to introduce gap locks at the RC isolation level, but for some reason, if you do not add gap locks, you will have multiple UNIQUE index columns with the same value in the UNIQUE secondary index, which violates the UNIQUE constraint. So the InnoDB designer reluctantly introduced gap locks at the RC isolation level.

Let’s also do an experiment. Now assume that both T1 and T2 are rolled back. Now set the isolation level to RC and restart the transaction for testing.

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.01sec) # mysql> BEGIN; Query OK, 0 rows affected (0.00 SEC) mysql> INSERT INTO hero VALUES(30, 'x ', 'w '); ERROR 1062 (23000): Duplicate entry 'x xun FOR key 'UK_name'Copy the code

Then execute the SHOW ENGINE INNODB STATUS statement to see what lock is added to T1:

You can see that even though the isolation level of T1 is RC, T1 still adds an S-type next-key lock (lock mode S in the red box) to the secondary index record whose name column is’ X Xun YU ‘.

If our INSERT statement also has ON DUPLICATE KEY… If a primary key value or a unique secondary index column is duplicated, an X lock is added to the existing records of the same key value in the B+ tree, instead of an S lock (the exact type of lock is the same as described above).

Ok, it’s time to look at the code to verify, let’s have a look:

Row_ins_scan_sec_index_for_duplicate is a function to check whether the column values of the unique secondary index are identical. The lock code is as follows:

As shown in the figure above, when a unique secondary index column is duplicated:

  • The red box number 1 indicates the pairON DUPLICATE ...Add type X lock.
  • The red box 2 indicates the normal INSERT statement, which adds the S-lock.

However, in either case, the value of lock_typed added is LOCK_ORDINARY, indicating a next-key lock.

We will not enumerate the lock code for INSERT statements when primary keys are repeated.

3. Foreign key check

When we insert records into a child table, we discuss two cases:

  • When the foreign key value in the child table can be found in the parent table, it is simply necessary to add an S-type serious record lock to the corresponding record in the parent table, regardless of the isolation level of the current transaction.

  • If the current isolation level is not greater than RC, the parent table records will not be locked. When the isolation level is not less than RR, a GAP lock is added to the next record in the parent table where the foreign key value is located.

Since foreign keys are not commonly used, examples and codes are not examples. If you are interested, you can open the “how MySQL works: Understanding MySQL from the root” to see examples.

Deadlocks are coming

All right, basics over, damn lock out.

Look at the bland INSERT statement below:

INSERT INTO hero(name, country) VALUES('g关羽', '蜀'), ('d邓艾', '魏');
Copy the code

This statement is used to insert two records, either at the RC or RR isolation level, that have a chance of triggering a deadlock if two transactions are executed concurrently. To stabilize the deadlock, we split the above statement into two statements:

INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
INSERT INTO hero(name, country) VALUES('d邓艾', '魏');
Copy the code

The function before and after the split is the same, but after the split we can artificially control the insertion of records. If T1 and T2 are executed in this order:

That is:

  • T1 first inserts name asG guan yu, can be successfully inserted, the corresponding only secondary index record isImplicit lockSHOW ENGINE INNODB STATUS does not display implicit locks:

  • And then T2 also inserts a value of nameG guan yuThe record. Since T1 has been inserted with a name value ofG guan yu, so WHEN T2 inserts the secondary index record, it will encounter the repeated unique secondary index column value. At this time, T2 wants to obtain an S-type next-key lock, but T1 does not commit it, and T1 inserts the name value asG guan yuAn implicit lock on a record is equivalent to an X-type serious lock (RC isolation level), so WHEN T2 attempts to acquire an S-type next-key lock, it encounters a lock conflict, enters a blocking state, and converts T1’s implicit lock to an explicit lock (the lock structure that helps T1 generate a serious lock). SHOW ENGINE INNODB STATUS statement

If T1 is holding an implicit lock whose name is g, then T1 is holding an explicit lock (lock_mode X locks rec but not gap). T2 is waiting for an S-type next-key lock.

  • And then T1 inserts a name value ofD wargoThe record. When a record is inserted, the position of the record is first located on the page. Insert the name value isD wargoThe distribution of records in the page is as follows:

Obviously, the name value of the next secondary index record where the name value is’d Deng Ai ‘should be ‘g Guan Yu’ (sorted by Hanyu pinyin). Then insert the secondary index record whose name value is D deng ai on T1, we need to check whether the secondary index record whose name value is ‘g guan yu ‘is locked by another transaction gap.

Mysql > select * from T2 where name = ‘g ‘; mysql > select * from T2 where name = ‘g ‘; mysql > select * from T2 where name = ‘g ‘ T1 can insert a secondary index whose name is ‘g ‘.

Let’s take a look at the result:

Mysql > INSERT INTO hero(name, country) VALUES('g ', 'sh '); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionCopy the code

Apparently, a deadlock was triggered and T2 was rolled back by InnoDB.

Why is that? Select * from T2 where name = ‘g ‘; select * from T2 where name = ‘d’; select * from T2 where name = ‘g ‘;

Let’s go back to the code and see how the lock conflicts are determined when inserting a new record:

The INSERT operation of the current transaction should be blocked whenever another transaction generates an explicit GAP lock, regardless of whether the transaction is granted or waiting for the lock.

To return to our example, T2 has generated a type S next-key lock on the secondary index record whose name is ‘g guan Yu ‘. T1 cannot insert the secondary index record whose name is’ D Deng Ai ‘even though T2 is blocking.

This explains why deadlocks occur:

  • T1 is waiting for T2 to release its nameGuan yu 'g'The gap lock on the secondary index record of.
  • T2 is waiting for T1 to release its nameGuan yu 'g'Type X canonical record lock on secondary index records of.

A deadlock occurs when two transactions wait for each other to release the lock.

How to solve the deadlock problem?

Two options:

  • Scheme 1: Insert only one record in a transaction.
  • Scheme 2: Insert name as'd wargo'Insert the name value asGuan yu 'g'The record of

Why do these two options work? The big brains in front of the screen should also turn a wave of bai ~