Analysis of the lock mechanism of MySQL InnoDB

In some cases, the online production environment often occurs with a deadlock in the database operation, which prevents the business personnel from operating. After the DBA's analysis, there was a deadlock in the insert operation and delete operation of a table. A brief introduction to the database (as it relates to real data, the simulation here does not affect the results of specific analysis and analysis.) Assume that there are 2 tables:

The data for the Order table is as follows:

The Customer table has the following data:

Order and customer have an association on the entity relationship, that is, the order entity has a pointer to the customer entity. In the design of the database, the customer_id of the order table is not designed as a foreign key because it does not want foreign keys to affect the operational performance of the database when doing operations on the order table. Here, the check of the foreign KEY constraint is placed in the application, that is, the database is only a guarantee of persistence and transactional. At the same time for the convenience of query, CUSTOMER_ID index.

In this simulated business scenario there is a business (because it is analog, so do not care whether the reality is correct), a customer-owned orders will change frequently. That the customer may delete some existing orders in his name, add some new orders, or modify some existing orders, these 3 operations may occur in a single request. At this point, the application made a less-than-good implementation: When a customer sent the order of his changes to the background. The developer replaces the update operation with a delete-after-insert operation for the customer's order, regardless of whether the change occurred this time. The implementation here is understandable, as the order in this request may require Delete,insert and update three operations, so that the data passed from the page will be identified by the Delete, those are inserts, those are Upadte, It's better to unify the first delete and insert operation.

Because of the relationship between the implementation of the business scenario above, the database operation in a transaction is abstracted as follows:

Start transaction; Open Business

Delete from ' order ' where customer_id = XXX; First delete all orders in XXX name

Insert into ' order ' (customer_id) values (XXX); Inset more orders in XXX name

Insert into ' order ' (customer_id) values (XXX);

Insert into ' order ' (customer_id) values (XXX);

..........

Commit; Transaction commit

This kind of operation in the case of high concurrency, there is a recurring database deadlock.

Let's say we do the following 2 transactions (Customer 3 and customer 5 want to add a record of their order):

T1:

T2:

If on the MySQL server side, the execution sequence is as follows:

T1 Start Transaction;

T2 Start Transaction;

T1 Delete from ' order ' where customer_id = 3;

T2 Delete from ' order ' where customer_id = 5;

T1 insert INTO ' order ' (customer_id) values (3);

T2 insert INTO ' order ' (customer_id) values (5);

.......

At this point, the T1 INSERT statement has no way to execute, and has been waiting for a lock authorization. The Mysql lock information is as follows:

Thread 5 tries to wait for a lock authorization at insert time and has waited for 10 seconds. You can see that transaction 0 10248 holds 2 locks, transaction 0 10247 has 2 locks, and 1 waits for lock authorization. The entire database has only these 2 transactions, so the lock that caused the insert wait must have been held by 0 10248.

If the T2 INSERT statement continues to execute, the deadlock occurs, and the MySQL information is as follows:

Analysis

First of all we need to understand the basic database of the lock knowledge.

In order to improve concurrency, the database has 2 different lock controls for read and write, called Shared (s) and exclusive (x) locks, respectively. Both of these locks are not unique to MySQL and are mentioned in the General Database fundamentals Introduction. There is also the concept of a corresponding intent lock.

In MySQL's InnoDB storage engine, a row lock (s,x) is used, as well as a table lock (Is,ix). Here are 4 types of locks with a compatible matrix (what does the compatible matrix do?) No need to explain, you can refer to the basic principles of the Database book) as follows:

We turn on the lock monitoring and then look at the lock situation in the execution of the transaction in detail.

A:t1 Start Transaction;

B:t2 Start Transaction;

C:T1 Delete from ' order ' where customer_id = 3;

D:t2 Delete from ' order ' where customer_id = 5;

E:T1 insert INTO ' order ' (customer_id) values (3);

F:t2 insert INTO ' order ' (customer_id) values (5);

.......

Let's start with the order to E, the following is the MySQL lock situation:

T1

T2

We can clearly see the T1 holding (including Waiting for authorization) 3 locks: One is an IX lock on the order of the table, and one is the X lock on the gap type of index customer_id above the table order; and the other is the table order above index customer_id The x lock for the Insert intention type is authorized.

T2 holds 2 locks: one is an IX lock on the order of the table, and one is the gap lock on the index customer_id above the table order.

Note that T1 's gap,insert intention, the gap in T2 is the same place as the lock. "Space ID 0 page no 198 n Bits 80"

Here are the types of locks under MySQL InnoDB:

Three common types

Take the example above

The record type, simple to understand is to execute the delete from ' order ' where id = 1, the locked order table inside the ID =1 records.

GAP Type: A simple understanding is to execute delete from ' order ' where customer_id = 3. There is no record of customer_id=3 in the order table. But because customer_id has an index, MySQL searches by index, the key of the index is (1,2,6), 3 is not in these keys but in the gap (GAP) between (2,6). MySQL for (2,6) this gap plus lock is called gap lock. The gap in this example has a total of (-∞,1), (2,6), (6,+∞) of these 4. Note Gap Lock-only gap is not locked record.

Next-key Type: The simple understanding is Gap + next Record. With the example of gap above, the lock is (2,6]. This includes 6 of this record.

In addition to the above three common lock types, there is a special lock type for INSERT statements

This means that the INSERT statement adds an X lock to the inserted row, but before inserting the line, an insert intention gap lock is set, called the insert intention lock.

In the example above, when executing insert INTO ' order ' (customer_id) VALUES (3), an insert is added to the index (2,6) due to the presence of customer_id index Intention Type of x lock.

Having learned this, let's go back to the example above.

Here we know clearly--"pay attention to the T1 Gap,insert intention, the gap of T2 is the same place of the lock" Space ID 0 page no 198 n bits 80 ""-3 locks locked in the same place for a reason. Because customer_id = 3 and customer_id = 5 are all part of the same gap (2,6).

T1 holds the Gap (2,6) x lock while having an X lock on the insert intention (2,6) waiting for the release of the X Lock of Gap (2,6);

T2 holds the Gap (2,6) x lock.

This is the real reason why the INSERT statement for T1 is not going to perform. When the T2 INSERT statement executes, (that is, the F statement) is foreseen, T2 will also have an X lock on the insert intention (2,6) waiting for the X lock of Gap (2,6) to be released. This creates a deadlock.

Is the analysis over here? It seems that the place is a bit wrong. T1 itself is to have a gap (2,6) x lock it? Wait, why T2 can also have Gap (2,6) x lock when T1 has a gap (2,6) x lock? X-Lock is not incompatible with X-lock (see Compatibility matrix)?

Yes, look at the compatibility matrix above. IX is compatible with IX and X is incompatible with X. It is understandable that T1 and T2 have an IX lock on the order of the table, but T1 and T2 also have an X lock on the index customer_id for the order of the table, which seems to be incomprehensible. According to the compatibility matrix, when the T2 executes the D statement should be block, because it needs to obtain the gap (2,6) x lock, but this lock has been T1 execute C statement when held, so only after the T1 transaction execution, T2 can continue to execute, in this order, There is no deadlock.

is Mysql, or Innodb, a mistake?

In fact, we analyze the error, MySQL is not mistaken, the only wrong place is the official document does not introduce in addition to this (is,ix,s,x) compatibility matrix, in the MySQL implementation of the inside there is also a more accurate is called "precise mode" compatibility matrix. (The matrix does not appear on official documents, but is inferred from the MySQL lock0lock.c:lock_rec_has_to_wait source code.) The following is the compatibility matrix for "precise mode": (This compatibility matrix occurs when X and X,s are incompatible with X)

G I R N (already existing lock, including waiting lock)
G + + + +
I - + +-
R + +--
N + +--
+ stands for compatibility,-represents incompatibility. I represents the insert intent lock,
G stands for Gap Lock, I for Insert intent Lock, R for Record lock, N for Next-key lock.

One thing to note here is that when an insert Intention lock is present, it is permissible to apply for a gap lock, but when a gap lock is present, an insert Intention lock is blocked when it is applied.

Back to the example above, this can be explained clearly.

After executing the C statement, T1 holds the X lock of Gap (2,6);

Execute the D statement, T2 apply for Gap (2,6) x lock, according to the "precise mode" compatibility matrix, the application is authorized, so T2 holds the Gap (2,6) x lock.

Execute the E statement, T1 request the X lock of Insert Intention (2,6), according to the "precise mode" compatibility matrix, the application is T2 block because 2,6 holds the X lock of Gap (T2).

Execute the F statement, T2 request the X lock of Insert Intention (2,6), according to the "precise mode" compatibility matrix, the application is T1 block because 2,6 holds the X lock of Gap (T1).

Here a deadlock is apparent, T1 and T2 both hold a lock, while waiting for each other to release a lock. Here, the reason for the entire deadlock has been analyzed clearly.

Solve

We analyzed the cause of the deadlock, and it was a good idea to solve the problem. It can be seen that t1,t2 are holding the gap lock, waiting for the insert intention to be authorized.

As long as the gap lock is eliminated, the deadlock is resolved. There are several scenarios:

A Delete Table order above the index customer_id. This will not create a gap lock at the time of the delete, and there will be no insert intention lock when insert. However, there are implications for queries.

B at Delete, do not let the transaction get to the gap lock. For example, before executing the delete from ' order ' where customer_id = 3, first query through the database select * from ' order ' where customer_id = 3; See if there is a record. No record exists this does not perform a delete operation. Because insert is always going to happen, delete is not something that must necessarily happen.

PostScript Li Xiaomong produced

In the real solution to this problem on the line, through some detours, some phenomena also let me think is to find the real reason, in fact, it is illusory illusion.

Because the deadlock occurs above the INSERT statement, at first we consider the primary key ID from the ' Order ' table to be the result of a self-locking (a bit judgmental, touyi). We then convert the primary key ID above the ' order ' to an Oracle-like sequence sequence, giving it an assignment ID through the application. Everyone can go to try to get rid of the auto_increment of the primary key ID of a table, it is how disgusting an operation (not to say how complicated, but that the operation of the way to have "operation neat" people unbearable). After the online, it does seem a lot better, but the root is still there, but it does not want to bite you now. After a while, the system pressure came up, and the problem was exposed. It is in the "house leakage of the Night Rain", and it pours, when the problem arises, we still think of the INSERT statement generated by the way the ID, and slowly the analysis of this problem more and more detailed, and finally realized that "id generation method" is the scapegoat, The real reason is that the problem is solved when there is too much meaningless delete operation.

In order to avoid the primary key ID from the self-locking bias, I briefly introduce the primary key ID self-locking mechanism, but also I am wronged it a compensation for it.

The primary key auto-lock is basically achieved by using Select Max (ID) from the table for update. Obviously, the for update adds a table lock and is x. The difference between a lock and another is the time it is released, and the other locks follow the transaction. The self-increment lock does not follow the transaction, but follows the INSERT statement.

After MySQL 5.1.22, the Innodb_autoinc_lock_mode parameter was added to adjust the performance of the primary key auto-lock. This is not necessarily a lock table operation, it is possible to directly in memory to calculate the ID value. In this case, MySQL will classify the INSERT statement, and different categories will have different self-innodb_autoinc_lock_mode under different parameters. You can refer to "MySQL Technology insider InnoDB storage Engine".

Analysis of the lock mechanism of MySQL InnoDB