Sql-server – SQL Server 2008R2 – Stored Procedure with 2 updates causing deadlocks

deadlocksql serversql-server-2008-r2

I have a web api web service that calls an authentication stored procedure.
This stored procedure calls a different stored procedure that does 1 update:

UPDATE  AccountLicensingDeviceConnectionsPerPeriod 
SET     DateLastConnected = GETDATE(),
        ConnectionPermitted = @IsValid
WHERE   AccountId = @AccountId
        AND UserId = @UserId
        AND Device = @Device

And then does a different update:

UPDATE  Users 
SET     DateLastLoggedIn = GETDATE(),
        LastLoggedInDevice = @Device,
        InvalidLoginCount = 0
WHERE   Id = @UserId

These updates are always in the same order or at least they should be, is there any way to avoid 2 concurrent processes to deadlock?

The deadlock graph:
deadlock graph
Deadlock graph data extracted

GetIsAccountLicenseConnectionValid SP

AuthenticateUser SP – h**ps://dl.dropboxusercontent.com/u/204180/AuthenticateUser.sql

Best Answer

Using SentryOne Plan Explorer I was able to make a little more sense of the deadlock graph (click to embiggen):

enter image description here

The victim also called the stored procedure GetIsAccountLicenseConnectionValid, and I suspect this procedure has some touch against one or both tables affected by the updates in the question. It is certainly feasible that locks taken by that stored procedure could interfere with locks affecting another transaction that hasn't called that stored procedure yet.

Related Question