It has been more than a year since I wrote this article last time. This time, I would like to share a weird deadlock event in the production environment at the end of 2019, but fortunately there was no significant business loss.

1. The background

A number of alarm emails were generated online on a certain day, and the contents of the emails are as follows. Due to the production environment, the table structure is simplified as follows

CREATE TABLE 'student' (' id 'int(11) NOT NULL AUTO_INCREMENT COMMENT' increment ', 'name' varchar(64) CHARACTER SET utf8 COLLATE UTf8_bin NOT NULL COMMENT '表 ',' age 'int(3) NOT NULL COMMENT' 表 ', 'school' varchar(64) CHARACTER SET utf8 COLLATE UTf8_bin NOT NULL COMMENT 'school ', PRIMARY KEY (' id') USING BTREE, UNIQUE INDEX `name_age`(`name`, age) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;Copy the code

The contents of the alarm email are as follows

### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error occurred while setting parameters ### SQL: Update student SET school = "update student SET school =" WHERE (update student SET school = ") ## com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transactionCopy the code

2. Code restoration

According to the log information in the alarm email, we locate the code location. The code logic is as follows, which still simplifies the code

Business 1

Start transaction.... Service Common query for (... Cyclic conditions) {.... Student object build.... Insert the constructed object Student into the library.... The constructed object Student is sent to MQ for asynchronous processing.... Service remaining conditions}.... Business remaining conditions commit transactionsCopy the code

The code logic for asynchronous processing is business 2

. Received message Student.... Calculate the parameters to be changed.... Assign the calculated parameter to the Student 'school' field.... Update Student data in table (deadlock here)Copy the code

3. Problem analysis

It is preliminarily speculated that since business 1 circularly inserted data after the transaction is started, and finally committed the transaction, the message sent asynchronously, namely business 2, is always blocked when executing the update due to the uncommitted transaction of business 1, which may cause deadlock during the blocked. However, the principle of deadlock is still not fully understood, and is not 100% necessary, we carry out a deep analysis of the problem.

Track MYSQL InnoDB status

So first we go to mysql and execute

show engine innodb status;
Copy the code

Getting a lot of information is as follows to extract LATEST DETECTED DEADLOCK information, which is the last DEADLOCK

------------------------ LATEST DETECTED DEADLOCK ------------------------ 191028 13:33:14 *** (1) TRANSACTION: TRANSACTION 2656E7, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 879805, OS thread handle 0x7f8d26308700, Query ID 3761780 XXXXXXXXXXXXX root Updating student SET school = "chingu" WHERE (name = 'chingu') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space ID 0 Page no 1362 N bits 376 index 'name_age' of table 1 '. 'student' TRX ID 2656E7 lock_mode X waiting XXXXXX *** (2) TRANSACTION: TRANSACTION 2656E2, ACTIVE 1 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 3 MySQL thread id 879796, OS thread handle 0x7f8d261c3700, query id 3761781 XXXXXXXXXXXXX root update insert into student (XXXXXXXXXXXXXXXXXXXXXXXX) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space ID 0 Page no 1362 N bits 376 index 'name_age' of table 1 '. 'student' TRX ID 2656E2 lock_mode X locks rec but not gap XXXXXX *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space ID 0 Page no 1362 N bits 376 index 'name_age' of table 1 '. 'student' TRX ID 2656E2 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 292 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 XXXXXX *** WE ROLL BACK TRANSACTION (1) ------------Copy the code

Information concept reserve

In order to interpret this log information, we need to explain MYSQL locks

Lock mode read lock_mode

The lock mode is defined in the MySQL source code as follows:

/* Basic lock modes */
enum lock_mode {
    LOCK_IS = 0, /* intention shared */
    LOCK_IX,    /* intention exclusive */
    LOCK_S,     /* shared */
    LOCK_X,     /* exclusive */
    LOCK_AUTO_INC,  /* locks the auto-inc counter of a table in an exclusive mode*/
    ...
};
Copy the code

• LOCK_IS: read intent lock
• LOCK_IX: Freehand lock
• LOCK_S: read lock
• LOCK_X: write locks
• LOCK_AUTO_INC: automatically adds a lock

LOCK_S and LOCK_X, also known as Share locks, are common read locks. All transactions can read the records, but they cannot be modified, and multiple transactions can simultaneously lock the records. A write lock is also called an Exclusive lock. After an Exclusive lock is added to a record, only the transactions that own the lock can read and modify the records. Only one transaction can be added to the write lock at a time.

Innodb downlink lock type

Innodb row lock type explanation

• Record Locks are row-level Locks, also known as row Locks, that lock index records.
• Gap Locks are row-level Locks, RR specific, that are placed before a specified index value, usually on the first index value that does not meet a condition.
• Next-key Locks are row-level Locks that are RR specific and are special GAP Locks, which are a combination of record and GAP Locks.

Special lock

• Insert Intention Locks, row-level Locks, RR specific, are a special type of GAP lock (hence the shorthand II GAP in some places). This lock indicates an Intention to Insert and is only available for inserts. Note that although this lock is also called an intention lock, it is essentially a gap lock

Information interpretation

Select * from student SET school WHERE id = ‘age’ WHERE id = ‘age’; select * from student SET school WHERE id = ‘age’

SQL > insert into student (XXXXXXXXXXXXXXXXXXXXXXXX) hold LOCK(S) Lock_mode X locks rec but not gap LOCK WAITING FOR THIS LOCK TO BE GRANTED: Lock_mode X locks gap before rec insert intention waiting

Finally, due to the small weight of transaction (1), we carried out the rollback operation

4. Locate the fault

Here we first insert two existing pieces of data into the database

insert student(name, age, school) values("test1", 10, "value1");
insert student(name, age, school) values("test10", 10, "value2");
Copy the code

Simulate an online database to perform operations

Two cases are listed here to describe the execution of the data

• Case 1

• Case 2(Online deadlock successfully reproduced)

4.1 Comparison Results

Why is the result inconsistent when the value of the name field is different in the same execution order?

• Core concept 1: Next-key Lock optimization mechanism, when the query index contains unique attributes next-key Lock to optimize, demoting it to Record Lock
• The sequence of next-key Lock is divided into two steps. The first step is to add a gap Lock, and the second step is to add a row Lock
• Insert Intention Locks are a special type of gap Locks that determine if there are any Locks that conflict with Intention Locks. If so, Insert Intention Locks and wait. If not, write data directly without any lock

4.2 Interpretation of test results

When creating table student, the default values are as follows

• Case 1:

• Transaction 1: Perform the first SQL insertname = test15To determine whether and insert intent lock{(test10, 10) ~ (+ up)}There is a conflict, not so direct insert data, get(test15, 10)Write lock on this line
• Transaction 2: Perform SQL update changesname = test15The record shcool is ‘XX’, and the next-key is tried to be obtained. At this time, because only name in the WHERE condition does not meet the unique index condition, the optimization is not carried out, and the gap lock is first tried to be obtained{(test10, 10) ~ (test15, 10)}Get success, get(test15, 10)This row was found to be so blocked when locked by transaction 1
• Transaction 1: Perform the second SQL insertname = test16To determine whether and insert intent lock{(test15, 10) ~ (+ up)}There is a conflict, not so direct insert data, get(test16, 10)Write lock on this line
• Transaction 3 performs SQL update changesname = test16The record is equivalent to transaction 2{(test15, 10) ~ ((+ up))}Gap lock, by(test16, 10)Row lock blocked up
• Transaction 1: Commit releases the row lock
• Transaction 2 3: The row lock was successfully executed

• Case 2

• Transaction 1: Perform the first SQL insertname = test15To determine whether and insert intent lock{(test10, 10) ~ (+ up)}There is a conflict, not so direct insert data, get(test15, 10)Write lock on this line
• Transaction 2: Perform SQL update changesname = test15The record shcool is ‘XX’, and the next-key is tried to be obtained. At this time, because only name in the WHERE condition does not meet the unique index condition, the optimization is not carried out, and the gap lock is first tried to be obtained{(test10, 10) ~ (test15, 10)}Get success, get(test15, 10)This row was found to be so blocked when locked by transaction 1
• Transaction 1: Perform the second SQL insertname = test14To determine whether and insert intent lock{(test10, 10) ~ (test15, 10)}Innodb found that there was deadlock relationship between transaction 1 and transaction 2. Because transaction 2 had a small weight, it directly rolled back to release the gap lock. Transaction 1 was successfully inserted with the intent lock, and data was inserted(test14, 10)
• Transaction 3: Perform SQL update changesname = test14At this time, because only name in the WHERE condition does not meet the unique index condition, the optimization is not carried out, and the clearance lock is first tried to obtain{(test10, 10) ~ (test14, 10)}Get success, get(test14, 10)This row was found to be so blocked when locked by transaction 1
• Transaction 1: Commit releases the row lock
• Transaction 3: The row lock was successfully executed

5 conclusion

In the use of where condition, because the joint unique index is not used, resulting in next-key Lock is not optimized and degraded to Record Lock, next-key Lock sequence is divided into two steps, the first step is to add gap Lock, the second step is to add row Lock, after the successful execution of the first step, Bolcked in the second step causes a conflict with the subsequent insert intent lock, resulting in a deadlock between two transactions waiting for each other

6. Resolve deadlocks

In the case that the conclusion is confirmed, we finally have a solution, which is to use the optimization degradation mechanism of unique index to supplement the WHERE condition in case 2 above, and use the joint unique index completely

• Case 2(Correction)

• Transaction 1: Perform the first SQL insertname = test15To determine whether and insert intent lock{(test10, 10) ~ (+ up)}There is a conflict, not so direct insert data, get(test15, 10)Write lock on this line
• Transaction 2: Perform SQL update changesname = test15At this time, because the where condition meets the unique index condition, the optimization is changed to Record Lock, trying to obtain the Next key(test15, 10)This row is blocked when it is locked and found to be occupied by transaction 1
• Transaction 1: Perform the second SQL insertname = test14Attempt to insert the intent lock{(test10, 10) ~ (test15, 10)}At this time, transaction 2 does not hold any lock, then transaction 1 adds the intended lock successfully and inserts the data(test14, 10)And get(test14, 10)Write lock on this line
• Transaction 3: Perform SQL update changesname = test14Record, try to obtain next-key, at this time because the WHERE condition meets the unique index condition optimization change to Record Lock, try to obtain(test14, 10)This row is blocked when it is locked and found to be occupied by transaction 1
• Transaction 1: Commit releases the row lock
• Transaction 2 3: The row lock was successfully executed

7. Reference materials

Dev.mysql.com/doc/refman/… www.aneasystone.com/archives/20… www.aneasystone.com/archives/20… www.aneasystone.com/archives/20… Blog.itpub.net/22664653/vi… Github.com/aneasystone… Time.geekbang.org/column/arti… Time.geekbang.org/column/arti…