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

How to solve the MySQL deadlock, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Environmental preparation

Database isolation level:

Mysql > select @ @ tx_isolation; +-+ | @ @ tx_isolation | +-+ | REPEATABLE-READ | +-+ 1 row in set, 1 warning (0.00 sec)

Auto submit off:

Mysql > set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql > select @ @ autocommit; +-+ | @ @ autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec)

Table structure:

/ / id is a self-increasing primary key, name is a non-unique index, balance normal fields CREATE TABLE `account` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (255) DEFAULT NULL, `balance` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_ name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Data in the table:

Analog concurrency

Open two terminals to simulate transaction concurrency, and the execution sequence and experimental phenomena are as follows:

1) transaction A performs an update operation, and the update is successful

Mysql > update account set balance = 1000 where name = 'Wei'; Query OK, 1 row affected (0.01sec)

2) transaction B performs an update operation, and the update is successful

Mysql > update account set balance = 1000 where name = 'Eason'; Query OK, 1 row affected (0.01sec)

3) transaction A performs insert operation and gets stuck ~

Mysql > insert into account values (null,'Jay',100)

You can use it at this time.

Select*frominformation_schema.innodb_locks

Check the lock condition:

4) transaction B performs the insert operation, and the insertion is successful, and the insertion of transaction A changes from blocking to deadlock error.

Mysql > insert into account values (null,'Yan',100); Query OK, 1 row affected (0.01sec)

Lock introduction

Before analyzing the deadlock log, let's do a lock introduction, .

Mainly introduce compatibility and lock mode types of locks:

Shared lock and exclusive lock

InnoDB implements standard row-level locks, including shared locks (s locks for short) and exclusive locks (x locks for short).

Shared lock (S lock): allows lock-holding transactions to read a row.

Exclusive lock (X lock): allows lock-holding transactions to update or delete a row.

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 the x lock of r, then the x and s locks of T2 request r cannot be allowed immediately. T2 must wait for T1 to release the x lock, because the X lock is not compatible with any lock.

Intention lock

Intention shared lock (IS lock): a transaction wants to acquire a shared lock for several rows in a table

Intention exclusive lock (IX lock): a transaction wants to acquire an exclusive lock for certain rows in a table

For example, after transaction 1 adds an S lock on Table 1, transaction 2 needs to add an IX lock if it wants to change a row record. Because it is not compatible, it needs to wait for the S lock to be released; if transaction 1 adds an IS lock on Table 1, the IX lock added by transaction 2 is compatible with the IS lock, and it can be operated, which enables finer-grained locking.

The compatibility of locks in the InnoDB storage engine is as follows:

Record lock (Record Locks)

The record lock is the simplest row lock, locking only one row. Such as: SELECT C1 FROM t WHERE c1=10FOR UPDATE

Record locks are always added to the index, and even if a table does not have an index, InnoDB implicitly creates an index and uses it to implement record locking.

It will block other transactions from inserting, updating and deleting it.

Record the transaction data of the lock (keyword: lock_mode X locks rec butnotgap), record as follows:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 10078 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000000a; asc;; 1: len 6; hex 000000274f; asc'Oscape; 2: len 7; hex b60000019d0110; asc

Gap lock (Gap Locks)

A gap lock is a lock added between two indexes, either before the first index or after the last index.

A gap lock is used to lock an interval, not just every piece of data in that interval.

Gap locks only prevent other transactions from being inserted into the gap, and they do not prevent other transactions from acquiring gap locks on the same gap, so gap x lock and gap s lock have the same effect.

The transaction data of the gap lock (keyword: gap before rec), recorded as follows:

RECORD LOCKS space id 177The page no 4 n bits 80 index idx_name of table `test2`.`roomt` trx id 38049 lock_mode X locks gap before rec Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 3; hex 576569; asc Wei;; 1: len 4; hex 800002; asc

Next-Key Locks

A Next-key lock is a combination of a record lock and a gap lock, which refers to the lock added to a record and the gap in front of the record.

Insert intention lock (Insert Intention)

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.

Assuming that there are index values 4 and 7, several different transactions are ready to insert 5 and 6, and each lock locks the gap between 4 and 7 with an insert intention lock before acquiring an exclusive lock for the inserted row, but does not block the other because the inserted row does not conflict.

Transaction data is similar to the following:

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child` trx id 8731 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 80000066; asc f-Ting; 1: len 6; hex 0000002215; asc "; 2: len 7; hex 90000172011c; asc r;;.

Lock mode compatibility matrix (horizontal is the lock already held, vertical is the lock being requested):

How to read the deadlock log?

Show engine innodb status

You can use show engine innodb status to view the last deadlock log. After execution, the deadlock log is as follows:

0x243c * * (1) TRANSACTION: TRANSACTION 38048, ACTIVE 92 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct (s), heap size 1136, 4 row lock (s), undo log entries 2 MySQL thread id 53, OS thread handle 2300, query id 2362 localhost:: 1 root update insert into account values (null,'Jay' (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 177page no 4 n bits 80 index idx_name of table `test2`.`roomt` trx id 38048 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 6 PHYSICAL RECORD: n_fields 2 Compact format; info bits 00: len 3; hex 576569; asc Wei;; 1: len 4; hex 80000002; asc * * (2) TRANSACTION: TRANSACTION 38049, ACTIVE 72 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 5 lock struct (s), heap size 1136, 4 row lock (s), undo log entries 2 MySQL thread id 52, OS thread handle 9276, query id 2363 localhost:: 1 root update insert into account values (null,'Yan') * * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 177page no 4 n bits 80 index idx_name of table `test2`.`roomt` trx id 38049 lock_mode X locks gap before rec Record lock, heap no 6 PHYSICAL RECORD: n_fields 2 Compact format; info bits 00: len 3; hex 576569; asc Wei;; 1: len 4; hex 80000002; asc; * (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 177page no 4n bits 80 index idx_name of table `test2`.`Secrett` trx id 38049 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;; * * WE ROLL BACK TRANSACTION (1)

How do we analyze the above deadlock log?

The first part

1) find the keyword TRANSACTION, transaction 38048

2) View the SQL that is being executed

Insert into account values (null,'Jay',100)

3) waiting for lock release (WAITING FOR THIS LOCK TO BE GRANTED), insert intention exclusive lock (lockmode X locks gap before rec insert intention waiting), normal index (idxname), physical record (PHYSICAL RECORD), gap interval (unknown, Wei)

Part II

1) find the keyword TRANSACTION, transaction 38049

2) View the SQL that is being executed

Insert into account values (null,'Yan',100)

3) hold lock (HOLDS THE LOCK), gap lock (lockmode X locks gap before rec), normal index (index idxname), physical record (physical record), interval (unknown, Wei)

4) waiting for lock release (waiting for this lock to be granted), insert intention lock (lockmode X insert intention waiting), normal index (index idxname), physical record (physical record), gap interval (unknown, + ∞)

5) transaction 1 rollback (we roll back transaction 1)

View log results

Check the log to get:

The insertion intention exclusive lock that transaction An is waiting for (transaction An is transaction 1 of the log, which is seated according to the insert statement) is in the arms of transaction B.

Transaction B holds a gap lock and is waiting to insert an intention exclusive lock

In this, some friends may have doubts.

What locks does transaction A hold? The journal doesn't show at all. What kind of insertion intention exclusive lock does it want to take?

What specific gap lock did transaction B take? Why does it also want to insert the intention lock?

How is the deadlock loop formed? At present, the log can not see the composition of the endless cycle?

Let's analyze the waves in detail in the next section, one question at a time.

Deadlock analysis

Four elements of deadlock cycle

Mutual exclusion condition: refers to the exclusive use of allocated resources by a process, that is, a resource is occupied by only one process for a period of time. If there are other processes requesting resources at this time, the requestor can only wait until the process in possession of the resource is released.

Request and retention condition: refers to that the process has maintained at least one resource, but has made a new resource request, and the resource has been occupied by other processes, and the requesting process is blocked, but does not let go of other resources it has acquired.

No deprivation condition: refers to the resources that have been acquired by the process, which cannot be deprived until they are used up, but can only be released by themselves at the end of use.

Loop waiting condition: when a deadlock occurs, there must be a circular chain of process-resources, that is, the P0 in the process set {P0reP1dP2, Pn} is waiting for the resources occupied by P1; P1 is waiting for the resources occupied by P2. Pn is waiting for resources that have been occupied by P0.

What locks does transaction A hold? What kind of insertion intention exclusive lock does it want to take?

In order to facilitate recording, the example uses W for Wei,J, Jay,E for Eason, ha ~

Let's first analyze the locking of update statements in transaction A.

Update account set balance = 1000 where name = 'Wei'

Gap lock:

The Update statement adds a gap lock in the left interval to the name of the non-unique index, and the gap lock in the right interval (because there is only one record of name='Wei' in the table at present, there is no intermediate gap lock ~), that is, (EMagol W) and (Wforce + ∞)

Why is there a gap lock? Because this is the database isolation level of RR, which is used to solve phantom reading problems.

Record lock

Because name is an index, the update statement must add a record lock of W

Next-Key lock

Next-Key lock = record lock + gap lock, so the update statement has a Next-Key lock of (EMagol W]

To sum up, transaction A holds the lock after executing the update update statement:

Next-key Lock: (Erecom W]

Gap Lock: (WBI + ∞)

Let's analyze the locking of insert statements in a wave of transactions A.

Insert into account values (null,'Jay',100)

Gap lock:

Because Jay (J is between E and W), you need to request a gap lock (EMagol W).

Insert intention lock (Insert Intention)

Insert intention lock is a gap lock set before inserting a row record operation. This lock sends a signal of insertion mode, that is, transaction A needs to insert intention lock (EForce W).

Therefore, after the execution of the update statement and the insert statement of transaction A, it holds the Next-Key lock of (EMagol W), the Gap lock of (WMague + ∞), and wants to get the insertion intention exclusive lock of (EMagol W). The waiting lock is matched with the deadlock log, ~

What gap locks does transaction B have? Why does it also want to insert the intention lock?

By the same token, let's take a look at a wave of lock analysis of the transaction BJR update statement:

Update account set balance = 1000 where name = 'Eason'

Gap lock:

The Update statement adds a gap lock in the left interval to the name of the non-unique index, and the gap lock in the right interval (because there is only one record of name='Eason' in the table, there is no intermediate gap lock ~), that is, (- ∞, E) and (EMagol W)

Record lock

Because name is an index, the update statement must add a record lock for E

Next-Key lock

Next-Key lock = record lock + gap lock, so the Update statement has a Next-Key lock of (- ∞, E)

To sum up, transaction B holds the lock after executing the update update statement:

Next-key Lock: (- ∞, E]

Gap Lock: (EMagol W)

Let's analyze the locking of insert statements in a wave of B.

Insert into account values (null,'Yan',100)

Gap lock:

Because Yan (Y is after W), you need to request a gap lock with (WMJ + ∞).

Insert intention lock (Insert Intention)

Insert intention lock is a gap lock set before inserting a row record operation. This lock sends a signal of insertion mode, that is, transaction A needs to insert intention lock (WGI + ∞)

Therefore, after the execution of the update statement and insert statement of transaction B, it holds the Next-Key lock of (- ∞, E) and the Gap lock of (EMagol W). It wants to get the gap lock of (WLI + ∞), that is, to insert the intention exclusive lock, and the locking situation is also matched with the deadlock log.

Deadlock truth restoration

Next, let's restore the truth of deadlock ~ ~

Transaction An executes the statement of Update Wei, holds the Next-key Lock of (EMagol W], the Gap Lock of (WMA + ∞), and inserts successfully ~

Transaction B executes the Update Eason statement, holds the Next-Key Lock of (- ∞, E) and the Gap Lock of (EMagol W), and inserts successfully ~

When transaction An executes the statement of Insert Jay, it gets stuck because it needs an insert intention lock, but it is in the arms of transaction B.

When transaction B executes the statement of Insert Yan, it also gets stuck because it needs an insert intention lock of (WMague + ∞), but (WMague + ∞) is in the arms of transaction A.

Transaction A holds (WMague + ∞) Gap Lock, while transaction B holds (EMagneW) insert intention lock, transaction B holds (EMagol W) Gap lock, and waits for (WGraI + ∞) insertion intention lock, so it forms a deadlock closed loop ~ (Gap lock conflicts with insertion intention lock, see the lock mode compatibility matrix introduced by the lock).

Because of the underlying mechanism of Innodb, it causes one transaction to give up resources and the other transaction executes successfully, which is why you finally see transaction B insert successfully, but the insertion of transaction A shows Deadlock found ~

This is the answer to how to solve the problem of MySQL deadlock. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

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.