巧用MySQL InnoDB引擎鎖機制解決死鎖問題[2]

　　索引KEY_TSKTASK_MONTIME (STATUS_ID MON_TIME)

　　分析涉及的兩條語句應該不會涉及相同的TSK_TASK記錄那為什麼會造成死鎖呢？

　　查詢MySQL官網文檔發現這跟MySQL的索引機制有關MySQL的InnoDB引擎是行級鎖我原來的理解是直接對記錄進行鎖定實際上並不是這樣的

　　要點如下:

　　不是對記錄進行鎖定而是對索引進行鎖定

　　在UPDATEDELETE操作時MySQL不僅鎖定WHERE條件掃描過的所有索引記錄而且會鎖定相鄰的鍵值即所謂的nextkey locking

　　如語句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 會鎖定所有主鍵大於等於的所有記錄在該語句完成之前你就不能對主鍵等於的記錄進行操作

　　當非簇索引(noncluster index)記錄被鎖定時相關的簇索引(cluster index)記錄也需要被鎖定才能完成相應的操作

　　再分析一下發生問題的兩條SQL語句就不難找到問題所在了

　　當update TSK_TASK set STATUS_ID=UPDATE_TIME=now () where STATUS_ID= and MON_TIME

　　假設update TSK_TASK set STATUS_ID=UPDATE_TIME=now () where ID in ()幾乎同時執行時本語句首先鎖定簇索引(主鍵)由於需要更新STATUS_ID的值所以還需要鎖定KEY_TSKTASK_MONTIME的某些索引記錄

　　這樣第一條語句鎖定了KEY_TSKTASK_MONTIME的記錄等待主鍵索引而第二條語句則鎖定了主鍵索引記錄而等待KEY_TSKTASK_MONTIME的記錄在此情況下死鎖就產生了

　　筆者通過拆分第一條語句解決死鎖問題

　　先查出符合條件的IDselect ID from TSK_TASK where STATUS_ID= and MON_TIME < date_sub(now() INTERVAL minute)然後再更新狀態update TSK_TASK set STATUS_ID= where ID in (…)

　　至此死鎖問題徹底解決

[]  []  

From:http://tw.wingwit.com/Article/program/MySQL/201311/29601.html