關於自治事務和鎖 PRAGMA AUTONOMOUS_TRANSACTION & LOCK，transaction事務

關於自治事務和鎖 PRAGMA AUTONOMOUS_TRANSACTION & LOCK，transaction事務

之前遇到的一個問題, 可以穩定重現.

Oracle 的 INV 提供了一個介面 inv_lot_api_pub.auto_gen_lot() , 用來自動為 item 產生 lot number, 調用一次這個介面, lot number 就會自動 + 1; 看裡頭的代碼, 是先從 MTL_SYSTEM_ITEMS 這個表裡面取出一個欄位 start_auto_lot_number, 比方說取出來的數值是1000, 那麼就會把 1000 當做當前的 lot number, 接著會調用一個 procedure, 去更新這個欄位, 變成 1001. 這個procedure 名字叫做 update_msi(). 最後去驗證 1000 這個 lot number 是不是已經存在了. 如果存在, 那麼會重新取出 1001 當做當前 lot number, 並且驗證是不是存在, 直到找到沒有重複的lot number, 就把它返回給調用 api 的地方.

那麼這段代碼會有什麼問題呢? 我們假設一個情境, 有兩個 session 同時調用這個 api, 那麼在從 MSI 取資料的階段, 有可能兩個 session 會取出來相同的lot number. 嗯, 這個是有可能的. 如果這兩個 session 是在為同一個 item 產生 lot number 就還好, 但是如果是不同的item, 就會為不同的兩個 item 產生相同的 lot number. 這樣資料就錯了.

解決的辦法就是通過鎖來鎖住被選擇的 lot number, 並且在更新成功之後釋放鎖.

鎖住選擇的資料的方法是使用 for update 語句

     SELECT auto_lot_alpha_prefix,            start_auto_lot_number       INTO l_lot_prefix,            l_lot_suffix       FROM mtl_system_items      WHERE organization_id = p_org_id        AND inventory_item_id = p_inventory_item_id for update

一旦有資料被選中, 那麼同樣使用這個 SQL 的session 將不能獲得這些資料, 這就保證了無法產生相同的lot number. 當成功更新 lot number 之後, 就可以用 commit 釋放鎖了.

那麼問題來了, 如果我們使用 commit 提交這個事務, 將會把所有的交易處理都提交, 那麼如果之後出現錯誤復原了, 將會有一部分資料提交, 另一部分資料復原了. 解決的辦法是使用自治事務. 從 select for update 語句開始, 到update MSI 並 commit 為止, 我們定義一個 procedure 並聲明這是一個 PRAGMA AUTONOMOUS_TRANSACTION. 這樣提交事務的時候就只會提交這個自治事務所做的更改, 並且釋放鎖給其他語句使用. 在自治事務之外的資料更新操作都不會受到影響.

這樣通過鎖和聲明自治事務, 就可以保證不會在兩個 session 裡面擷取到相同的 lot number 了;