mysql报错：Deadlock found when trying to get lock; try restarting transaction的解决方法

2024-02-06 23:01:25 61

发现问题

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

[2017-02-1013:12:06.678][INFO]mysqlLog-updatetbl_playerdata_error:{[Error:ER_LOCK_DEADLOCK:Deadlockfoundwhentryingtogetlock;tryrestartingtransaction]
code:'ER_LOCK_DEADLOCK',
errno:1213,
sqlState:'40001',
index:0}

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

问题分析

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

解决方法

知道错误code之后，先来查看mysql的说明，关于上面的Error:1213SQLSTATE:40001，参见：ServerErrorCodesandMessages

Message:Deadlockfoundwhentryingtogetlock;tryrestartingtransaction

InnoDBreportsthiserrorwhenatransactionencountersadeadlockandisautomaticallyrolledbacksothatyourapplicationcantakecorrectiveaction.Torecoverfromthiserror,runalltheoperationsinthistransactionagain.Adeadlockoccurswhenrequestsforlocksarriveininconsistentorderbetweentransactions.Thetransactionthatwasrolledbackreleasedallitslocks,andtheothertransactioncannowgetallthelocksitrequested.Thus,whenyoure-runthetransactionthatwasrolledback,itmighthavetowaitforothertransactionstocomplete,buttypicallythedeadlockdoesnotrecur.Ifyouencounterfrequentdeadlocks,makethesequenceoflockingoperations(LOCKTABLES,SELECT...FORUPDATE,andsoon)consistentbetweenthedifferenttransactionsorapplicationsthatexperiencetheissue.SeeSection14.8.5,“DeadlocksinInnoDB”fordetails.

上面有两句：

Torecoverfromthiserror,runalltheoperationsinthistransactionagain

Ifyouencounterfrequentdeadlocks,makethesequenceoflockingoperations(LOCKTABLES,SELECT...FORUPDATE,andsoon)
consistentbetweenthedifferenttransactionsorapplicationsthatexperiencetheissue

这两句也就道出了处理死锁的方法了，我就是在死锁错误发生的时候，使用定时器再重新做一次更新操作，这样就避免了上面出现的问题。

另外，参考了stackoverflow上面一个回答：http://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

Oneeasytrickthatcanhelpwithmostdeadlocksissortingtheoperationsinaspecificorder.

Yougetadeadlockwhentwotransactionsaretryingtolocktwolocksatoppositeorders,ie:

connection1:lockskey(1),lockskey(2);
connection2:lockskey(2),lockskey(1);
Ifbothrunatthesametime,connection1willlockkey(1),connection2willlockkey(2)andeachconnectionwillwaitfortheothertoreleasethekey->deadlock.

Now,ifyouchangedyourqueriessuchthattheconnectionswouldlockthekeysatthesameorder,ie:

connection1:lockskey(1),lockskey(2);
connection2:lockskey(1),lockskey(2);
itwillbeimpossibletogetadeadlock.

SothisiswhatIsuggest:

Makesureyouhavenootherqueriesthatlockaccessmorethanonekeyatatimeexceptforthedeletestatement.ifyoudo(andIsuspectyoudo),ordertheirWHEREin(k1,k2,..kn)inascendingorder.
Fixyourdeletestatementtoworkinascendingorder:
Change

DELETEFROMonlineusersWHEREdatetime<=now()-INTERVAL900SECOND
To

DELETEFROMonlineusersWHEREidIN(SELECTidFROMonlineusers
WHEREdatetime<=now()-INTERVAL900SECONDorderbyid)u;
Anotherthingtokeepinmindisthatmysqldocumentationsuggestthatincaseofadeadlocktheclientshouldretryautomatically.youcanaddthislogictoyourclientcode.(Say,3retriesonthisparticularerrorbeforegivingup).

参考：http://blog.sina.com.cn/s/blog_4acbd39c01014gsq.html

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。

mysql报错：Deadlock found when trying to get lock; try restarting transaction的解决方法

热门推荐

随机推荐