Mysql deadlock problem analysis and solution of the example detailed _mysql

MySQL deadlock problem is a lot of programmers in the development of the project often encountered problems, the MySQL deadlock and the solution is as follows:

1, MySQL common storage engine lock mechanism

MyISAM and memory using table-level locks (table-level locking)

BdB with page locks (page-level locking) or table-level locks, default to page locks

InnoDB supports row-level locks (row-level locking) and table-level locks, which default to row-level locks

2, all kinds of lock characteristics

Table-level Lock: small overhead, lock fast, no deadlock, locking granularity, the highest probability of lock conflict, the lowest degree of concurrency

Row-level locks: large overhead, slow lock, deadlock, minimum locking granularity, lowest probability of lock collision and highest concurrency

Page Lock: Overhead and lock time bounded between table and row locks, deadlocks occur, lock granularity bounded between table and row locks, concurrency is generally

3, all kinds of locks applicable to the scene

Table-level locks are more appropriate for queries, and only a small number of applications that update data by index conditions, such as Web applications

Row-level locks are more suitable for applications that have a large number of concurrent updates to the index and concurrent queries, such as some online transaction processing systems

4, Dead Lock

Refers to two or more than two processes in the implementation process, as a result of competing for resources caused by a mutual wait for the phenomenon, without external forces, they will not be able to carry forward.

A table-level lock does not produce a deadlock. So the main solution to the deadlock is the most commonly used InnoDB.

5, deadlock example analysis

In MySQL, row-level locks are not direct lock records, but lock indexes. The index is divided into two types, primary key and non primary key index, if a SQL statement operates a primary key index, MySQL locks the primary key index, and if a statement operates a Non-key key index, MySQL locks the Non-key key index before locking the associated primary key index.

In an update, delete operation, MySQL not only locks all index records that the where condition scans, but also locks adjacent key values, known as Next-key locking.

For example, a table db.tab_test, structured as follows:

ID: primary key;
State: status;
Time: times;
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 my SQL tables in use 1, locked 1 lock wait 3 lock struct (s), heap size, MySQL thread ID, query ID 162348740 dcnet03 D CNET searching rows for update update tab_test set State=1064,time=now () where state=1061 and Time < Date_sub (now (), I Nterval minute) (SQL statement for Task 1) * * * * (1) Waiting for this LOCK to is granted: (Task 1 waiting index record) records LOCKS Space ID 0 page No 849384 n Bits the index ' PRIMARY ' of table ' db/tab_test ' Trx ID 0 677833455 _mode X Locks Rec but not gap waiting 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 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, t Hread declared inside InnoDB 499 mysql tables in use 1, locked 1 3 lock struct (s), heap size, undo log Entries 1 my SQL thread ID, query ID 162348739 dcnet03 dcnet updating update tab_test set State=1067,time=now () where ID in (992118 0) (Task 2 's SQL statement) * * * * (2) holds the Lock (S): (Task 2 acquired lock) record LOCKS Space ID 0 page no 849384 n bits the index ' PRIMARY ' O F table ' db/tab_test ' Trx ID 0 677833454 lock_mode X Locks Rec but not gap record lock, heap no physical record:n_fie lds 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 is 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 Rd: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 task 1 to unlock deadlock)

Reason Analysis:

When the update tab_test set State=1064,time=now () where state=1061 and Time < Date_sub (now (), INTERVAL minute) executes, MySQL causes With the idx_1 index, the associated index record is locked first because the idx_1 is not a primary key index, and MySQL also locks the primary key index to execute the statement.

Assuming that the update tab_test set State=1067,time=now () where ID in (9921180) is executed almost simultaneously, this statement first locks the primary key index and needs to be locked because the value of the state needs to be updated IDX_ Some index records for 1.

This first statement locks the idx_1 record, waits for the primary key index, and the second statement locks the primary key index record and waits for the idx_1 record, so the deadlock occurs.

6. Solutions

Split the first SQL, identify the primary key values that match the criteria, and then update the records by 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 MySQL deadlock problem can be resolved!