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.
begin transaction update Customers set LastName = 'Kharazmi' waitfor delay '00:00:5'; -- waits for 5 second update Orders set OrderId=13 commit transaction
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.