Is it possible for parent transaction to fail if nested transaction was successfully committed

Question

I'm trying to understand nested transactions in SQL Server. Lets consider following chain for SQL commands:

BEGIN TRANSACTION; -- #1
BEGIN TRANSACTION; -- #2
UPDATE foo SET column = 'something'; -- Change something in one table.
COMMIT TRANSACTION; -- #2

If commit of transaction #2 succeed is it possible for commit of transaction #1 to fail? If yes, could you provide an example when this might happen?

If you're asking if transaction 2 gets rolled back even after being committed when transaction 1 is rolled back, then yes. See https://stackoverflow.com/questions/527855/nested-transactions-in-sql-server — sticky bit, Jul 07 '18 at 15:12

score 7 · Accepted Answer · edited Jun 20 '20 at 09:12

From A SQL Server DBA myth a day: (26/30) nested transactions are real:

The commit of a nested transaction has absolutely no effect – as the only transaction that really exists as far as SQL Server is concerned is the outer one. ...

The rollback of a nested transaction rolls back the entire set of transactions – as there is no such thing as a nested transaction.

SELECT @@TRANCOUNT;
BEGIN TRANSACTION; -- #1
SELECT @@TRANCOUNT;
BEGIN TRANSACTION; -- #2
SELECT @@TRANCOUNT;
UPDATE foo SET [column] = 'something';
COMMIT TRANSACTION; -- #2
SELECT @@TRANCOUNT;
ROLLBACK;      -- simulate error or explicit rollback
               -- update is lost

DBFiddle Demo

If you want something like Oracle autonomous transaction please read: Commit transaction outside the current transaction (like autonomous transaction in Oracle)

Is it possible for parent transaction to fail if nested transaction was successfully committed

1 Answers1