Clever use of MySQLInnoDB engine lock mechanism to solve the deadlock problem _ MySQL

Use MySQLInnoDB engine lock mechanism to solve deadlocks:

When I used Show innodb status to check the engine status, I found a deadlock problem:

* ** (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 wait3 lock struct (s), heap size 320

MySQL thread id 83, 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_mode X locks rec but not gap waiting

Record lock, heap no 92 physical record: n_fields 11; compact format; info bits 0

0: len 8; hex 800000000097629c; asc B; 1: len 6; hex 00002866 eaee; 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 20178616e642e706870; asc xxx.com/133; 8: len 8; hex 80021300000042b; 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 320, undo log entries 1

MySQL thread id 84, 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 208 index 'primary' of table 'dcnet _ db/TSK_TASK 'trx id 0 677833454 lock_mode X locks rec but not gap

Record lock, heap no 92 physical record: n_fields 11; compact format; info bits 0

0: len 8; hex 800000000097629c; asc B; 1: len 6; hex 00002866 eaee; 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 listen 8616e642e706870; asc listen 8: len 8; hex 8001_00000042b; 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 600 index 'key _ tsktask_montime2' of table 'dcnet _ db/TSK_TASK 'trx id 0 677833454 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. 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. Why does it cause a deadlock?

Query the MySQL documentation on the official website and find that this is related to the MySQL index mechanism. MySQL's InnoDB engine is a row-level lock. I originally understood it as locking records directly. In fact, this is not the case.

Key points are as follows:

Instead of locking the record, the index is locked;

During the UPDATE and DELETE operations, MySQL not only locks all index records scanned by the WHERE condition, but also locks adjacent key values, that is, the so-called next-key locking;

If the statement UPDATE TSK_TASK SET UPDATE_TIME = NOW () where id> 10000 locks all records whose primary keys are greater than or equal to 1000, before the statement is completed, you cannot operate records with a primary key equal to 10000;

When the non-cluster index record is locked, the related cluster index record also needs to be locked to complete corresponding operations.

After analyzing the two SQL statements with the problem, 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

If "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 ), to update the value of STATUS_ID, you also need to lock some index records of KEY_TSKTASK_MONTIME2.

In this way, the first statement locks the record of KEY_TSKTASK_MONTIME2, waits for the primary key index, and the second statement locks the primary key index record, and waits for the record of KEY_TSKTASK_MONTIME2. in this case, the deadlock occurs.

I solve the deadlock problem by splitting the first statement:

First find the qualified ID: select ID from TSK_TASK where STATUS_ID = 1061 and MON_TIME <date_sub (now (), INTERVAL 30 minute); then update the status: update TSK_TASK set STATUS_ID = 1064 where ID in (....)

Now, the deadlock problem is completely solved.