Hello everyone, I am Wolf King, a programmer who loves playing ball

We use MySQL most likely will encounter deadlock problem, this is a very headache problem. This article will introduce deadlocks, analyze and discuss common deadlock cases, and give some suggestions on how to avoid deadlocks as much as possible.

Don't say a word, turn it on!

What is a deadlock

Deadlock is a common problem in concurrent systems, and also occurs in the scenario of concurrent read and write requests of MySQL database. A “deadlock” occurs when two or more transactions are waiting for each other to release a lock they already hold or are waiting for resources to be locked in a loop because they are not locked in the same order. A common error message is that no Deadlock is found when trying to get lock… .

For example, transaction A holds the X1 lock and applies for the X2 lock, while transaction B holds the X2 lock and applies for the X1 lock. A deadlock occurs when transactions A and B hold the lock and apply the lock held by the other party to wait in A loop.

As shown in the figure above, it is the four cars on the right that are requesting resources that create a loop phenomenon, or an infinite loop, resulting in a deadlock.

From the definition of deadlock, the following factors can occur in MySQL:

  1. Two or more transactions
  2. Each transaction already holds a lock and requests a new lock
  3. Lock resources can only be held by the same transaction at the same time or are incompatible
  4. Transactions loop around each other because they hold and apply for locks

InnoDB lock type

To analyze deadlocks, it is necessary to have an understanding of InnoDB’s lock types.

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

  1. Different transactions can lock the same row at the same time.
  2. If one transaction holds an X lock on a row, other transactions cannot hold an S lock or an X lock, resulting in a lock wait.

If transaction T1 holds the S lock on row R, then another transaction T2 requests the lock on row R and does the following:

  1. T2’s request for the S lock is immediately granted, resulting in both T1 and T2 holding the S lock on row R
  2. T2 requesting X lock was not allowed immediately

If T1 holds the X lock on R, then T2 cannot request the X and S locks on r immediately. T2 must wait for T1 to release the X lock, because the X lock is incompatible with any locks. The compatibility of shared and exclusive locks is as follows:

Gap lock

A gap lock locks a gap to prevent insertion. If the index column has 2,4, and 8 values, then the gaps (2,4) and (4,8) will also be locked if 4 is locked. Other transactions cannot insert records with index values between these two gaps. However, there is an exception to gap locking:

  1. If the index column is a unique index, only the record (row only) is locked, not the gap.
  2. For a federated index that is unique, a gap lock is still applied if the WHERE condition contains only a portion of the federated index.

next-key lock

Next-key lock is essentially a combination of the row lock and the gap lock that precedes the record. Given the index values 10,11,13, and 20, the possible next-key locks include:

(minus infinity,10],(10,11],(11,13],(13,20],(20, plus infinity)

InnoDB uses next-key lock in RR isolation to prevent phantom read problems.

Intention lock

InnoDB allows row and table locks in order to support multi-granularity locking. To support locking at different granularity, InnoDB supports an additional locking method called Intention Lock. Intent locking is to divide the locked object into multiple layers. Intent locking means that the transaction wants to be locked at a finer granularity. There are two types of intent locks:

  1. Intentional shared lock (IS) : A transaction intends to place a shared lock on some rows in a table
  2. Intentional exclusive lock (IX) : the transaction has the intention to apply exclusive locks on certain rows in the table

Because InnoDB storage engine supports row-level locks, intentional locks don’t actually block any requests other than full table scans. Table level intent locks are compatible with row level locks as follows:

Insert Intention lock

An insert intent lock is a gap lock that is set prior to an insert row operation. This lock releases an insert signal that multiple transactions inserted at the same index gap do not need to wait for each other unless they are inserted at the same place in the gap. Given that A column has index values 2,6, as long as the two transactions are inserted in different places (for example, transaction A is inserted into 3 and transaction B is inserted into 4), then they can be inserted simultaneously.

Lock mode compatibility matrix

Horizontal is the lock held, vertical is the lock being requested:

Read the deadlock log

Before doing a case study, let’s look at how to read the deadlock log and use the information in the deadlock log to help solve the deadlock problem as much as possible.

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

Table structure and data are as follows:

For example:

You can run the show engine Innodb status command to view logs about the latest deadlock.

Log analysis is as follows:

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

The transaction number is 2322, and the value is active for 6 seconds. Starting Index read indicates that the transaction state is read from 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, LOCK_IX for DML statements

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

2 LOCK struct(s) TRX ->trx_locks the length of the chain table is 2. Each chain node represents a LOCK structure for the transaction, including table locks, record locks, and increment locks. In this use case, 2LOCKS represents IX locks 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 = show processList; MySQL thread ID 37 = show processList; Display ID)

Delete from student where stuno=5 indicates the SQL being executed by transaction 1. Show Engine Innodb status does not show the full SQL, and usually shows the SQL currently awaiting 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 RECORD LOCKS. This item indicates that transaction 1 is waiting for the X Lock of IDx_STUNo on table Student, which in this case is actually a next-key Lock.

The log for 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

Insert into Student (stuno,score) values(2,10) holds Lock mode X with a=5

| LOCK_gap, but we can’t see the transaction 2 implementation from the log delete from student where stuno = 5;

This is also the root cause of the difficulty for DBAs to analyze deadlock problems based on logs 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

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:For example:Log analysis is as follows:

  1. SQL > insert into t7(id,a) values (26,10)Xlocks rec but no gap
  2. Insert into t7(id,a) values (30,10), Insert a=10 into T1 (a=10); insert a=10 into T2 (a=10); insert a=10 into T1 (a=10)Clearance lockThe gap area between (,10],(10,20) will be locked.
  3. Transaction T2 insert into t7(id,a) values (40,9Gap lock between 4 and 10, so the second INSERT statement that requires transaction T2 will wait for transaction T1S - the Next - key Lock LockLock_mode X locks gap before REc INSERT intention waiting

Case 1: Concurrent update and insert deadlock problem

The table structure is as follows, with no data:For example:Deadlock analysis:

You can see that two transaction update records that do not exist are obtained successivelyGap lock (gap lock)Gap locks are compatible with each other so they do not block during update. Both hold gap locks and then compete for insertsIntent locks. When another session holds the GAP lock, the current session cannot apply for the intended lock, resulting in a deadlock.

How to avoid deadlocks as much as possible

  1. Design the index properly, placing highly differentiated columns in front of the composite index so that the business SQL passes through the index as much as possibleLocate fewer rows, reducing lock contention.
  2. Adjust the order of business logic SQL execution to avoid UPDATE/DELETE SQL that holds locks for a long time before transactions.
  3. avoidLarge transactions, try to split large transactions into multiple small transactions to handle, small transactions are less likely to lock conflicts.
  4. In order toFixed orderAccess tables and rows. For example, if two transactions update data, transaction A updates data in the order of 1,2; Transaction B updates data in the order of 2,1. This is more likely to cause deadlocks.
  5. In systems with high concurrency, do not explicitly lock, especially in transactions. Such as the select… For UPDATE statement, if in a transaction(Run start Transaction or set autoCOMMIT to 0), the records found are locked.
  6. As far as possible according to thePrimary key/indexLook for records, range lookup increases the possibility of lock conflicts, and don’t use the database to do extra quota calculations. For example, some programs use “select… The where… The order by the rand ();” Statements like this, which do not use indexes, will result in the entire table being locked.
  7. Optimize SQL and table design to reduce the use of too many resources at once. For instance,Reduce joined tables, will be complex SQLdecompositionFor multiple simple SQL.

All right. That’s all for today. I will continue to share what I have learned and thought. I hope we can walk on the road to success together!

Willing to output dry Java technology public number: Wolf king programming. The public number has a large number of technical articles, massive video resources, beautiful brain map, might as well pay attention to it! Get lots of learning resources and free books!

Forwarding moments is the biggest support for me!

\

Click “like and watching” if you feel something! Thank you for your support!