Originally published at SQL & Cloud Lab .

A deadlock happens when two sessions each hold a lock the other one needs. Neither can continue, so SQL Server picks one as the victim , rolls its transaction back and raises error 1205 . The other session carries on. Occasional deadlocks are normal in busy systems; frequent ones point to a fixable pattern in your code or indexes.

What a deadlock looks like

The classic example is two transactions updating the same two rows in opposite order:

-- Session 1 BEGIN TRAN ; UPDATE dbo . Account SET Balance -= 100 WHERE AccountId = 1 ; UPDATE dbo . Account SET Balance += 100 WHERE AccountId = 2 ; COMMIT ;

-- Session 2 (runs at the same time) BEGIN TRAN ; UPDATE dbo . Account SET Balance -= 50 WHERE AccountId = 2 ; UPDATE dbo . Account SET Balance += 50 WHERE AccountId = 1 ; COMMIT ;

Enter fullscreen mode

Exit fullscreen mode

Session 1 locks row 1 and waits for row 2. Session 2 locks row 2 and waits for row 1.…