mysql error report: Deadlock found when trying to get lock; Solution of try restarting transaction

  • 2021-08-28 21:26:54
  • OfStack

Find a problem

Recently, when filling in the previous data, the program suddenly reported the following error:


[2017-02-10 13:12:06.678] [INFO] mysqlLog - update tbl_playerdata_error: { [Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction]
 code: 'ER_LOCK_DEADLOCK',
 errno: 1213,
 sqlState: '40001',
 index: 0 }

1 Look, there is a deadlock problem in mysql. In fact, the program running above has no problem after running in the test suit for a long time. Why is there a deadlock problem in mysql in the formal suit? The first reaction is that the amount of data is too large (more than 3 million pieces), but it is impossible. Besides, what is the relationship between deadlock and these chicken feathers? It seems that we should solve it well.

Problem analysis

My analysis is: Because we are dealing with the data of formal service now, and there are many users operating formal service, it should be in the user query, or other operations, and the data update on my side produced deadlock (first of all, it is explained that InnoDB storage engine is used. Because the query or other operation on the user side locks the resources I need, and the update on my side also locks the resources of the user operation, both sides are waiting for the other side to release the resources, resulting in deadlock.

Solution

After knowing the error code, first look at the description of mysql. For Error: 1213 SQLSTATE: 40001 above, see Server Error Codes and Messages


Message: Deadlock found when trying to get lock; try restarting transaction

InnoDB reports this error when a transaction encounters a deadlock and is automatically rolled back so that your application can take corrective action. To recover from this error, run all the operations in this transaction again. A deadlock occurs when requests for locks arrive in inconsistent order between transactions. The transaction that was rolled back released all its locks, and the other transaction can now get all the locks it requested. Thus, when you re-run the transaction that was rolled back, it might have to wait for other transactions to complete, but typically the deadlock does not recur. If you encounter frequent deadlocks, make the sequence of locking operations (LOCK TABLES, SELECT ... FOR UPDATE, and so on) consistent between the different transactions or applications that experience the issue. See Section 14.8.5,  " Deadlocks in InnoDB "  for details.

There are two sentences on it:


To recover from this error, run all the operations in this transaction again<br><br>If you encounter frequent deadlocks, make the sequence of locking operations (<code class="literal">LOCK TABLES</code>, <code class="literal">SELECT ... FOR UPDATE</code>, and so on) <br>consistent between the different transactions or applications that experience the issue 

These two sentences also tell the way to deal with deadlock. When the deadlock error occurs, I use the timer to do the update operation again, so as to avoid the above problems.

In addition, I refer to the above answer of stack overflow: http://stackoverflow.com/questions/2332768/how-to-avoid-deadlock-found-when-to-get-lock-try-restarting-trans


One easy trick that can help with most deadlocks is sorting the operations in a specific order.

You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(2), locks key(1);
If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.

Now, if you changed your queries such that the connections would lock the keys at the same order, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(1), locks key(2);
it will be impossible to get a deadlock.

So this is what I suggest:

Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.
Fix your delete statement to work in ascending order:
Change

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
To

DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
 WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;
Another thing to keep in mind is that mysql documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).

Reference: http://blog.sina.com.cn/s/blog _ 4acbd39c01014gsq. html

Summarize


Related articles: