can I resolve a deadlock with the rowlock hint?

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 isola

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).

StackExchange Database Administrators Q#5189, answer score: 4