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

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

暫存資料表小知識
* `#` 或 `##` 建立的暫存資料表，可從 `tempdb.暫存資料表` 找到。
* `#` 建立的暫存資料表只有該連線可以取用；`##` 則是全部連線均可使用。
* 連線結束時皆會自動刪除，若程式中有 Connection Pool 來管理連線則不會立刻刪除。
* 父層 Stored Procedure 建立的暫存資料表子層也可以使用；子層 Stored Procedure 建立的暫存資料表父層也可以使用
若開啟交易 (Transaction) 但無提交異動 (Commit) 會發生資料表鎖死 (Table Lock) 的問題，切記要提交異動 (Commit)。
使用主鍵來 `UPDATE` 為資料鎖 (Row Lock)，若使用其他條件則有可能會升級成資料頁鎖(Page Lock) 或資料表鎖 (Table Lock)。
交易 (Transaction) 具有復原機制 (RollBack)，但實際上資料表中的資料已經被異動且鎖定的關係所以無法取得，可利用 `WITH(NOLOCK)` 來取得被鎖定的髒資料。

未開啟交易時使用 `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

高負載情況下使用 `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

高負載情況下可能會將隔離層級拉高到 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

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

相關

lex.xu