小知識

1. 1. 交易中資料表會持續鎖定至交易結束或是連線結束
   2. 最外層的交易 `ROLLBACK`，內層就算 `COMMIT` 依然會復原變更
   3. 巢狀交易過程中的資料表都會鎖定直到交易結束，外層交易 `COMMIT` 內層交易卻沒有 `COMMIT` 資料表會被鎖定不會釋放
   4. 交易前加上 `SET XACT_ABORT ON` 可防止過程中發生錯誤沒有 `ROLLBACK` 造成鎖表的問題

閱讀更多

目前工作 Database 方面主要都在使用 SQL Server，寫了近兩年的 Stored Procedure 也遇到了許多的問題，在這裡簡單筆記一下。

1. 暫存資料表小知識
   * `#` 或 `##` 建立的暫存資料表，可從 `tempdb.暫存資料表` 找到。
   * `#` 建立的暫存資料表只有該連線可以取用；`##` 則是全部連線均可使用。
   * 連線結束時皆會自動刪除，若程式中有 Connection Pool 來管理連線則不會立刻刪除。
   * 父層 Stored Procedure 建立的暫存資料表子層也可以使用；子層 Stored Procedure 建立的暫存資料表父層也可以使用
2. 若開啟交易 (Transaction) 但無提交異動 (Commit) 會發生資料表鎖死 (Table Lock) 的問題，切記要提交異動 (Commit)。
3. 使用主鍵來 `UPDATE` 為資料鎖 (Row Lock)，若使用其他條件則有可能會升級成資料頁鎖(Page Lock) 或資料表鎖 (Table Lock)。
4. 交易 (Transaction) 具有復原機制 (RollBack)，但實際上資料表中的資料已經被異動且鎖定的關係所以無法取得，可利用 `WITH(NOLOCK)` 來取得被鎖定的髒資料。
5. 未開啟交易時使用 `CURSOR` 或 `UPDATE FROM` 若超過五千筆時可能會造成鎖定與死結，可使用 `ROW_NUMBER()` 排序資料塞入暫存資料表並使用 `WHILE` 來一筆一筆更新資料避開鎖定，速度雖慢但能減少鎖死。

   SET NOCOUNT ON
   
   DECLARE
       @RowNum         INT,
       @RowCount       INT,
       @Temp_Id        INT
   
   SELECT
       ROW_NUMBER() OVER(ORDER BY [Id]) AS RowNum,
       [Id],
       [Name]
   INTO
       #Temp
   FROM
       [exfast].[dbo].[TableA] WITH(NOLOCK)
   
   SELECT
       @RowNum = 1,
       @RowCount = (SELECT SUM(1) FROM #Temp)
   
   WHILE(@RowNum <= @RowCount)
   BEGIN
   
       SELECT
           @Temp_Id = [Id]
       FROM
           #Temp
       WHERE
           RowNum = @RowNum
   
       UPDATE
           [exfast].[dbo].[TableA]
       SET
           [Name] = 'dddd'
       WHERE
           [Id] = @Temp_Id
   
       SET @RowNum = @RowNum + 1
   
   END
   
   DROP TABLE #Temp
   

6. 高負載情況下使用 `UPDATE OUTPUT` 的方法來取得資料更新前後的狀態，可減少 `SELECT` 次數提高效率。

   DECLARE  @TempA  TABLE
   (
       [INSERTED_Id]       INT,
       [INSERTED_Name]     NVARCHAR(32),
       [DELETED_Id]        INT,
       [DELETED_Name]      NVARCHAR(32)
   )
   
   UPDATE
       [exfast.Helper].[dbo].[TableA]
   SET
       [Name] = 'qqqq'
   OUTPUT
       INSERTED.[Id],
       INSERTED.[Name],
       DELETED.[Id],
       DELETED.[Name]
   INTO
       @TempA
   
   SELECT * FROM @TempA

7. 高負載情況下可能會將隔離層級拉高到 Serializable，這時使用交易會發生相同資料表 `SELECT` `UPDATE` 中的 Shared Lock 與 Exclusive Lock 撞車造成 DeadLock，可在 `SELECT` 加上 `WITH(UPDLOCK)` 來減少此問題。

   -- 故意指定隔離層級模擬撞車的情況
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   
   BEGIN TRAN
   
   SELECT * FROM [exfast.Helper].[dbo].[TableA]
   
   WAITFOR DELAY '00:00:10'
   
   UPDATE
       [exfast.Helper].[dbo].[TableA]
   SET
       [Name] = 'cccc'
   WHERE
       [Id] = 2
       
   ROLLBACK