InnoDB database deadlock problem handling

Scene description

The DeadlockLoserDataAccessException anomaly occurs in the update table (Deadlock found when trying to get lock; try restarting transaction...) .

Problem analysis

This exception does not affect user usage because the database automatically rolls back and retries when it encounters a deadlock. The user's perception is that the operation is slightly delayed. However, the monitor is always reporting exceptions, so it needs to be solved 1 time.

The solution

Use ES24en-ES25en where update is in your application.

I encapsulate 1 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'm using mybatis, so I'll just pass mapper into the function, but if I don't use mybatis, I'll have to create and close the database connection myself.

Extension: Database deadlock

Database deadlocks are a common problem for transactional databases such as SQL Server, MySql, etc. Unless the database deadlock problem occurs frequently and the user is unable to operate, the database deadlock problem is generally not serious. Just do ES41en-ES42en in your application. So how do data deadlocks occur?

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

The shared lock is used for transaction read1 rows.
The mutex is used for transaction update or delete1 lines.
When client A holds the shared lock S and requests the mutex X; At the same time, client B holds the mutex X and requests the shared lock S. In this case, a database deadlock can occur. If that's not clear enough, here's an example.

Database deadlock example

First, client A creates a table T and inserts a piece of data into T. Client A starts an select transaction, so hold 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 |
+------+

Client B then starts a new transaction, which is the only 11 pieces of data in 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 placed in the lock request queue and client B blocks.

Finally, customer A also wants to delete that data in table T:

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

Deadlocks are created! Because client A needs to lock X to delete rows, client B holds the lock X and is waiting for client A to release the lock S. Check the status of client A and B:

Client A: Hold the lock S and wait for client B to release the lock X.
Client B: Holding the lock X, waiting for client A to release the lock S.

When a deadlock occurs, InnoDB generates an error message for one customer and releases the lock. Information returned to the customer:

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
Therefore, the other customer can perform the task normally. Deadlock over.