MySQL deadlock analysis and solution

This article mainly introduces the analysis and solution of MySQL deadlock to coders. For more information about coders, see the next article. MySQL deadlock is a problem that many programmers often encounter in project development. The following describes the MySQL deadlock and solutions:

1. lock mechanism of common MySQL storage engines

MyISAM and MEMORY use table-level locking)

BDB uses page-level locking or table-level locking. the default value is page lock.

InnoDB supports row-level locking and table-level locking. the default value is row-level locking.

2. various lock features

Table-level lock: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency

Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, minimum probability of lock conflict, and highest concurrency

Page Lock: overhead and lock time are between table locks and row locks. deadlocks may occur. the lock granularity is between table locks and row locks, and the concurrency is average.

3. applicable scenarios of various locks

Table-level locks are more suitable for queries, and only a few applications update data based on index conditions, such as Web applications.

Row-level locks are more suitable for applications with a large number of concurrent data updates based on index conditions and concurrent queries, such as some online transaction processing systems.

4. deadlock

Two or more processes are waiting for each other due to resource competition during execution. if there is no external force, they will not be able to proceed.

Table-level locks do not produce deadlocks. Therefore, the most commonly used InnoDB is used to solve deadlocks.

5. deadlock example analysis

In MySQL, row-level locks do not directly lock records, but lock indexes. Indexes can be classified into primary key indexes and non-primary key indexes. If an SQL statement operates on a primary key index, MySQL locks the primary key index. if a statement operates on a non-primary key index, mySQL will first lock the non-primary key index, and then lock the relevant primary key index.

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.

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

Id: primary key;
State: status;
Time: time;
Index: idx_1 (state, time)

The deadlock log is as follows:

* ** (1) TRANSACTION: TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index readmysql 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 updateupdate tab_test set state = 1064, time = now () where state = 1061 and time <date_sub (now (), INTERVAL 30 minute) (SQL statement of task 1) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: (index record waiting for task 1) 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 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 commandid; asc xxx.com/#; 8: len 8; hex 8000000000000000042b; 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) (SQL statement of task 2) *** (2) holds the lock (S): (the lock obtained by task 2) 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 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 20178: len 8; hex 8000000000000000042b; asc ++; 9: len 4; hex 474bfa2b; asc GK ++; 10: len 8; hex 8000000000004e24; asc N $; *** (2) waiting for this lock to be granted: (lock waiting for task 2) 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, f; 2: len 8; hex 800000000097629c; asc B; *** WE ROLL BACK TRANSACTION (1) (rolling BACK Task 1 to unlock the deadlock)

Cause analysis:

When "update tab_test set state = 1064, time = now () where state = 1061 and time <date_sub (now (), INTERVAL 30 minute)" is executed, mySQL uses the idx_1 index, so the related index records are locked first. because idx_1 is a non-primary key index, MySQL also locks the primary key index to execute this statement.

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

In this way, the first statement locks the record of idx_1, waits for the primary key index, and the second statement locks the primary key index record, and waits for the record of idx_1, resulting in a deadlock.

6. Solution

Split the first SQL statement, first identify 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(......);