Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL deadlock problem analysis and solution", the explanation content in the article is simple and clear, easy to learn and understand, please follow the small series of ideas slowly in-depth, together to study and learn "MySQL deadlock problem analysis and solution" bar!

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

1. MySQL common storage engine locking mechanism

MyISAM and MEMORY use table-level locking

BDB uses page-level locking or table-level locking, default is page locking

InnoDB supports row-level locking and table-level locking, default is row-level locking

2, a variety of lock characteristics

Table-level lock: low overhead, fast locking; no deadlock; large locking granularity, probability of lock conflict ***, concurrency ***

Row-level lock: high overhead, slow locking; deadlock occurs; locking granularity is the smallest, probability of lock conflict ***, concurrency is also ***

Page lock: overhead and locking time bound between table lock and row lock; deadlock occurs; locking granularity bound between table lock and row lock, concurrency is average

3. Applicable scenarios of various locks

Table-level locks are better suited for query-based applications, such as Web applications, with only a small number of updates to data based on index criteria

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

4. Deadlock

It refers to the phenomenon that two or more processes wait for each other due to competition for resources in the implementation process. If there is no external force, they will not be able to advance.

Table-level locks do not create deadlocks. Therefore, deadlock resolution is mainly aimed at the most commonly used InnoDB.

5. Deadlock example analysis

In MySQL, row-level locking is not a direct lock record, but an index lock. Indexes are divided into primary key index and non-primary key index. If a 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 will lock the non-primary key index first and then lock the relevant primary key index.

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

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

id: primary key;

state: state;

time: time;

Index: idx_1 (state, time)

Deadlock logs appear 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)***(1) WAITING FOR THIS LOCK TO BE GRANTED: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)(sql statement for Task 2)*** (2) HOLDS THE LOCK(S):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 recc 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 8000000004e24; asc N$;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 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 recc 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)

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 it locks the related index records first, because idx_1 is a non-primary key index, MySQL also locks the primary key index for execution of this 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, it also needs to lock some index records of idx_1.

Thus *** statement locks the record of idx_1, waiting for the primary key index, and the second statement locks the record of the primary key index, waiting for the record of idx_1, so deadlock occurs.

6. Solutions

Split *** sql, first find the primary key value that meets the conditions, 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(......);

The MySQL deadlock problem has been solved!

Thank you for reading, the above is "MySQL deadlock analysis and solution" content, after the study of this article, I believe we have a deeper understanding of MySQL deadlock analysis and solution to this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.