Transactions, isolation levels, RCSI and SNAPSHOT

Nested transactions are a counter

SQL Server does not really nest transactions. BEGIN TRAN increments @@TRANCOUNT; COMMIT decrements it and only commits when it reaches zero; ROLLBACK (without a savepoint name) rolls back everything and sets it to zero. An inner COMMIT therefore saves nothing on its own.

To undo part of a transaction, use a savepoint: SAVE TRANSACTION before_line; and later ROLLBACK TRANSACTION before_line;. That undoes the work since the savepoint and leaves the transaction open.

1CREATE TABLE t (id INT PRIMARY KEY, note NVARCHAR(20) NOT NULL);

1DECLARE @log TABLE (step INT, what NVARCHAR(40), trancount INT);2 3BEGIN TRAN;4  INSERT INTO @log VALUES (1, N'outer BEGIN', @@TRANCOUNT);5  INSERT INTO t VALUES (1, N'outer');6  BEGIN TRAN;7    INSERT INTO @log VALUES (2, N'inner BEGIN', @@TRANCOUNT);8    INSERT INTO t VALUES (2, N'inner');9  COMMIT;10  INSERT INTO @log VALUES (3, N'inner COMMIT', @@TRANCOUNT);11ROLLBACK;12INSERT INTO @log VALUES (4, N'outer ROLLBACK', @@TRANCOUNT);13 14BEGIN TRAN;15  INSERT INTO t VALUES (3, N'kept');16  SAVE TRANSACTION before_risky;17  INSERT INTO t VALUES (4, N'undone');18  ROLLBACK TRANSACTION before_risky;19  INSERT INTO @log VALUES (5, N'rollback to savepoint', @@TRANCOUNT);20COMMIT;21 22SELECT step, what, trancount,23       (SELECT STRING_AGG(CAST(id AS VARCHAR(5)), ',') WITHIN GROUP (ORDER BY id) FROM t) AS ids_in_table_at_end24FROM @log25ORDER BY step;

The locking isolation levels

In the locking implementation, writers always take exclusive locks held to the end of the transaction. What changes is the readers: under READ COMMITTED a shared lock is released as soon as the row is read; REPEATABLE READ keeps shared locks until the end; SERIALIZABLE also locks the it read, so nobody can insert a phantom into them. Each step up means more blocking.

1CREATE TABLE t (id INT PRIMARY KEY, v INT NOT NULL);2INSERT INTO t VALUES (1, 10);

1DECLARE @log TABLE (outcome NVARCHAR(40));2SET TRANSACTION ISOLATION LEVEL SNAPSHOT;3BEGIN TRY4  BEGIN TRAN;5    SELECT v FROM t WHERE id = 1;6  COMMIT;7  INSERT INTO @log VALUES (N'read succeeded');8END TRY9BEGIN CATCH10  IF @@TRANCOUNT > 0 ROLLBACK;11  INSERT INTO @log VALUES (CONCAT(N'error ', ERROR_NUMBER()));12END CATCH;13SELECT outcome FROM @log;

1ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;2GO3ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;4GO5CREATE TABLE t (id INT PRIMARY KEY, v INT NOT NULL);6INSERT INTO t VALUES (1, 10);

1DECLARE @v INT;2SET TRANSACTION ISOLATION LEVEL SNAPSHOT;3BEGIN TRAN;4  SELECT @v = v FROM t WHERE id = 1;5COMMIT;6 7SELECT @v AS value_read,8       d.snapshot_isolation_state_desc,9       d.is_read_committed_snapshot_on10FROM sys.databases AS d11WHERE d.database_id = DB_ID();

Common mistakes

• Expecting an inner COMMIT to persist work. The first example rolls back both rows with one outer ROLLBACK.
• NOLOCK everywhere to cure blocking, then explaining duplicated rows in a report.
• Asking for SNAPSHOT without enabling it. The second example fails with error 3952.
• Not retrying on error 3960 under SNAPSHOT.

What interviewers ask

• What does RCSI change, and what does it cost? Readers see the last committed version per statement without shared locks; costs version store space (tempdb, or the database itself with ADR) and a little per-row overhead.
• RCSI versus SNAPSHOT isolation? Statement-level versus transaction-level consistency; SNAPSHOT is opt-in per session and can fail with update conflicts.
• What happens to @@TRANCOUNT with nested transactions? It counts; only the outermost COMMIT commits, and any ROLLBACK undoes it all.