-1

I am unable to understand which transaction will commit first .

   BEGIN TRANSACTION T1
         UPDATE mcnnew SET id =3 WHERE id=4
       BEGIN TRANSACTION T2
            DELETE FROM mcnnew WHERE id=3
       COMMIT TRANSACTION T2
   COMMIT TRANSACTION T1
1
3

The query does not break ACID compliance. The rows/pages/table (depending on your database) are locked for the duration of the execution (or actually, until the outer-most COMMIT statement).

It's:

  • Atomic: it's all or nothing at all,
  • Consistent: when the transaction completes, all rows that used to have id IN (3, 4) will have been removed.
  • Isolated: once you lock the affected rows, they're not affected by other transactions (which will either block your transaction or wait for it to finish).
  • Durable: Once committed, your delete is "hardened".

This query would be functionally the same if you removed BEGIN TRANSACTION T2 and COMMIT TRANSACTION T2.

The point of having a nested transaction like that is that you can roll back some of the work if you want to (for instance, if you find something went wrong with your initial update).

2
  • But when Transaction T1 starts then according to Isolation property Transaction T2 will only start when Transaction T1 completes. Mar 24 '17 at 6:38
  • 1
    T2 is the same transaction for the purpose of ACID. Think of it as isolation from other processes. Mar 24 '17 at 6:58
2

To compliment what @Daniel posted in his answer, I would say that:

  1. There is only one Transaction. The second BEGIN TRAN merely increments the @@TRANCOUNT, which is merely decremented by all but the last / outer-most COMMIT.

  2. If there were true nested Transactions, it doesn't make sense, even conceptually, that any system would allow for committing changes out of sequence from the order in which they were executed.

Please see my answer to the following question, also here on DBA.SE, that details how Transactions work:

How to rollback when 3 stored procedures are started from one stored procedure

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.