“This is the third day of my participation in the First Challenge 2022. For details: First Challenge 2022”
This article aims to learn how to read deadlock logs.
The phenomenon of
The online alarm display task is abnormal. The main function of this task is to convert the data delivered by the upstream system to the data structure of the system according to the rules and store the data. Exception stack details:
org.springframework.dao.DeadlockLoserDataAccessException: ### Error updating database.
Cause: java.sql.SQLException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
Deadlock found when trying to get lock; tryrestarting transaction <! BatchInsert -Inline ### The error may involve xxxmapper. batchInsert-Inline ### The error occurredwhile setting parameters ### SQL: insert into xxx (xxx) values(xxx)
Copy the codeFrom the exception stack, we can see that a deadlock occurred while the data was being written, causing the task to execute abnormally.
How to troubleshoot problems
Key point: learn to analyze and view deadlock logs
Locating the exception stack
From the stack information we can see the specific SQL where the deadlock occurred:
<! BatchInsert -Inline ### The error may involve xxxmapper. batchInsert-Inline ### The error occurredwhile setting parameters ### SQL: insert into xxx (xxx) values(xxx)
Copy the codeView deadlock logs
A quick refresher on logging for different locks:
| The lock type | Lock information | The log |
|---|---|---|
| Record Lock | A lock placed on an index record | lock_mode X locks rec but not gap / lock_mode S locks rec but not gap |
| Gap Lock | Locks a range of locks, excluding the record itself | lock_mode X locks gap before rec |
| Next-key Lock | A combination of gap locking and row locking, with either an S lock or an X lock (S lock for read and X lock for write) on the next index record itself and the gap before the index. | lock_mode X |
| Insert intent lock | A kind of clearance lock. Before insertion, if there is clearance lock in the range, the intention lock will be applied for insertion | lock_mode X locks gap before rec insert intention |
LATEST DETECTED DEADLOCK -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- time:2021- 09 -22 23:03:00 0x7f15a45bf700The transaction1: * * * (1) TRANSACTION:
// Transaction ID: 38118847464, active time: 0s, current status: inserting
TRANSACTION 38118847464, ACTIVE 0 sec inserting
// Use a table with a table lock
mysql tables in use 1, locked 1
// Waiting for a lock, lock information: chain length is 3, transaction allocation lock heap memory size is 1136
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
// The SQL currently being executed
MySQL thread id 14416460, OS thread handle 139733411297024, query id 61272162890 xx.xx.xx.xx table_xxx update /*id:90d9868d*//*ip=xx.xx.xx.xx*/INSERT INTO table_xxx (id, uid, xxx) VALUES (null, xxx, xxx)
// Transaction number: 38118847464 Waiting for lock information* * *(1) WAITING FOR THIS LOCK TO BE GRANTED:
// Wait for record lock, space ID: 149413, Page: 1370
RECORD LOCKS space id 149413 page no 1370 n bits 368
// The current transaction is waiting for the idx_UID index to insert intention (lock_mode X locks gap before REc insert intention), so the current transaction is blocked**index idx_uid of table 'XXX'. 'XXX' TRX id 38118847464 lock_mode X locks gap before rec insert intention ** Transaction 2: The details are as follows, similar to the analysis view process for transaction 1. * * *(2) TRANSACTION:
// Transaction ID: 38118847465, active time: 0s, current status: inserting
TRANSACTION 38118847465, ACTIVE 0 sec inserting
// Use a table with a table lock
mysql tables in use 1, locked 1
// Lock information: chain length is 3, transaction allocation lock heap memory size is 1136
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
// SQL being executed
MySQL thread id 14415908, OS thread handle 139730928531200, query id 61272162892 xx.xx.xxx.xxx table_xxx update /*id:90d9868d*//*ip=xx.xx.xxx.xxx*/INSERT INTO table_xxx (id, uid, xxx) VALUES (null, xxx, xxx)Hold lock: ***(2) HOLDS THE LOCK(S):
// Hold record lock, space ID: 149413, Page: 1370
RECORD LOCKS space id 149413 page no 1370 n bits 368
(lock_mode X locks gap before rec)**index idx_uid of table table 'XXX'. 'XXX' TRX id 38118847465 lock_mode X locks gap before REc ** LOCKS gap: ***(2) WAITING FOR THIS LOCK TO BE GRANTED:
(lock_mode X locks gap before rec insert intention)
RECORD LOCKS space id 149413 page no 1370 n bits 368
**index idx_uid of table `xxx库`.`xxx表` trx id 38118847465 lock_mode X locks gap before rec insert intention waiting**
Rollback transaction 2
*** WE ROLL BACK TRANSACTION (2)
Copy the codeFrom the above analysis, we can know the cause of the deadlock: transaction 1insert statement applied for the intent lock to be inserted, but transaction 2 held the gap lock and was waiting for the intent lock to be inserted, so the deadlock occurred.
Analyze the cause of the deadlock
This service scenario involves concurrent data insertion, which may cause deadlocks.
The solution
- Try to avoid large transactions and reduce the lock time of each transaction.
Thinking summary
First of all, we must learn to view and analyze deadlock log, and then can analyze the cause of birth and death lock and solve.
Insert statement deadlock problem, insert statement deadlock problem, insert statement deadlock problem. More on that next time, I haven’t figured it out yet…