Ali on the second side: How to solve the MySQL deadlock problem?

Hello everyone, I am the wolf king, a programmer who loves to play

There is a high probability that we will encounter deadlock when using MySQL, which is really a very headache. This article will give a corresponding introduction to deadlock, analyze and discuss common deadlock cases, and give some suggestions on how to avoid deadlock as much as possible.

话不多说,开整!

What is a deadlock

Deadlock is a common problem in concurrent systems, and it also appears in the scenario of concurrent read and write requests for the database MySQL. When two or more transactions, both parties are waiting for the other to release the locks they have already held, or because the lock sequence is inconsistent, a "deadlock" will occur. The common error message is Deadlock found when trying to get lock....

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

As shown in the figure above, it is the four car resource requests on the right that have caused a loop phenomenon, that is, an infinite loop, which leads to a deadlock.

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

  1. Two or more transactions
  2. Each transaction already holds a lock and applies for a new lock
  3. Lock resources can only be held by the same transaction at the same time or are incompatible
  4. Because of holding locks and applying locks between transactions, they wait for each other in a loop

InnoDB lock type

In order to analyze deadlocks, it is necessary for us to have an understanding of InnoDB's lock types.

The MySQL InnoDB engine implements the standard行级别锁:共享锁( S lock ) 和排他锁 ( X lock )

  1. Different transactions can add S locks to the same row at the same time.
  2. If a transaction adds X locks to a row record, other transactions cannot add S locks or X locks, resulting in lock waiting.

If transaction T1 holds the S lock of row r, when another transaction T2 requests the lock of r, it will do the following:

  1. T2 requests the S lock to be allowed immediately, and as a result, both T1 and T2 hold the S lock for row r
  2. T2 request X lock cannot be allowed immediately

If T1 holds the X lock of r, neither X nor S locks of r requested by T2 can be immediately allowed, and T2 must wait for T1 to release the X lock, because X locks are incompatible with any locks. The compatibility of shared locks and exclusive locks is as follows:

Gap lock

The gap lock locks a gap to prevent insertion. Assuming that the index column has three values ​​of 2, 4, and 8, if 4 is locked, the two gaps (2,4) and (4,8) will also be locked at the same time. Other transactions cannot insert records whose index values ​​are between these two gaps. However, there is an exception to the gap lock:

  1. If the index column is the only index, then only this record will be locked (only row locks are added), and the gap will not be locked.
  2. For a joint index and a unique index, if the where condition only includes a part of the joint index, the gap lock will still be added.

next-key lock

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

(Negative infinity, 10), (10, 11), (11, 13), (13, 20), (20, positive infinity)

In the RR isolation level, InnoDB uses next-key lock to prevent 幻读problems.

Intention lock

InnoDB supports multi-granularity locking, allowing 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. Intentional locks divide the locked objects into multiple levels. Intentional locks mean that the transaction hopes to lock at a more granular level. There are two types of intention locks:

  1. Intentional shared lock (IS ): The transaction intentionally adds a shared lock to certain rows in the table
  2. Intentional exclusive lock (IX): The transaction intentionally adds exclusive locks to certain rows in the table

Since the InnoDB storage engine supports row-level locks, intent locks do not actually block any requests other than full table scans. The compatibility of table-level intent locks and row-level locks is as follows:

Insert Intention lock

The insertion intention lock is a gap lock set before inserting a row of records. This lock releases a signal of the insertion mode, that is, when multiple transactions are inserted in the same index gap, if they are not inserted in the same position in the gap, they do not need to Waiting for each other. Assuming that a column has index values ​​of 2, 6, as long as two transactions are inserted at different positions (for example, transaction A inserts 3, transaction B inserts 4), then they can be inserted at the same time.

Lock mode compatibility matrix

The horizontal is the held lock, and the vertical is the lock being requested:

Read the deadlock log

Before conducting specific case analysis, 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 scenarios of the following test cases are as follows:MySQL 5.7 事务隔离级别为 RR

The table structure and data are as follows:

The test case is as follows:

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

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, 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 Explain that the current transaction uses a table.

locked 1 Indicates that there is a table lock on the table, which is LOCK_IX for DML statements

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

LOCK WAITIndicates that it is waiting for a lock, which 2 lock struct(s)means that the length of the trx->trx_locks chain is 2, and each chain node represents a lock structure held by the transaction, including table locks, record locks, and self-increasing locks. In this use case, 2locks means IX lock and lock_mode X (Next-key lock)

1 row lock(s) Indicates 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 ID that executes the transaction is 37 (i.e. show processlist; ID shown)

delete from student where stuno=5Sql 1 represents the transaction is being executed, the more uncomfortable thing show engine innodb statusis to look at less than full sql usually show 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 means record locks. This entry means that transaction 1 is waiting for the X lock of idx_stuno on the student table. In this case, it is actually Next-Key Lock.

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

  1. ***** (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

Show that insert into student(stuno,score) values(2,10) of transaction 2 holds Lock mode X with a=5

| LOCK_gap, but we cannot see the delete from student where stuno=5;

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

  1. ***** (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

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

Classic case analysis

Case 1: Transaction Concurrent Insert Unique Key Conflict

The table structure and data are as follows: The

test case is as follows: The

log analysis is as follows:

  1. Transaction T2 insert into t7(id,a) values ​​(26,10) statement insert is successful, holding a=10 排他行锁( Xlocks rec but no gap )
  2. Transaction T1 insert into t7(id,a) values ​​(30,10), because the first insert of T2 has inserted a=10 record, transaction T1 insert a=10 will cause a unique key conflict, and you need to apply for the uniqueness of the conflict. Index plus S Next-key Lock (ie lock mode S waiting) This is a 间隙锁gap area between (,10] and (10,20 ) that will apply to lock.
  3. Transaction T2 insert into t7 (id, a ) values (40,9) of the statement into a transaction value T1 = 9 in the application gap 锁4-10之间, it takes a second transaction T2 waits for the transaction T1 insert statement is S-Next-key Lock 锁released, the log Show lock_mode X locks gap before rec insert intention waiting.

Case 1: Concurrent deadlock problem of update first and then insert

The table structure is as follows, no data: The

test case is as follows:

Deadlock analysis:
You can see that the records of two transaction updates that do not exist are obtained one after another 间隙锁( gap 锁). The gap locks are compatible, so the update link will not be blocked. Both hold gap locks and then compete for insertion 意向锁. When there are other sessions holding gap locks, the current session cannot apply for the insertion intention lock, resulting in deadlock.

How to avoid deadlock as much as possible

  1. Design the index reasonably, put the columns with high degree of discrimination in front of the composite index, so that the business SQL can pass the index as much as possible 定位更少的行,减少锁竞争.
  2. Adjust the execution order of business logic SQL to avoid update/delete SQL holding locks for a long time before the transaction.
  3. Avoid 大事务, try to split large transactions into multiple small transactions for processing, and the chance of lock conflicts in small transactions is smaller.
  4. To 固定的顺序access tables and rows. For example, for two transactions that update data, the order of transaction A to update data is 1, 2; the order of transaction B to update data is 2, 1. This is more likely to cause deadlock.
  5. In a system with high concurrency, do not explicitly add locks, especially in transactions. Such as select… for update statement, if it is in a transaction (运行了 start transaction 或设置了autocommit 等于0), then the records found will be locked.
  6. Try 主键/索引to search for records as far as possible . Range search increases the possibility of lock conflicts, and don't use the database to do some extra quota calculations. For example, some programs will use statements such as "select… where… order by rand();". Since such statements do not use indexes, the data in the entire table will be locked.
  7. Optimize SQL and table design to reduce the situation of occupying too many resources at the same time. For example,, 减少连接的表the complex SQL 分解is divided into multiple simple SQL.

All right. That's it for today, and I will continue to share what I have learned and thought, and hope that we will walk on the road to success together!

Java technology public account that is willing to output dry goods: Wolf King Programming . There are a large number of technical articles, massive video resources, and exquisite brain maps in the public account, so please pay attention! Reply to the information to receive a large number of learning resources and free books!   Reposting the circle of friends is my greatest support!    

If you feel something, just click "Like and watching"! Thank you for your support!