使用 CTE 查詢的後端表/行鎖定結構

這個問題是我之前提出的問題的延伸。 使用聯合時的表鎖定

使用我們正在使用的相同查詢導致阻塞在這些查詢中使用的各種查詢UNIONS是 CTE 查詢。

例如：

CREATE TABLE #TempTable (
   [Columns]
)

WITH CTE1 (Columns)
(
   Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE2 (Columns)
(
   Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE3 (Columns)
(
   Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE4 (Columns)
(
   Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE5 (Columns)
(
   Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE6 (Columns)
(
   Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE7 (Columns)
(
   Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
)

INSERT INTO #TempTable
SELECT * FROM CTE1
UNION
SELECT * FROM CTE2
UNION
SELECT * FROM CTE3
UNION
SELECT * FROM CTE4
UNION
SELECT * FROM CTE5
UNION
SELECT * FROM CTE6 WHERE [Where-Condition]
UNION
SELECT * FROM CTE7

假設這種特殊情況（並且我們沒有聲明任何顯式事務）物理表鎖定結構是否會發生變化，因為我們正在使用 CTE 查詢？

當SELECTCTE1 作為 的一部分完成時UNION，物理表是否仍處於鎖定狀態或鎖定仍處於釋放狀態？

根據我上一個問題的答案，我的印像是，在各種語句之一完成UNION時，表被釋放。這是不正確的嗎？SELECT``SELECT

我們沒有修改此事務的隔離級別。據我了解，這意味著我們正在使用READ_COMMITTED. 這個問題仍然適用於只有 2 個SELECT陳述。我試圖了解此功能的工作原理。我正在使用的特定查詢使用 7，這是我指定 7 的唯一原因。

不，鎖不會因為 CTE 而改變。您展示的是非遞歸 CTE。這些只是將子查詢引入 SQL 語句的另一種方式。查詢

with some_CTE as
(
 select < whatever > from < something >
)
select * from some_CTE;

在語義上等同於

select * from
(
 select < whatever > from < something >
) as some_alias;

由於查詢優化器可以自由地將您的語句重新排列為任何邏輯等價物，因此這兩個語句很可能最終會得到相同的執行計劃。（對於非常複雜的查詢，CTE 或主查詢有很多連接，或者 CTE 被多次引用，執行計劃可能不同，但這是優化的產物，而不是 CTE 工作方式的基本屬性。 )

本段與您關於鎖定的主要觀點相切，但值得了解。你說“當 CTE1 的 SELECT 完成時……”它不一定像那樣工作。執行可以以任何滿足我們查詢含義的方式進行，但不必遵循編寫它的*順序。*有關執行順序可能與書面順序不同的範例，請參見此處和此處。

至於你的主要問題“我的印像是……當 SELECT .. 完成時，表格被釋放。這不正確嗎？” 我將向您推薦此Microsoft 頁面：

READ COMMITTED 的行為取決於 READ_COMMITTED_SNAPSHOT 數據庫選項的設置：

如果 READ_COMMITTED_SNAPSHOT 設置為 OFF（預設值），則數據庫引擎使用共享鎖來防止其他事務在目前事務正在執行讀取操作時修改行。共享鎖還阻止語句讀取其他事務修改的行，直到其他事務完成。共享鎖類型決定何時釋放。在處理下一行之前釋放行鎖。讀取下一頁時釋放頁鎖，語句完成時釋放表鎖。

這是另一種解釋，可以更好地解釋它。這兩個來源都清楚地表明，鎖定的粒度（行、頁或表）會影響鎖定的釋放時間。影響這一點的因素很多，包括系統配置和鎖升級。

我還要注意您的範例使用了 UNION。這會從結果中刪除重複項。您可能會使用 UNION ALL 獲得不同的鎖定行為，但這會留下重複並可能返回更多行，具體取決於您的數據。

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