SQLSERVER 的 nolock 到底是怎樣的無鎖?

一線碼農發表於2023-01-31

一:背景

1. 講故事

相信絕大部分用 SQLSERVER 作為底層儲存的程式設計師都知道 nolock 關鍵詞,即使當時不知道也會在踩過若干阻塞坑之後果斷的加上 nolock,但這玩意有什麼注意事項呢?這就需要了解它的底層原理了。

二:nolock 的原理

1. sql 阻塞還原

為了方便講述,先建立一個 post 表,插個 6 條記錄,參考程式碼如下:


CREATE TABLE post(id INT IDENTITY,content char(4000))
GO

INSERT INTO dbo.post VALUES('aaa')
INSERT INTO dbo.post VALUES('bbb')
INSERT INTO dbo.post VALUES('ccc');
INSERT INTO dbo.post VALUES('ddd');
INSERT INTO dbo.post VALUES('eee');
INSERT INTO dbo.post VALUES('fff');

這裡為了簡單我沒有建立索引,所以會出現 Table Scan 的情況,畢竟生產環境下的sql也避免不了 Table ScanClustered Index Scan 的存在,接下來還原下阻塞場景,開啟兩個 session 會話, session1 為正在執行的 update 事務, session2 為一個簡單的 select 操作,這種場景下會導致 session2 阻塞,參考程式碼如下:

  • session1

BEGIN TRAN
UPDATE post SET content='xxxxx' WHERE id=3

  • session2

SELECT * FROM post WHERE id=4

從圖中可以看到,這個 select 已經阻塞 9 分鐘了,那為什麼會被阻塞呢? 可以觀察 SQLSERVER 內部的統計資訊,比如鎖相關的動態檢視 sys.dm_tran_locks ,參考程式碼如下:


SELECT t.request_session_id,
       CASE
           WHEN t.resource_type = 'OBJECT' THEN
               OBJECT_NAME(t.resource_associated_entity_id)
           WHEN t.resource_associated_entity_id = 0 THEN
               '/'
           ELSE
               OBJECT_NAME(p.object_id)
       END AS resource_name,
       index_id,
       t.resource_type,
       t.resource_description AS description,
       t.request_mode AS mode,
       t.request_status AS status
FROM sys.dm_tran_locks AS t
    LEFT JOIN sys.partitions AS p
        ON p.hobt_id = t.resource_associated_entity_id
WHERE t.resource_database_id = DB_ID()

從圖中看,session55 準備在 1:489:0 這個槽位指向的記錄上附加 S 鎖時被阻塞,因為 1:489:0 已經被附加了 X 鎖,很顯然這個 X 鎖是 update 給的。

上面給出的是一個 靜態檢視,為了方便顯示動態檢視,這裡把 sql profile 開起來觀察兩個 session 給鎖的過程,事件選擇上如下所示:

將 sqlprofile 開啟後,重新執行下剛才的兩個會話,觀察 profile 的走勢,截圖如下:

圖中的註釋已經說的非常清楚了,和 sys.dm_tran_locks 顯示的一致,有了這些基礎後接下來觀察下如果加上 with (nolock) 會怎麼樣?

SELECT * FROM post(NOLOCK) WHERE id=4

你會發現結果是可以出來的,那為什麼可以出來呢?繼續觀察下 profile 即可。

從 session 55 的 lock 輸出來看,with(nolock) 會對 post 表附加 Sch-S 架構穩定鎖,以及分割槽中的 堆或BTree 附加S鎖, 而不再對 PAGE 附加任何鎖了,所以就不存在阻塞的情況,但肯定會引起髒讀。

到這裡基本上就是 nolock 的底層玩法了吧,不過也有一個注意點,nolock 真的不會引發阻塞嗎? 接下來我們好好聊一聊。

3. nolock 真的無視阻塞嗎

從 sqlprofile 觀察鎖的走勢圖來看,nolock 只是在上限為 page 頁級別上做到無視,但在 page 之上就無法做到了,比如你看到的 Sch-S,可能有些朋友要問了,為什麼要加上 Sch-S 鎖呢? 其實很簡單,在 query 的過程中一定要保持架構穩定嘛,不能在 query 的過程中,post 表突然被刪了,這樣大家多尷尬。

接下來也可以做個簡單的測試。


----- session 1
BEGIN TRAN
TRUNCATE TABLE post;

----- session 2
SELECT * FROM post(NOLOCK) WHERE id=4

可以發現 nolock 查詢也被阻塞了,原因就在於拿不到 post 表的 Sch-S 鎖,因為 TRUNCATE 已經給 post 附加了 Sch-M 架構修改鎖,那有沒有資料支撐呢? 繼續用動態檢視 sys.dm_tran_locks 觀察便可。

三:總結

綜上所述,nolock 也僅在 page 級別上暢通無阻,在某些情況下也會有阻塞情況的發生,由於無鎖自然就會讀到別的會話已修改但還未提交的記錄,sqlserver 作為一個資料庫應用程式,裡面包含了大量的執行時統計資訊,這些統計資訊可以用 系統檢視動態檢視 獲取,完全可以基於它們做一個完善的 APM 監控。

相關文章