MySQL更新鎖表超時 Lock wait timeout exceeded

• 2022 年 8 月 16 日
• 筆記

背景

最近在做一個訂單的釘釘審批功能，釘釘審批通過之後，訂單更新審核狀態，然後添加一條付款，並且更新付款狀態：

// 訂單審批通過
@Transactional(rollbackFor = Exception.class)
	public void orderPass() {
		// 更新訂單審核狀態
		updateOrderAuditStatus(id);
		// 添加入庫
		addPutInStorage(id);
		// 更新訂單入庫狀態
		updateOrderStorageStatus(id);
	}

其中的添加入庫是遠程ERP入庫，添加出庫之後更新出庫狀態。因為ERP可能因為庫存不足，會入庫失敗。但此時審批流程已經結束，不可能再發起一遍審批流程。當添加入庫失敗時訂單審核狀態正常更新，添加入庫和更新入庫狀態失敗。這裡的解決方案是：

拆分成兩個方法，一個是更新訂單審核狀態，另一個添加入庫和更新入庫狀態。添加入庫和更新入庫狀態開啟一個事務，也就是添加嵌套事務 REQUIRES_NEW,REQUIRES_NEW表示無論是否有事務，都會創建一個新的事務。

修改後的程式碼如下：

// 訂單審批通過
@Transactional(rollbackFor = Exception.class)
public void orderPass() {
    // 更新訂單審核狀態
    updateOrderAuditStatus(id);
    try {
        // 更新出庫  
        updatePutInStorage(id);
    } catch (Exception e) {
        System.out.println("更新出庫失敗");
    }

}

// 更新出庫
@Transactional(rollbackFor = Exception.class,propagation = Propagation.REQUIRES_NEW)
public void updatePutInStorage(Long id) throws Exception{
    // 添加入庫
    addPutInStorage(id);
    // 更新訂單入庫狀態
    updateOrderStorageStatus(id);
    System.out.println("更新出庫成功");
}

上面講程式碼拆分成更新訂單審核狀態和更新入庫,其中更新入庫報錯會被try catch異常捕獲，不會影響到訂單審核狀態更新。而添加入庫和更新訂單入庫狀態處於同一個事務下，要麼同時成功，要麼同時失敗。上述問題也解決了。

然而運行結果：

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

原因分析

鎖超時了，為什麼會有鎖呢？主要是這裡添加了REQUIRES_NEW。

• 外層事務對錶的更新鎖住了表的行，外層事務還沒有提交，就調用了內層事務updatePutInStorage,內層事務調用了updatePutInStorage。
• updatePutInStorage需要更新訂單的入庫狀態,此時外層事務鎖住了該表，所以更新訂單的入庫狀態無法更新。
• 更新訂單的入庫狀態等待更新訂單的審核狀態,而REQUIRES_NEW又會讓更新訂單的審核狀態等待更新訂單的入庫狀態。造成相互等待，也就造成死鎖。

解決方案

死鎖：兩個執行緒為了保護兩個不同的共享資源而使用了兩個互斥鎖，那麼這兩個互斥鎖應用不當的時候，可能會造成兩個執行緒都在等待對方釋放鎖，在沒有外力的作用下，這些執行緒會一直相互等待，就沒辦法繼續運行，這種情況就是發生了死鎖。

上面鎖超時原因，就是死鎖的一種原因。所以需要把更新訂單審核狀態方法放在最後:

// 訂單審批通過
@Transactional(rollbackFor = Exception.class)
public void orderPass() {
    
    try {
        // 更新出庫  
        updatePutInStorage(id);
    } catch (Exception e) {
        System.out.println("更新出庫失敗");
    }
    // 更新訂單審核狀態
    updateOrderAuditStatus(id);

}

// 更新出庫
@Transactional(rollbackFor = Exception.class,propagation = Propagation.REQUIRES_NEW)
public void updatePutInStorage(Long id) throws Exception{
    // 添加入庫
    addPutInStorage(id);
    // 更新訂單入庫狀態
    updateOrderStorageStatus(id);
    System.out.println("更新出庫成功");
}

總結

• 添加嵌套事務需要考慮到死鎖的問題。
• 一個事務只有等全部方法執行完畢之後才會提交事務。
• 含有嵌套的事務的更新，需要按照相同的順序更新，不然可能會出現鎖相互等待的情況。

參考

業務上第一次遇到MySQL更新鎖表超時（ Lock wait timeout exceeded; try restarting transaction）