preface

Deadlock occurs, how to troubleshoot and solve it? This article will explore that question with you

  • Prepare the data environment
  • Simulate a deadlock
  • Analyze deadlock logs
  • Analyze deadlock results

Environment to prepare

Database isolation level:

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

Automatic submission 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)
Copy the code

Table structure:

//id is an autoincrement primary key, name is a non-unique index, 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;Copy the code

Table data:

Simulation of concurrent

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

1) Transaction A performs the update operation and the update succeeds

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

2) Transaction B performs the update operation and the update succeeds

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

3) Transaction A is blocked by the insert operation

mysql> insert into account values(null,'Jay', 100);Copy the code

select * from information_schema.innodb_locks;

4) Transaction B performs the insert operation and the insert succeeds. Meanwhile, transaction A’s insert changes from blocking to deadlock error.

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

Lock is introduced

Before analyzing the deadlock log, let’s introduce the lock

Shared locks and exclusive locks

InnoDB implements standard row-level locks, including two types: shared locks (s-locks) and exclusive locks (X-locks).

  • Shared lock (S lock) : Allows a locking transaction to read a row.
  • Exclusive lock (X lock) : Allows a locked transaction to update or delete a row.

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

  • T2’s request for the S lock is immediately granted, resulting in both T1 and T2 holding the S lock on row R
  • 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.

Intent locks

  • Intentional shared lock (IS lock) : a transaction wants to acquire a shared lock on rows of a table
  • Intentional exclusive locks (IX locks) : a transaction wants to acquire exclusive locks for rows in a table

For example, if transaction 1 adds S lock to table 1 and transaction 2 wants to change a row, it needs to add IX lock and wait for S lock to be released due to incompatibility. If transaction 1 has an IS lock on table 1, the IX lock added by transaction 2 IS compatible with the IS lock and can be operated, which enables more fine-grained locking.

InnoDB storage engine locks are compatible with the following table:

Record Locks

  • Record locks are the simplest row locks,Only one row is locked. Such as:SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
  • A record lock is always placed on an index. Even if a table does not have an index, InnoDB implicitly creates an index and uses that index to enforce a record lock.
  • Blocks other transactions from inserting, updating, or deleting it

Lock_mode X locks REc but not GAP

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 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;; 2: len 7; hex b60000019d0110; asc ;;Copy the code

Gap Locks

  • A gap lock is a lock placed between two indexes, either before the first index or after the last index.
  • Using gap locks locks an interval, not just each piece of data in that interval.
  • Gap locks only prevent other transactions from inserting into the gap, they do not prevent other transactions from acquiring a gap lock on the same gap, so gap X lock and GAP S lock have the same effect.

The transaction data of gap lock (keywords: GAP before REC) is recorded as follows:

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

Next-Key Locks

  • A next-key lock is a combination of a record lock and a gap lock. It refers to a lock placed on a record and the gap in front of that record.

Insert Intention

  • An insert intent lock is a gap lock that is placed before 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.
  • Suppose there are index values 4 and 7, and several different transactions are about to insert 5 and 6. Each lock locks the gap between 4 and 7 with insert intent before acquiring an exclusive lock on the inserted row, but does not block the other because the inserted rows do not conflict.

Transaction data looks like 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 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     "; 2: len 7; hex 9000000172011c; asc r ;; .Copy the code

Lock mode compatibility matrix (horizontal is held locks, vertical is requested locks) :

How to read deadlock logs?

show engine innodb status

You can run the show engine innodb status command to check the latest deadlock log.

2020-04-11 00:35:55 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',100)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
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 0
 0: 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',100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)
Copy the code

How do we analyze the deadlock logs above?

The first part

TRANSACTION: TRANSACTION 38048

2) View the SQL being executed

insert into account values(null,'Jay', 100).Copy the code

WAITING FOR THIS LOCK TO BE GRANTED lock_mode X locks gap before rec insert intention WAITING Normal index (IDx_name), PHYSICAL RECORD, gap interval (unknown, Wei);

The second part

TRANSACTION: TRANSACTION 38049

insert into account  values(null,'Yan', 100).Copy the code

3) HOLDS THE LOCK, locks gap (lock_mode X locks gap before REC), index idx_name, physical record, interval (unknown, Wei);

5) waiting for this lock to be granted, insert intention (lock_mode X insert intention waiting), Physical record, gap interval (unknown, +∞);

5) We roll back transaction 1;

Viewing Log Results

  • The insert intent exclusive lock that transaction A is waiting on is in the arms of transaction B
  • Transaction B holds a gap lock and is waiting to insert an intentional exclusive lock

Some of you may be wondering,

  • What locks does transaction A hold? The logs don’t tell. What kind of insert intention exclusive lock does it want to take?
  • What exactly does transaction B take the gap lock? Why does it also take the insert intent lock?
  • How is an infinite loop of deadlocks created? The current log can not see the structure of the loop?

Let’s analyze each wave in detail in the next section, one question at a time

Deadlock analysis

Deadlock deadlock cycle four elements

  • Mutually exclusive: A process uses the allocated resources exclusively. That is, only one process occupies a resource in a period of time. If another process requests the resource at this time, the requester can only wait until the process holding the resource is released.
  • Request and hold conditions: a process that has held at least one resource makes a new request for the resource that has been occupied by another process. In this case, the requesting process blocks but does not release other resources that it has obtained.
  • Undeprivable condition: a resource acquired by a process cannot be deprivable until it is used up. It can only be released when it is used up.
  • Loop waiting condition: when a deadlock occurs, there must be a process — resource loop chain, that is, P0 in process set {P0, P1, P2, ··· Pn} is waiting for a resource occupied by P1. P1 is waiting for resources occupied by P2…… Pn is waiting for a resource that has been occupied by P0.

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

For ease of recording, examples use W for Wei, J for Jay, and E for Eason ha ~

Update (A); update (A); update (b)

update  account  set balance =1000 where name ='Wei';
Copy the code

Clearance lock:

  • The Update statement will add a gap lock to the name of the non-unique index (E,W) and (W, +∞).
  • Why is there a gap lock? Because this is the RR database isolation level, used to solve the phantom problem of ~

Record locks

  • Because name is an index, the UPDATE statement must have a record lock for W

Next, the Key lock

  • Next-key lock = record lock + gap lock, so the update statement has a next-key lock for (E, W)

Transaction A holds the lock after executing the update statement:

  • Next-key Lock :(E, W)
  • Gap Lock :(W, +∞)

Let’s analyze the lock condition of insert statement in transaction A

insert into account values(null,'Jay', 100);Copy the code

Clearance lock:

  • Since Jay(J is between E and W), a gap lock (E,W) needs to be requested

Insert Intention

  • An insert intent lock is A gap lock that is set prior to an insert row operation. This lock releases A signal that transaction A needs to insert an intent lock (E,W).

Insert (E,W); insert (W,W); insert (W,W); insert (W,W)

What gap locks does transaction B have? Why does it also take the insert intent lock?

SQL > update SQL > update SQL > update SQL > lock;

update  account  set balance =1000 where name ='Eason';
Copy the code

Clearance lock:

  • Select * from * where name=’Eason’; select * from * where name=’Eason’; delete from * where name=’Eason’;

Record locks

  • Since name is an index, the update statement must have a record lock for E

Next, the Key lock

  • Next-key lock = record lock + gap lock, so the Update statement has a next-key lock (-∞, E)

Transaction B will hold the lock after executing the update statement:

  • Next-key Lock :(-∞, E)
  • Gap Lock :(E, W)

Let’s analyze the lock condition of insert statement in wave B

insert into account  values(null,'Yan', 100);Copy the code

Clearance lock:

  • Since Yan(Y comes after W), we need to request a gap lock plus (W,+∞)

Insert Intention

  • The insert intent lock is A gap lock set before the insert row operation. This lock releases the signal of one insertion mode that transaction A needs to insert the intent lock (W,+∞).

(E, W); (W,+∞); (W,+∞); (W,+∞

Deadlock truth restore

Next, let’s restore the deadlock truth ~ haha ~

  • Insert (E, W) next-key Lock (W, +∞) Gap Lock (W, +∞)
  • (E, W); (E, W); (E, W)
  • When transaction A executes Insert Jay, (E, W) needs an Insert intent lock, but (E, W) is in transaction B’s arms, so it falls into A heart block
  • When transaction B executes the Insert Yan statement, it also gets stuck because it needs (W,+∞) to Insert the intent lock, but (W,+∞) is in transaction A’s arms.
  • Transaction A holds the Gap Lock of (W,+∞) and is waiting for the insertion intention Lock of (E, W). Transaction B holds the Gap Lock of (E, W) and is waiting for the insertion intention Lock of (W,+∞), so the closed loop of deadlock is formed ~ (Gap Lock and insertion intention Lock will conflict.
  • After transaction A and transaction B form A Deadlock ring, because of Innodb’s underlying mechanism, it will cause one transaction to give up resources and the other transaction to execute successfully. This is why you finally see transaction B insert successfully, but the insert of transaction A shows that Deadlock found ~

conclusion

Finally, how should we analyze deadlock problems?

  • Simulate a deadlock scenario
  • show engine innodb status; View deadlock logs
  • Find the deadlock SQL
  • SQL lock analysis, this can go to the official website to see ha
  • Analyzing deadlock logs (what locks are held, what locks are waiting for)
  • Familiar with lock mode compatibility matrix, lock compatibility matrix in InnoDB storage engine.

Personal public account

  • If you think it’s good, give me a thumbs up and attention. Thank you
  • If there is any incorrect place, please kindly point out, thank you very much.
  • At the same time, I am looking forward to friends can pay attention to my public account, behind slowly launch better dry goods ~ hee hee