SQL - Transaction

Transaction Intro

每筆跟 SQL 的互動都算是交易

為何需要 Transaction：

假設今天轉帳到一半，銀行突然停電
對方還沒接收到你轉帳的錢，但你的帳戶已經扣錢下去了，你會不會氣炸 XD

所以必須要讓整個交易都完成，
你的帳戶扣錢了，對方的帳戶也收到錢了，才能算是交易成功
否則需要 ROLLBACK 回原本的狀態

Isolation Levels

Transaction 有多個隔離層級

Serializable 可序列化
- 系統會依照搜尋條件對查詢結果資料設定範圍，確保範圍內的資料不會被新增、修改、刪除
- 一定要等前一個交易完成，後一筆交易才能去使用該筆交易用到的資料
- 不會出現 Dirty 讀取，不可重複讀取
Repeatable reads 可重複讀取
- 不能讀取其他交易已經修改但尚未確認的資料，且在交易完成前，任何其他交易都不能修改目前交易已讀取的資料
Read committed 授權讀取 (SQL Server 預設)
- 不能讀取其他交易已經修改但尚未確認的資料
Read uncommitted 未授權讀取
- 可讀取其他交易已經修改但尚未確認的資料

Implementing Transaction

Define Transaction

利用 TRY…CATCH，可確保欲插入的 table 確實存在，否則會回傳錯誤

ERROR_NUMBER() 是 SQL 內建函數，用於檢測是否有 ERROR 產生

BEGIN TRANSACTION：判斷交易開始
COMMIT TRANSACTION：如果過程中沒有錯誤沒有錯誤，便開始交易
ROLLBACK TRANSACTION：如果過程中有錯誤，則需 ROLLBACK 回原本狀態

BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO tableName1 VALUE...
        INSERT INTO tableName2 VALUE...
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER()
    ROLLBACK TRANSACTION
END CATCH;

TRANSACTION 要包的很小心，以免若有其他需要讀取被包入的資料時，會被 lock 住讀不到

Using XACT_ABORT

亦可以利用 XACT_ABORT，在錯誤產生時自動啟動 ROLLBACK
因為有時有些 ERROR 難以判斷或難以判斷如何包入 TRANSACTION 中

XACT_ABORT 預設為 OFF，故將其 ON 起來

但其實當你想到要 ON 起來，你就會想到要包 TRANSACTION ㄌ

SET XACT_ABORT ON;

TRANSACTION 不一定都包在資料庫，也有可能會包在程式前端，但也要包得小心