Using MySQL InnoDB engine lock mechanism to solve the deadlock problem

This paper will further explain the locking mechanism of INNODB through the process of solving the deadlock problem in a practical example.

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 of understanding gradually deepened.

The cases are as follows:

A deadlock problem was found when checking engine status with show InnoDB status:

(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 this LOCK to be Granted:

RECORD LOCKS Space ID 0 page no 849384 n bits 208 index ' 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 162348739, query ID 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 208 index ' 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, then why does it cause deadlocks?

Check the MySQL website documentation and find out about MySQL's indexing mechanism. MySQL's InnoDB engine is a row-level lock, and my original understanding is to lock the record directly, which is actually not the case.

Key points are as follows:

Instead of locking the record, the index is locked;

In the update, delete operation, MySQL not only locks all index records scanned by the Where condition, but also locks adjacent key values, the so-called next-key locking;

such as 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 with primary key equal to 10000 until the statement is complete;

When a Non-cluster index record is locked, the associated cluster index (cluster index) record also needs to be locked to complete the corresponding operation.

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

When "Update Tsk_task set Status_id=1064,update_time=now () where status_id=1061 and Mon_time

Assuming that "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), due to the need to update the STATUS_ ID, you also need to lock some index records for Key_tsktask_montime2.

The 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 solves 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: UPDA Te Tsk_task set status_id=1064 where ID in (...)

At this point, the deadlock problem is solved completely.

Http://www.cnblogs.com/Arlen/articles/1756915.html

Using MySQL InnoDB engine lock mechanism to solve the deadlock problem (turn)