mysql 报ERROR：Deadlock found when trying to get lock; try restarting transaction

Q132284591

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

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

　　上面有两句：
　　

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>
　　
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>　　WHERE datetime
<= now() - INTERVAL 900 SECOND order by>
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