!!! It is important to analyze the deadlock principle and the deadlock principle.

!!! It is important to analyze the deadlock principle and the deadlock principle.

Original Link

Author: Desert solitary smoke

Background and Symptom

In the online production environment, database operation deadlocks often occur in some cases, and business personnel cannot perform operations. According to DBA analysis, the insert and delete operations on a table are deadlocked. This section briefly introduces the database situation (because it involves real data, we have done a simulation here, without affecting the specific analysis and analysis results .) Assume that the following two tables exist:

 

The data in the Order table is as follows:

 

 

 

 

The data in the Customer table is as follows:

 

 

 

Order and Customer have an association in the object relationship, that is, the order object has a pointer to the customer object. During database design, the order table's mermer_id is not designed as a foreign key, because the operation on the order table does not require the foreign key to affect the database operation performance. The check of foreign key constraints is put in the application, that is, the database is only a guarantee of persistence and transaction. In addition, customer_id is indexed for ease of query.

 

In this simulated business scenario, there is a business (because it is simulated, so don't care if it is correct in reality), and orders owned by a customer will change frequently. That is, the customer may delete some existing orders under his account, add new orders, or modify some existing orders. These three operations may occur in one request. At this time, the application staff made a poor implementation: when a customer transfers his changed order to the background. No matter whether this change happens or not, the developer performs the delete and insert operations on the customer's order to replace the update operation. The implementation here is understandable, because the order in this request may need delete, insert, and update operations. In this way, we need to identify the data imported from the page that is delete, if the values are insert and those are upadte, it is better to perform the delete operation before the insert operation.

 

Because of the implementation relationship of the preceding business scenario, the database operations in an abstracted transaction are as follows:

Start transaction; // Start a transaction

Delete from 'order' where mermer_id = XXX; // Delete all orders under XXX

Insert into 'order' (mermer_id) values (xxx); // Inset multiple orders under XXX

Insert into 'order' (mermer_id) values (xxx );

Insert into 'order' (mermer_id) values (xxx );

..........

Commit; // transaction Commit

 

In the case of high concurrency, database deadlocks often occur.

 

Suppose we perform the following two transaction operations (both client 3 and Client 5 want to add their own order records ):

 

T1:

 

T2:

 

On the mysql server, the execution sequence is as follows:

T1 Start Transaction;

T2 Start Transaction;

T1 delete from 'order' where mermer_id = 3;

T2 delete from 'order' where mermer_id = 5;

T1 insert into 'order' (mermer_id) values (3 );

T2 insert into 'order' (mermer_id) values (5 );

.......

At this time, the T1 insert statement cannot be executed and remains waiting for a lock authorization. The Mysql lock information is as follows:

 

 

Thread 5 is waiting for a lock authorization during the insert operation and has waited for 10 seconds. We can see that transaction 0 10248 holds 2 locks; transaction 0 10247 has 2 locks and 1 waits for Lock authorization. The entire database only has these two transactions, so the insert wait lock must be held by 0 10248.

 

If the insert Statement of T2 continues to be executed, the deadlock will occur. The mysql information is as follows:

 

 

Analysis

First, we need to understand the basic knowledge of database locks.

In order to improve the concurrency, the database implements two different lock controls for read and write, namely the shared lock (S lock) and exclusive lock (X lock ). These two locks are not unique to mysql and will be mentioned in the general introduction to the basic principles of the database. At the same time, there is a concept of intention lock.

In the innodb Storage engine of mysql, row locks (S, X) and Table locks (IS, IX) are used ). The four locks have a compatibility matrix (what is the compatibility matrix used? You do not need to explain it. For details, refer to the basic principles of the database:

We open the lock monitoring, and then observe the lock situation in the transaction execution.

A: T1 Start Transaction;

B: T2 Start Transaction;

C: T1 delete from 'order' where mermer_id = 3;

D: T2 delete from 'order' where mermer_id = 5;

E: T1 insert into 'order' (mermer_id) values (3 );

F: T2 insert into 'order' (mermer_id) values (5 );

.......

We first execute E in order. below is the mysql lock:

T1

T2

 

 

We can clearly see three locks held by T1 (including those waiting for authorization): one is the IX lock of table order; the other is the Gap Type X lock of index customer_id on table order; in addition, the X lock of the Insert intention type of index customer_id on the table Order is waiting for authorization.

T2 holds two locks: one is the IX lock for table Order; the other is the Gap lock for index customer_id on table order.

Note that T1 Gap, Insert intention, and T2 Gap are all locked in the same place, "space id 0 page no 198 n bits 80"

 

Here we will introduce the lock types in mysql innodb:

Three Common Types

 

Take the above example as an example.

The Record type. A simple understanding is to execute delete from 'order' where id = 1 and lock the records with id = 1 in the order table.

Gap type: simply execute delete from 'order' where customer_id = 3. Here, there is no record with customer_id = 3 in the order table. However, because customer_id has an index, mysql searches based on the index. The index key is (, 6), and 3 is located in the gap between () instead of these keys). Mysql locks applied to the Gap () are called Gap locks. In this example, there are four gaps: (-∞, 1), (1, 2), (2, 6), (6, + ∞. Note that the gap only locks the gap and does not lock the record.

Next-Key type: Gap + Next Record. In the example of the Gap above, the lock is (2, 6]. Here we have 6 records.

 

Apart from the above three common lock types, there is also a special lock type for the Insert statement

 

 

That is to say, the insert statement will add an X lock to the inserted row, but before the row is inserted, an Insert intention Gap lock will be set, which is called the Insert intention lock.

In the preceding example, when executing insert into 'order' (customer_id) values (3), because of the mer_id index) add an X lock of the Insert Intention type.

 

After learning about this, let's go back to the above example.

Here we know clearly -- "Pay attention to the Gap of T1, Insert intention, the Gap of T2 is the reason why space id 0 page no 198 n bits 80 is locked in the same place. Because both customer_id = 3 and customer_id = 5 belong to the same gap (2, 6 ).

T1 holds the gap () X lock, and an insert intention () X lock is waiting for the release of the gap () X lock;

T2 holds the gap (2, 6) X lock.

This is the real reason that the insert Statement of T1 cannot be executed. When the insert Statement of T2 is executed, (that is, the F statement) can predict that T2 will also have an insert intention () X lock waiting for the release of the X lock of gap. In this way, a deadlock occurs.

Is the analysis over here? It seems that this place is a bit wrong. Does T1 itself have an X lock of gap? So, why does T2. T2 still have the gap () X lock when T1 has the gap () X lock? Is the X lock incompatible with the X lock (see Compatibility Matrix )?

 

Yes. Check the compatibility matrix above. IX is compatible with IX, and X is not compatible with X. T1 and T2 have the IX lock for table order at the same time, but T1 and T2 have the X lock for index customer_id of table order at the same time. According to the compatibility matrix, it should be block when T2 executes the D statement, because it needs to obtain the X lock of Gap, however, this lock has been held when T1 executes the C statement. Therefore, T2. it can continue to be executed only after T1. in this order, no deadlock will occur.

Is Mysql or Innodb wrong?

In fact, there IS no error in our analysis and Mysql IS not mistaken. The only error IS that the compatibility matrix of this (IS, IX, S, X) IS not described in the official document, there is also a more precise compatibility matrix called "precise mode" in the Mysql implementation. (This matrix does not appear in the official document. It was estimated by Mysql lock0lock. c: lock_rec_has_to_wait source code .) The following is the compatibility matrix of "precise mode": (this compatibility matrix occurs when X and X, S and X are incompatible)

G I R N (existing locks, including waiting locks)
G ++
I-++-
R ++ --
N ++ --
+ Compatible.-incompatible. I indicates inserting an intent lock,
G indicates the Gap lock, I indicates the inserted intention lock, R indicates the record lock, and N indicates the Next-Key lock.

Http://www.mysqlops.com/2012/05/19/locks_in_innodb.html#more-3169)

Note that when an Insert Intention lock exists, applying for a Gap lock is allowed. However, when a Gap lock exists, applying for an Insert Intention lock is blocked.

Go back to the above example, and then you can explain it clearly.

After the C statement is executed, T1 holds the Gap () X lock;

Execute the D statement. T2 applies for the Gap () X lock. According to the "precise mode" compatibility matrix, this application is authorized, so T2 holds the Gap () X lock.

Run the E Statement. T1 applies for the X lock of Insert Intention (). According to the "precise mode" compatibility matrix, the application is blocked by T2 because T2 holds the X lock of Gap.

Execute the F statement. T2 applies for the X lock of Insert Intention (). According to the "precise mode" compatibility matrix, because T1 holds the X lock of Gap (), this application is blocked by T1 block.

Here, a deadlock occurs obviously. T1 and T2 both hold a lock and are waiting for the other party to release a lock. Here, the cause of the entire deadlock is clearly analyzed.

 

Solution

After analyzing the cause of the deadlock, we can solve the problem well. We can see that T1 and T2 both hold the Gap lock and wait for the insert intention to be authorized.

As long as the Gap lock is eliminated, the deadlock will be solved. There are several solutions:

The index customer_id above the order in the delete table. In this way, the Gap lock will not be generated during the delete operation, and the insert intention lock will not exist during the insert operation. However, the query may be affected.

B does not allow the transaction to obtain the Gap lock during the delete operation. For example, before executing delete from 'order' where mermer_id = 3;, query select * from 'order' where customer_id = 3 through the database to check whether a record exists. If the record does not exist, this operation does not perform the delete operation. Because insert always occurs, delete is not mandatory.

 

 

Postscript

When we solved the online problem, we had some detours and some phenomena made me think they were the real reasons. In fact, they were just an illusion.

Because the deadlock occurs in the Insert statement, we initially think it is caused by the auto-increment lock of the primary key id on the 'order' table (A Bit subjective, and it is very difficult to handle the problem ). Then, we convert the primary key id above 'order' to a sequence similar to Oracle, and assign the id to it through the application. You can try to get rid of the auto_increment of the primary key id of a table (not to say it is complicated, rather, this operation method is unacceptable to people with "Operation cleanliness ). After the launch, it seems a lot better, but the root cause still exists, but it doesn't want to bite you now. After a while, the system was under pressure and the problem was exposed again. It is due to the phrase "House leakage and Night Rain", which is a single disaster. When the problem arises, we still think it is caused by the id generated by the insert statement, the analysis of this problem gradually became more and more detailed, and finally realized that the "id generation method" is an alternative. The real reason is that when there are too many meaningless delete operations, this problem is solved.

To avoid your prejudice against the auto-increment lock of the primary key id, I will briefly introduce the auto-increment lock mechanism of the primary key id, which is also a kind of compensation for it.

The primary key auto-increment lock is basically implemented through select Max (id) from table for update. Obviously, for update adds a table lock and X. The difference from other locks lies in its release time, and other locks follow the transaction. The auto-increment lock does not follow the transaction, but follows the Insert statement.

After Mysql 5.1.22, The innodb_autoinc_lock_mode parameter is added to adjust the performance of the primary key auto-increment lock. At this time, the lock table operation may not be performed, but the id value may be calculated directly in the memory. In this case, mysql classifies the Insert statement. Different types have different auto-increment methods under different innodb_autoinc_lock_mode parameters. You can refer to the mysql technology insider InnoDB Storage engine.