Sql-Server

我可以使用 rowlock 提示解決死鎖嗎？

  • August 31, 2011

我有一個大型刪除儲存過程，並且在刪除不會刪除任何內容的情況下重現了死鎖。

看起來遇到死鎖的儲存過程部分是這樣的（更改了表名）：

DELETE d
FROM Table1 d
inner join dbo.Table2 orc on orc.id = d.Table2Id
inner join dbo.Table3 orr on orr.id = orc.Table3Id
inner join Table4 oeh on oeh.id = orr.Table4Id
inner join @deleteEntities de on de.id = oeh.EntityId

在我看來，兩個刪除操作同時執行並且在嘗試從這個大表中刪除時相互死鎖。對於這些項目，我知道不會有記錄非常大的表 table1、table2、table3。

我想知道這是否可以通過更改為來解決：

DELETE d
FROM Table1 d WITH(rowlock)
inner join dbo.Table2 orc on orc.id = d.Table2Id
inner join dbo.Table3 orr on orr.id = orc.Table3Id
inner join Table4 oeh on oeh.id = orr.Table4Id
inner join @deleteEntities de on de.id = oeh.EntityId

我在想，由於 table1 是一個大表，sql-server 會鎖定整個頁面，這個提示會使其只鎖定行。請注意，我在 Table2Id、Table3Id、Table4Id 和 entityid 上為 fk 編制了索引。

我已啟用跟踪，如下所述：http: //blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

和：

DBCC TRACEON (1222, -1)

下面是日誌輸出，其中“2011-08-29 15:46:57.78 spid15s”切斷了每行的開頭。從我看到的兩個usp_EntityFullDelete 在同一個語句上死鎖 - 一個刪除行 746946 和一個刪除行 628302。我對這個跟踪輸出的分析是否正確？還有什麼可以幫助防止這種情況的嗎？

deadlock-list
deadlock victim=process3e9ada8
 process-list
  process id=processbaf048 taskpriority=0 logused=20022 waittime=3890 schedulerid=1 kpid=1304 status=suspended spid=59 sbid=0 ecid=1 priority=0 transcount=0 lastbatchstarted=2011-08-29T15:46:53.263 lastbatchcompleted=2011-08-29T15:46:53.263 clientapp=.Net SqlClient Data Provider hostname=RGDS hostpid=9108 isolationlevel=read committed (2) xactid=1135559188 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
   executionStack
    frame procname=Bugfixes.dbo.usp_EntityFullDelete line=178 stmtstart=11180 stmtend=11776 sqlhandle=0x030013007b725817b4bfe400499f00000100000000000000
DELETE d
FROM Table1 d
   inner join dbo.Table2 orc on orc.id = d.Table2Id
   inner join dbo.Table3 orr on orr.id = orc.Table3Id
   inner join Table4 oeh on oeh.id = orr.Table4Id
   inner join @deleteEntities de on de.id = oeh.EntityId     
    frame procname=adhoc line=1 sqlhandle=0x01001300ef337933809a04fd000000000000000000000000
exec dbo.usp_EntityFullDelete 746946,0     
   inputbuf
  process id=processbaf588 taskpriority=0 logused=20022 waittime=3906 schedulerid=1 kpid=6244 status=suspended spid=62 sbid=0 ecid=3 priority=0 transcount=0 lastbatchstarted=2011-08-29T15:46:45.637 lastbatchcompleted=2011-08-29T15:46:45.637 clientapp=.Net SqlClient Data Provider hostname=RGDS hostpid=9108 isolationlevel=read committed (2) xactid=1135558120 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
   executionStack
    frame procname=Bugfixes.dbo.usp_EntityFullDelete line=178 stmtstart=11180 stmtend=11776 sqlhandle=0x030013007b725817b4bfe400499f00000100000000000000
DELETE d
FROM Table1 d
   inner join dbo.Table2 orc on orc.id = d.Table2Id
   inner join dbo.Table3 orr on orr.id = orc.Table3Id
   inner join Table4 oeh on oeh.id = orr.Table4Id
   inner join @deleteEntities de on de.id = oeh.EntityId     
    frame procname=adhoc line=1 sqlhandle=0x01001300fc3e1016609402c4000000000000000000000000
exec dbo.usp_EntityFullDelete 628302,0     
   inputbuf
  process id=process3e9a868 taskpriority=0 logused=580 waitresource=PAGE: 19:1:1942004 waittime=3890 ownerId=1135558120 transactionname=user_transaction lasttranstarted=2011-08-29T15:46:53.053 XDES=0xf2512b30 lockMode=U schedulerid=3 kpid=8808 status=suspended spid=62 sbid=0 ecid=8 priority=0 transcount=0 lastbatchstarted=2011-08-29T15:46:45.637 lastbatchcompleted=2011-08-29T15:46:45.637 clientapp=.Net SqlClient Data Provider hostname=RGDS hostpid=9108 isolationlevel=read committed (2) xactid=1135558120 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
   executionStack
    frame procname=Bugfixes.dbo.usp_EntityFullDelete line=178 stmtstart=11180 stmtend=11776 sqlhandle=0x030013007b725817b4bfe400499f00000100000000000000
DELETE d
FROM Table1 d
   inner join dbo.Table2 orc on orc.id = d.Table2Id
   inner join dbo.Table3 orr on orr.id = orc.Table3Id
   inner join Table4 oeh on oeh.id = orr.Table4Id
   inner join @deleteEntities de on de.id = oeh.EntityId     
    frame procname=adhoc line=1 sqlhandle=0x01001300fc3e1016609402c4000000000000000000000000
exec dbo.usp_EntityFullDelete 628302,0     
   inputbuf
  process id=process3e9ada8 taskpriority=0 logused=0 waitresource=PAGE: 19:1:1928384 waittime=3765 ownerId=1135559188 transactionname=user_transaction lasttranstarted=2011-08-29T15:46:53.263 XDES=0xf2512d70 lockMode=U schedulerid=3 kpid=9196 status=suspended spid=59 sbid=0 ecid=6 priority=0 transcount=0 lastbatchstarted=2011-08-29T15:46:53.263 lastbatchcompleted=2011-08-29T15:46:53.263 clientapp=.Net SqlClient Data Provider hostname=RGDS hostpid=9108 isolationlevel=read committed (2) xactid=1135559188 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
   executionStack
    frame procname=Bugfixes.dbo.usp_EntityFullDelete line=178 stmtstart=11180 stmtend=11776 sqlhandle=0x030013007b725817b4bfe400499f00000100000000000000
DELETE d
FROM Table1 d
   inner join dbo.Table2 orc on orc.id = d.Table2Id
   inner join dbo.Table3 orr on orr.id = orc.Table3Id
   inner join Table4 oeh on oeh.id = orr.Table4Id
   inner join @deleteEntities de on de.id = oeh.EntityId     
    frame procname=adhoc line=1 sqlhandle=0x01001300ef337933809a04fd000000000000000000000000
exec dbo.usp_EntityFullDelete 746946,0     
   inputbuf
  process id=process3e9b198 taskpriority=0 logused=20006 waittime=3984 schedulerid=3 kpid=9212 status=suspended spid=59 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-08-29T15:46:53.263 lastbatchcompleted=2011-08-29T15:46:53.263 clientapp=.Net SqlClient Data Provider hostname=RGDS hostpid=9108 loginname=sa isolationlevel=read committed (2) xactid=1135559188 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
   executionStack
    frame procname=Bugfixes.dbo.usp_EntityFullDelete line=178 stmtstart=11180 stmtend=11776 sqlhandle=0x030013007b725817b4bfe400499f00000100000000000000
DELETE d
FROM Table1 d
   inner join dbo.Table2 orc on orc.id = d.Table2Id
   inner join dbo.Table3 orr on orr.id = orc.Table3Id
   inner join Table4 oeh on oeh.id = orr.Table4Id
   inner join @deleteEntities de on de.id = oeh.EntityId     
    frame procname=adhoc line=1 sqlhandle=0x01001300ef337933809a04fd000000000000000000000000
exec dbo.usp_EntityFullDelete 746946,0     
   inputbuf
exec dbo.usp_EntityFullDelete 746946,0    
  process id=process46b0da8 taskpriority=0 logused=20006 waittime=4000 schedulerid=4 kpid=6596 status=suspended spid=62 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-08-29T15:46:45.637 lastbatchcompleted=2011-08-29T15:46:45.637 clientapp=.Net SqlClient Data Provider hostname=RGDS hostpid=9108 loginname=sa isolationlevel=read committed (2) xactid=1135558120 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
   executionStack
    frame procname=Bugfixes.dbo.usp_EntityFullDelete line=178 stmtstart=11180 stmtend=11776 sqlhandle=0x030013007b725817b4bfe400499f00000100000000000000
DELETE d
FROM Table1 d
   inner join dbo.Table2 orc on orc.id = d.Table2Id
   inner join dbo.Table3 orr on orr.id = orc.Table3Id
   inner join Table4 oeh on oeh.id = orr.Table4Id
   inner join @deleteEntities de on de.id = oeh.EntityId     
    frame procname=adhoc line=1 sqlhandle=0x01001300fc3e1016609402c4000000000000000000000000
exec dbo.usp_EntityFullDelete 628302,0     
   inputbuf
exec dbo.usp_EntityFullDelete 628302,0    
  process id=process46b1048 taskpriority=0 logused=0 waitresource=PAGE: 19:1:1942003 waittime=3937 ownerId=1135559188 transactionname=user_transaction lasttranstarted=2011-08-29T15:46:53.263 XDES=0xd0224ab0 lockMode=U schedulerid=4 kpid=7892 status=suspended spid=59 sbid=0 ecid=8 priority=0 transcount=0 lastbatchstarted=2011-08-29T15:46:53.263 lastbatchcompleted=2011-08-29T15:46:53.263 clientapp=.Net SqlClient Data Provider hostname=RGDS hostpid=9108 isolationlevel=read committed (2) xactid=1135559188 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
   executionStack
    frame procname=Bugfixes.dbo.usp_EntityFullDelete line=178 stmtstart=11180 stmtend=11776 sqlhandle=0x030013007b725817b4bfe400499f00000100000000000000
DELETE d
FROM Table1 d
   inner join dbo.Table2 orc on orc.id = d.Table2Id
   inner join dbo.Table3 orr on orr.id = orc.Table3Id
   inner join Table4 oeh on oeh.id = orr.Table4Id
   inner join @deleteEntities de on de.id = oeh.EntityId     
    frame procname=adhoc line=1 sqlhandle=0x01001300ef337933809a04fd000000000000000000000000
exec dbo.usp_EntityFullDelete 746946,0     
   inputbuf
  process id=process46b16d8 taskpriority=0 logused=580 waitresource=PAGE: 19:1:441708 waittime=3937 ownerId=1135558120 transactionname=user_transaction lasttranstarted=2011-08-29T15:46:53.053 XDES=0xd0224870 lockMode=U schedulerid=4 kpid=6676 status=suspended spid=62 sbid=0 ecid=6 priority=0 transcount=0 lastbatchstarted=2011-08-29T15:46:45.637 lastbatchcompleted=2011-08-29T15:46:45.637 clientapp=.Net SqlClient Data Provider hostname=RGDS hostpid=9108 isolationlevel=read committed (2) xactid=1135558120 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
   executionStack
    frame procname=Bugfixes.dbo.usp_EntityFullDelete line=178 stmtstart=11180 stmtend=11776 sqlhandle=0x030013007b725817b4bfe400499f00000100000000000000
DELETE d
FROM Table1 d
   inner join dbo.Table2 orc on orc.id = d.Table2Id
   inner join dbo.Table3 orr on orr.id = orc.Table3Id
   inner join Table4 oeh on oeh.id = orr.Table4Id
   inner join @deleteEntities de on de.id = oeh.EntityId     
    frame procname=adhoc line=1 sqlhandle=0x01001300fc3e1016609402c4000000000000000000000000
exec dbo.usp_EntityFullDelete 628302,0     
   inputbuf
 resource-list
  pagelock fileid=1 pageid=1928384 dbid=19 objectname=Bugfixes.dbo.Table1 id=lockaef9db80 mode=U associatedObjectId=72057595211284480
   owner-list
    owner id=process46b0da8 mode=U
   waiter-list
    waiter id=process3e9ada8 mode=U requestType=wait
  exchangeEvent id=port80128a00 nodeId=22
   owner-list
    owner event=e_waitNone type=producer id=process3e9ada8
    owner event=e_waitNone type=producer id=process46b1048
   waiter-list
    waiter event=e_waitPortClose type=consumer id=processbaf048
  exchangeEvent id=port80128e20 nodeId=6
   owner-list
    owner event=e_waitNone type=producer id=processbaf048
   waiter-list
    waiter event=e_waitPortOpen type=consumer id=process3e9b198
  pagelock fileid=1 pageid=1942004 dbid=19 objectname=Bugfixes.dbo.Table1 id=lockf3d1f080 mode=U associatedObjectId=72057595211284480
   owner-list
    owner id=process3e9b198 mode=U
   waiter-list
    waiter id=process3e9a868 mode=U requestType=wait
  exchangeEvent id=port80128ed0 nodeId=22
   owner-list
    owner event=e_waitNone type=producer id=process46b16d8
    owner event=e_waitNone type=producer id=process3e9a868
   waiter-list
    waiter event=e_waitPortClose type=consumer id=processbaf588
  exchangeEvent id=port80128320 nodeId=6
   owner-list
    owner event=e_waitNone type=producer id=processbaf588
   waiter-list
    waiter event=e_waitPortOpen type=consumer id=process46b0da8
  pagelock fileid=1 pageid=1942003 dbid=19 objectname=Bugfixes.dbo.Table1 id=lockfbcc1680 mode=U associatedObjectId=72057595211284480
   owner-list
    owner id=process46b0da8 mode=U
   waiter-list
    waiter id=process46b1048 mode=U requestType=wait
  pagelock fileid=1 pageid=441708 dbid=19 objectname=Bugfixes.dbo.Table1 id=lockfc628980 mode=U associatedObjectId=72057595211284480
   owner-list
    owner id=process3e9b198 mode=U
   waiter-list
    waiter id=process46b16d8 mode=U requestType=wait

當我意識到跟踪顯示並行性時，刪除了我之前的答案。

有一個非常徹底的測試這個大警告，您可以通過限制 MAXDOP 並在 Table1 上添加一個 UPDLOCK 提示來緩解死鎖。我也會（根據@Aaron 的建議）嘗試 EXISTS。

DELETE 
   d
FROM 
   Table1 d WITH (UPDLOCK)
INNER JOIN
   #deleteEntities de
ON  de.id = oeh.EntityId 
WHERE EXISTS
   (
   SELECT
       NULL
   FROM
       dbo.Table2 orc 
   INNER JOIN
       dbo.Table3 orr 
   ON  orr.id = orc.Table3Id
   INNER JOIN 
       dbo.Table4 oeh            
   ON  oeh.id = orr.Table4Id
   WHERE
       oeh.id = de.Table2Id
   ) OPTION (MAXDOP 1, RECOMPILE)

這將是大錘方法。您可能可以通過適當的索引消除並行性，但除非我們看到執行計劃和/或統計資訊，否則無法就此提供建議。

死鎖跟踪中的@deleteEntities 有點“可疑”。您正在傳遞一個標識符，但那裡有這個臨時表？優化器可能會為此生成一個估計為 1 的執行計劃，因此如果它包含可變數量的行，我將切換到臨時表並強制重新編譯（如上所述）。

SQL Server 仍然可以選擇將行鎖升級為表鎖，即使指定了 WITH(ROWLOCK)。這可能取決於許多因素，包括：刪除的行數、事務獲取的總鎖數以及獲取的所有鎖的總記憶體壓力。

Sunil Agarwal 寫了一篇很棒的文章來描述這個過程，可以在這裡找到：http: //blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx

為了減輕這些 DELETE 語句的影響，您可以做的一件事是分塊執行刪除：

WHILE 1 = 1
BEGIN
   DELETE TOP(5000)
   FROM <table>
   WHERE <condition>

   IF @@ROWCOUNT = 0
   BEGIN
       BREAK;
   END
END

不幸的是，您最初問題的答案是一個很大的“它取決於”。

編輯（添加查詢以查看鎖定資訊）：

SELECT
   DB_NAME([tl].[resource_database_id]) AS [database_name],
   SCHEMA_NAME([o].[schema_id]) + '.' + [o].[name] AS [object_name],
   [tl].[request_type],
   [tl].[request_mode],
   [tl].[request_status]
FROM sys.dm_exec_sessions AS [es]

   INNER JOIN sys.dm_tran_locks AS [tl]
       ON [es].[session_id] = [tl].[request_session_id]    

       INNER JOIN sys.objects AS [o]
           ON [tl].[resource_associated_entity_id] = [o].[object_id]

WHERE [es].[is_user_process] = 1;

我希望這個“幾乎答案”有所幫助，

馬特

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

相關問答

Sql-Server

外鍵會導致死鎖並阻礙 READ COMMITTED SNAPSHOT 嗎？

  • May 8, 2020
檢視問答
Sql-Server

管理和刪除大表中的數據

  • May 7, 2020
檢視問答
Sql-Server

刪除查詢會阻塞其他並發事務

  • July 4, 2019
檢視問答
Sql-Server

使用 OR 運算符刪除時出現死鎖

  • October 14, 2018
檢視問答
Sql-Server

可以從 SQL Server 2008 中的環形緩衝區中提取的死鎖 xml 創建圖表嗎？

  • July 4, 2018
檢視問答
Sql-Server

如果並發讀取/更新不影響相同的行，2 個表可以相互鎖定嗎？頁面鎖定關閉

  • July 26, 2017
檢視問答