[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
    
  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


這裡的資訊對您有用嗎?歡迎斗內給我