title: Why does a SQL Server deadlock victim error (1205) end all its transactions?
body: <p>Consider the following two tables:</p>
  
  <pre><code>create table testDeadlockTable1 (
    c1   int not null,
    c2   int not null
  )
  create table testDeadlockTable2 (
    c1   int not null,
    c2   int not null
  )
  </code></pre>
  
  <p>With this data:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>And consider the following two stored procedures:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>In one query session, call <code>testDeadlockTestA</code> and then immediately in another session call <code>testDeadlockTestB</code>.  The latter session will get chosen as the deadlock victim.</p>
  
  <p>In both sessions, <code>@@trancount</code> is appropriately 0 at the end.</p>
  
  <p>Now start each query session with <code>begin tran</code>, so that the <code>@@trancount</code> is 1 when the stored procedures are called.  The deadlock should therefore occur <em>within</em> the transaction that each stored procedure starts (that is, the inner transaction).</p>
  
  <p>Session A, which did not get chosen as the deadlock victim, has a <code>@@trancount</code> of 1, as we'd expect (we did not end the outer transaction).  But session B, the victim, has a <code>@@trancount</code> of 0.</p>
  
  <p>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?</p>
  
  <p>It appears as if a deadlock error behaves as if <code>XACT_ABORT</code> 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.</p>
  
  <p>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 <em>not</em> 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.</p>