MySQL deadlock problem

Conditions for forming a deadlock

Our MySQL storage engine is InnoDB, from the log, InnoDB actively detect to deadlock, and rollback of a waiting for a transaction.

The question is, how did InnoDB know about the deadlock?

The intuitive approach is that when two transactions are waiting for each other, when a wait time exceeds one of the set thresholds, one transaction is rolled back, and the other transaction can continue to execute. This method is simple and effective, and in InnoDB, the parameter innodb_lock_wait_timeout is used to set the time-out.

Using the above method only to detect the deadlock is too passive, InnoDB also provides the Wait-for graph algorithm to actively deadlock detection, the wait-for graph algorithm will be triggered whenever locking request can not immediately meet the needs and enter the wait.

Wait-for Graph Principle:

When node 1 needs to wait for the resources of Node 2, a directed edge is generated to point to Node 2, and a directed graph is finally formed. All we have to do is detect if there is a loop on the graph, and the loop is a deadlock! This is the Wait-for graph algorithm.

InnoDB each transaction as a node, the resource is the lock that each transaction occupies, and when transaction 1 waits for the lock of transaction 2, it generates a directed edge from 1 to 2, and the last line into a directed graph.

Lock-to-index relationship

Suppose we have a message table (MSG) with 3 fields in it. Assuming the ID is a primary key, token is a non-unique index and the message is not indexed.

InnoDB uses a clustered index for the primary key , which is a way of storing data, and the table data is stored with the primary key, and the leaf node of the primary key index stores the row data. For a normal index , its leaf child nodes store index entries and primary key values .

Figure 4 Clustered index and level two index
The following is an analysis of the relationship between indexes and locks.

1) Delete from msg where id=2;

Because the ID is the primary key, the entire row of records can be locked directly.

2) Delete from msg where token= ' CVS ';

Since token is a level two index, it locks the level two index (two rows) first, and then locks the record corresponding to the corresponding primary key;

3) Delete from MSG where message= order number is ';

The message is not indexed, so it is a full table scan filter. Each record on the table is then added with an X lock.

Relationship of lock to isolation level

To ensure the correctness of the concurrency operation data, the database has the concept of transaction ISOLATION level:

1) unread (read UNCOMMITTED), 2) Read (Read Committed (RC)), 3) repeatable READ (Repeatable read (RR)), 4) serializable (Serializable). We use RC and RR more often.

Commit Read (RC): Only data that has been committed can be read.

Repeatable Read (RR): Queries within the same transaction are all consistent at the beginning of the transaction, InnoDB the default level.

Transaction a receives 1 records for the first query and two records for the second execution of the same query. From the perspective of transaction A, it's a ghost! This is the Phantom read, the RC level despite the addition of a row lock (x lock), but still can not avoid phantom reading.

The RR isolation level of the InnoDB avoids Phantom reads, and how does it happen? Of course we need to use the lock!

Execute on Transaction A: Update msg set message= ' order ' where token= ' ASD ';

InnoDB will first add an X lock to the records on the index, as well as the RC level, plus a lock on the non-unique index ' ASD ' with the interval of the adjacent two indexes .

This way, when transaction B is executed, insert into MSG values (null, ' ASD ', ' hello '); Commit, the first check whether the interval is locked, if locked, it can not be executed immediately, need to wait for the gap lock is released. This avoids the illusion of reading.

recommend a good article, you can deeply understand the principle of lock:http://hedengcheng.com/?p=771#_Toc374698322

How to avoid deadlocks as much as possible

1) Access tables and rows in a fixed order, adjust the SQL order of two transactions to be consistent, and avoid deadlocks.

2) Large transaction is small. Large transactions tend to deadlock more, and if business permits, large transactions are removed.

3) in the same transaction, as much as possible to lock all the resources needed, reduce the deadlock probability.

4) Reduce the isolation level. If the business allows it, it is also a good choice to lower the isolation level, such as adjusting the isolation level from RR to RC, which avoids a lot of deadlocks due to gap locks.

5) Add a reasonable index to the table. You can see that if you do not go the index will add a lock for each row of records in the table, the probability of deadlock greatly increases.

How to locate the cause of deadlock

1) Locate the appropriate SQL for the corresponding transaction by applying the business log to the problem code;

Because the deadlock is detected and rolled back, the information is reflected in the application's business log, which allows us to locate the corresponding code and sort out the SQL for the transaction.

In addition, we found that the transaction was rolled back when a deadlock was detected, based on the information of the log rollback.

2) Determine the database isolation level.

Executes select @ @global. tx_isolation, you can determine the isolation level of the database, our database isolation level is RC, so can be a great probability to exclude the gap lock caused by the suspicion of deadlock;

3) Look for the DBA to perform the next show InnoDB status to see the recent deadlock log.

This step is critical. With the help of the DBA, we can have more detailed deadlock information. With this verbose log, you can see that the transaction structure that conflicts with the previous transaction is as follows:

MySQL deadlock problem