mysql報(bào)錯(cuò):Deadlock found when trying to get lock; try restarting transaction的解決方法
發(fā)現(xiàn)問題
最近在補(bǔ)以前數(shù)據(jù)的時(shí)候程序突然報(bào)如下錯(cuò)誤:
[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] code: 'ER_LOCK_DEADLOCK', errno: 1213, sqlState: '40001', index: 0 }
一看就是mysql出現(xiàn)了死鎖問題,其實(shí)上面跑的程序在測試服跑了好久都沒什么問題,為什么在正式服上會(huì)出現(xiàn)mysql的死鎖問題呢,第一反應(yīng)是不是數(shù)據(jù)量太大(3百多萬條),可是也不可能啊,再說死鎖和這些有什么雞毛的關(guān)系,看來要好好解決下了。
問題分析
我的分析是:由于現(xiàn)在處理的是正式服的數(shù)據(jù),而正式服還有許多用戶在操作,應(yīng)該是在用戶查詢,或者是其他操作的時(shí)候,和我這邊的數(shù)據(jù)更新產(chǎn)生了死鎖(首先說明使用的是:InnoDB存儲引擎。由于用戶那邊的查詢或者其他操作鎖定了我需要的資源,而我這邊更新也鎖定了用戶操作的一部分資源,兩邊都等著對方釋放資源,從而導(dǎo)致死鎖)。
解決方法
知道錯(cuò)誤code之后,先來查看mysql的說明,關(guān)于上面的 Error: 1213 SQLSTATE: 40001,參見:Server Error Codes and Messages
Message: Deadlock found when trying to get lock; 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 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.
上面有兩句:
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
這兩句也就道出了處理死鎖的方法了,我就是在死鎖錯(cuò)誤發(fā)生的時(shí)候,使用定時(shí)器再重新做一次更新操作,這樣就避免了上面出現(xiàn)的問題。
另外,參考了stack overflow上面一個(gè)回答: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 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; 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
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。
相關(guān)文章
Mysql中distinct與group by的去重方面的區(qū)別
distinct簡單來說就是用來去重的,而group by的設(shè)計(jì)目的則是用來聚合統(tǒng)計(jì)的,兩者在能夠?qū)崿F(xiàn)的功能上有些相同之處,但應(yīng)該仔細(xì)區(qū)分,因?yàn)橛缅e(cuò)場景的話,效率相差可以倍計(jì)。2020-03-03MySQL復(fù)合查詢操作實(shí)戰(zhàn)案例
這篇文章主要給大家介紹了關(guān)于MySQL復(fù)合查詢操作的相關(guān)資料,MySQL復(fù)合查詢是指在一個(gè)SQL語句中使用多個(gè)查詢條件,以過濾和檢索數(shù)據(jù),需要的朋友可以參考下2023-08-08MYSQL數(shù)據(jù)庫主從同步設(shè)置的實(shí)現(xiàn)步驟
本文主要介紹了MYSQL數(shù)據(jù)庫主從同步設(shè)置的實(shí)現(xiàn)步驟,文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2022-03-03MySQL版本低了不支持兩個(gè)時(shí)間戳類型的值解決方法
在本篇文章里小編給大家分享了關(guān)于MySQL 版本低了,不支持兩個(gè)時(shí)間戳類型的值的相關(guān)知識點(diǎn),有興趣的朋友們可以參考下。2019-09-09mysql 1130錯(cuò)誤,無法登錄遠(yuǎn)程服務(wù)的解決
這篇文章主要介紹了mysql 1130錯(cuò)誤,無法登錄遠(yuǎn)程服務(wù)的解決方案,具有很好的參考價(jià)值,希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-03-03MySQL thread_stack連接線程的優(yōu)化
當(dāng)有新的連接請求時(shí),MySQL首先會(huì)檢查Thread Cache中是否存在空閑連接線程,如果存在則取出來直接使用,如果沒有空閑連接線程,才創(chuàng)建新的連接線程2017-04-04MySQL中實(shí)現(xiàn)高性能高并發(fā)計(jì)數(shù)器方案(例如文章點(diǎn)擊數(shù))
這篇文章主要介紹了MySQL中實(shí)現(xiàn)高性能高并發(fā)計(jì)數(shù)器方案,本文中的計(jì)數(shù)器是指如文章的點(diǎn)擊數(shù)、喜歡數(shù)、瀏覽次數(shù)等,需要的朋友可以參考下2014-10-10MySQL開啟遠(yuǎn)程訪問權(quán)限的最新方法
這篇文章主要給大家介紹了關(guān)于MySQL開啟遠(yuǎn)程訪問權(quán)限的最新方法,在MySQL中,要實(shí)現(xiàn)遠(yuǎn)程訪問,首先需要在MySQL服務(wù)端上開啟相應(yīng)的權(quán)限,需要的朋友可以參考下2023-08-08