MySQL deadlock problem related knowledge is the main content of this article, we will introduce this part of the content, we hope to be able to help you.
1. The lock mechanism of MySQL common storage engine
MyISAM and memory with table-level lock (Table-level locking)
BDB with page lock (page-level locking) or table-level lock, default to page lock
InnoDB supports row-level locks (row-level locking) and table-level locks, which are row-level locks by default
2. Various lock features
Table-level locks: low overhead, fast lock-up, no deadlock, lock granularity, highest probability of lock collisions, lowest concurrency
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, lowest probability of lock collisions, and highest degree of concurrency
Page locks: overhead and lock times are bounded between table and row locks, deadlock occurs, lock granularity bounds between table and row locks, and concurrency is common
3, various locks of the applicable scene
Table-level locks are more appropriate for applications that are primarily query-based and have only a small number of updates by index criteria, such as web apps
Row-level locks are more suitable for applications that have a large number of concurrent queries, such as some online transaction processing systems, that update data concurrently by index conditions.
4. Deadlock
Refers to two or more than two processes in the execution process, because of the contention for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to proceed.
Table-level locks do not generate deadlocks. So the solution to the deadlock is mainly for the most commonly used InnoDB.
5, deadlock example analysis
in MySQL, a row-level lock is not a direct lock record, but a lock index. Indexes are primary key index and non-primary key index Two, if a SQL statement operation primary Key index, MySQL will lock the primary key index, if a statement operation non-primary key index, MySQL will first lock the non-primary key index, and then lock the relevant primary key index.
In the update, delete operation, MySQL not only locks all index records scanned by the Where condition (in MySQL, where the conditional filter order is left-to-right versus Oracle, If the first condition of the where statement is how the non-primary key index locks all records that are filtered by the condition, the first condition should try to filter out the most likely duplicate records, or the top two conditions for the federated index to uniquely determine the record so that the deadlock can be avoided as much as possible, and the adjacent key values are locked. That is the so-called Next-key locking.
For example, a table db.tab_test with the following structure:
ID: primary key;
State: status;
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 Mys QL tables in use 1, locked 1 lock WAIT 3 lock struct (s), heap size on MySQL thread ID, query ID 162348740 dcnet03 dcne T searching rows for update update tab_test set State=1064,time=now () where state=1061 and Time < Date_sub (now (), INTER VAL-minute) (SQL statement for Task 1) * * * (1) Waiting for this LOCK to be granted: (Task 1 waiting index record) 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, H EAP 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, th Read declared inside InnoDB 499 mysql tables in use 1, locked 1 3 lock struct (s), heap size, undo log Entries 1 MySQL Thread ID 9921180, query ID 162348739 dcnet03 dcnet Updating update tab_test set State=1067,time=now () where ID in () ( SQL statement for Task 2 * * * * (2) holds the Lock (S): (Task 2 acquired lock) RECORD LOCKS Space ID 0 page no 849384 n bits 208 index ' PRIMARY ' of tabl E ' db/tab_test ' Trx ID 0 677833454 lock_mode 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: (Task 2 waiting Lock) RECORD LOCKS Space ID 0 page no 843102 n bits 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) (Rollback of 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 minute)" executes, MySQL will make With the idx_1 index, the associated index record is locked first, because Idx_1 is a non-primary key index, and MySQL locks the primary key index for executing the statement.
Assuming that "update tab_test set State=1067,time=now () where ID in (9921180)" is executed almost simultaneously, this statement locks the primary key index first, and because it needs to update the value of state, it also needs to be locked idx_ Some index records for 1.
The first statement locks the idx_1 record, waits for the primary key index, and the second statement locks the primary key index record while waiting for the idx_1 record, so the deadlock occurs.
6. Solutions
Split the first SQL, first identify the eligible primary key values, and then update the records according to the primary key:
Select ID from tab_test where state=1061 and Time < Date_sub (now (), INTERVAL-minute); Update Tab_test State=1064,time=now () where ID in (...);
The case of MySQL deadlock problem analysis and the solution is introduced here, I hope this introduction can have a harvest for you!
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
A case analysis and solution of MySQL deadlock problem
