Sql Server 資料庫事務與鎖，同一事務更新又查詢鎖？期望大家來解惑

XSpringSun發表於2023-04-28
SQLServer資料庫

我有一個People表，有三行資料：
image

如果我們沒詳細瞭解資料庫事務執行加鎖的過程中，會不會有這樣一個疑問：如下的這段 SQL 開啟了事務，並且在事務中進行了更新和查詢操作。

BEGIN TRAN 
	update People set Name='張三' where id=1;
	
	select * from People where id=1;
commit tran

我們知道sql server資料庫的預設事務級別是READ COMMITTED（已提交的讀取），我們再看一下已提交讀事務隔離級別描述：

允許事務讀取另一個事務以前讀取（未修改）的資料，而不必等待第一個事務完成。 SQL Server資料庫引擎將保留 (對所選資料) 獲取的寫入鎖，直到事務結束，但讀取鎖將在執行 SELECT 操作後立即釋放。 這是SQL Server資料庫引擎預設級別。

那麼我們在READ COMMITTED 隔離級別下更新People表資料庫，按照這個邏輯在id=1的資料行上新增排它鎖（X鎖）並等到事務提交後才會釋放鎖。
但是事務繼續執行查詢，在READ COMMITTED隔離級別下 Select 會對查詢資料施加共享鎖（S鎖）。因為有排它鎖，所以查詢無法獲得共享鎖需要等待排它鎖釋放，如果按照這個邏輯的話這個事務自身就死鎖無法執行了。

但這個事務還是會正常執行完成，針對這個疑問，那麼我們看下資料庫的事務和鎖：

資料庫引擎隔離級別

ISO 標準定義了以下隔離級別，SQL Server資料庫引擎支援所有這些隔離級別：

隔離級別 定義
未提交的讀取 隔離事務的最低階別，只能保證不讀取物理上損壞的資料。 在此級別上，允許髒讀，因此一個事務可能看見其他事務所做的尚未提交的更改。
已提交的讀取 允許事務讀取另一個事務以前讀取（未修改）的資料，而不必等待第一個事務完成。 SQL Server資料庫引擎將保留 (對所選資料) 獲取的寫入鎖，直到事務結束，但讀取鎖將在執行 SELECT 操作後立即釋放。 這是SQL Server資料庫引擎預設級別。
可重複的讀取 SQL Server資料庫引擎會保留對所選資料獲取的讀取和寫入鎖定，直到事務結束。 但是，因為不管理範圍鎖，可能發生虛擬讀取。
可序列化 隔離事務的最高階別，事務之間完全隔離。 SQL Server資料庫引擎保留對所選資料獲取的讀取和寫入鎖定，這些鎖將在事務結束時釋放。 SELECT 操作使用分範圍的 WHERE 子句時獲取範圍鎖，主要為了避免虛擬讀取。 注意： 請求可序列化隔離級別時，複製的表上的 DDL 操作和事務可能失敗。 這是因為複製查詢使用的提示可能與可序列化隔離級別不相容。

image
SQL Server資料庫引擎使用不同的鎖模式鎖定資源，這些模式確定併發事務如何訪問資源。

T-SQL 設定事務隔離級別，只對當前會話連線一直有效

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

鎖模式

下表顯示了SQL Server資料庫引擎使用的資源鎖模式。

鎖模式 說明
共享 (S) 用於不更改或不更新資料的讀取操作，如 SELECT 語句。
更新 (U) 用於可更新的資源中。 防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖。
排他 (X) 用於資料修改操作，例如 INSERT、UPDATE 或 DELETE。 確保不會同時對同一資源進行多重更新。
意向 用於建立鎖的層次結構。 意向鎖包含三種型別：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。
架構 在執行依賴於表架構的操作時使用。 架構鎖包含兩種型別：架構修改 (Sch-M) 和架構穩定性 (Sch-S)。
大容量更新 (BU) 在將資料大容量複製到表中且指定了 TABLOCK 提示時使用。
鍵範圍 當使用可序列化事務隔離級別時保護查詢讀取的行的範圍。 確保再次執行查詢時其他事務無法插入符合可序列化事務的查詢的行。

鎖相容性

鎖相容性控制多個事務能否同時獲取同一資源上的鎖。 如果資源已被另一事務鎖定，則僅當請求鎖的模式與現有鎖的模式相相容時，才會授予新的鎖請求。 如果請求鎖的模式與現有鎖的模式不相容，則請求新鎖的事務將等待釋放現有鎖或等待鎖超時間隔過期。 例如，沒有與排他鎖相容的鎖模式。 如果具有排他鎖（X 鎖），則在釋放排他鎖（X 鎖）之前，其他事務均無法獲取該資源的任何型別（共享、更新或排他）的鎖。 另一種情況是，如果共享鎖（S 鎖）已應用到資源，則即使第一個事務尚未完成，其他事務也可以獲取該項的共享鎖或更新鎖（U 鎖）。 但是，在釋放共享鎖之前，其他事務無法獲取排他鎖。

下表顯示了最常見的鎖模式的相容性。
image

檢視執行時鎖的情況

透過鎖的相容性模式我們知道在id=1的行上新增了排它鎖，那麼它就無法再接收任何鎖，那我們除錯這個事務看看鎖的情況。
image

我們除錯到第3行，這個時候看下鎖的情況，此時事務新增了key(行）排它鎖X鎖，page（頁）和object（表）新增了意向排它鎖IX鎖。

SELECT 
resource_type,
resource_database_id,
resource_description,
request_mode,
request_type,
request_session_id,
request_owner_type,
request_owner_id,
lock_owner_address
FROM sys.dm_tran_locks where request_owner_type='transaction'

image

然後我們再繼續除錯到第4行，此時還沒提交事務，排它鎖X依然存在，但是沒有S鎖。

image

我們知道在讀提交事務隔離級別下，S鎖是使用完了就釋放的，所以我們用SQL Server Profiler來監視下鎖的情況，設定監控的項為lock，然後設定篩選條件。

image
image

上面我已經將 張三1 改為了 張三，我們再將 張三 改回 張三1，並啟動監控。

BEGIN TRAN 
	update People set Name='張三1' where id=1;
	select * from People where id=1;
commit tran

image

可以看到事務 transactionid=30010685 的鎖監控 :

  • 首先申請IX更新意向鎖(object,page) 準備更新，然後獲得行上的X排它鎖進行更新，更新後釋放了行鎖和page鎖（EventClass= Lock:released,Mode=0-null）。
  • 等查詢時申請page頁IS意向讀取鎖，並獲得行S鎖讀取資料後釋放行鎖和page頁鎖。
  • 最後還有幾個順序釋放，依次是key、page、Object，這裡恰好和上面除錯還沒提交事務時查詢sys.dm_tran_locks的鎖情況一樣，也就是說事務提交後依次又進行了一遍釋放。

透過上面我們得出結論，事務裡面並不是取得了X鎖要等事務結束後才釋放，在事務執行過程中也是有釋放的，只是事務還保持著對於資源鎖的邏輯標識，防止其它事務併發（這裡是我推斷的，沒找到相關文獻，望大佬指正）。

所以事務是在鎖上更宏觀的邏輯隔離，事務隔離級別只是在業務上保證資料符合隔離級別預期，至於事務中如何控制鎖是基於資料庫內在設計，而不能透過事務的描述去推斷鎖過程。

我查閱網上很多博文和官方資料都是講事務和鎖概念，有時候結合兩種也是模稜兩可看不出什麼強聯絡，沒有講事務執行過程中鎖是如何變化的，不知道我這篇推論是否正確，歡迎指正。

再次驗證

我將事務隔離級別設定為REPEATABLE READ（可重複讀），然後除錯到commit行還沒提交，我們看跟蹤的鎖和事務鎖表dm_tran_locks查詢的結果，按照REPEATABLE READ描述，select查詢的S鎖會在事務提交後釋放，我們看看截圖情況

image

開啟了SQL Server Profiler結果，查詢id=3後S鎖已經釋放。

image

再查dm_tran_locks表，表中依然顯示事務獲取了S鎖，並且 resource_description=98ec012aa510 資源描述和上面跟蹤是對應的。
image

最後我們執行完除錯，跟蹤鎖顯示又按照順序釋放了一遍鎖

image

相關文章