can I resolve a deadlock with the rowlock hint?

Question

I have a large delete stored proc and I've reproduced a deadlock in a situation where the deletes would not have deleted anything.

It looks like the part of the stored proc that hit a deadlock was like this (changed table names):

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

It looks to me like two deletes were running at the same time and deadlock each other when trying to delete from this large table. For these items I know that there would have been no records very large tables table1, table2, table3.

I am wondering if this might be fixed by changing to:

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

I am thinking that since table1 is a large table sql-server locks whole pages and this hint will make it only lock rows. Note that I have indexed fk's on Table2Id, Table3Id, Table4Id and entityid.

I have enabled tracing as described here: Link

with:

DBCC TRACEON (1222, -1)

Below is the log output with "2011-08-29 15:46:57.78 spid15s" cut off the start of each line. From what I see two usp_EntityFullDelete's are deadlocking on the same statement - one deleting row 746946 and one deleting row 628302. Am I correct in my analysis of this trace output? Is there anything else that might help prevent this?

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

Answer 1

Deleted my previous answer when I realised the trace shows parallelism.

With a big warning to test this very very thoroughly, you might alleviate the deadlocks by restricting MAXDOP and adding a UPDLOCK hint on Table1. I would also (as per @Aaron's suggestion) try 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)

This would be the sledgehammer approach. It's likely you could eliminate the parallelism with appropriate indexing but can't advise on that unless we see an execution plan and or statistics.

@deleteEntities in the deadlock trace is a little "suspicious". You're passing in a single identifer but have this temporary table in there?. Optimiser is likely to be producing an execution plan that estimates 1 for this, so if it contains a variable number of rows I'd switch to temporary table and force recompile (as above).

Answer 2

SQL Server can still choose to escalate the row lock to a table lock, even if WITH(ROWLOCK) is specified. This can depend on a number of things including: number of rows deleted, number of total locks acquired by transaction, and total memory pressure of all locks acquired.

Sunil Agarwal has written a great article describing this process, which can be found here: Link

One thing you can do to alleviate the impact of these DELETE statements is to perform the deletes in chunks:

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

    IF @@ROWCOUNT = 0
    BEGIN
        BREAK;
    END
END

Unfortunately, the answer to your original question is a big fat "It Depends".

EDIT (Adding Query to look at lock information):

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;

I hope this "almost answer" helps,

Matt