Sql-Server

只插入死鎖

  • March 5, 2019

我們有一個將數據插入表的應用程序。不幸的是，我們遇到了死鎖，而死鎖僅來自插入。我們看到插入在導致問題的非聚集索引上以不同的順序獲取鍵鎖。

為什麼插入行為會這樣？我們應該怎麼做才能緩解死鎖？任何幫助或見解表示讚賞。

在下面的範例中，只涉及兩個插入，但我們有多達 4 個不同的插入涉及死鎖。

這是死鎖圖：

   <deadlock>
   <victim-list>
       <victimProcess id="process3ab355868" />
   </victim-list>
   <process-list>
       <process id="process3ab355868" taskpriority="0" logused="1184" waitresource="KEY: 5:72057594043629568 (6234ed5bf036)" waittime="7493" ownerId="92332106" transactionname="implicit_transaction" lasttranstarted="2014-10-13T12:37:43.060" XDES="0x123699668" lockMode="X" schedulerid="3" kpid="3540" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-13T12:37:44.333" lastbatchcompleted="2014-10-13T12:37:44.333" lastattention="1900-01-01T00:00:00.333" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="92332106" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
           <executionStack>
               <frame procname="adhoc" line="1" stmtstart="278" stmtend="818" sqlhandle="0x0200000053a65d302154b91e9fee55234669030a42479c050000000000000000000000000000000000000000">
                   INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)
               </frame>
               <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                   unknown
               </frame>
           </executionStack>
           <inputbuf>
               (@P0 datetime2,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime2,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 decimal(38,1),@P7 int,@P8 int)INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS
           </inputbuf>
       </process>
       <process id="process14b38c928" taskpriority="0" logused="2564" waitresource="KEY: 5:72057594043629568 (275232b7b238)" waittime="7491" ownerId="92325909" transactionname="implicit_transaction" lasttranstarted="2014-10-13T12:37:39.567" XDES="0x16b38b988" lockMode="X" schedulerid="3" kpid="3668" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-13T12:37:44.337" lastbatchcompleted="2014-10-13T12:37:44.337" lastattention="1900-01-01T00:00:00.337" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="92325909" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
           <executionStack>
               <frame procname="adhoc" line="1" stmtstart="278" stmtend="818" sqlhandle="0x0200000053a65d302154b91e9fee55234669030a42479c050000000000000000000000000000000000000000">
                   INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)
               </frame>
               <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                   unknown
               </frame>
           </executionStack>
           <inputbuf>
               (@P0 datetime2,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime2,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 decimal(38,1),@P7 int,@P8 int)INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS
           </inputbuf>
       </process>
   </process-list>
   <resource-list>
       <keylock hobtid="72057594043629568" dbid="5" objectname="table1" indexname="unique_index" id="lock17bc3a480" mode="X" associatedObjectId="72057594043629568">
           <owner-list>
               <owner id="process14b38c928" mode="X" />
           </owner-list>
           <waiter-list>
               <waiter id="process3ab355868" mode="X" requestType="wait" />
           </waiter-list>
       </keylock>
       <keylock hobtid="72057594043629568" dbid="5" objectname="table1" indexname="unique_index" id="lock10735ce00" mode="X" associatedObjectId="72057594043629568">
           <owner-list>
               <owner id="process3ab355868" mode="X" />
           </owner-list>
           <waiter-list>
               <waiter id="process14b38c928" mode="X" requestType="wait" />
           </waiter-list>
       </keylock>
   </resource-list>
</deadlock>

這是 DDL 表：

CREATE TABLE [table1](
   [col0] [int] IDENTITY(1,1) NOT NULL,
   [col1] [int] NOT NULL,
   [col2] [int] NOT NULL,
   [col3] [decimal](15, 4) NULL,
   [col4] [datetime2](7) NOT NULL,
   [col5] [varchar](8) NOT NULL,
   [col6] [varchar](30) NOT NULL,
   [col7] [datetime2](7) NOT NULL,
   [col8] [varchar](8) NOT NULL,
   [col9] [varchar](30) NOT NULL,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
(
   [col0] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [unique_index] UNIQUE NONCLUSTERED 
(
   [col2] ASC,
   [col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE table1 ADD  DEFAULT (sysdatetime()) FOR [col4]
GO

ALTER TABLE table1 ADD  DEFAULT (sysdatetime()) FOR [col7]
GO

我在這裡回答我自己的問題，因為我們終於找到了問題所在。

*簡短版本：*我們在非聚集索引中添加了第三列。死鎖消失了。

長版：

首先，查看James Rowland-Jones 的有關鎖散列衝突的 dynamite 部落格文章（我的解釋與他的質量相去甚遠）。

來自部落格文章：

當 SQL Server 需要鎖定一行時，它會根據表的鍵值創建一個雜湊值。鎖管理器使用的正是這個散列值，這意味著在檢查行是否已被鎖定時，它只有一個值可供查看。

當生成重複的雜湊值時，就會發生鎖雜湊衝突。

在對許多死鎖圖進行更深入的分析後，我們注意到很多 WAITRESOURCE Key 雜湊值（括號之間的值）是相同的。我開始製作一個簡短的清單來跟踪：

waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (8328314847df)
waitresource="KEY: 5:72057594043629568 (bb0d06c12baa)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (bb0d06c12baa)
waitresource="KEY: 5:72057594043629568 (8328314847df)
waitresource="KEY: 5:72057594043629568 (bb0d06c12baa)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (5b39284eef16)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (8328314847df)
waitresource="KEY: 5:72057594043629568 (5b39284eef16)

果然，我們從不同的死鎖圖中得到了很多重複的雜湊值。我決定查看 unique_index 索引（發生死鎖的位置）的兩列（col2 和 col1）中的數據。所有表格 DDL 都在問題的上方。

對於 col1 列中的單個值，col2 列的值始終為 1-6。所以這開始有意義了。SQL 可用於生成雜湊值的數據種類有限 - 這解釋了為什麼我們會得到重複的雜湊值。

JRJ在部落格中提到的修復之一是在索引中添加一個額外的列。這為數據增加了一些多樣性，並為散列算法提供了更多選擇。幸運的是，我們能夠向索引添加一個 create_timestamp 列，並保持與兩列相同的唯一性。繁榮！將第三列添加到索引後，死鎖就消失了。

*旁注：*部落格上的評論之一建議禁用索引上的行鎖定。我們首先嘗試了這個。它確實擺脫了死鎖，但導致更多的鎖定並將整體吞吐量降低了大約 40-50%，所以我們不喜歡我們的系統使用這個選項。但是，在工作負載較輕的數據庫上，這可能會正常工作。

希望這一切都有意義。

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

相關問答

Sql-Server

如何完成這種插入或更新策略？

  • January 22, 2022
檢視問答
Sql-Server

如何在 SQL Server 中鎖定表以防止其他事務 INSERT 並避免死鎖？

  • August 4, 2021
檢視問答
Sql-Server

啟用擴展事件的成本是多少？

  • June 2, 2021
檢視問答
Sql-Server

我可以創建一個使用者定義的表類型並在同一個事務中使用它嗎？

  • November 25, 2020
檢視問答
Sql-Server

如何將 SQL Server 死鎖報告中的鍵轉換為值？

  • August 27, 2020
檢視問答
Sql-Server

從單行插入兩行

  • May 8, 2020
檢視問答