Troubleshooting of InnoDB database deadlocks and innodb database deadlocks

Troubleshooting of InnoDB database deadlocks and innodb database deadlocks

Scenario Description

DeadlockLoserDataAccessException exception (Deadlock found when trying to get lock; try restarting transaction...) occurs during table update ...).

Problem Analysis

This exception does not affect user usage, because the database will automatically roll back and retry in case of a deadlock. The user feels that the operation is slow. However, monitoring always reports exceptions, so we need to solve the problem.

Solution

Use try-catch in the update Area of the application.

I encapsulated a function as follows.

/**   * 2016-03-15   * linxuan   * handle deadlock while update table   */  private void updateWithDeadLock(TestMapper mapper, Test record) throws InterruptedException {    boolean oops;    int retries = 5;    do{      oops = false;      try{        mapper.updateByPrimaryKeySelective(record);      }      catch (DeadlockLoserDataAccessException dlEx){        oops = true;        Thread.sleep((long) (Math.random() * 500));      }      finally {      }    } while(oops == true && retries-- >0);  }

I use mybatis, so I only need to pass mapper into the function. If mybatis is not needed, I need to create and close the database connection.

Extended: Database deadlock

Database deadlocks are common problems in transactional databases (such as SQL Server and MySql. Unless the database deadlock problem occurs frequently and the user cannot operate, the database deadlock problem is generally not serious. Try-catch in the application. How is a data deadlock generated?

InnoDB implements row-level lock (S) and mutex lock (X ).

A shared lock is used to read a row in a transaction.
The mutex lock is used to update transactions or delete a row.
When customer A holds the shared Lock S and requests the mutex lock X, Customer B also holds the mutex lock X and requests the shared Lock S. In the above cases, a database deadlock occurs. If not, see the following example.

Database deadlock example

First, Customer A creates A table T and inserts A piece of data into it. Customer A starts A select transaction, so it holds the shared Lock S.

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;Query OK, 0 rows affected (1.07 sec)mysql> INSERT INTO t (i) VALUES(1);Query OK, 1 row affected (0.09 sec)mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;+------+| i  |+------+|  1 |+------+

Then, Customer B starts a new transaction, which is the only data in the delete table T.

mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> DELETE FROM t WHERE i = 1;

The delete operation requires a mutex lock (X), but the exclusive lock X and the shared Lock S are incompatible. Therefore, the deletion transaction is put in the lock Request queue, and customer B is blocked.

Finally, Customer A also wants to delete the data in table T:

mysql> DELETE FROM t WHERE i = 1;ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction

A deadlock is generated! Because customer A needs to lock X to delete rows, and customer B holds the lock X and is waiting for Customer A to release the Lock S. Check the status of Customer A and B:

Customer A: holding the Lock S, waiting for Customer B to release the lock X.
Customer B: holding the lock X, waiting for Customer A to release the Lock S.

After a deadlock occurs, InnoDB will generate an error message for a customer and release the lock. Information returned to the customer:

ERROR 1213 (40001): Deadlock found when trying to get lock;
Try restarting transaction
Therefore, another customer can execute tasks normally. The deadlock ends.

Articles you may be interested in:

• Analysis and Induction of deadlock logs caused by MySQL Innodb tables
• MySQL deadlock analysis and solution example
• How to find the deadlock ID in the MySQL thread
• Mysql database deadlock Process Analysis (select for update)
• Mysql database deadlock causes and solutions
• MySQL deadlock caused by the load data statement
• Details about deadlocks in MySQL and solutions to deadlocks