0

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

4
  • The "Deadlock graph data extracted" appears to have little or no relationship to the "deadlock graph" you posted? Feb 26 '14 at 15:36
  • What does GetIsAccountLicenseConnectionValid do? Can you post that definition? It would also be useful to see the whole code block, to give information about sequencing as well as transactions and isolation semantics. Feb 26 '14 at 15:42
  • Had posted wrong image, it wasn't related with the extracted graph data. Feb 26 '14 at 15:53
  • 1
    And can you show the stored procedure that calls this other stored procedure? Feb 26 '14 at 16:08
2

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.

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.