防禦式 SQL 更新
2 | 5,635 |
系統維運難免會有直接修改正式台資料庫的需求,這類更新程序需要被嚴謹規範,萬一指令 WHERE 條件下錯,更新對象從一筆變成一萬筆,可不是亂著玩的。
為防範出錯,除了制定申請程序、要求指令複核,通常還要保留更新前後資料狀態,一方面可供人為確認,另一方面作為佐證。不過,要防止更新結果與預期不一致,除了靠人為把關(延伸閱讀:小心駛得萬年船 - SQL指令保險栓),在撰寫 SQL 更新指令時採取防禦性設計概念,會是更好的做法。
用個實例說明,假設我有個 Product 資料庫:
資訊部門接獲業務單位指示,要將原本編號為 1 的產品由 980 元調成 990,更新指令怎麼寫?
UPDATE Product SET Price = 990 WHERE Id = 1
?
可行。但請考慮以下情況:
- 資料庫裡沒 Id = 1 這筆 (申請單位搞錯資料庫?)
- Id = 1 其實是 Notebook,原價 12,500 (業務單位給錯產品編號)
- 它的價格前陣子早已被調成 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, 感謝指正,已更新。