mysql报ERROR:Deadlock found when trying to get lock; try restarting transaction(nodejs)
1 前言
出现错误 Deadlock found when trying to get lock; try restarting transaction。然后通过网上查找资料,重要看到有用信息了。
错误图片如下:
2 解决方案
由于mysql执行delete操作时WHERE 中字段使用了非主键,然而那个表有在进行其它操作时,就会出现这个错了。所以只要删除时使用主键作为条件即可。
参考文章中部分解释如下:
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; //翻译:另一件重要的事,当发现死锁时,客户端最好自动尝试3次机会,如果都失败就放弃吧,真的是失败了。 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).
3 参考
1.https://www.jb51.net/article/118468.htm(参考3)
2.https://www.cnblogs.com/duhuo/p/6386331.html
3.http://blog.sina.com.cn/s/blog_4acbd39c01014gsq.html