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

1小时前来源:cnblogs

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

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

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

知道错误code之后,先来查看mysql的说明,关于上面的 Error: SQLSTATE: Server Error Codes and Messages

Message: Deadlock found when trying to getlock; 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 fromthis error, run all the operations inthis 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 thisis 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 incase 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).

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: InnoDBSQL数据其实
分页:12
转载请注明
本文标题:mysql 报ERROR:Deadlock found when trying to get lock; try restarting transactio ...
本站链接:http://www.codesec.net/view/531991.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(37)