• 售前

  • 售后

热门帖子
入门百科

mysql报错:Deadlock found when trying to get lock; try restarting tran

[复制链接]
心随674 显示全部楼层 发表于 2021-10-25 20:06:41 |阅读模式 打印 上一主题 下一主题
发现题目
最近在补以前数据的时候步伐忽然报如下错误:
  1. [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]
  2. code: 'ER_LOCK_DEADLOCK',
  3. errno: 1213,
  4. sqlState: '40001',
  5. index: 0 }
复制代码
一看就是mysql出现了死锁题目,着实上面跑的步伐在测试服跑了好久都没什么题目,为什么在正式服上会出现mysql的死锁题目呢,第一反应是不是数据量太大(3百多万条),但是也不可能啊,再说死锁和这些有什么鸡毛的关系,看来要好好办理下了。
题目分析
我的分析是:由于现在处理的是正式服的数据,而正式服另有许多用户在操作,应该是在用户查询,大概是其他操作的时候,和我这边的数据更新产生了死锁(起首阐明利用的是:InnoDB存储引擎。由于用户那边的查询大概其他操作锁定了我须要的资源,而我这边更新也锁定了用户操作的一部分资源,双方都等着对方释放资源,从而导致死锁)。
办理方法
知道错误code之后,先来查看mysql的阐明,关于上面的 Error: 1213 SQLSTATE: 40001,拜见:Server Error Codes and Messages
  1. Message: Deadlock found when trying to get lock; try restarting transaction
  2. 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.
复制代码
上面有两句:
  1. 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
复制代码
这两句也就道出了处理死锁的方法了,我就是在死锁错误发生的时候,利用定时器再重新做一次更新操作,如许就克制了上面出现的题目。
另外,参考了stack overflow上面一个答复:http://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans
  1. One easy trick that can help with most deadlocks is sorting the operations in a specific order.
  2. You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:
  3. connection 1: locks key(1), locks key(2);
  4. connection 2: locks key(2), locks key(1);
  5. 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.
  6. Now, if you changed your queries such that the connections would lock the keys at the same order, ie:
  7. connection 1: locks key(1), locks key(2);
  8. connection 2: locks key(1), locks key(2);
  9. it will be impossible to get a deadlock.
  10. So this is what I suggest:
  11. 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.
  12. Fix your delete statement to work in ascending order:
  13. Change
  14. DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
  15. To
  16. DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
  17. WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;
  18. 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
总结
以上就是这篇文章的全部内容了,盼望本文的内容对大家的学习大概工作能带来一定的帮助,假如有疑问大家可以留言交换,谢谢大家对脚本之家的支持。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作