3

MSSQL 2012. Stored procedure fires twice with the same parameters within 10ms of each other, and we get deadlocks.

Stored procedure basically looks like this (can't share actual code but here's the pertinent pseudocode):

BEGIN TRAN
UPDATE t
SET
    t.Column1 = NewValue1,
    t.Column2 = NewValue2,
    t.Column3 = NewValue3,
    etc for 20 columns...
FROM Table t
    INNER JOIN Results r
        ON r.ID = t.ResultID
WHERE 
    UserID = @userid
    AND Deleted = 0

UPDATE t
    SET Deleted = 1
FROM Table t
    LEFT JOIN Results r
        ON r.ID = t.ResultID
WHERE
    t.UserID = @userid
    AND t.Deleted = 0
    AND r.ID IS NULL
COMMIT TRAN

Deadlocks are occurring on Table, indexes involved are UserID (non-clustered covering index) and the Primary Key. When I run the procedure in my test environment, the execution plans show that both UPDATEs are using the covering index to get the rows (seek, no lookups), then having to update the PK and the covering index.

Possible solutions I've thought of are:

  1. Removing the explicit transaction control. Since the two updates don't really rely on each other, that seems viable. Would that resolve the deadlock?
  2. Removing the PK and making UserID the (non-unique) clustered index. This, however, may impact other things that use the PK elsewhere.
  3. Implementing some boolean logic in the second UPDATE to avoid running it unnecessarily. Doesn't actually fix the problem though, just makes it happen less often.

I'm about 2 months into my career as a dba, and deadlocks have been difficult to get my head around. Any advice would be greatly appreciated!

  • Have you considered combining the two UPDATE statements into one? – Andriy M Oct 7 '16 at 14:29
  • Are you updating one or both involved indexed columns, i.e. is the same index read and updated? – JimmyB Oct 7 '16 at 14:45
2

Each of your 2 update statements may cause deadlock when executing within different transactions, no matter explicit or implicit. They both change the same set of rows , and nothing guaranties in which order the rows are locked.
One option is just to catch error and re-run failed transactions. Another approach is to somehow "serialize" updates.

In my opinion, the easiest and fastest way is to modify procedure so it waits until the other instance of the same procedure completes updates. It can be implemented, for instance, by using sp_getapplock.

Another option is to ensure that rows are locked in the same order (e.g. -open cursor that reads all required rows in particular (unique) order, iterate through it, and for each row issue SELECT WITH ROWLOCK), and issue update after all rows are locked. Then in case of simultaneous execution the second transaction will wait for the first one. I think that for SQLServer a very little chance of deadlocks might exist because of potential lock escalation though.

2

Why not combine the 2 SQLs into 1:

BEGIN TRAN
    UPDATE t
    SET
        t.Column1 = case when r.ID is null and Deleted = 0 then NewValue1 else t.Column1 end,
        ...
        Deleted = case when r.ID is null then 1 else 0 end
    FROM Table t
        left outer join Results r ON r.ID = t.ResultID
    WHERE 
        UserID = @userid
COMMIT TRAN
  • Note: and Deleted = 0 ought to be moved out of the CASE statements and into the WHERE clause; like the UserID check, it's done for both original queries. – RDFozz Mar 9 '18 at 23:42
0

Deadlocks can occur because two processes are accessing the same tables, but each from the other side, while additional locking occurs. Looking at your code I wouldn't think the two updates are the root cause of the deadlocks, but more victims of other statements running in the background.

If you can stop all other activity on the database and run only the two update procedures simultaneously, do you still have deadlocks?

Deadlocking is explained here: Detecting and Ending Deadlocks

You can monitor deadlocking by following the information in the article: How to monitor deadlock using extended events in SQL Server 2008 and later

A quick win can be achieved by turning on two trace flags to capture the deadlocks in the errorlog file of SQL Server.

If you have the deadlock information you will be able to pinpoint the actual root cause.

On a side note: Are you actually having deadlocks where one statement is rolled back? Or are you having a blocking chain where multiple spids are waiting for one spid to finish? (You should be able to see deadlocks in the SQL Server errorlog at all times).

Deadlocks != Blocking Chains

  • I am actually getting deadlocks. I've been recording them via xml_deadlock_reports in XE, that's how I know they're happening. – Zaphodb2002 Oct 7 '16 at 14:08
  • And this might be a stupid question but how would I run the procedure twice simultaneously to try to reproduce? I can break out the statements and not commit one transaction, then run the other, that does just seem to cause blocking, no deadlock. – Zaphodb2002 Oct 7 '16 at 14:11
  • That's what I was trying to get at. If you get only blocking with two statements, then it must be another statement outside of your procedure that is causing the deadlock. When you examine the deadlock information, can you retrieve the actual statements being executed/rolled back? – John aka hot2use Oct 7 '16 at 14:20
0

Both of update statements, obviously are not looking a single business unit. First update statement is about updating table based on matching records from results table and second update statement is for non matching records.

So removing explicit transaction is looking obvious choice in my opinion.

There are ways to avoid deadlock but I don't feel to put effort there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.