濮阳杆衣贸易有限公司

主頁 > 知識庫 > mysql報錯:Deadlock found when trying to get lock; try restarting transaction的解決方法

mysql報錯:Deadlock found when trying to get lock; try restarting transaction的解決方法

熱門標簽:沈陽電銷外呼系統(tǒng)原理是什么 上海智能外呼系統(tǒng)代理商 ps制作地圖標注gif 成都優(yōu)派外呼系統(tǒng) 電子地圖標注電話 寧波企業(yè)外呼系統(tǒng)收費 聊城智能外呼系統(tǒng)運營商 地圖標注人員兼職 扎樣申請400電話

發(fā)現(xiàn)問題

最近在補以前數(shù)據(jù)的時候程序突然報如下錯誤:

[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)了死鎖問題,其實上面跑的程序在測試服跑了好久都沒什么問題,為什么在正式服上會出現(xiàn)mysql的死鎖問題呢,第一反應是不是數(shù)據(jù)量太大(3百多萬條),可是也不可能啊,再說死鎖和這些有什么雞毛的關(guān)系,看來要好好解決下了。

問題分析

我的分析是:由于現(xiàn)在處理的是正式服的數(shù)據(jù),而正式服還有許多用戶在操作,應該是在用戶查詢,或者是其他操作的時候,和我這邊的數(shù)據(jù)更新產(chǎn)生了死鎖(首先說明使用的是:InnoDB存儲引擎。由于用戶那邊的查詢或者其他操作鎖定了我需要的資源,而我這邊更新也鎖定了用戶操作的一部分資源,兩邊都等著對方釋放資源,從而導致死鎖)。

解決方法

知道錯誤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 againbr>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 

這兩句也就道出了處理死鎖的方法了,我就是在死鎖錯誤發(fā)生的時候,使用定時器再重新做一次更新操作,這樣就避免了上面出現(xiàn)的問題。

另外,參考了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 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)容對大家的學習或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。

您可能感興趣的文章:
  • c#中SqlTransaction——事務詳解
  • mysql Non-Transactional Database Only(只支持MyISAM)
  • 淺談Transact-SQL
  • MySQL數(shù)據(jù)庫事務隔離級別介紹(Transaction Isolation Level)
  • Transact_SQL 小手冊
  • Sql Server數(shù)據(jù)庫常用Transact-SQL腳本(推薦)

標簽:朔州 三明 林芝 AXB 汕頭 咸寧 宿州 內(nèi)江

巨人網(wǎng)絡(luò)通訊聲明:本文標題《mysql報錯:Deadlock found when trying to get lock; try restarting transaction的解決方法》,本文關(guān)鍵詞  mysql,報錯,Deadlock,found,when,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《mysql報錯:Deadlock found when trying to get lock; try restarting transaction的解決方法》相關(guān)的同類信息!
  • 本頁收集關(guān)于mysql報錯:Deadlock found when trying to get lock; try restarting transaction的解決方法的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章
    泸西县| 南华县| 鱼台县| 房山区| 界首市| 滨海县| 尚志市| 天水市| 陆川县| 南陵县| 昭苏县| 镇原县| 蒙阴县| 北京市| 太和县| 富平县| 龙泉市| 章丘市| 宜章县| 北京市| 邯郸县| 偏关县| 鲜城| 依安县| 辽源市| 北京市| 拉孜县| 岑巩县| 九寨沟县| 清新县| 习水县| 获嘉县| 阳城县| 固原市| 星子县| 融水| 南召县| 玉环县| 上蔡县| 沙坪坝区| 玉溪市|