Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "how to solve the MySQL deadlock problem". The editor shows you the operation process through the actual case. The operation method is simple, fast and practical. I hope this article "how to solve the MySQL deadlock problem" can help you solve the problem.

1. What is a deadlock

Deadlock is a common problem in concurrent systems, and it also occurs in the scenario of concurrent read and write requests in database MySQL. A "deadlock" occurs when two or more transactions are waiting for each other to release the lock already held or a loop waiting for lock resources due to inconsistent locking order. The common error message is Deadlock found when trying to get lock....

For example, A transaction holds X1 lock, applies for X2 lock, B transaction holds X2 lock, and applies for X1 lock. An and B transactions hold the lock and apply for the lock held by the other party to enter the loop, resulting in a deadlock.

As pictured above, it is the four car resource requests on the right that create a loop phenomenon, that is, a dead loop, leading to a deadlock.

From the definition of deadlock, several elements of deadlock in MySQL are:

Two or more transactions

Each transaction already holds a lock and applies for a new lock

Lock resources can only be held by the same transaction or incompatible at the same time

Transactions loop waiting for each other because of holding locks and applying for locks

2. InnoDB lock type

In order to analyze deadlocks, it is necessary to have an understanding of the lock types of InnoDB.

The MySQL InnoDB engine implements standard row-level locks: shared locks (S lock) and exclusive locks (X lock)

Different transactions can add S locks to the same row record at the same time.

If a transaction adds an X lock to a row of records, other transactions cannot add an S lock or an X lock, causing the lock to wait.

If transaction T1 holds an S lock for row r, when another transaction T2 requests a lock for r, it does the following:

T2 request S lock is allowed immediately, as a result, T1 T2 holds the S lock of r line.

T2 request X lock cannot be allowed immediately

If T1 holds r's X lock, then T2 requests r's X and S locks cannot be allowed immediately. T2 must wait for T1 to release X locks, because X locks are not compatible with any locks. The compatibility of shared and exclusive locks is as follows:

2.1.Gap lock (gap lock)

The gap lock locks a gap to prevent insertion. Assuming that the index column has three values of 2J4 and 8, if you lock 4, you will also lock the gaps (2p4) and (4J8). Other transactions cannot insert records with index values between the two gaps. However, there is one exception to the gap lock:

If the index column is a unique index, only the record is locked (only row locks are added), not gaps.

For federated and unique indexes, a gap lock will still be added if the where condition includes only part of the federated index.

2.2 、 next-key lock

Next-key lock is actually a combination of row locks plus the gap lock in front of this record. Assuming that there are index values of 10, 10, 11, 13 and 20, then possible next-key lock include:

(negative infinity, 10], (10), (11) 13], (13) 20], (20, positive infinity)

Under the RR isolation level, InnoDB uses next-key lock primarily to prevent phantom reading problems.

2.3.intention lock (Intention lock)

In order to support multi-granularity locking, InnoDB allows row locks and table locks to exist at the same time. In order to support locking operations at different granularities, InnoDB supports an additional locking method, called Intention Lock. The intention lock is to divide the locked object into multiple levels, which means that the transaction wants to lock at a finer granularity. There are two types of intention locks:

Intention shared lock (IS): a transaction intentionally imposes a shared lock on some rows in a table

Intention exclusive lock (IX): a transaction intentionally adds an exclusive lock to certain rows in a table

Because the InnoDB storage engine supports row-level locks, intentional locks do not actually block any request except a full table scan. The compatibility of table-level intention locks with row-level locks is as follows:

2.4. insert intention lock (Insert Intention lock)

An insert intention lock is a gap lock set before inserting a row of records, which signals an insertion mode, that is, when multiple transactions are inserted in the same index gap, they do not have to wait for each other if they are not in the same position in the insertion gap. Suppose a column has an index value of 2Power6, as long as two transactions are inserted in different positions (such as transaction An insert 3, transaction B insert 4), then they can be inserted at the same time.

2.5. Lock mode compatibility matrix

Horizontally, the lock is held, and vertically, the lock is being requested:

3. Read the deadlock log

Before conducting a specific case study, let's first understand how to read the deadlock log and use the information in the deadlock log as much as possible to help us solve the deadlock problem.

The database scenario for the following test case is as follows: MySQL 5.7transaction isolation level is RR

The table structure and data are as follows:

Test cases are as follows:

You can see the log of the most recent deadlock by executing show engine innodb status.

3.1. The log analysis is as follows:

1. * (1) TRANSACTION: TRANSACTION 2322, ACTIVE 6 sec starting index read

The transaction number is 2322, active for 6 seconds, and starting index read indicates that the transaction status is to read data according to the index. Other common states are:

Mysql tables in use 1 indicates that the current transaction uses a table.

Locked 1 indicates that there is a table lock on the table, or LOCK_IX for DML statements

LOCK WAIT 2 lock struct (s), heap size 1136, 1 row lock (s)

LOCK WAIT indicates waiting for a lock, 2 lock struct (s) indicates that the length of the trx- > trx_locks linked list is 2, and each linked list node represents a lock structure held by the transaction, including table locks, record locks, self-adding locks, and so on. In this use case, 2locks stands for IX lock and lock_mode X (Next-key lock)

1 row lock (s) represents the number of row record locks / gap locks held by the current transaction.

MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating

MySQL thread id 37 indicates that the thread executing the transaction ID is 37 (that is, the ID shown by show processlist;)

Delete from student where stuno=5 represents the sql that transaction 1 is executing. The sad thing is that the show engine innodb status cannot view the complete sql, and usually shows the sql currently waiting for the lock.

* (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting

RECORD LOCKS represents the record lock, which indicates that transaction 1 is waiting for the X lock of idx_stuno on the table student, which in this case is actually Next-Key Lock.

The log for transaction 2 is similar to the above analysis:

2. * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X

Shows that insert into student (stuno,score) values (2m 10) of transaction 2 holds the Lock mode X of axiom 5

LOCK_gap, but we cannot see the delete from student where stuno=5 executed by transaction 2 from the log

This is also the root cause of the problem that it is difficult for DBA to analyze deadlocks based on logs alone.

3.* (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X locks gap before rec insert intention waiting

The insert statement indicating transaction 2 is waiting for the insertion of the intention lock lock_mode X locks gap before rec insert intention waiting (LOCK_X + LOCK_REC_gap)

4. Classic case study 4.1.Transactional concurrency insert unique key conflict

The table structure and data are as follows:

Test cases are as follows:

The log analysis is as follows:

Transaction T2 insert into T7 (id,a) values (26Jing 10) statement insert succeeded, holding exclusive row lock (Xlocks rec but no gap) of axiom 10

Transaction T1 insert into T7 (id,a) values (30jue 10), because the first insert of T2 has been inserted into the record of axiom 10, and transaction T1 insert axiom 10 has a unique key conflict, which needs to apply for a unique index of the conflict plus S Next-key Lock (that is, lock mode S waiting). This is a gap lock that will apply to lock the gap area between (, 10], (10) and 20].

Transaction T2 insert into T7 (id,a) values (40Magne9) the value of axi9 inserted by this statement is between the gap lock 4-10 requested by transaction T1, so the second insert statement of transaction T2 waits for the S-Next-key Lock lock of transaction T1 to be released and the lock_mode X locks gap before rec insert intention waiting is displayed in the log.

4.2.The problem of concurrent deadlock after update and insert

The table is structured as follows, with no data:

Test cases are as follows:

Deadlock analysis:

You can see the records in which the two transaction update do not exist, and then obtain the gap lock (gap lock). The gap locks are compatible so there is no blocking in the update link. Both hold gap locks and then compete to insert intention locks. When there is another session holding a gap lock, the current session cannot apply for an insert intent lock, resulting in a deadlock.

5. How to avoid deadlock as much as possible

The index is designed reasonably, and the columns with high differentiation are placed in front of the composite index, so that the business SQL can locate fewer rows through the index as much as possible, and reduce lock competition.

Adjust the execution order of the business logic SQL to avoid update/delete holding the lock SQL in front of the transaction for a long time.

Avoid large transactions, try to split large transactions into multiple small transactions to deal with, small transactions are less likely to have lock conflicts.

Access tables and rows in a fixed order. For example, for two transactions that update data, transaction A updates data in the order of 1mem2, and transaction B updates data in the order of 2mem1. This is more likely to cause a deadlock.

In systems with high concurrency, do not explicitly lock, especially in transactions. Such as select... For update statement, if you are in a transaction (running start transaction or setting autocommit equal to 0), then the found record is locked.

Try to press the primary key / index to find records, range search increases the possibility of lock conflicts, and do not use the database to do some extra quota calculation. For example, some programs will use statements like "select … where … order by rand ();". Because statements like this do not need an index, it will cause the data of the entire table to be locked.

Optimize SQL and table design to reduce the consumption of too many resources at the same time. For example, reduce the number of joined tables and decompose a complex SQL into multiple simple SQL.

This is the end of the content on "how to solve the MySQL deadlock problem". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.