Preventing Inconsistent Data with ACID Transactions

Wrapping work in a transaction feels like buying insurance. You write BEGIN, you write COMMIT, and the four ACID properties are supposed to take care of the rest.

They do, but only up to the settings you inherited. Atomicity, Consistency, Isolation and Durability each arrive with a default, and three of those defaults quietly deliver less than the word suggests. Here's where each one stops.

Atomicity stops at the first error it decides to ignore

Atomicity is all or nothing, and the surprise is what counts as an error worth aborting for.

On SQL Server, XACT_ABORT is OFF by default in T-SQL. With it off, a run-time error inside an explicit transaction may roll back only the statement that failed and let execution continue. The transaction stays open. If the next line is COMMIT, you commit half the work and the database reports success.

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 999;  -- fails a constraint

COMMIT;   -- with XACT_ABORT OFF, this can still commit the debit

One line fixes it, and it belongs at the top of anything doing multi-statement writes:

SET XACT_ABORT ON;

Now any run-time error terminates the whole transaction and rolls it back. In .NET this matters less if you let EF Core own the unit of work, because a single SaveChangesAsync call is already wrapped in one transaction that rolls back as a unit. The trap there is splitting one business operation across two SaveChangesAsync calls: that's two transactions, and the gap between them is a window where half the work is durable and half never happened.

Consistency only enforces what you wrote down

This is the property people most often assume is automatic. It isn't automatic in the slightest.

Consistency guarantees a transaction moves the database from one valid state to another, where valid means the constraints you declared. Primary keys, foreign keys, unique indexes, CHECK, NOT NULL. That's the whole list. The database has never heard of your business rules.

So a rule like "an order total is never negative" living in a C# guard clause is not a database guarantee. It holds exactly as long as every write goes through that class, which stops being true the first time someone runs a migration script, a bulk import, or a second service against the same table.

ALTER TABLE Orders
ADD CONSTRAINT CK_Orders_Total_Positive CHECK (Total > 0);

Or declared in EF Core, so it ships with the migration rather than living in a runbook:

builder.ToTable(t => t.HasCheckConstraint("CK_Orders_Total_Positive", "Total > 0"));

Atomicity and isolation are behaviour you get from the engine. Consistency is a property of your schema, and an empty schema promises nothing.

Isolation defaults to a level that still loses writes

SQL Server runs at READ COMMITTED unless told otherwise. That stops you reading uncommitted data, and people reasonably hear "committed" as "safe."

It releases its shared locks as each statement finishes, so it says nothing about the gap between your read and your write:

Session A: SELECT Balance FROM Accounts WHERE Id = 1;   -- reads 100
Session B: SELECT Balance FROM Accounts WHERE Id = 1;   -- reads 100
Session A: UPDATE Accounts SET Balance = 50 WHERE Id = 1;
Session B: UPDATE Accounts SET Balance = 50 WHERE Id = 1;   -- A's write is gone

Both transactions were valid alone. Together they lost an update. Being inside a transaction and being safe from concurrent writers are separate guarantees, and the default only gives you the first.

The usual fix isn't a stricter isolation level, which costs concurrency everywhere to solve a problem in one place. It's a version column, so the second write fails loudly instead of winning silently:

[Timestamp]
public byte[] RowVersion { get; set; } = [];

EF Core then adds WHERE RowVersion = @original to the update, and throws DbUpdateConcurrencyException when zero rows match.

Durability has the honest default, and that's the problem

Durability is the exception. Its default is correct: SQL Server uses write-ahead logging, the log record reaches durable storage before the commit is acknowledged, and a crash is replayed on recovery. COMMIT means what you think it means.

Which is exactly why nobody checks it. Since SQL Server 2014 it can be traded away:

ALTER DATABASE AppDb SET DELAYED_DURABILITY = FORCED;

Now COMMIT returns as soon as the log record is in memory and the flush happens in batches. You buy real throughput on write-heavy workloads, and you pay with a window where transactions the application was told had committed disappear in a power cut.

It ships DISABLED, so nobody enables it by accident. But it's a plausible fix for a logging bottleneck, applied at the database level, invisible from the application. Every developer above that change carries on believing COMMIT means what it used to.

The short version

Transactions are worth reaching for, and the defaults are mostly reasonable. They're just not the guarantees the four words imply.

Turn on XACT_ABORT for multi-statement writes. Put your invariants in the schema, because that's the only place consistency is enforced. Assume the default isolation level will lose a concurrent write and add a version column where it matters. And treat durability as a setting somebody can change, rather than a law of the database.