When a deadlock occurs in SQL Server, which transaction is aborted?
Which transactions will be canceled if a deadlock occurs in SQL Server? I mean, what is SQL Server's plan for determining which transactions should be killed?
Consider the two transactions listed below.
Transaction A:
begin transaction
update Customers set LastName = 'Kharazmi'
waitfor delay '00:00:5'; -- waits for 5 second
update Orders set OrderId=13
commit transaction
Transaction B:
begin transaction
update Orders set OrderId=14
waitfor delay '00:00:5'; -- waits for 5 second
update Customers set LastName = 'EbneSina'
commit transaction
When both transactions are run concurrently, transaction A locks and updates the Customers table, while transaction B locks and updates the Orders table. After a 5-second wait, transaction A looks for the lock on the Orders table that transaction B already has, and process B looks for the lock on the Customers table that transaction A already has. As a result, both transactions are stalled, resulting in a stalemate.
My issue is, if a stalemate develops, which of the two transactions indicated by this article will be aborted? I run transaction A first, then transaction B and the SQL server aborts transaction A. I then perform transaction B first, and the outcome is the same, and transaction A is alerted again. It perplexed me! Thank you for any assistance.
Please sign in to leave a comment.