sql-server - SQL Server 2008中的临时表上的nolock

Question

从临时表中进行选择时，添加with (Nolock)会减少争用吗？还是SQL Server足够聪明，因此一开始就不会在临时表上创建争用？

PS:是的，我知道READUNCOMMITTED的危险。

select * from #myTempTable

与

select * from #myTempTable with (nolock) --is this faster?

Answer 1

您可以使用跟踪标志1200(在开发机器上，因为我认为这是全局的)来查看自己取出的锁

SET NOCOUNT ON;

CREATE TABLE ##T
(
X INT
)

INSERT INTO ##T 
SELECT number
FROM master..spt_values

CREATE TABLE #T
(
X INT
)
INSERT INTO #T
SELECT *
FROM ##T

/*Run the commands first with the trace flag off so the locking
info is less full of irrelevant stuff about plan compilation 
*/
GO

PRINT '##T Read Committed'
SELECT COUNT(*) FROM ##T
PRINT '##T NOLOCK'
SELECT COUNT(*) FROM ##T WITH (NOLOCK)
PRINT '##T Finished'

GO

PRINT '#T Read Committed'
SELECT COUNT(*) FROM #T
PRINT '#T NOLOCK'
SELECT COUNT(*) FROM #T WITH (NOLOCK)
PRINT '#T Finished'

GO

DBCC TRACEON(-1,3604)
DBCC TRACEON(-1,1200)

GO

PRINT '##T Read Committed'
SELECT COUNT(*) FROM ##T
PRINT '##T NOLOCK'
SELECT COUNT(*) FROM ##T WITH (NOLOCK)
PRINT '##T Finished'

GO

PRINT '#T Read Committed'
SELECT COUNT(*) FROM #T
PRINT '#T NOLOCK'
SELECT COUNT(*) FROM #T WITH (NOLOCK)
PRINT '#T Finished'

GO

DBCC TRACEOFF(-1,3604)
DBCC TRACEOFF(-1,1200)

DROP TABLE ##T
DROP TABLE #T

对于全局临时表，毫不奇怪的是，它的作用更大。

但是，本地 #temp表的锁类型仍然存在很小的差异。我在下面复制了输出的一部分

#T Read Committed
Process 56 acquiring IS lock on OBJECT: 2:301244128:0  (class bit0 ref1) result: OK

Process 56 acquiring S lock on OBJECT: 2:301244128:0  (class bit0 ref1) result: OK

Process 56 releasing lock on OBJECT: 2:301244128:0 

#T NOLOCK
Process 56 acquiring Sch-S lock on OBJECT: 2:301244128:0  (class bit0 ref1) result: OK

Process 56 acquiring S lock on HOBT: 2:9079256880114171904 [BULK_OPERATION] (class bit0 ref1) result: OK

Process 56 releasing lock on OBJECT: 2:301244128:0 

编辑:以上结果是针对堆的。对于具有聚集索引的临时表，结果如下。

#T Read Committed
Process 55 acquiring IS lock on OBJECT: 2:1790629422:0  (class bit0 ref1) result: OK

Process 55 acquiring S lock on OBJECT: 2:1790629422:0  (class bit0 ref1) result: OK

Process 55 releasing lock on OBJECT: 2:1790629422:0 

#T NOLOCK
Process 55 acquiring Sch-S lock on OBJECT: 2:1790629422:0  (class bit0 ref1) result: OK

Process 55 releasing lock on OBJECT: 2:1790629422:0 

#T Finished

堆版本上的 BULK_OPERATION锁定的原因是 explained here。但是可以看出，无论如何，锁定开销都非常小。