SQLSERVER临时表引发的"锁"案

在一个CS结构的项目里使用SQLServer时碰到一个有意思的现象，以下是从日志中摘出来的用户操作：

用户A的操作会引发程序在事务中使用Local临时表，例如：

1 BEGIN TRAN
2 
3 SELECT * INTO #temp FROM DB1.dbo.Table1
4 
5 --do something
6 
7 DROP TABLE #temp
8 
9 COMMIT TRAN

用户B的操作也会引发程序使用临时表且在删除临时表前会先判断临时表的存在性，例如：

1 SELECT * INTO #MyTempTable FROM TestJJV9.dbo.test
2 
3 IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE name like '%#MyTempTable%')
4 BEGIN
5 DROP TABLE tempdb.dbo.#MyTempTable
6 END

用户A和用户B使用的是同一个SQLServer实例，但是用的是不同的数据库。

 

那么，现象来了，用户A的操作越频繁，用户B越容易碰到长时间的等待甚至超时。

 

分析：
从用户B的操作来看，判断临时表存在性的SQL语句(line3)导致了对tempdb.dbo.sysobjects的扫描（sysobjects是一个view，实际上扫描的是sys.sysschobjs的聚集索引），该扫描需要对sys.sysschobjs的聚集索引申请S锁。
而用户A的操作会对sys.sysschobjs表的聚集索引的KEY持有一个X锁，在用户A的事务提交之前，用户B无法获得S锁，所以处于等待状态。
如果有C，D，E等用户持续在执行类似用户A的操作，那么用户B基本上就只能等死了。。。

解决方案：
使用OBJECT_ID判断临时表的存在性，可以避免对sys.sysschobjs的扫描，防止被锁（猜想其内部可能是从类似缓存的结构中取得的结果），例如：

SELECT * INTO #MyTempTable FROM DB2.dbo.AnyTable

IF OBJECT_ID('tempdb.dbo.#MyTempTable') IS NOT NULL    
BEGIN
DROP TABLE tempdb.dbo.#MyTempTable
END

 

另外，上述用户A的操作无论生成的是Local临时表还是Global临时表，都有这个现象，原因是一样的。

最后转载一个即时调查当前服务器状况的SP，要调查类似上述问题时，可以如下使用：

exec sp_WhoIsActive @get_locks = 1, @find_block_leaders = 1

从blocking_session_id, blocked_session_count, locks列可以看出谁正在被谁阻塞

转载于:https://www.cnblogs.com/royliugc/p/5051265.html