BEGIN TRANSACTION 對正在讀取的特定表造成什麼類型的鎖？

在以下範例中，從包裝 SELECT INTO 查詢的顯式事務塊中生成了哪些類型的鎖？

BEGIN TRANSACTION T1

SELECT Field1, Field2, Field3
INTO TableB
FROM TableA
WHERE Field3 > Value1

COMMIT TRANSACTION T1

筆記：

1. TableA 有大約 1000 億行，但是過濾器導致查詢只返回大約 500 萬行。

2）Field3是TableA上的聚集索引，生成的查詢計劃是做聚集索引查找。

3）我的場景中的隔離級別是預設的（READ COMMITTED）。

如果查詢沒有包含在顯式事務中，那麼像這樣的顯式事務塊是否可能會導致對錶的更多鎖定（或鎖定升級的機會更高）？

500萬行很多，理論上在任何情況下都會導致TableA的共享鎖升級到表級鎖（顯式或隱式事務）

但是您可以通過實驗發現這一點，您只需要設置擴展事件會話並監視“lock_escalation”事件

然後在顯式和隱式事務中執行查詢並查看 XE 會話日誌以查看 lock_escalation 事件

在這個具體的例子中，因為它包含一個SELECT...INTO語句，我認為可以公平地說是，鎖定行為是有區別的。

源表（和元數據表）

該SELECT...INTO語句不是“原子語句”，它分兩部分執行：創建新表，然後執行查詢的SELECTandINSERT部分。

至少，具有顯式事務的版本將持有與創建目標表相關的所有元數據鎖，以及目標表本身的表級鎖，直到事務結束。

EXEC master.dbo.sp_WhoIsActive @get_locks = 1;以下是使用顯式事務執行此查詢版本後的簡短摘錄：

   <Object name="sysrowsets" schema_name="sys">
     <Locks>
       <Lock resource_type="KEY" index_name="clust" request_mode="X" request_status="GRANT" request_count="2" />
       <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
     </Locks>
   </Object>
   <Object name="sysrscols" schema_name="sys">
     <Locks>
       <Lock resource_type="KEY" index_name="clst" request_mode="X" request_status="GRANT" request_count="6" />
       <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
     </Locks>
   </Object>
   <Object name="sysschobjs" schema_name="sys">
     <Locks>
       <Lock resource_type="KEY" index_name="clst" request_mode="X" request_status="GRANT" request_count="2" />
       <Lock resource_type="KEY" index_name="nc1" request_mode="X" request_status="GRANT" request_count="1" />
       <Lock resource_type="KEY" index_name="nc2" request_mode="X" request_status="GRANT" request_count="1" />
       <Lock resource_type="KEY" index_name="nc3" request_mode="X" request_status="GRANT" request_count="2" />
       <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
     </Locks>
   </Object>
   <Object name="TableB" schema_name="dbo">
     <Locks>
       <Lock resource_type="ALLOCATION_UNIT.BULK_OPERATION_PAGE" request_mode="S" request_status="GRANT" request_count="1" />
       <Lock resource_type="HOBT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
       <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
       <Lock resource_type="PAGE" page_type="*" request_mode="X" request_status="GRANT" request_count="55" />
     </Locks>
   </Object>

這可能會導致與元數據相關的阻塞（如果其他會話正在創建對象），當然只有創建會話才能訪問目標表。

---

目的地表

這部分更有可能與您的問題直接相關。

除了這些差異之外，我還看到證據表明，添加顯式事務可能會導致其他鎖定行為在使用時發生變化SELECT...INTO——特別是當源“表”是流 UDF 時，但在其他場景中也可能如此。

我注意到的行為的要點是，SELECT...INTO顯式事務導致查詢內並行死鎖的發生率更高。我對此沒有解釋，但是當存在顯式事務時，鎖定（在源和目標中）很可能是不同的。OPTION (MAXDOP 1)如果這些死鎖成為問題，我真正能提供的唯一建議是添加一個提示以完全避免並行性。

我在我的部落格上談到了這個奇怪的東西，並且還向微軟報告了一個關於它的錯誤。

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