Transactions and the Transaction Log: The Engine Side of ACID, XACT_ABORT, Savepoints and Accelerated Database Recovery

Version note: The T-SQL (Transact-SQL, Microsoft's dialect of SQL (Structured Query Language)) examples in this post were run on SQL Server 2025 CU10 (17.0.5035.5, compatibility level 170), and the C# examples with .NET 10, Microsoft.Data.SqlClient 7.1.1 and Dapper 2.1.89.

Guess First

Order 42 has 4 items. We open a transaction and add two items; the second item's BookId does not exist, so it will get a foreign key error. Then we write COMMIT:

SET XACT_ABORT OFF;   /* the default */
BEGIN TRANSACTION;
INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice) VALUES (42, 7, 1, 10.00);
INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice) VALUES (42, 999999, 1, 10.00);
SELECT @@TRANCOUNT AS trancount, XACT_STATE() AS xact_state;
COMMIT;

We learned that "a transaction is atomic, all or nothing". So how many items does the order have after this batch: 4 or 5? The real output (from SqlClient):

items before: 4
  Msg 547, Level 16: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_OrderItems_Books". ...
  Msg 3621, Level 0: The statement has been terminated.
  trancount=1, xact_state=1
items after: 5

5. The error rolled back only the failing statement; the transaction stayed open and "committable" (XACT_STATE() = 1), and COMMIT made the first item durable. Atomicity is not guaranteed; it is something you build. In this post we examine the engine side of transactions: transaction modes, the "nested transaction" myth, the TRY/CATCH pattern with XACT_ABORT, savepoints, the inside of the transaction log (LSN (Log Sequence Number), VLF (Virtual Log File), recovery model), how a long transaction locks the log, ADR (Accelerated Database Recovery) and what to watch out for on the .NET side.

Transaction Modes and the "Nested Transaction" Myth

SQL Server has three modes. Autocommit is the default: every statement is its own transaction. In explicit mode you open the transaction with BEGIN TRANSACTION and close it with COMMIT or ROLLBACK. In implicit mode (SET IMPLICIT_TRANSACTIONS ON), the first data statement opens the transaction on its own, but closing it is still up to you; when it is switched on out of an Oracle habit and forgotten, it is a classic source of long transactions. SqlClient runs in autocommit by default; Entity Framework Core's (EF Core) SaveChanges, on the other hand, opens an explicit transaction internally.

You can write nested BEGIN TRANSACTION statements, but SQL Server has no truly nested transactions:

BEGIN TRANSACTION;                      /* outer */
    INSERT dbo.AuditLog VALUES ('outer work');
    BEGIN TRANSACTION;                  /* "inner" */
        INSERT dbo.AuditLog VALUES ('inner work');
        PRINT CONCAT('trancount inside inner: ', @@TRANCOUNT);
    COMMIT;                             /* inner commit */
    PRINT CONCAT('trancount after inner commit: ', @@TRANCOUNT);
ROLLBACK;                               /* outer rollback */
PRINT CONCAT('trancount after rollback: ', @@TRANCOUNT);
SELECT COUNT(*) AS audit_rows FROM dbo.AuditLog;

trancount inside inner: 2
trancount after inner commit: 1
trancount after rollback: 0
audit_rows
----------
0

The inner COMMIT made nothing durable; it only lowered @@TRANCOUNT from 2 to 1. The only thing that decides durability is the outermost COMMIT. ROLLBACK, on the contrary, ignores the counter and always undoes everything up to the outermost level. That is why a procedure that opens its own transaction and does a ROLLBACK on error destroys the caller's transaction too:

CREATE OR ALTER PROCEDURE dbo.InnerProc AS
BEGIN
    BEGIN TRANSACTION;
    INSERT dbo.AuditLog VALUES ('inner proc');
    ROLLBACK;
END;
GO
BEGIN TRANSACTION;
EXEC dbo.InnerProc;

HResult 0x10A, Level 16, State 2
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

0x10A is error 266: there was a transaction when entering the procedure and none when leaving. The calling code still thinks it is inside a transaction. In the stored procedure post we will see the "commit/rollback only if I opened the transaction" pattern used for this.

XACT_ABORT and the TRY/CATCH/THROW Pattern

The fix for the opening problem is SET XACT_ABORT ON. With it on, most run-time errors roll back the whole transaction and stop the batch:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice) VALUES (42, 7, 1, 10.00);
INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice) VALUES (42, 999999, 1, 10.00);
SELECT 'not reached' AS msg;
COMMIT;

  Msg 547, Level 16: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_OrderItems_Books". ...
trancount after batch: 0
items after: 4

The first item was rolled back too, the SELECT never ran, and no transaction was left open. XACT_ABORT has a second, less known benefit on the application side: when the client sends a command timeout or a cancel (attention), with XACT_ABORT OFF the transaction stays open on the server and keeps holding its locks until the connection is reused from the pool and reset. With ON, the cancel rolls back the transaction as well.

Here is the pattern that combines this with TRY/CATCH error handling. Stock is decremented, then the item is added; if the item cannot be added, the stock must come back too:

CREATE OR ALTER PROCEDURE dbo.AddOrderItem @OrderId int, @BookId int, @Quantity smallint
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE dbo.StockItems SET QuantityOnHand = QuantityOnHand - @Quantity WHERE BookId = @BookId;
        INSERT dbo.OrderItems (OrderId, BookId, Quantity, UnitPrice)
        SELECT @OrderId, @BookId, @Quantity, Price FROM dbo.Books WHERE BookId = @BookId;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH;
END;
GO
EXEC dbo.AddOrderItem @OrderId = 2000000, @BookId = 1, @Quantity = 5;   /* no such order */

stock of book 1 before: 36
  Msg 547, Level 16: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_OrderItems_Orders". ...
stock of book 1 after: 36, trancount: 0

Three parts work together: XACT_ABORT ON makes the error put the transaction into a "doomed" state (XACT_STATE() = -1), the CATCH block rolls back the open transaction, and THROW passes the original error with its number and message to the caller. Producing a new message with RAISERROR, or swallowing the error and saying RETURN -1, prevents the application from learning what the error was.

Savepoints: Rolling Back Part of a Transaction

When you need to roll back only part of a transaction rather than all of it, you use a savepoint:

SET XACT_ABORT OFF;
BEGIN TRANSACTION;
UPDATE dbo.Orders SET Status = 'Shipped' WHERE OrderId = 999999;
SAVE TRANSACTION BeforeShipment;
INSERT dbo.Shipments (OrderId, Carrier, TrackingNumber, ShippedAt) VALUES (999999, 'Express', 'TRK-TEST', '2026-10-21');
ROLLBACK TRANSACTION BeforeShipment;
SELECT @@TRANCOUNT AS trancount,
       (SELECT Status FROM dbo.Orders WHERE OrderId = 999999) AS status,
       (SELECT COUNT(*) FROM dbo.Shipments WHERE TrackingNumber = 'TRK-TEST') AS test_shipments;
ROLLBACK;

  trancount=1, status=Shipped, test_shipments=0

The shipment row was rolled back; the order status change and the transaction remain. There are two constraints: rolling back to a savepoint does not release locks, and in a doomed transaction (XACT_STATE() = -1) you cannot roll back to a savepoint, only roll back completely. Since EF Core 5, SaveChanges uses a savepoint automatically when called inside an open transaction; the details are in the EF Core series' transaction post.

Inside the Transaction Log: LSN, VLF, Recovery Model and Long Transactions

In post one we saw that every change is written to the log first (WAL, write-ahead logging) and that every log record carries an increasing LSN (Log Sequence Number). The physical log file is divided into pieces called VLFs (Virtual Log Files); the log is used circularly, and VLFs whose records are no longer needed become reusable (log truncation). The recovery model decides when a log record stops being needed:

• SIMPLE: a record is no longer needed once its transaction ends and a checkpoint passes. There is no point-in-time restore.
• FULL: a record is additionally kept until it is included in a log backup; if you take no log backups, the log grows forever.
• BULK_LOGGED: like FULL, but some bulk operations are minimally logged.

sys.databases.log_reuse_wait_desc tells you what truncation is waiting for. To see it, I started a transaction in one session that stays open for 25 seconds (it updates a single stock row and waits), and in another session ran three separate, committed 200,000-row updates and a CHECKPOINT:

/* session 1 */
BEGIN TRANSACTION;
UPDATE dbo.StockItems SET QuantityReserved = QuantityReserved + 1 WHERE BookId = 5;
WAITFOR DELAY '00:00:25';
ROLLBACK;

/* session 2 */
UPDATE dbo.Payments SET Amount = Amount + 0.01 WHERE PaymentId <= 200000;   /* three times */
CHECKPOINT;
SELECT recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = DB_NAME();
SELECT used_log_space_in_percent FROM sys.dm_db_log_space_usage;
SELECT COUNT(*) AS vlfs, SUM(CASE WHEN vlf_active = 1 THEN 1 ELSE 0 END) AS active_vlfs FROM sys.dm_db_log_info(DB_ID());
DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS;

OLDACT_SPID      68
OLDACT_NAME      user_transaction
OLDACT_LSN       (65:8360:1)
OLDACT_STARTTIME Nov  8 2026 12:34:22:750PM

The database is in SIMPLE recovery and all of session 2's updates were committed; still the log could not be truncated, because the log can only be cut up to the LSN where the oldest open transaction began. Session 1, updating a single row and waiting, keeps everything written after it in the log. DBCC OPENTRAN shows the culprit with its session number and start time. In production this session is an application holding a transaction open while waiting for a user confirmation dialog, a debug breakpoint, or an SSMS (SQL Server Management Studio) window opened with IMPLICIT_TRANSACTIONS and forgotten, and it goes unnoticed until the log disk fills up.

Accelerated Database Recovery

In classic recovery, rolling back a large transaction means reading every change from the log and applying its inverse; it takes as long backwards as it did forwards. ADR, introduced with SQL Server 2019, changed this: it keeps the previous versions of rows in the PVS (persistent version store) inside the database, or within the row itself, and turns rollback into a logical operation that goes back to these versions. I rolled back the same 200,000-row update with ADR off and on, and counted with sys.fn_dblog how many log records the update and the rollback produced:

CHECKPOINT;
BEGIN TRANSACTION;
UPDATE dbo.Payments SET Amount = Amount + 1 WHERE PaymentId <= 200000;
/* the transaction's log usage from sys.dm_tran_database_transactions */
ROLLBACK;

ALTER DATABASE sqls_09 SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE;
/* the same experiment */

With ADR off, the rollback produced 200,031 log records by writing a compensation record for every row; with ADR on, a single record. The rollback is instant because the rows already point to their previous versions, and the old version is cleaned up in the background. The price is in the table too: since version information is also written during the update, the log doubled. ADR's real gain is recovery time: even if the server crashes in the middle of a long transaction, the undo phase at startup finishes quickly regardless of that transaction's size, and the log is less prone to the can't-truncate-because-of-an-open-transaction problem (the scenario above). According to the edition table, ADR exists in Enterprise and Standard but not in Express. SQL Server 2025 brought ADR to tempdb too; since that is a server-level setting, I did not enable it on the shared container. ADR is also a prerequisite for SQL Server 2025's optimized locking feature, which we will see in the upcoming locking posts.

How SQL Server 2019/2022 differ: transaction semantics, XACT_ABORT, savepoints and the log architecture are the same in all three versions. ADR arrived in 2019, and 2022 improved PVS cleanup and scaling across many databases; ADR for tempdb and the ADR-based optimized locking exist only in 2025.

The .NET Side: SaveChanges, Dapper and TransactionScope

In EF Core, SaveChanges is already a single transaction; to combine several SaveChanges calls, or EF Core and Dapper, in the same transaction you use BeginTransaction (the EF Core series' transaction post and post 7 of the Dapper series). Be careful when doing the same with TransactionScope: if a second connection is opened inside the scope, the transaction tries to escalate to a distributed transaction, even if both go to the same database:

using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
await using var c1 = new SqlConnection(connectionString);
await c1.OpenAsync();
await c1.ExecuteAsync("UPDATE dbo.StockItems SET QuantityReserved = QuantityReserved + 1 WHERE BookId = 1");
await using var c2 = new SqlConnection(connectionString);
await c2.OpenAsync();   /* a second connection in the same scope */
await c2.ExecuteAsync("UPDATE dbo.StockItems SET QuantityReserved = QuantityReserved + 1 WHERE BookId = 2");
scope.Complete();

PlatformNotSupportedException: This platform does not support distributed transactions.

This output is from macOS. Since .NET 7, distributed transactions are possible only on Windows and only when explicitly allowed with TransactionManager.ImplicitDistributedTransactions = true; on Linux containers and macOS this code always fails. On Windows it silently escalates to MSDTC (Microsoft Distributed Transaction Coordinator), which is a separate piece of infrastructure with a separate performance cost. Use a single connection and BeginTransaction for a single database; if you need to keep several systems consistent, look at approaches such as the outbox covered in the Messaging and Event-Driven Architecture series.

In Production

Keep transactions short. A transaction should not contain an HTTP (Hypertext Transfer Protocol) call, a user confirmation, sending an email or a large computation. Every second it stays open, it holds locks and prevents the log from being truncated.

Monitor log_reuse_wait_desc. Values such as ACTIVE_TRANSACTION, LOG_BACKUP and AVAILABILITY_REPLICA tell you directly why the log is growing. In a database in FULL recovery without regular log backups, the log file will sooner or later fill the disk.

Keep the VLF count reasonable. Growing the log file in very small steps produces thousands of VLFs and slows down recovery. Give the log a realistic size from the start and a fixed growth step in MB.

Common Pitfalls

Thinking "it rolls back on error anyway" with XACT_ABORT OFF. As in the opening example, a COMMIT after the error makes the half-done work durable.

Thinking the inner COMMIT commits something. Only the counter goes down; durability lives in the outermost COMMIT, and a ROLLBACK at any level undoes everything.

Thinking the log will not grow in SIMPLE recovery. A long open transaction stops truncation in SIMPLE too; in the experiment, log usage went from 1.5% to 20.2%.

Testing on Windows and assuming TransactionScope will work on Linux as well. Code that escalates to a distributed transaction throws PlatformNotSupportedException in a Linux container.

A Small Experiment

★☆☆ Write the opening batch with XACT_ABORT OFF but inside TRY/CATCH. What value does XACT_STATE() return in the CATCH block? Is committing possible?

★★☆ Examine the log's VLF count and sizes with sys.dm_db_log_info. What happens to the VLF count if you grow the log file by 100 MB in 1 MB steps? Measure it, then compare with a fixed growth step.

★★★ With ADR on, leave the 200,000-row update uncommitted and watch sys.dm_tran_persistent_version_store_stats from another session. When does the PVS grow, and when is it cleaned up after the rollback?

We saw how a transaction behaves on its own. The real difficulty begins when two transactions touch the same data at the same time. In the next post we examine isolation levels, the anomalies from dirty reads to write skew, and row versioning, with two-session experiments.