Read the original: almost kneel in the face of database deadlock
I don’t know if you have ever encountered the problem of database deadlock? Once encountered how to troubleshoot the problem?
Environment: MySQL 5.7.25 engine InnoDB
If your system log suddenly reports this kind of error, will you panic? Thought: MD, met before, but do not remember how to do it!!! It’s over! The leader knows that I can’t solve this problem, so I won’t be fired!
2019-10-23 13:07:17.144 ERROR nested exception is org.springframework.dao.DeadlockLoserDataAccessException: ### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may involve com.x.x.mapper.XMapper.update-Inline ### The error occurred while setting parameters ### SQL: UPDATE tb_a SET start_time = ?, end_time = ? WHERE id = ? ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
If I’m a developer who has worked for several years, it’s not disgraceful to hang here! Practice.
What is deadlock?
When multiple processes access the samedata baseEach process haslockallyesWhat other processes need, which makes it impossible for each process to continue. In short, process a waits for process B to release its resources, and process B waits for a to release its resources, so that mutual waiting is formeddeadlock。
View database basic information
To view the database version:
Transaction isolation level query method:
show enginesCheck the features of InnoDB
InnoDB supports transactions, row level locks and foreign keys.
What we usually encounter is the row level lock between multiple transactions.
The records in the business log are too simple. We only know which method’s transaction is deadlocked and there is no redundant information. Therefore, we need to find more useful information in the database through the command
show engine Innodb statusSee:
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-10-23 16:46:42 0x7fa919415700 1 affairs *** (1) TRANSACTION: TRANSACTION 21010939, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 255825, OS thread handle 140363604055808, query id 179915249 localhost 127.0.0.1 root updating UPDATE tb_b SET end_time = 1571821300000 WHERE id = 18199 #Wait for X lock of table b *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1924 page no 284 n bits 80 index PRIMARY of table `dmeeting`.`tb_b` trx id 21010939 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 26; compact format; info bits 0 2 affairs *** (2) TRANSACTION: TRANSACTION 21010938, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 255826, OS thread handle 140364249913088, query id 179915304 localhost 127.0.0.1 root updating UPDATE tb_a SET actual_start_time = 1571820362678, actual_end_time = null WHERE id = 14266 #X lock holding B table *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1924 page no 284 n bits 80 index PRIMARY of table `dmeeting`.`tb_b` trx id 21010938 lock_mode X locks rec but not gap Record lock, heap no 9 PHYSICAL RECORD: n_fields 26; compact format; info bits 0 #Wait for X lock of a table *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1934 page no 324 n bits 112 index PRIMARY of table `dmeeting`.`tb_a` trx id 21010938 lock_mode X locks rec but not gap waiting Record lock, heap no 45 PHYSICAL RECORD: n_fields 38; compact format; info bits 0 #Rollback transaction 2 *** WE ROLL BACK TRANSACTION (2)
By analyzing the deadlock log above, the following deadlock scenarios can be obtained:
Just according to the deadlock log analysis, I can’t think about it. In transaction 1, there is no X lock holding a table, so how does this cause deadlock! I am just a dazed youth. I know that when I think about the deadlock log, I waste time. Fortunately, I have God ‘s instruction beside me. When I look at these two transaction codes in the business system, I find that the original transaction 1 updates the table a when the time sequence 2, and has already held the row level lock of table a! Now it is fully understood that two transactions wait for each other to release the lock, which is the cause of deadlock.
If you know the reason, you can change the code to make the update order of the tables in the two transactions consistent.
Summarize troubleshooting steps
- Quickly locate the code block with deadlock through business system log
- Check the deadlock log of InnoDB and find out the code block corresponding to each transaction
- Infer and draw the deadlock transaction scenario through deadlock log and business code
Reduce the probability of deadlock
- To avoid large transactions, you can split them into multiple small transactions, because large transactions take a long time and have a high probability of occurrence with other transactions.
- Multiple transactions operate on the same resources in the same order.
- Update statements try to update only the necessary fields, and do not update the fields with the same content.
Record the complete deadlock log
show engine innodb statusThe displayed information is not complete.
This is a bug of MySQL client: bug × 19825. Interactive client limits the maximum output information to64KB, so more information cannot be displayed.
However, we can view the complete log by opening lock monitoring as follows:
#It is recommended to shut down after troubleshooting and output once in 15 seconds, which will lead to larger and larger logs --Open standard monitoring on / off set GLOBAL innodb_status_output=ON; --Open lock monitoring on / off set GLOBAL innodb_status_output_locks=ON;
You can also use a special parameter to record the deadlock log:
set GLOBAL innodb_print_all_deadlocks=ON;
Generally, the content is output to MySQL error log to view the log location:
Types of locks
Row level lock (engine InnoDB): high overhead, slow lock adding; deadlock will occur; the lock granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.
Table level lock (engine MyISAM): small overhead, fast lock adding, no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency.
Next keylocks lock the record (data) and the gap in front of the record
Gap lock, do not lock the record, just record the gap in front
Recordlock lock (lock data, do not lock gap)
So in fact, next keylocks = gap lock + recordlock lock lock
First of all, we need to know the two most easily understood lock modes: read plus shared lock and write plus exclusive lock.
- Lock? S (read lock, shared lock)
- Lock? X (write lock, exclusive lock)
- Lock? Is (read intent lock)
- Lock? IX (freehand lock)
- Lock? Auto? Inc
For a more detailed introduction, please refer to this article: https://www.aneasytone.com/a