Στον SQL Server μας δίνεται η δυνατότητα να ορίσουμε ένα ή περισσότερα transactions μέσα σε ένα ήδη ανοιχτό transaction. Ποια είναι όμως η συμπεριφορά των transactions όταν κάνουμε rollback ή commit ένα εσωτερικό transaction;

Αρχικά, θα δημιουργήσουμε ένα πίνακα με δύο πεδία με τον οποίο θα δοκιμάσουμε τα δύο σενάρια.

--Table to test nesting 
CREATE TABLE NestingTransactions (Id INT IDENTITY(1,1), Description NVARCHAR(20));
GO

Στο πρώτο σενάριο θα δοκιμάσουμε να κάνουμε rollback το εσωτερικό transaction:

BEGIN TRANSACTION OuterTransaction;
    --The outer transaction executes an insert
    SELECT @@TRANCOUNT AS FirstTran;
    INSERT INTO NestingTransactions (Description) VALUES ('Description1'),('Description2'),('Description3');
    
    SELECT COUNT(*) AS InsertedRows
    FROM NestingTransactions;

    BEGIN TRANSACTION;
        --The inner transaction adds one more rows, then it rollbacks
        SELECT @@TRANCOUNT AS SecondTran;
        INSERT INTO NestingTransactions (Description) VALUES ('Description4');
        
        SELECT COUNT(*) AS InsertedRows
        FROM NestingTransactions;

    ROLLBACK TRANSACTION;

    SELECT @@TRANCOUNT AS FinalTranCount;
    SELECT COUNT(*) AS FinalRows
    FROM NestingTransactions;

-- FirstTran: 1
-- InsertedRows: 3
-- SecondTran: 2
-- InsertedRows: 4
-- FinalTranCount: 0
-- FinalRows: 0

Το πρώτο πράγμα το οποίο παρατηρούμε είναι ότι το rollback το οποίο καλούμε αφού έχουμε κάνει begin το εσωτερικό transaction, στην πραγματικότητα κάνει rollback το εξωτερικό και κατά συνέπεια μετά την εκτέλεσή του ο πίνακας θα είναι άδειος. Τελικά, και ενώ μετά από δύο συνεχόμενα BEGIN TRANSACTION η τιμή του @@TRANCOUNT είναι όπως θα περιμέναμε 2, μετά το ROLLBACK TRANSACTION η τιμή του επιστρέφει σε μηδέν καθώς δεν έχουμε πλέον κανένα transaction ανοιχτό.

Στο επόμενο σενάριο, θα προσπαθήσουμε να κάνουμε commit το εσωτερικό transaction και στην συνέχεια rollback το εξωτερικό:

BEGIN TRANSACTION;
    SELECT @@TRANCOUNT AS FirstTran;
    INSERT INTO NestingTransactions (Description) VALUES ('Description1'),('Description2'),('Description3');

    BEGIN TRANSACTION;
        SELECT @@TRANCOUNT AS SecondTran;
        INSERT INTO NestingTransactions (Description) VALUES ('Description4');
    COMMIT;
    SELECT @@TRANCOUNT AS TranCountAfterInnerCommit;
ROLLBACK;
GO

SELECT COUNT(*) AS FinalRows
FROM NestingTransactions;
-- FirstTran: 1
-- SecondTran: 2
-- TranCountAfterInnerCommit: 1
-- FinalRows: 0

Και σε αυτό το σενάριο, στο τέλος ο πίνακας δεν θα περιέχει καμία γραμμή. Το @@TRANCOUNT μετά το commit του εσωτερικού transaction, θα μειωθεί κατά 1 αλλά μετά το ROLLBACK το INSERT που έγινε στο nested transaction θα "χαθεί".

Στον SQL Server λοιπόν, αν και έχουμε την δυνατότητα να δηλώσουμε nested transactions, στην πραγματικότητα δεν έχουμε την συμπεριφορά που θα περίμενε κανείς από αυτά. Αυτό που γίνεται τελικά, είναι ότι ορίζει το εξωτερικό transaction, αρκεί να υπάρχει η σύμφωνη γνώμη των εσωτερικών.