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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
id IN (3, 4)will have been removed.
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).
To compliment what @Daniel posted in his answer, I would say that:
There is only one Transaction. The second
BEGIN TRAN merely increments the
@@TRANCOUNT, which is merely decremented by all but the last / outer-most
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: