[MSSQL] SQL Server 的一些眉眉角角

同步发表至部落格：[MSSQL] SQL Server 的一些眉眉角角 (2018/11/01)

目前工作 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 ONDECLARE@RowNumINT,@RowCountINT,@Temp_IdINTSELECTROW_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 次数提高效率。

DECLARE@TempATABLE([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) 来减少此问题。

-- 故意指定隔离层级模拟撞车的情况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