Analysis and solution of MySQL deadlock problem

  • 2020-06-19 11:49:35
  • OfStack

MySQL deadlock is a common problem encountered by many programmers in project development. The MySQL deadlock and its solution are explained in detail as follows:

1. Locking mechanism of MySQL commonly used storage engine

MyISAM and MEMORY use table-level locking (table-ES11en locking)

BDB USES page locks (ES16en-ES17en locking) or table-level locks and defaults to page locks

InnoDB supports row-level locking (ES22en-ES23en locking) and table-level locking and defaults to row-level locking

2. Various lock features

Table level lock: low overhead, fast lock; No deadlocks will occur; The lock size is large, the probability of lock conflict is the highest, and the concurrency is the lowest

Row level lock: high overhead, slow lock; Deadlock can occur; The locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest

Page locking: The cost and locking time boundary is between the table lock and the row lock; Deadlock can occur; The locking granularity is bounded between table and row locks with a degree of concurrency of 1

3. Applicable scenarios of various locks

Table-level locking is more suitable for query-based applications with only a few updates to data by index criteria, such as Web applications

Row-level locking is more suitable for applications with large Numbers of concurrent updates to index criteria and concurrent queries, such as some online transaction processing systems

4, a deadlock

It refers to a phenomenon of waiting for each other caused by competition for resources between two or more processes in the execution process. If there is no external force, they will not be able to advance.

Table level locks do not create deadlocks, so resolving deadlocks is mostly for the most commonly used InnoDB.

5. Deadlock analysis with examples

In MySQL, row-level locking is not a direct lock record but a lock index. There are primary key indexes and non-primary key indexes. If an sql statement operates on a primary key index, MySQL will lock the primary key index. If a statement operates on a non-primary key index, MySQL locks the non-primary key index before locking the associated primary key index.

During UPDATE and DELETE operations, MySQL locks not only all index records scanned by the WHERE condition, but also adjacent key values, known as ES61en-ES62en locking.

For example, a table db.tab_ES68en has the following structure:

id: Primary key;
state: Status;
time: Time;
Index: idx_1 (state, time)

The deadlock log appears as follows:


***(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 83, query id 162348740 dcnet03 dcnet Searching rows for update 
update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)  (task 1 the sql Statement)  
***(1) WAITING FOR THIS LOCK TO BE GRANTED:  (task 1 Waiting index records)  
RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _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 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 320, undo log entries 1 
MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180)  (task 2 the sql Statement)  
*** (2) HOLDS THE LOCK(S):  (task 2 Acquired lock)  
RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` 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 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:  (task 2 Waiting lock)  
RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` 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) 
 (Rolled back the task 1 To release the deadlock) 

Reason analysis:

When "update tab_test state=1064,time=now() where state=1061 and time < When date_sub(now(), INTERVAL 30 minute) "executes, MySQL USES the idx_1 index, so it locks the relevant index record first, since idx_1 is not a primary key index and MySQL also locks the primary key index for that statement.

Assuming that "update tab_test set state=1067,time=now () where id in (9921180)" is executed almost simultaneously, this statement first locks the primary key index. Since the value of state needs to be updated, some index records of idx_1 need to be locked.

The first statement locks the record for idx_1 and waits for the primary key index, while the second statement locks the record for the primary key index and waits for the record for idx_1, resulting in a deadlock.

6. Solutions

Split sql 1, first find out the qualified primary key value, and then update the record according to the primary key:


select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute); 
update tab_test state=1064,time=now() where id in(......); 

So far the MySQL deadlock problem has been solved!


Related articles: