目前工作 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
來一筆一筆更新資料避開鎖定,速度雖慢但能減少鎖死。
123456789101112131415161718192021222324252627282930313233343536373839404142SET NOCOUNT ONDECLARE@RowNum INT,@RowCount INT,@Temp_Id INTSELECTROW_NUMBER() OVER(ORDER BY [Id]) AS RowNum,[Id],[Name]INTO#TempFROM[exfast].[dbo].[TableA] WITH(NOLOCK)SELECT@RowNum = 1,@RowCount = (SELECT SUM(1) FROM #Temp)WHILE(@RowNum <= @RowCount)BEGINSELECT@Temp_Id = [Id]FROM#TempWHERERowNum = @RowNumUPDATE[exfast].[dbo].[TableA]SET[Name] = 'dddd'WHERE[Id] = @Temp_IdSET @RowNum = @RowNum + 1ENDDROP TABLE #Temp - 高負載情況下使用
UPDATE OUTPUT
的方法來取得資料更新前後的狀態,可減少SELECT
次數提高效率。
123456789101112131415161718192021DECLARE @TempA TABLE([INSERTED_Id] INT,[INSERTED_Name] NVARCHAR(32),[DELETED_Id] INT,[DELETED_Name] NVARCHAR(32))UPDATE[exfast.Helper].[dbo].[TableA]SET[Name] = 'qqqq'OUTPUTINSERTED.[Id],INSERTED.[Name],DELETED.[Id],DELETED.[Name]INTO@TempASELECT * FROM @TempA - 高負載情況下可能會將隔離層級拉高到 Serializable,這時使用交易會發生相同資料表
SELECT
UPDATE
中的 Shared Lock 與 Exclusive Lock 撞車造成 DeadLock,可在SELECT
加上WITH(UPDLOCK)
來減少此問題。
1234567891011121314151617-- 故意指定隔離層級模擬撞車的情況SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT * FROM [exfast.Helper].[dbo].[TableA]WAITFOR DELAY '00:00:10'UPDATE[exfast.Helper].[dbo].[TableA]SET[Name] = 'cccc'WHERE[Id] = 2ROLLBACK