Post reply

What happens to the deadlock victim?

  • August 24, 2016 at 3:37 am

    #316516

    Hi Guys,

    When SQL chooses a deadlock victim what happens to the stored procedure, does it roll back everything done in that stored procedure or does it just not complete whatever is left in that stored procedure?

  • August 24, 2016 at 3:43 am

    #1897126

    It gets rolled back.

  • August 24, 2016 at 4:32 am

    #1897134

    Rolled back to the beginning of the transaction. Hence if you have a procedure that does multiple data modifications without an explicit transaction, what had completed and committed is permanent and the changes still uncommitted are rolled back.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 24, 2016 at 4:54 am

    #1897137

    So that means that it will do half of the work, commit it, and then rollback the second half? In my scenario there are no explicit transactions.

  • August 24, 2016 at 5:23 am

    #1897140

    If there's no explicit transaction, then any changes that have been made and commited are persistent, any uncommitted are rolled back when the deadlock victim is killed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply