系統維運難免會有直接修改正式台資料庫的需求,這類更新程序需要被嚴謹規範,萬一指令 WHERE 條件下錯,更新對象從一筆變成一萬筆,可不是亂著玩的。

為防範出錯,除了制定申請程序、要求指令複核,通常還要保留更新前後資料狀態,一方面可供人為確認,另一方面作為佐證。不過,要防止更新結果與預期不一致,除了靠人為把關(延伸閱讀:小心駛得萬年船 - SQL指令保險栓),在撰寫 SQL 更新指令時採取防禦性設計概念,會是更好的做法。

用個實例說明,假設我有個 Product 資料庫:

資訊部門接獲業務單位指示,要將原本編號為 1 的產品由 980 元調成 990,更新指令怎麼寫?

UPDATE Product SET Price = 990 WHERE Id = 1

可行。但請考慮以下情況:

  1. 資料庫裡沒 Id = 1 這筆 (申請單位搞錯資料庫?)
  2. Id = 1 其實是 Notebook,原價 12,500 (業務單位給錯產品編號)
  3. 它的價格前陣子早已被調成 1000 元 (業務單位接收到錯誤訊息給錯指示)

依我的看法,以上三種狀況 UPDATE 指令都不該執行,應退回申請單位釐清問題根源,說不定還能及時阻止另一場災難。

基於以上考量,一個有效防呆設計是在更新指令前後加入檢查,當遇到檢查結果不如預期就取消更新作業並拋出錯誤。

以 SQL Server 而言,我們可以用 BEGIN TRAN 啟用交易將相關更新包在一起,配合 SET XACT_ABORT ON 指定遇到錯誤即 Rollback 取消整批更新動作。在執行過程,我們可加入自訂檢查邏輯,一旦察覺結果與預期不同,使呼叫 THROW 丟出錯誤(注意:RAISERROR 不會觸發 XACT_ABORT,要用 THROW)觸發取消交易。

以本案例可寫成:

SET XACT_ABORT ON
BEGIN TRAN

--實驗用,插入一筆判別 Commit 或 Rollback
INSERT INTO Product VALUES (255,'Committed', -1);

SELECT * FROM Product WHERE Id = 1;
IF @@ROWCOUNT <> 1 THROW 51000, N'Id=1 not found', 1;
UPDATE Product SET Price = 990 WHERE Id = 1 AND Price = 980;
IF @@ROWCOUNT <> 1 THROW 51000, N'Id=1, Price=980 not matched', 1;
SELECT * FROM Product WHERE Id = 1;

COMMIT TRAN

正常執行的話會像這樣:

修改前後各 SELECT 一次,對照佐證也有了。

正常流程測完,先 DELETE Product WHERE Price < 0 將觀察用的 Id = 255 移除,再來動點手腳做實驗。

實驗 1,將 Id = 1 改成 Notebook,價格 12500:

如上圖所示,發生 Id=1, Price=980 not matched 錯誤,資料不會更新。(另開視窗 SELECT * FROM Product 並沒有 Id = 255,證實交易被 Rollback)

實驗 2,將 Id = 1 刪除:

得到 Id=1 not found,一樣不會執行更新。

透過這種方式,我們就可讓資料更新程序更嚴謹,降低失誤風險。以上提供大家參考。

Tutorial of how to update SQL in defensive way to decrease risk of mistakes.


Comments

# by paicheng0111

a typo: nto -> not

# by Jeffrey

to paicheng0111, 感謝指正,已更新。

Post a comment


89 - 45 =