Using MySQL InnoDB engine lock mechanism to solve deadlock problem

Recently, in the project development process, encountered the database deadlock problem, in the process of solving the problem, the author of the MySQL InnoDB engine lock mechanism understanding gradually deepened.

The case is as follows:

The deadlock problem was found when using show InnoDB status to check engine state:

(1) TRANSACTION:
TRANSACTION 0 677833455, ACTIVE 0 sec, Process no 11393, OS thread ID 278546 starting index read
MySQL tables in use 1, locked 1
Lock wait 3 lock struct (s), Heap size 320
MySQL thread ID, query ID 162348740 dcnet03 dcnet searching rows for update
Update Tsk_task set Status_id=1064,update_time=now () where status_id=1061 and mon_time*** (1) waiting for the LOCK to IS Granted:
Record LOCKS Space ID 0 page no 849384 n bits ' PRIMARY ' of table ' Dcnet_db/tsk_task ' Trx ID 0 677833455 Lock_mod E X Locks Rec but not gap waiting
Record lock, Heap no physical record:n_fields 11; Compact format; Info bits 0
0:len 8; Hex 800000000097629c; ASC B;; 1:len 6; Hex 00002866eaee; ASC (f;; 2:len 7; hex 00000d40040110; asc @;; 3:len 8; hex 80000000000050b2; ASC P;; 4:len 8; hex 800000000000502a; ASC p*;; 5:len 8; Hex 8000000000005426; ASC t&;; 6:len 8; Hex 800012412c66d29c; ASC a,f;; 7:len 23; Hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; ASC xxx.com/;; 8:len 8; Hex 800000000000042b; ASC +;; 9:len 4; Hex 474bfa2b; ASC GK +;; 10:len 8; Hex 8000000000004e24; ASC n$;;
(2) TRANSACTION:
TRANSACTION 0 677833454, ACTIVE 0 sec, Process no 11397, OS thread ID 344086 updating or deleting, thread declared inside InnoDB 499
MySQL tables in use 1, locked 1
3 lock struct (s), heap size, undo log Entries 1
MySQL thread ID, query ID 162348739 dcnet03 dcnet updating
Update Tsk_task set Status_id=1067,update_time=now () where ID in (9921180)
(2) holds the LOCK (S):
Record LOCKS Space ID 0 page no 849384 n bits ' PRIMARY ' of table ' Dcnet_db/tsk_task ' Trx ID 0 677833454 Lock_mod E X Locks Rec but not gap
Record lock, Heap no physical record:n_fields 11; Compact format; Info bits 0
0:len 8; Hex 800000000097629c; ASC B;; 1:len 6; Hex 00002866eaee; ASC (f;; 2:len 7; hex 00000d40040110; asc @;; 3:len 8; hex 80000000000050b2; ASC P;; 4:len 8; hex 800000000000502a; ASC p*;; 5:len 8; Hex 8000000000005426; ASC t&;; 6:len 8; Hex 800012412c66d29c; ASC a,f;; 7:len 23; Hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; ASC uploadfire.com/hand.php;; 8:len 8; Hex 800000000000042b; ASC +;; 9:len 4; Hex 474bfa2b; ASC GK +;; 10:len 8; Hex 8000000000004e24; ASC n$;;
(2) Waiting for this LOCK to be granted:
Record LOCKS Space ID 0 page no 843102 n bits index ' key_tsktask_montime2 ' of table ' Dcnet_db/tsk_task ' Trx ID 0 67783 3454 Lock_mode X Locks Rec but not gap waiting
Record lock, Heap no 395 physical record:n_fields 3; Compact format; Info bits 0
0:len 8; Hex 8000000000000425; Asc%;; 1:len 8; Hex 800012412c66d29c; ASC a,f;; 2:len 8; Hex 800000000097629c; ASC B;;
WE ROLL back TRANSACTION (1)

This deadlock problem involves the Tsk_task table, which is used to save system monitoring tasks, and the following are related fields and indexes:

ID: Primary key;

Mon_time: monitoring time;

status_id: Task status;

Index: Key_tsktask_montime2 (status_id, Mon_time).

Analysis, the two statements involved should not involve the same tsk_task record, so why would a deadlock be caused?

Query MySQL official website documents, found that this is related to the indexing mechanism of MySQL. MySQL's InnoDB engine is a row-level lock, and my original understanding is to lock the record directly, which is not actually the case.

The main points are as follows:

Instead of locking the record, the index is locked;

In the update and delete operations, MySQL not only locks all index records that the where condition scans, but also locks adjacent key values, known as Next-key locking;

If the statement UPDATE tsk_task SET update_time = Now () where ID > 10000 locks all records with a primary key greater than or equal to 1000, you cannot operate on records that have a primary key equal to 10000 before the statement is completed;

When a nonclustered index (non-cluster index) record is locked, the associated cluster index (cluster index) record also needs to be locked to perform the appropriate action.

Once you analyze the two SQL statements that have occurred, it is not difficult to find the problem:

When the update tsk_task set Status_id=1064,update_time=now () where status_id=1061 and Mon_time

Assuming that the update tsk_task set Status_id=1067,update_time=now () where ID in (9921180) is executed almost simultaneously, this statement first locks the cluster index (primary key) because it needs to be updated Status_ ID, so you also need to lock some of the index records for the key_tsktask_montime2.

This first statement locks the key_tsktask_montime2 record, waits for the primary key index, and the second statement locks the primary key index record while waiting for the key_tsktask_montime2 record, in which case the deadlock occurs.

The author resolves the deadlock problem by splitting the first statement:

First identify the eligible Id:select ID from Tsk_task where status_id=1061 and Mon_time < Date_sub (now (), INTERVAL minute), and then update the status: UPD Ate tsk_task set status_id=1064 where ID in (...)

So far, the deadlock problem is completely resolved.