查詢 sys 實體和 DMV 是否會鎖定基礎表以及 SQL Server 嘗試寫入它們時會發生什麼？

如果我做一個香草SELECT * FROM sys.tables或者SELECT * FROM sys.dm_exec_query_stats那些表因為我正在從它們讀取數據而被鎖定？

如果是這樣，當我鎖定它們時，當 SQL Server 綁定寫入它們時會發生什麼，例如當創建一個新表時？…在​​釋放讀鎖之前，表創建是否真的掛起？

首先，sys.tables是系統目錄視圖，而不是表。並且 DMV / DMF（尤其是那些報告統計數據的）經常查詢記憶體，這就是為什麼當您重新啟動或執行時它們會被清除DBCC FREESYSTEMCACHE(N'ALL');。

儘管如此，由於sys.tables最終會導致實際的系統表，我認為我應該對此進行測試，特別是因為我過去注意到有時WITH (NOLOCK)在查詢某些系統目錄視圖時添加確實有幫助。

為了測試這一點，我使用 SQLCLR 函式在返回每一行後暫停幾秒鐘。這使自動送出事務保持打開足夠長的時間，以便能夠查看操作期間實際發生的鎖定情況（無需提出幾個交叉連接來嘗試返回數千萬行，這也需要一段時間但佔用還有很多記憶體）。SQLCLR 函式DB_WaitForDelay在免費版本的SQL#中可用，這是我編寫的 SQLCLR 過程和函式庫。

USE [tempdb];

SELECT *, SQL#_Full.SQL#.DB_WaitForDelay(5000, NEWID())
FROM   sys.tables  -- WITH (NOLOCK)

在執行該操作時，該會話鎖定了以下資源：

LockResource                             Mode   Type  Status
[sys].[sysschobjs] (SYSTEM_TABLE)        Sch-S  LOCK  GRANT
[sys].[sysidxstats] (SYSTEM_TABLE)       Sch-S  LOCK  GRANT
[sys].[sysobjvalues] (SYSTEM_TABLE)      Sch-S  LOCK  GRANT
[sys].[syssingleobjrefs] (SYSTEM_TABLE)  Sch-S  LOCK  GRANT
[sys].[sysmultiobjrefs] (SYSTEM_TABLE)   Sch-S  LOCK  GRANT

[sys].[sysschobjs]                       S      LOCK  GRANT {not if WITH (NOLOCK) is used}

如果我在從中選擇時取消註釋，則最後一項sysschobjs不會顯示。有趣的。WITH (NOLOCK)``sys.tables

在執行時（沒有WITH (NOLOCK)），我能夠在 中執行以下操作[tempdb]：

CREATE TABLE dbo.IsLocked (Col2 INT);
GO
DROP TABLE dbo.IsLocked;
GO

因此，即使使用了一些鎖，您似乎也應該沒問題。實際上，我什至再次測試了，但是這次我首先執行了以下操作SELECT：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;

再次沒有乾擾CREATE TABLE/DROP TABLE操作。

 

從sys.dm_exec_query_statsDMV 中選擇並使用DB_WaitForDelay 時，我沒有看到該會話佔用了任何鎖。

---

只是為了說明這一點：我只測試了問題中提到的兩個對象。其他系統目錄視圖和/或 DMF/DMV可能會執行一些額外的鎖定，這些鎖定可能會產生更明顯的影響。

引用自：https://dba.stackexchange.com/questions/253427