MySQL deadlock troubleshooting notes

This article is an accident troubleshooting note for deadlock in the production environment. You can learn from reading this article:

What are the locks commonly used in Innodb?
Are locks compatible or incompatible?
What is the locking principle of Update statement?
Let's restore the scene of the accident with me

Operation background

MySQL 8.0.20
Enable auto commit transactions (autocommit=1)
Transaction isolation level REPEATABLE-READ (RR)
The table for the operation has no primary key and index

Reproduction steps

Note: client 1 is transaction 1, which will be defaulted if not described later

Create a new blank table t with only two id and name fields and no primary key

CREATE TABLE `t` (
  `id` int DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Create a new client, open new transaction 1, and client 2, open new transaction 2

The client adds a new piece of data

 insert into t values(1,'1');
 Query OK, 1 row affected (0.00 sec)

Client 2 adds a new piece of data

insert into t values(2,'2');
Query OK, 1 row affected (0.00 sec)

As soon as the client executes the update statement, blocking occurs, waiting for execution, and no results are output
```
update t set name ='update 1' where id =1;
```
Client 2 executes the update statement and returns the deadlock found, which is what we will discuss in this article. Who is competing for resources
```
update t set name='update2' where id =2;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
```
At this time, once the client returns the response, the execution is successful, that is, we see a deadlock, but one data insertion is successful and the other data insertion fails

The reproduction process is over. Let's follow me to see who is competing for resources?

Essential concepts

Lock information

S (Shared lock) Shared lock if transaction T adds s lock to data object a, transaction T can read a but cannot modify A. other transactions can only add s lock to a instead of X lock until T releases s lock
X () exclusive lock if transaction T applies an X lock to data object a, it is only allowed to read and modify A. other transactions cannot apply any type of lock to a until T releases the X lock on a
IS intended to share lock transaction T needs to add an IS (or stronger IX) lock to the table before adding an S lock to the data object in the table
IX intent exclusive lock transaction T needs to add an IX lock to the table before adding an X lock to the data object in the table

Lock compatibility

	X	IX	S	IS
X	conflict	conflict	conflict	conflict
IX	conflict	compatible	conflict	compatible
S	conflict	conflict	compatible	compatible
IS	conflict	compatible	compatible	compatible

When locking existing rows (primary key), mysql has only row locks.

When locking nonexistent rows (even if the condition is primary key), mysql will lock a range (with gap lock)

Row lock

record lock
Only one row of the index record is locked. A single index record is locked. record lock always locks the index, not the record itself
gap lock
Lock only one index interval (open interval), lock in the gap between index records, or lock before or after an index record, excluding the index record itself
Next key lock
record lock+gap lock left open right close interval

Statements used to troubleshoot deadlocks

View deadlock information
```
show engine innodb status
```
Viewing the current transaction information will be output in the form of table without \ g, and the output with \ G is more intuitive and good-looking
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
```
View lock information held by current transaction
```
SELECT * FROM performance_schema.data_locks\G
```

View the meaning of the returned field of the transaction holding lock information

LOCK_STATUS
Status of the lock request.
This value depends on the storage engine. For InnoDB, the allowed values are GRANTED and WAITING.

Principle analysis

Create a new blank table

CREATE TABLE `t` (  `id` int DEFAULT NULL,  `name` varchar(45) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Start the client transaction, execute the insert statement and view the lock information
By viewing the information about the lock held by the transaction started at this time, you can see that the transaction holds the intended exclusive lock of the current table t
Transaction 2 executes the insert statement and checks the lock information held by the currently running transaction. It can be seen that both transactions have obtained the intentional exclusive lock of table t, so the intentional exclusive locks at the table level are compatible with each other
As soon as the transaction finishes executing the update statement, check the lock holding status and verify the lock held
Note: update = select For UPDATE statement, a row lock will be set for all rows scanned
At this time, we use the following statement to view the lock information held. Because there are many locks involved, the view using the \ G parameter is not as intuitive as the table
```
select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
```
At this time, the lock information held by the transaction is returned as follows
You can see that in addition to the two table level intentional exclusive locks just now, four row locks are added. The four row locks are from top to bottom
Row lock with id=2 (non gap lock) (why can you see this record? You can view the difference between snapshot reading and current reading. Step by step to the MVCC article link at the end of the article to view the principle)
Row lock with id=1 (non gap lock)
Row lock with id=1, gap lock
Wait for row lock with id=2
In fact, you can see that transaction 1 holds its own row lock and waits for the row lock held by transaction 2. If transaction 2 needs to obtain the row lock held by transaction 1 at this time, deadlock will occur. Let's continue to view the deadlock information
Transaction 2 continues to execute the update statement. At this time, it is prompted that a deadlock is detected and the current transaction is rolled back

View deadlock information

show engine innodb status

Because there are too many returned information, only the key information display is intercepted

------------------------LATEST DETECTED DEADLOCK------------------------2022-01-15 03:48:46 0x7fd520ceb700*** (1) TRANSACTION: # Transaction one TRANSACTION 682000, ACTIVE 36 sec fetching rowsmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2MySQL thread id 58, OS thread handle 140553615279872, query id 4198 localhost root updatingupdate t set name ='update 1' where id =3*** (1) HOLDS THE LOCK(S): # Lock information held by transaction 1 RECORD LOCKS space id 430 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 682000 lock_mode X locks rec but not gapRecord lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000f23; asc      #;; 1: len 6; hex 0000000a6810; asc     h ;; 2: len 7; hex 020000067712ea; asc     w  ;; 3: len 4; hex 80000003; asc     ;; 4: len 8; hex 7570646174652031; asc update 1;;*** (1) WAITING FOR THIS LOCK TO BE GRANTED: # Lock information waiting for transaction 1 RECORD LOCKS space id 430 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 682000 lock_mode X waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000f24; asc      $;; 1: len 6; hex 0000000a6811; asc     h ;; 2: len 7; hex 81000000b10110; asc        ;; 3: len 4; hex 80000002; asc     ;; 4: len 1; hex 32; asc 2;;*** (2) TRANSACTION:# Transaction II TRANSACTION 682001, ACTIVE 26 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 59, OS thread handle 140553614690048, query id 4200 localhost root updatingupdate t set name='update2' where id =2*** (2) HOLDS THE LOCK(S): # Lock information held by transaction 2 RECORD LOCKS space id 430 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 682001 lock_mode X locks rec but not gapRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000f24; asc      $;; 1: len 6; hex 0000000a6811; asc     h ;; 2: len 7; hex 81000000b10110; asc        ;; 3: len 4; hex 80000002; asc     ;; 4: len 1; hex 32; asc 2;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:# Lock information waiting for transaction 2 RECORD LOCKS space id 430 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 682001 lock_mode X waitingRecord lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000f23; asc      #;; 1: len 6; hex 0000000a6810; asc     h ;; 2: len 7; hex 020000067712ea; asc     w  ;; 3: len 4; hex 80000003; asc     ;; 4: len 8; hex 7570646174652031; asc update 1;;*** WE ROLL BACK TRANSACTION (2) # Deadlock processing result

As can be seen from the information in the figure, the results are divided into three parts

(1) TRANSACTION is the information of the first transaction;

(2) TRANSACTION is the information of the second transaction;

WE ROLL BACK TRANSACTION (2) is the final result of the transaction, which indicates that second transactions are rolled back. What is the official account of second transactions?

conclusion
At the end of the execution of the two insert statements, transaction 1 and transaction 2 each hold an intention exclusive lock, and the intention exclusive lock is compatible with the intention exclusive lock
At this time, as soon as the transaction executes the update statement, use the view lock statement SELECT * FROM performance_schema.data_locks\G can see several row locks held by itself, as well as gap locks and waiting row locks. The waiting row lock is held by transaction 2, so transaction 1 is blocked.
At this time, transaction 2 executes the update statement, and similarly uses SELECT * FROM performance_schema.data_locks\G checks the lock information and finds that it is necessary to wait for the row lock with id=1 held by transaction 1 and the row lock with id=2 held by transaction 1. Therefore, transaction 1 holds the row lock with id=1, waits for the row lock with id=2 held by transaction 2, holds the row lock with id=2 and waits for the row lock with id=1 held by transaction 1. Therefore, deadlock occurs at this time. Therefore, mysql sends a deadlock retrieval rollback. Finally, the deadlock rollback strategy selects rollback transaction 2. At this time, transaction 2 rolls back successfully and releases the row lock with id=2. At this time, transaction 1 obtains the lock successfully and the transaction is submitted successfully. Therefore, this leads to the phenomenon we just started to see. A deadlock occurs. One transaction succeeds and the other fails.

Understanding key points

What is the locking method of Update statement?
What is the compatibility between various types of locks in Innodb?
Current read snapshot read?

summary

After the above series of analysis and log viewing, after the execution of the two insert statements, each holds an IX lock. After the transaction 1 executes the update statement, it creates its own row lock and gap lock with id=1, creates a row lock with id=2 for transaction 2, and waits for the row lock with id=2 for transaction 2; Transaction 2 needs to obtain the row lock with transaction 1 id=1 when updating. At this time, a deadlock occurs. The reason is that transaction 1 holds the row lock with id=1 and waits for the row lock with id=2. Transaction 2 holds the row lock with id=2 and waits for the row lock with id=1, resulting in mutual blocking

Easter egg

View lock information of Update statement and Select for Update statement

When there is only one piece of data in the table
Second data
Three data
You can see that when the table has no primary key and index, the lock held by the transaction is to add locks to the scanned rows
The lock of select for update is the same as that of update. A new client is opened to execute query statements
```
select * from t for update;
```