[MSSQL] SQL Server 的一些眉眉角角 part.1

目前工作 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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

   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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

   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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   -- 故意指定隔離層級模擬撞車的情況 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