Sql-Server

使用 SELECT… WITH XLOCK 的充分理由？

  • January 22, 2016

我面臨著一些重複發生的死鎖，其中一個是 Keylock 並包含一個帶有 XLOCK 提示的 SELECT 查詢，該查詢成為死鎖的受害者。另一條語句是插入到作為第一個查詢的視圖的一部分的表中。

看法：

create view dbo.viewE
as
   select * from dbo.E  
   where myValue > 13000

選擇查詢：

select * from dbo.viewE with (XLOCK) where A > GETUTCDATE()

插入語句：

INSERT INTO [dbo].[E] (myValue,A) VALUES (10,GetDate())

基礎表 dbo.E 在大約 20 列中保存了大約 300 萬行，其中一些是 ntext。

取出查詢並使用兩個事務手動模擬它，該行為是可重現的。如果從選擇中刪除 XLOCK，則行為會發生變化。

死鎖圖：

<deadlock-list>
<deadlock victim="process222222221">
 <process-list>
  <process id="process222222221" taskpriority="0" logused="0" waitresource="KEY: 5:72057604035644444 (ccdf51accc0c)" waittime="2522" ownerId="27202256401" transactionname="SELECT" lasttranstarted="2015-09-14T16:32:36.160" XDES="0x2f1ec5ca0" lockMode="RangeX-X" schedulerid="15" kpid="12936" status="suspended" spid="359" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-09-14T16:32:36.160" lastbatchcompleted="2015-09-14T16:32:36.160" clientapp="x" hostname="x" hostpid="14536" loginname="x" isolationlevel="serializable (4)" xactid="27202256401" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="48" sqlhandle="0x02000000611e4523142b2318c47c87313a9b2ba587ff3130">
       SELECT * FROM viewE WITH (XLOCK) WHERE A < GetUtcDate()      </frame>
    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
   </executionStack>
   <inputbuf>
(@UICulture nvarchar(5))SELECT * FROM viewE WITH (XLOCK) WHERE A < GetUtcDate()    </inputbuf>
  </process>
  <process id="process6022222" taskpriority="0" logused="161152" waitresource="KEY: 5:72057604035644444 (cd874c2ba438)" waittime="1370" ownerId="27202248438" transactionguid="0x8de5ccd6eeef67469c6234af59e44ca5" transactionname="DTCXact" lasttranstarted="2015-09-14T16:32:34.767" XDES="0x4aa0bf950" lockMode="RangeI-N" schedulerid="14" kpid="6636" status="suspended" spid="329" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-09-14T16:32:37.300" lastbatchcompleted="2015-09-14T16:32:37.300" clientapp="x" hostname="x" hostpid="14536" loginname="x" isolationlevel="read uncommitted (1)" xactid="27202248438" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="936" sqlhandle="0x020000004853462f09790a4ddedc0d574c2afa539aef1c0e">
    INSERT INTO [E] ([a], [b], [c],...) VALUES (@aDate, @bDate, @c, ...)
    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
   </executionStack>
   <inputbuf>INSERT INTO [E] ([a], [b], [c],...) VALUES (@aDate, @bDate, @c, ...)
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057604035644444" dbid="5" objectname="db.dbo.E" indexname="IX_index1" id="lock258b6dc80" mode="X" associatedObjectId="72057604035644444">
   <owner-list>
    <owner id="process6022222" mode="X"/>
   </owner-list>
   <waiter-list>
    <waiter id="process222222221" mode="RangeX-X" requestType="wait"/>
   </waiter-list>
  </keylock>
  <keylock hobtid="72057604035644444" dbid="5" objectname="db.dbo.E" indexname="IX_index1" id="lock7b145c400" mode="RangeX-X" associatedObjectId="72057604035644444">
   <owner-list>
    <owner id="process222222221" mode="RangeX-X"/>
   </owner-list>
   <waiter-list>
    <waiter id="process6022222" mode="RangeI-N" requestType="wait"/>
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>
</deadlock-list>

據我了解，我正在查看一個 KEYLOCK 死鎖，該死鎖基本上是由未發現的索引查詢引起的，該查詢使用非聚集索引和聚集索引來收集所需的值，對吧？

我的問題：

  1. 由於涉及到所需的 NTEXT 列，我無法創建覆蓋索引。大幅減少行數會有所幫助嗎？
  2. 我只是不知道 SELECT 是用 XLOCK 執行的，有什麼好的理由嗎？如果沒有 XLOCK，死鎖也會發生嗎？

據我了解，我正在查看一個 KEYLOCK 死鎖，該死鎖基本上是由未發現的索引查詢引起的，該查詢使用非聚集索引和聚集索引來收集所需的值，對吧？

本質上，是的。讀取操作（選擇）首先訪問非聚集索引，然後是聚集索引（查找）。寫操作（插入）首先訪問聚集索引，然後是非聚集索引。以不同的順序訪問相同的資源持有不兼容的鎖可能會導致死鎖。

大幅減少行數會有所幫助嗎？

可能，因為鎖定的資源更少，操作往往會更快完成。如果它確實有幫助，它可能會減少死鎖，但很可能不會消除它們（但請繼續閱讀）。

我只是不知道 SELECT 是用 XLOCK 執行的，有什麼好的理由嗎？

並不真地。像這樣的鎖定提示通常是由沒有完全理解隔離、鎖定和死鎖如何工作的人引入的，他們不顧一切地試圖減少或消除問題。

如果沒有 XLOCK，死鎖也會發生嗎？

不，如果選擇實際上在讀取未送出隔離下執行，因為不兼容的鎖不會以不同的順序被獲取（和持有）。

是的，如果使用了鎖定隔離級別，並且以不一致的順序獲取和持有不兼容的鎖，例如在非集群上共享 (S)，然後在讀取時在集群上使用 S。在這種情況下發生死鎖的可能性取決於佔用了多少鎖，以及持有多長時間。

建議

真正突出的事情（審查中）是選擇事務在可序列化隔離下執行。這可能是由您的框架設置的，或者是由於使用了 DTC（分佈式事務協調器） - 請參見死鎖圖中的 transactionname=“DTCXact”。您應該調查造成這種情況的原因，並在可能的情況下進行更改。

XLOCK如果沒有升級到可序列化，假設提示被刪除，這種死鎖很可能不會發生。也就是說，您將在讀取未送出隔離下閱讀，這幾乎沒有一致性保證。

如果您的應用程序和 SQL Server 程式碼可以容忍讀取行的版本，則更改為讀取已**送出的快照隔離(RCSI) 或快照隔離(SI)也將避免死鎖（已刪除！），同時呈現一致的指向- 送出數據的時間視圖。當然，這也假設您可以避免可序列化的隔離。XLOCK

最終，XLOCK提示會適得其反，但您確實需要研究使用可序列化隔離級別的原因。這trancount = 2也很有趣——也許你無意中在這裡嵌套了事務。還有什麼要檢查的。

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

相關問答

Sql-Server

基於未索引列的 UPDATE WHERE 中的表掃描如何工作？

  • May 15, 2015
檢視問答
Sql-Server

插入/更新死鎖

  • February 27, 2012
檢視問答
Sql-Server

如何避免在具有 SERIALIZABLE 隔離級別、INSERT、UPDATE 和 DELETE 的儲存過程中出現死鎖

  • March 16, 2022
檢視問答
Sql-Server

普通 IX 鎖死鎖

  • March 16, 2022
檢視問答
Sql-Server

為什麼upsert會導致死鎖？

  • February 17, 2022
檢視問答
Sql-Server

我在使用 Database Tuning Advisor 時做錯了什麼？

  • February 8, 2022
檢視問答