Deadlock - Lock of a column whereas there is no data

Question

I have a deadlock when I execute this stored procedure :

-- Delete transactions
delete from ADVICESEQUENCETRANSACTION
where ADVICESEQUENCETRANSACTION.id in (
  select TR.id from ADVICESEQUENCETRANSACTION TR
  inner join ACCOUNTDESCRIPTIONITEM IT on TR.ACCOUNTDESCRIPTIONITEMID = IT.id
  inner join ACCOUNTDESCRIPTION ACC on IT.ACCOUNTDESCRIPTIONID = ACC.id
  inner join RECOMMENDATIONDESCRIPTION RD on ACC.RECOMMENDATIONDESCRIPTIONID = RD.id
  inner join RECOMMENDATION REC on REC.id = RD.RECOMMENDATIONID
  inner join ADVICESEQUENCE ADV on ADV.id = REC.ADVICESEQUENCEID
  where adv.Id = @AdviceSequenceId AND (@RecommendationState is NULL OR @RecommendationState=REC.[State])
    );

Here is the schema of the table :

Here is the deadlock graph :

you can see the detail of the deadlock graph here

So, when I retrieve the associatedobjid of the ressource node, I identify that it's the primary key and an index of the table AdviceSequenceTransaction :

SELECT OBJECT_SCHEMA_NAME([object_id]), * ,
OBJECT_NAME([object_id]) 
FROM sys.partitions 
WHERE partition_id = 72057595553120256 OR partition_id = 72057595553316864;

SELECT name FROM sys.indexes WHERE object_id = 31339176 and (index_id = 1 or index_id = 4)

PK_AdviceSequenceTransaction IX_ADVICESEQUENCEID_ADVICE

As there is a relation on the table AdviceSequenceTransaction on the key ParentTransactionId and the key Primary key, I have created an index on the column ParentTransactionId.

And I have no more Deadlock. But the problem is I don't know exactly why there is no more deadlock :-/

Moreover, on the set of data to test it, there is no data in ParentTransactionId. All are NULL.

So, Even is there no data (null) in the ParentTransactionId, is there an access to the Primary key by SQL Server ???

An other thing is that I want to remove a join in the delete statement :

delete from ADVICESEQUENCETRANSACTION
where ADVICESEQUENCETRANSACTION.id in (
  select TR.id from ADVICESEQUENCETRANSACTION TR
  inner join ACCOUNTDESCRIPTIONITEM IT on TR.ACCOUNTDESCRIPTIONITEMID = IT.id
  inner join ACCOUNTDESCRIPTION ACC on IT.ACCOUNTDESCRIPTIONID = ACC.id
  inner join RECOMMENDATIONDESCRIPTION RD on ACC.RECOMMENDATIONDESCRIPTIONID = RD.id
  inner join RECOMMENDATION REC on REC.id = RD.RECOMMENDATIONID
  inner join ADVICESEQUENCE ADV on ADV.id = REC.ADVICESEQUENCEID
  where adv.Id = @AdviceSequenceId AND (@RecommendationState is NULL OR @RecommendationState=REC.[State])
    );

into :

delete from ADVICESEQUENCETRANSACTION
where ADVICESEQUENCETRANSACTION.id in (
  select TR.id from ADVICESEQUENCETRANSACTION TR
  inner join ACCOUNTDESCRIPTIONITEM IT on TR.ACCOUNTDESCRIPTIONITEMID = IT.id
  inner join ACCOUNTDESCRIPTION ACC on IT.ACCOUNTDESCRIPTIONID = ACC.id
  inner join RECOMMENDATIONDESCRIPTION RD on ACC.RECOMMENDATIONDESCRIPTIONID = RD.id
  inner join RECOMMENDATION REC on REC.id = RD.RECOMMENDATIONID
  where TR.AdviceSequenceId = @AdviceSequenceId AND (@RecommendationState is NULL OR @RecommendationState=REC.[State])
    );

I removed the last join. But if I do this, I have again the deadlock ! And here again, I don't know why...

Thank you for your enlightment :)

Answer 1

Using a complex, compound join in your WHERE clause can often cause problems. SQL Server processes the clauses using the following Logical Processing Order (view here):

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Using views, or derived tables or views greatly reduces the number of iterative (TABLE) scans required to obtain the desired result because the emphasis in your query is better aligned to use the logical execution order. The FROM clause for each of the derived tables (or views) is executed first, limiting the result set passed to the ON cause, then the JOIN clause and so on, because you're passing your parameters to an "inside FROM" rather than the "outermost WHERE".

So your code could look something like this:

delete from (SELECT   ADVICESEQUENCETRANSACTION
             FROM     (SELECT tr.id 
                      FROM ADVICESEQUENCETRANSACTION WITH NOLOCK
                      WHERE AdviceSequenceId = @AdviceSequenceId
                      )TR
            INNER JOIN (SELECT [NEXT COLUMN]
                        FROM [NEXT TABLE] WITH NOLOCK
                        WHERE COLUMN = [PARAM]
                        )B
            ON        TR.COL = B.COL            
            )ALIAS
            WHERE [COLUMN] = COL.PARAM
);

and so on... (I know the code isn't cut and paste usable, but it should serve to convey the general idea)

In this way, you're passing your parameters to "inner queries" first, pre-loading your limited result set (particularly if you should use views), and then working outward. Using Locking Hints everywhere appropriate will also help to prevent some of the problems you might otherwise encounter. This technique can also help make the execution plan more effective in helping you diagnose where your blocks are coming from, should you still have any.