mysql报错：Deadlock found when trying to get lock; try restarting transaction的解决方法

发现问题

最近在补以前数据的时候程序突然报如下错误：

[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 }

一看就是mysql出现了死锁问题，其实上面跑的程序在测试服跑了好久都没什么问题，为什么在正式服上会出现mysql的死锁问题呢，第一反应是不是数据量太大（3百多万条），可是也不可能啊，再说死锁和这些有什么鸡毛的关系，看来要好好解决下了。

问题分析

我的分析是：由于现在处理的是正式服的数据，而正式服还有许多用户在操作，应该是在用户查询，或者是其他操作的时候，和我这边的数据更新产生了死锁（首先说明使用的是：InnoDB存储引擎。由于用户那边的查询或者其他操作锁定了我需要的资源，而我这边更新也锁定了用户操作的一部分资源，两边都等着对方释放资源，从而导致死锁）。

解决方法

知道错误code之后，先来查看mysql的说明，关于上面的 Error: 1213 SQLSTATE: 40001，参见：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.

上面有两句：

To recover from this error, run all the operations in this transaction again

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 

这两句也就道出了处理死锁的方法了，我就是在死锁错误发生的时候，使用定时器再重新做一次更新操作，这样就避免了上面出现的问题。

另外，参考了stack overflow上面一个回答：http://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-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).

参考：http://blog.sina.com.cn/s/blog_4acbd39c01014gsq.html

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对潘少俊衡的支持。