科帮网-Java论坛、Java社区、JavaWeb毕业设计

登录/注册
您现在的位置:论坛 资料库 数据库开发 > Deadlock found when trying to get lock; try restarti ...
总共46340条微博

动态微博

查看: 176|回复: 0

Deadlock found when trying to get lock; try restarting transaction

[复制链接]

277

主题

39

听众

678

金钱

版主

  • TA的每日心情

    2015-4-25 10:06
  • 签到天数: 2 天

    [LV.1]初来乍到

    发表于 2018-05-14 14:52:42 |显示全部楼层
    今天在补以前数据的时候程序突然报如下错误:

    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百多万条),可是也不可能啊,再说死锁和这些有什么鸡毛的关系,马丹看来要好好解决下了。[/url]
    我的分析是:由于现在处理的是正式服的数据,而正式服还有许多用户在操作,应该是在用户查询,或者是其他操作的时候,和我这边的数据更新产生了死锁(首先说明使用的是: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/questio ... ry-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).
    复制代码


    科帮网-Java论坛、Java社区、JavaWeb毕业设计 1、本主题所有言论和图片纯属会员个人意见,与本社区立场无关
    2、本站所有主题由该帖子作者发表,该帖子作者与科帮网-Java论坛、Java社区、JavaWeb毕业设计享有帖子相关版权
    3、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者和科帮网-Java论坛、Java社区、JavaWeb毕业设计的同意
    4、帖子作者须承担一切因本文发表而直接或间接导致的民事或刑事法律责任
    5、本帖部分内容转载自其它媒体,但并不代表本站赞同其观点和对其真实性负责
    6、如本帖侵犯到任何版权问题,请立即告知本站,本站将及时予与删除并致以最深的歉意
    7、科帮网-Java论坛、Java社区、JavaWeb毕业设计管理员和版主有权不事先通知发贴者而删除本文


    JAVA爱好者①群:JAVA爱好者① JAVA爱好者②群:JAVA爱好者② JAVA爱好者③ : JAVA爱好者③

    快速回复
    您需要登录后才可以回帖 登录 | 立即注册

       

    发布主题 快速回复 返回列表 联系我们 官方QQ群 科帮网手机客户端
    快速回复 返回顶部 返回列表