Why does a SQL Server deadlock victim error (1205) end all its transactions?

Question

Consider the following two tables:

create table testDeadlockTable1 (
  c1   int not null,
  c2   int not null
)
create table testDeadlockTable2 (
  c1   int not null,
  c2   int not null
)

With this data:

insert testDeadlockTable1 values (1, 1)
insert testDeadlockTable1 values (2, 2)
insert testDeadlockTable1 values (3, 3)
insert testDeadlockTable1 values (4, 4)

insert testDeadlockTable2 values (1, 1)
insert testDeadlockTable2 values (2, 2)
insert testDeadlockTable2 values (3, 3)
insert testDeadlockTable2 values (4, 4)

And consider the following two stored procedures:

create proc testDeadlockTestA
as
  begin tran

  update testDeadlockTable1
    set c1 = 3
    where c2 = 1

  waitfor delay '00:00:01' -- sleep 1 second

  select c1
    from testDeadlockTable2
    where c2 = 3

  commit tran
go
create proc testDeadlockTestB
as
  begin tran

  update testDeadlockTable2
    set c1 = 5
    where c2 = 2

  waitfor delay '00:00:01' -- sleep 1 second

  select c1
    from testDeadlockTable1
    where c2 = 4

  commit tran
go

In one query session, call testDeadlockTestA and then immediately in another session call testDeadlockTestB. The latter session will get chosen as the deadlock victim.

In both sessions, @@trancount is appropriately 0 at the end.

Now start each query session with begin tran, so that the @@trancount is 1 when the stored procedures are called. The deadlock should therefore occur within the transaction that each stored procedure starts (that is, the inner transaction).

Session A, which did not get chosen as the deadlock victim, has a @@trancount of 1, as we'd expect (we did not end the outer transaction). But session B, the victim, has a @@trancount of 0.

Why did both the inner and outer transactions end when the deadlock occurred? Is there a way to ensure that only the inner transaction is ended in case of a deadlock?

It appears as if a deadlock error behaves as if XACT_ABORT was set to on (it is not in this case), as any further query statements following a call that results in a deadlock are not executed.

The reason for this question is whether to know whether one can run a query again, if a deadlock occurs. If it occurs within a larger transaction, that is intended for several queries to be called within, then destroying that outer transaction would mean it would not be safe to re-run the query that was victimised by a deadlock. But if it only stops its immediate surroundings, then it would be safe.

Answer 1

SQL Server doesn't have true nested transactions. There are features like SAVE TRANSACTION and savepoint names that kind of, maybe, can be forced to work like nested transactions, but not really¹.

So the behaviour of ROLLBACK (without trying to use savepoints) always affects all transactions, no matter what the nesting level.

And a rollback forced by the deadlock breaker never has a chance of specifying a savepoint name.

---

¹Notably, everyone has to be "in on the joke". You cannot take existing code written to use BEGIN/ROLLBACK and nest it inside a transaction. It has to be re-written as SAVE TRANSACTION name/ROLLBACK name and now it depends on calling code to always wrap it in an existing transaction

Answer 2

Why did both the inner and outer transactions end when the deadlock occurred?

What else do you propose? Writing an AI that can solve every possible scenario? Fail Fast is a known principle of safe programming. Choose one, kill it, done - let the programmers fix the mess they created.

Also understand that there is no "inner transaction". there only ever is ONE transaction - other internal transactions are contained in the outer one, so the outer one must be rolled back.