發現問題
最近在補以前數據的時候程序突然報如下錯誤：
| [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出現了死鎖問題，其實上面跑的程序在測試服跑了好久都沒什么問題，為什么在正式服上會出現mysql的死鎖問題呢，第一反應是不是數據量太大（3百多萬條），可是也不可能啊，再說死鎖和這些有什么雞毛的關系，看來要好好解決下了。
問題分析
我的分析是：由于現在處理的是正式服的數據，而正式服還有許多用戶在操作，應該是在用戶查詢，或者是其他操作的時候，和我這邊的數據更新產生了死鎖（首先說明使用的是：InnoDB存儲引擎。由于用戶那邊的查詢或者其他操作鎖定了我需要的資源，而我這邊更新也鎖定了用戶操作的一部分資源，兩邊都等著對方釋放資源，從而導致死鎖）。
解決方法
知道錯誤code之后，先來查看mysql的說明，關于上面的 Error: 1213 SQLSTATE: 40001，參見：Server Error Codes and Messages
| Message: Deadlock found when trying to get lock; try restarting transactionInnoDB 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 |
這兩句也就道出了處理死鎖的方法了，我就是在死鎖錯誤發生的時候，使用定時器再重新做一次更新操作，這樣就避免了上面出現的問題。
另外，參考了stack overflow上面一個回答：http://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans
新聞熱點
疑難解答
