在預存程序中使用交易防止資料不一致 2021 年 4 月 20 日，由 Robert Gravelle 撰寫

在認識資料庫交易文章中，我們學習了交易是如何透過保證使用交易執行的所有作業同時成功或同時失敗來防止資料遺失和不一致。在今天的後續文章中，我們將學習如何在預存程序中使用交易，以確保所涉及的所有資料表保持一致的狀態。

關於 sp_delete_from_table 預存程序

如果你讀過我以前的文章，你可能知道我經常使用 Sakila 範例資料庫來說明新概念。我們這次不妨也使用這個資料庫。它是專門為 MySQL 開發的學習資料庫。如果你還不知道它是什麼，我可以告訴你 Sakila 範例資料庫包含與虛構的電影租賃連鎖店有關的資料。除了資料表和檢視之外，還可以找到使用者函式、觸發器、査詢和預存程序，這些都是最常用的資料庫物件和工作。

與本文相關的預存程序之一是 sp_delete_from_table。它接受以下三個輸入參數：

@table：要從中刪除列的資料表的名稱。
@whereclause：用於識別要刪除哪些列的條件。
@delcnt：我們希望删除多少列。

該程序傳回 @actcnt（bigint）輸出參數，它包含實際删除的列數目。

以下是 Navicat Premium 中顯示的完整定義：

重要的交易陳述式

關聯式資料庫為我們提供了幾個重要的陳述式來控制交易：

若要開始一個交易，請使用 BEGIN TRANSACTION 陳述式。START 或 BEGIN WORK 都是BEGIN TRANSACTION 的別名。你可以在 sp_delete_from_table 程序的第 17 行找到它。
若要認可目前的交易並使其變更永久生效，請使用 COMMIT 陳述式。這發生在程序的第 32 行。
若要回溯目前的交易並取消其變更，請使用 ROLLBACK 陳述式。程式碼中出現了兩種情况：
1. 如果陳述式將删除資料表中的所有列，則會有訊息提示，並在第 26 行回溯交易。
2. 如果删除的列數目與預期的列數目不相符，則會再有訊息提示，並回溯交易。這發生在第 38 行。
若要為目前的交易停用或啟用自動認可模式，請使用 SET autocommit 陳述式。根據預設，某些資料庫（如 MySQL）在預設情況下啟用了 autocommit 模式。這意味著，當不在交易內時，每個陳述式都是不可部分完成的，就像被 START TRANSACTION 和 COMMIT 包著一樣。你不能使用 ROLLBACK 來復原陳述式的影響。但是，如果在執行期間發生錯誤，則會回溯該陳述式。由於大部分工作在 sp_delete_from_table 程序中的交易中進行，因此不需要 SET autocommit 陳述式。