Does this code breaks ACID properties Isolation rule?

Question

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

Answer 1

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).

Answer 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