InnoDB database deadlock problem handling _mysql

Scene description

A deadlockloserdataaccessexception exception appears when the Update table (deadlock found when trying to get lock; try restarting transaction ...).

Problem analysis

This exception does not affect user use because the database encounters a deadlock and automatically rolls back and tries again. The user's feeling is to operate slightly cotton. But the monitoring is always reported abnormal, so need to solve.

Solving method

Use Try-catch where update is 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 ());
      }
      finally {
      }
    } while (oops = = True && retries-->0);
  }

I'm using MyBatis, so just pass the mapper into the function, and if you don't have to mybatis, you need to create and close the database connection yourself.

Extending: Database Deadlock

Database deadlock is a recurring problem for transactional databases such as SQL Server, MySQL, and so on. Unless the database deadlock problem occurs frequently, causing the user to not operate, the database deadlock problem is generally not serious. You can do try-catch in your application. So how does data deadlock occur?

InnoDB Implements row locks (row level lock), divided into shared locks (S) and mutexes (X).

A shared lock is used for a transaction read row.
A mutex is used for a transaction update or delete row.
When client a holds shared lock s and requests mutual exclusion lock X, client B holds the mutex X and requests shared lock S. In the above case, a database deadlock will occur. If it's not clear enough, take a look at the example below.

Database Deadlock Example

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

Customer B then 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 (x), but the mutex X and shared lock s are incompatible. So the delete transaction is placed 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

The deadlock has been created! Because customer a needs a lock x to delete the row, Customer B takes a lock x and is waiting for customer A to release lock S. Look at the status of the customer A,b:

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

After a deadlock occurs, InnoDB generates an error message for a customer and releases the lock. Return information to the customer:

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