Almost kneeling in the face of database deadlock

Read the original: Almost kneeling 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 same database, each process has the lock required by other processes, which makes each process unable to continue Simply put, process A waits for process B to release its resources, and then process B waits for A to release its resources, thus waiting for each other to form A deadlock.

View database basic information

View database version: select version();

Transaction isolation level query method: select@ @ TX \ isolation

Check the features of InnoDB with the command show engines

InnoDB supports transactions, row level locks and foreign keys.

What we usually encounter is the row level lock between multiple transactions.

Analysis

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, and view it through the command show engine Innodb status:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-10-23 16:46:42 0x7fa919415700
# Transaction 1
*** (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

# Transaction 2
*** (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:

Only 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'm just a fool. I know that I've wasted time thinking about deadlock logs. Fortunately, I've got a great God's advice. I went to look at these two transaction codes in the business system and found that table a was updated in transaction 1 in time series 2. I have 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

  1. Quickly locate the code block with deadlock through business system log
  2. Check the deadlock log of InnoDB and find out the code block corresponding to each transaction
  3. Infer and draw the deadlock transaction scenario through deadlock log and business code

Reduce the probability of deadlock

  1. 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.
  2. Multiple transactions operate on the same resources in the same order.
  3. Update statements try to update only the necessary fields, and do not update the fields with the same content.

Record the complete deadlock log

When show engine innodb status is displayed, the information displayed is incomplete.

This is a bug of mysql client: BUG#19825 , interactive clients limit the maximum output information to 64KB, 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: select @ log error

Types of locks

Lock level

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.

Lock type

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

Lock mode

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)

Also:

  • Lock? Is (read intent lock)
  • Lock? IX (freehand lock)
  • Lock? Auto? Inc

For a more detailed introduction, go to this article: https://www.aneasystone.com/a...

http://www.throwable.club/2019/05/11/mysql-deadlock-troubleshoot-1st/#%E5%AF%BC%E8%87%B4%E6%AD%BB%E9%94%81%E7%9A%84%E5%8E%9F%E5%9B%A0

Tags: MySQL Database JDBC SQL

Posted on Wed, 06 Nov 2019 21:10:31 -0800 by rroc