InnoDB Database Deadlock

Scenario Description

An deadlockloserdataaccessexception exception occurred in the Update table (Deadlock found when trying to get lock; try restarting transaction ...).

Problem analysis

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

Workaround

Use Try-catch in the application where the update is being used.

I have encapsulated a function myself, as follows.

/*** 2016-03-15 * Linxuan * handle deadlock while Update table*/    Private voidUpdatewithdeadlock (Testmapper Mapper, Test record)throwsinterruptedexception {Booleanoops; intRetries = 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 just pass the mapper into the function, and if not mybatis, you need to create and close the database connection yourself.

extension: Database deadlock

Database deadlocks are problems that are frequently encountered by transactional databases such as SQL Server, MySQL, and so on. Unless the database deadlock problem occurs frequently causing the user to be unable to operate, the database deadlock problem is generally not serious. You can do try-catch in the application. So how is data deadlock generated?

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

• A shared lock is used for a transaction read line.
• The mutex is used for the transaction update or delete row.

Database Deadlock Example

First, customer a creates a table T and inserts a piece of data into T, and customer a starts a select transaction, so hold the shared lock S.

Mysql> CREATE TABLET (iINT) ENGINE=InnoDB; Query OK,0Rows Affected (1.07sec) MySQL> INSERT  intoT (i)VALUES(1); Query OK,1Row affected (0.09sec) MySQL>STARTTRANSACTION; Query OK,0Rows Affected (0.00sec) MySQL> SELECT *  fromTWHEREI= 1LOCKinchSHARE MODE;+------+|I|+------+|    1 |+------+

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

MySQL>TRANSACTION0 rows affected (0.00  sec) MySQL>  DELETEfromWHERE=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 customer B is blocked.

Finally, customer a also wants to delete that data from table T:

MySQL>DELETEfromWHERE=11213 (  40001when totransaction 

The deadlock has been created! Because customer a needs to lock X to delete the row, customer B is holding the lock x and is waiting for customer A to release the lock S. Look at the status of customer A/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 generates an error message for a customer and releases the lock. Information returned to the customer:

1213 (40001when totransaction 

Therefore, another customer can perform the task normally. Deadlock ended.

Finish

InnoDB Database Deadlock