Scene description

A DeadlockLoserDataAccessException occurred when updating the table (Deadlock found when trying to get lock; try restarting transaction...).

Problem analysis

This exception will not affect the user's use, because the database will automatically roll back and try again when it encounters a deadlock.The user's perception is that the operation is a bit stuck.But the monitoring always reports exceptions, so it needs to be solved.

Solution

Use try-catch in the update place in the application.

I encapsulated a function myself, 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 just pass the mapper into the function.If you don’t use mybatis, you need to create and close the database connection yourself.

Extension: Database deadlock

Database deadlock is a problem frequently encountered in transactional databases (such as SQL Server, MySql, etc.).Unless the database deadlock problem frequently occurs and the user cannot operate, the database deadlock problem is not serious under normal circumstances.Just try-catch in the application.So how does the data deadlock occur?

InnoDB implements row level lock, which is divided into shared lock (S) and mutex lock (X).

Shared lock is used for transaction read line.
Mutex locks are used for transaction update or delete a row.
When client A holds a shared lock S and requests a mutex lock X; at the same time, client B holds a mutex lock X and requests a shared lock S.In the above situation, a database deadlock will occur.If it is not clear enough, please see the example below.

Database deadlock example

First, customer A creates a table T and inserts a piece of data into T.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, and the new transaction is the only piece of 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 (X), but the mutex X and the shared lock S are incompatible.So the delete transaction is put in the lock request queue, and client 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 has occurred! Because client A needs lock X to delete the row, and client B is holding lock X and is waiting for client A to release lock S.Look at the status of customers A and B:

Customer A: Holds lock S and waits for customer B to release lock X.
Client B: Hold lock X and wait for client A to release lock S.

After a deadlock occurs, InnoDB will generate an error message for a client 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 perform the task normally.The deadlock is over.