兩個選擇和一個表上的一個更新之間的 SQL Server 死鎖

我們的應用程序時不時地遇到死鎖（大約每週一次）。

罪魁禍首似乎是帶有兩個選擇的查詢。其中一個是出於性能原因填充臨時表，另一個是一個相對複雜的選擇，具有許多連接以返回具有許多詳細資訊的所有約會列表。我看到的關於第二個選擇的唯一可能特別之處是它包含一個自聯接。雙選查詢始終是 SQL Server 死鎖事件報告的一部分。

另一個查詢是對同一個表的簡單 DML 查詢（插入或更新），儘管這並不總是相同的 DML 查詢。這兩個查詢都以標準READ COMMITTED隔離執行，而不是在顯式事務中執行。

這兩個查詢大致如下（我已將它們縮短以進行澄清）

DECLARE @futureAppointments TABLE(clientId int, StartDate date)
INSERT INTO @futureAppointments SELECT clientId, StartDate FROM Appointments where StartDate >= @startDate

SELECT *, (SELECT COUNT(*) FROM @futureAppointments fa WHERE fa.clientId = a.clientId AND fa.StartDate > a.StartDate)
FROM Appointments a
join b on a.fk_b = b.id
join c on a.fk_c = c.id
join Appointments d on c.somefield = d.anotherfield
WHERE a.StartDate >= @startDate AND a.StartDate <= @endDate

UPDATE Appointments SET someField = @value WHERE id = @id

範例 2：deadlock2.xml， 範例 3：deadlock3.xml，

在這種情況下，我將如何嘗試防止死鎖發生？另外，有誰知道為什麼第一個帶有兩個選擇的語句會像範例 3 中那樣在所選表的 PK 上獲得 U 鎖？我不認為這很重要，但這似乎很奇怪。

查看死鎖圖時：

更新查詢 - SPID 75

更新查詢鎖定*（spid 75）*非常簡單，在鍵/行值上請求X（排他）鎖定，而該行目前被選擇查詢鎖定。

更新查詢還在頁面上持有IX（意圖排他）鎖，這在對屬於該頁面的行進行排他鎖時是預期的。IX鎖與選擇查詢發出的 IS（意圖共享）鎖兼容（作為對行的S鎖的結果）。

請參閱：鎖兼容性矩陣

---

選擇查詢 - SPID 103

死鎖的不尋常部分是選擇查詢*（spid 103）*希望在行和具有行數據（可能還有其他行數據）的頁面上都有一個**S （共享）鎖。**特別是因為不可能從行升級到頁（行到表和頁到表）。先前持有鎖的事務也被排除。

解釋似乎在於對dbo.Appointments錶格的雙重訪問。鎖被佔用了兩次，其中一個表訪問需要頁鎖，而另一個已經獲得了行鎖。

更新在這些被獲取的共享鎖之間觸發。

按順序請求/獲取的鎖的範例

1. 作為選擇查詢的一部分，對 dbo.Appointments 表的第一次讀取訪問會獲取鍵/行上的S鎖
2. 更新查詢在包含行數據的頁面上獲取IX鎖
3. 更新查詢在行上請求X鎖
4. 作為選擇查詢的一部分，第二次訪問 dbo.Appointments 請求了同樣具有行數據的頁面上的S鎖

這一切意味著選擇查詢的這一部分是你的死鎖問題：

SELECT *, (SELECT COUNT(*) FROM @futureAppointments fa WHERE fa.clientId = a.clientId AND fa.StartDate > a.StartDate)
FROM Appointments a
join b on a.fk_b = b.id
join c on a.fk_c = c.id
join Appointments d on c.somefield = d.anotherfield
WHERE a.StartDate >= @startDate AND a.StartDate <= @endDate;

在這種情況下，我將如何嘗試防止死鎖發生？

減少

為了減少發生死鎖的可能性，您可以考慮通過重寫或添加索引來優化選擇查詢。

消除

要完全消除這兩個查詢之間出現死鎖的可能性，您可以使用臨時表將自聯接分成兩部分，或者使用WTIH(PAGLOCK)/對 Appointments 表進行共享頁或表鎖定WITH(TABLOCK)。請記住，這會影響並發性。

另外，有誰知道為什麼第一個帶有兩個選擇的語句會像範例 3 中那樣在所選表的 PK 上獲得 U 鎖？我不認為這很重要，但這似乎很奇怪。

所有者（選擇查詢）持有密鑰的共享鎖。

U （更新）鎖（來自更新查詢）與共享鎖兼容，請參閱：鎖兼容性矩陣。

更新查詢嘗試將 U 鎖轉換為排他鎖：

<waiter id="process24b7826bc28" mode="X" requestType="convert" />.

使用SentryOne Plan Explorer（免費工具）打開 XML 時，該資訊是正確的。

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