ACID in Practice for .NET: Isolation Levels, Anomalies, and Transaction Pitfalls

1 Beyond the Acronym: Why ACID Is Not a Silver Bullet

ACID is often described as a guarantee, something that magically keeps data correct as long as you “use transactions.” Senior developers know that production systems do not behave that way. ACID defines constraints, not outcomes, and those constraints interact with real workloads, concurrency, and infrastructure in ways that are easy to misunderstand.

Atomicity, Consistency, and Durability usually behave the way developers expect. Atomicity ensures all-or-nothing execution within a transaction boundary. Consistency relies mostly on schema constraints, foreign keys, and application logic. Durability is largely solved by modern databases with write-ahead logging and replicated storage. These properties matter, but they are rarely where production systems break.

Isolation is different. Isolation is subtle, configurable, and full of trade-offs. And in .NET systems backed by SQL Server, isolation is the property most likely to violate your assumptions even when everything appears “correct.”

When isolation behaves differently than expected—which is common in cloud deployments such as Azure SQL Database and Amazon RDS for SQL Server—developers see anomalies, deadlocks, race conditions, and logically impossible states even though every operation is wrapped in a transaction.

The core problem is not that isolation is broken. The problem is that isolation levels intentionally allow interference to balance correctness against performance, and most application code assumes stronger guarantees than it actually gets.

1.1 The “I” in ACID: Why Isolation Is the Most Misunderstood Property

Isolation does not guarantee that a transaction sees a stable or globally consistent view of the database. It only guarantees the level of protection you explicitly request, and only for the specific phenomena that isolation level addresses.

The SQL standard defines isolation as a spectrum rather than a switch. At lower levels, the database explicitly allows certain anomalies:

• Dirty reads
• Non-repeatable reads
• Phantom reads
• Write skew (not part of the SQL standard, but relevant under MVCC and Snapshot Isolation)

Developers often reason about business logic as if transactions execute serially. In practice, most .NET applications run under Read Committed or Read Committed Snapshot Isolation (RCSI), where these anomalies are allowed by design.

A common assumption sounds like this:

“If I wrap this block in a transaction, nobody else can interfere.”

What actually happens under typical isolation levels is very different:

• Reads may not reflect the latest committed state.
• Rows you read can be changed by other transactions before you write.
• Writes can silently overwrite changes made by concurrent transactions.
• A transaction can commit successfully even though its assumptions were invalidated midway.

This gap between mental models and actual behavior is where most concurrency bugs are born.

A short example

using var scope = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted },
    TransactionScopeAsyncFlowOption.Enabled);

var account = db.Accounts.Find(id);
account.Balance -= 100;
db.SaveChanges();

scope.Complete();

At a glance, this looks safe. The code runs inside a transaction, reads a row, modifies it, and commits. Many developers assume this prevents overdrafts.

At Read Committed, it does not. Another transaction can read and update the same row after the first read but before the write. The second update can overwrite the first with no error and no warning.

Isolation does not enforce business invariants. Isolation only controls what data you are allowed to see, and when.

1.2 The Performance vs. Consistency Trade-off: Why “Perfect” Isolation Is Rarely the Goal

The strongest isolation level—Serializable—prevents almost all anomalies. In theory, it gives developers the mental model they want: transactions behave as if they ran one at a time.

In SQL Server, that guarantee is enforced through aggressive locking:

• Key-range locks are held for the duration of the transaction.
• Hot tables and indexes become contention points.
• Deadlocks become more frequent under concurrent writes.
• Overall throughput drops sharply as concurrency increases.

Serializable isolation is correct, but it is expensive. Most OLTP systems cannot afford to run everything at that level without sacrificing latency and scalability.

Why most systems accept weaker isolation

Production systems typically prioritize:

• low latency
• high concurrency
• predictable throughput
• fast recovery under load

To achieve that, they rely on Read Committed, RCSI, or Snapshot Isolation. These levels allow anomalies because correctness is enforced elsewhere: in application logic, concurrency tokens, retries, idempotency, and compensating workflows.

Isolation is a cost dial, not a correctness switch.

That dial affects more than the database. In .NET applications, your isolation choice also shapes:

• how long a DbContext can safely live
• whether you need explicit concurrency tokens
• how often you must retry operations
• whether optimistic or pessimistic locking makes sense

Choosing an isolation level is a cross-layer decision. It affects database behavior, EF Core semantics, and the surrounding retry and error-handling strategy.

In many systems, weaker isolation plus explicit guardrails produces better results than trying to force serializable behavior everywhere.

1.3 Reality Check: How Modern Cloud Environments Change Defaults

When teams move from local or on-prem SQL Server to managed cloud offerings, isolation behavior often changes without code changes. These differences matter, especially under concurrency.

Azure SQL Database

• Read Committed Snapshot Isolation is enabled by default.
• Snapshot Isolation is available but must be explicitly enabled per database.
• TempDB is optimized and shared across replicas, making version store operations cheap in most scenarios.
• Reads never block writes, which changes query timing and race windows compared to lock-based Read Committed.

The practical result is that many .NET applications unknowingly switch from lock-based reads to versioned reads. Code that “worked fine locally” may behave differently under load because reads observe a slightly older snapshot.

AWS RDS for SQL Server

• RCSI defaults depend on the engine edition:

  • Express and Standard typically default to lock-based Read Committed.
  • Enterprise editions more often enable RCSI, but it is not guaranteed.

• Version store cleanup depends on TempDB I/O and provisioned IOPS.

• Under constrained I/O, version store cleanup can lag, increasing memory pressure and causing writers to block unexpectedly.

For a .NET application, this can surface as sudden spikes in query latency, timeouts during SaveChanges, or lock waits that only appear under sustained load.

What this means for .NET developers

1. You may already be using row versioning without realizing it.
2. Concurrency bugs can appear only after moving to the cloud.
3. The same TransactionScope or EF Core code can behave very differently across environments.
4. Cloud databases optimize for throughput and availability, not for preserving your assumptions.

The environment shapes your transaction semantics. ACID is the starting point—but isolation behavior is where reality diverges.

---

2 The Anatomy of SQL Server Isolation Levels

SQL Server supports both locking-based and version-based isolation. Understanding how these models behave—and how .NET maps onto them—is essential for predicting real-world outcomes under concurrency. Most transactional bugs are not caused by “wrong code,” but by isolation behavior that doesn’t match the developer’s mental model.

2.1 Standard Levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable

2.1.1 Read Uncommitted

Read Uncommitted allows dirty reads. Queries can observe data that has not yet committed and may never commit at all.

Example query hint:

SELECT * FROM Orders WITH (NOLOCK)

Problems:

• Returns uncommitted data.
• Can read rows that later roll back.
• Can skip rows or read the same row twice due to allocation movement.

That said, Read Uncommitted is not never valid. In .NET systems, NOLOCK is sometimes used for:

• dashboard widgets
• monitoring queries
• health checks
• approximate counts or trend analysis

In those cases, slightly incorrect data is acceptable and the goal is to avoid blocking production traffic. Read Uncommitted should never be used for transactional logic, validation, or decision-making code.

2.1.2 Read Committed (Default Without RCSI)

Read Committed is the traditional default for on-prem SQL Server installations.

Behavior:

• Prevents dirty reads.
• Each read acquires a shared lock.
• Locks are released immediately after the read completes.
• Allows non-repeatable reads and phantom reads.

The key detail is that protection is applied per statement, not for the duration of the transaction. Two reads in the same transaction can observe different values.

For many OLTP workloads, this is “good enough,” but it is also where many lost updates and race conditions originate.

2.1.3 Repeatable Read

Repeatable Read strengthens guarantees by holding shared locks on rows that are read until the transaction completes.

Guarantees:

• Prevents dirty reads.
• Prevents non-repeatable reads.
• Still allows phantom reads.

This works well for stable point reads, but it scales poorly. Locks are held longer, increasing the likelihood of deadlocks and lock escalation under write-heavy workloads.

Repeatable Read is rarely used in modern .NET systems because it provides neither the scalability of versioning nor the full safety of serializable isolation.

2.1.4 Serializable

Serializable is the strongest isolation level.

Guarantees:

• Prevents dirty reads.
• Prevents non-repeatable reads.
• Prevents phantom reads.
• Enforces logical serial execution.

SQL Server achieves this using:

• Key-range locks
• Index range locks
• Broad lock coverage across access paths

The cost is significant. Range locks reduce concurrency dramatically, especially on hot tables. Under load, this often manifests as blocking chains and deadlocks rather than clean serialization.

Serializable is correct, but it is expensive enough that most OLTP systems cannot use it as a default.

2.2 The Modern Standard: Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation

SQL Server introduced row versioning to address the scalability limits of lock-based isolation. These modes rely on multi-version concurrency control (MVCC) and are increasingly common in cloud environments.

2.2.1 RCSI vs. Read Committed (Side-by-Side)

This comparison is the source of frequent confusion in .NET systems.

Behavior	Read Committed (Locking)	Read Committed Snapshot (RCSI)
Dirty reads	Prevented	Prevented
Shared locks on reads	Yes	No
Reads block writes	Yes	No
Writes block reads	Yes	No
Non-repeatable reads	Allowed	Allowed
Phantom reads	Allowed	Allowed
Uses TempDB version store	No	Yes
Enabled via IsolationLevel	Yes	No (DB setting)

RCSI changes how Read Committed works without changing the isolation level requested by the application. From .NET’s perspective, nothing changes—yet the behavior is fundamentally different.

2.2.2 Snapshot Isolation (SI)

Snapshot Isolation provides a consistent snapshot for the entire transaction.

Example demonstrating SI conflict detection (not write skew):

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;

SELECT Balance FROM Accounts WHERE Id = 42;
/* application computes new balance */
UPDATE Accounts SET Balance = @newBalance WHERE Id = 42;

COMMIT;

If another transaction modifies the same row after the snapshot was taken, the UPDATE fails at commit time. SQL Server detects the write-write conflict and aborts the transaction.

This is optimistic concurrency in action. The transaction runs without blocking, but correctness is validated at commit time.

Snapshot Isolation guarantees:

• No dirty reads
• No non-repeatable reads
• No phantom reads

However, Snapshot Isolation still allows write skew, which occurs when multiple transactions update different rows based on a shared invariant. That scenario is covered in detail later and requires domain-level protection.

Snapshot feels close to serializable but is not equivalent.

2.3 Under the Hood: Locking vs. Row Versioning (MVCC)

Understanding how SQL Server enforces isolation helps explain many surprising behaviors.

Locking Model

• Reads acquire shared locks.
• Writes acquire exclusive locks.
• Locks block other incompatible locks.
• Deadlocks occur when lock graphs cycle.

This model is simple but does not scale well under mixed read/write workloads.

MVCC Model

• Writers create new row versions in TempDB.
• Readers see a consistent version based on their snapshot timestamp.
• Writers still take locks, but mainly to protect writes.
• Readers never block writers.

Advantages:

• High concurrency
• Predictable read latency
• Better scalability for read-heavy systems

Costs:

• TempDB growth
• Version store cleanup overhead
• Write conflicts under Snapshot Isolation

These costs show up in .NET as longer transaction times, retries, or occasional concurrency exceptions rather than blocking.

2.4 Mapping SQL Levels to System.Data.IsolationLevel and TransactionScope

.NET developers often assume isolation levels map one-to-one between code and SQL Server. That assumption breaks down with snapshot-based behavior.

Mapping Table

SQL Server Behavior	.NET IsolationLevel
Read Uncommitted	ReadUncommitted
Read Committed (locking)	ReadCommitted
Read Committed Snapshot (RCSI)	ReadCommitted (database setting)
Repeatable Read	RepeatableRead
Serializable	Serializable
Snapshot Isolation	Snapshot
Connection default	Unspecified

Important details:

• RCSI has no IsolationLevel value. It silently changes the behavior of ReadCommitted.
• IsolationLevel.Unspecified means “use the database default,” which may be locking or versioned depending on configuration.
• Snapshot Isolation must be explicitly enabled at the database level before it can be used from .NET.

Detecting RCSI from .NET

Because RCSI is invisible at the API level, developers must verify it explicitly:

var isRcsiEnabled = await context.Database
    .SqlQueryRaw<int>(
        "SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME()")
    .SingleAsync() == 1;

This check is critical when diagnosing concurrency issues that appear only in certain environments.

Example using TransactionScope

var options = new TransactionOptions
{
    IsolationLevel = IsolationLevel.ReadCommitted,
    Timeout = TimeSpan.FromSeconds(30)
};

using var scope = new TransactionScope(
    TransactionScopeOption.Required,
    options,
    TransactionScopeAsyncFlowOption.Enabled);

await DoWorkAsync();
scope.Complete();

If RCSI is enabled, this code runs with versioned reads even though it explicitly requests ReadCommitted. This silent behavior shift is one of the most common sources of confusion in .NET transactional systems.

The isolation level you request is not always the isolation level you get.

---

3 The .NET Transaction Pitfalls: TransactionScope and EF Core

TransactionScope has been part of .NET for a long time, and it still shows up in modern codebases. The problem is not that it is broken, but that its defaults and interaction with async code, EF Core, and cloud runtimes make it easy to use incorrectly. As EF Core has matured—especially in recent versions—the recommended patterns have shifted toward more explicit transaction control.

This section focuses on the practical traps that still cause production issues in .NET systems today.

3.1 The TransactionScope Trap: Why the Default Serializable Level Causes “Hidden” Deadlocks

When you create a TransactionScope without options, .NET applies two defaults that surprise most developers:

• IsolationLevel.Serializable
• Timeout = 1 minute

Serializable forces SQL Server to acquire key-range locks, often spanning more rows than the code actually touches. This is rarely what application code intends.

Example of the default trap

using var scope = new TransactionScope();
// Implicitly Serializable
await _context.SaveChangesAsync();
scope.Complete();

Under load, this pattern often produces:

• deadlocks on otherwise simple updates
• blocking chains that are hard to trace back to the source
• sharp throughput drops when concurrency increases

In cloud environments, the impact is amplified. Higher I/O latency means locks are held longer, increasing the chance of contention.

Correct approach

Always specify the isolation level explicitly.

using var scope = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions
    {
        IsolationLevel = IsolationLevel.ReadCommitted,
        Timeout = TimeSpan.FromSeconds(30)
    },
    TransactionScopeAsyncFlowOption.Enabled);

Isolation level selection should be a conscious architectural choice, not an accidental default inherited from a framework API.

3.2 Async/Await and TransactionScopeAsyncFlowOption: Avoiding the “Transaction Lost” Failure Mode

Ambient transactions were designed before async/await existed. Without explicit opt-in, the transaction context does not reliably flow across async continuations.

Before .NET 4.5.1, this caused:

• TransactionAbortedException
• unexpected transaction rollbacks
• silent loss of the ambient transaction

Even today, missing the async flow option produces subtle failures that look like random database errors.

Correct pattern

using var scope = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted },
    TransactionScopeAsyncFlowOption.Enabled);

await _context.SaveChangesAsync();
scope.Complete();

Without TransactionScopeAsyncFlowOption.Enabled, the continuation after await may execute outside the transaction. The code compiles, tests may pass, and failures only appear under concurrency.

When diagnosing inconsistent writes, always verify that the ambient transaction is actually flowing.

3.3 EF Core Database.BeginTransactionAsync() vs. Ambient Transactions

Modern EF Core usage favors explicit transactions over ambient ones. This is not about version-specific APIs—BeginTransactionAsync, IDbContextTransaction, and savepoints have existed for several releases—but about clarity and correctness in modern .NET services.

Why ambient transactions are harder to reason about

• Transaction boundaries are implicit and spread across call stacks.
• It is unclear which code owns commit or rollback.
• Testing requires special setup to observe transactional behavior.
• Performance problems are harder to attribute.

A common concern is MSDTC escalation. That escalation does not happen just because scopes are nested. It occurs only when a TransactionScope enlists more than one connection or resource manager. If EF Core reuses the same SqlConnection, no escalation occurs.

However, escalation becomes likely when:

• multiple DbContext instances open separate connections
• multiple databases are involved
• non-SQL resources enlist in the transaction

On modern .NET, there is another practical concern: MSDTC is not supported on Linux. In containerized or cloud-native deployments, any accidental escalation can fail outright rather than degrade gracefully.

EF Core recommended pattern

await using var tx = await _context.Database.BeginTransactionAsync();

var order = new Order { /* ... */ };
_context.Orders.Add(order);

await _context.SaveChangesAsync();
await tx.CommitAsync();

Benefits:

• The transaction boundary is explicit.
• Ownership is clear.
• No ambient side effects.
• Savepoints and retries are easier to manage.

For most services, explicit transactions are easier to reason about and safer to operate.

DbContext lifetime guidance

Ensure your DbContext lifetime aligns with your transaction boundary. A single DbContext should not span multiple independent transactions. Reusing the same context across scopes leads to confusing change tracking behavior and stale entity state.

A good rule of thumb: one logical unit of work, one DbContext, one transaction.

3.4 Managing Transactional Boundaries: IDbContextTransaction and Explicit Savepoints

Some workflows need partial recovery. EF Core supports savepoints, which allow you to roll back part of a transaction without aborting the entire unit of work.

Example: Savepoints with meaningful commit work

await using var tx = await _context.Database.BeginTransactionAsync();

// This work should always commit
_context.AuditLogs.Add(new AuditLog("Order processing started"));
await _context.SaveChangesAsync();

try
{
    await _context.Database.CreateSavepointAsync("beforeInventory");

    await AdjustInventoryAsync();
    await _context.SaveChangesAsync();
}
catch
{
    // Roll back only inventory changes
    await _context.Database.RollbackToSavepointAsync("beforeInventory");

    // Compensating action or fallback logic
    _context.AuditLogs.Add(new AuditLog("Inventory adjustment failed"));
    await _context.SaveChangesAsync();
}

await tx.CommitAsync();

The key point is that something outside the savepoint must still be meaningful to commit. Otherwise, the transaction commits nothing of value.

Why savepoints matter

• They avoid nested transactions.
• They keep transactions short and focused.
• They support retry and compensation without full rollback.
• They work cleanly with EF Core’s change tracker.

Savepoints are especially useful in order processing, inventory management, and workflows where one step may fail but the overall operation should still complete in a controlled way.

---

4 Visualizing Failure: Reproducing Real-World Anomalies

Many transactional bugs only show up under pressure: concurrent writers, overlapping reads, and small timing gaps between database and application logic. These issues are hard to catch in development because local databases rarely generate enough contention to expose them. In production, they surface as inconsistent state, overwritten data, or behavior that fails “once in a while.”

This section focuses on making those failures visible. Each example shows how an anomaly occurs, why it is allowed by the isolation level, and what kind of guardrail is required to prevent it. All examples assume EF Core with SQL Server under moderate concurrency.

4.1 The Lost Update: Why _context.Update(entity) Isn’t Enough Under Load

A lost update happens when two transactions read the same row, compute changes independently, and then overwrite each other’s work. Under Read Committed or RCSI, SQL Server does not treat this as an error. EF Core makes the problem easier to trigger because _context.Update(entity) marks every column as modified, not just the one you changed.

Typical failure sequence

1. Request A reads Balance = 100.
2. Request B reads Balance = 100.
3. A subtracts 20 and writes 80.
4. B subtracts 50 and writes 50.
5. B silently overwrites A’s update.

The correct result should be 30. No exception is raised.

Example demonstrating the issue

public async Task WithdrawAsync(int accountId, decimal amount)
{
    var account = await _context.Accounts.FindAsync(accountId);
    account.Balance -= amount;

    // Intentionally dangerous: marks all columns as modified
    _context.Update(account);
    await _context.SaveChangesAsync();
}

Why this fails under concurrency

• All properties are included in the UPDATE statement.
• EF Core does not enable optimistic concurrency by default.
• SQL Server allows last-write-wins behavior at these isolation levels.

Safer pattern with optimistic concurrency and retries

To make this safe, the entity must include a concurrency token (for example, a RowVersion column). See Section 6.1 for full configuration details.

public async Task WithdrawAsync(int accountId, decimal amount)
{
    for (int attempt = 0; attempt < 3; attempt++)
    {
        var account = await _context.Accounts
            .SingleAsync(a => a.Id == accountId);

        account.Balance -= amount;

        try
        {
            await _context.SaveChangesAsync();
            return;
        }
        catch (DbUpdateConcurrencyException)
        {
            // Another transaction modified the row.
            // Reload and retry.
        }
    }

    throw new InvalidOperationException("Concurrent update failed after retries.");
}

Here, failure is detected explicitly, and the system has a chance to retry or escalate. This turns silent corruption into a controlled outcome.

4.2 Phantom Reads and Non-Repeatable Reads: Breaking Inventory and Pricing Logic

Non-repeatable reads occur when a row changes between two reads. Phantom reads occur when the set of rows returned by a query changes. Both are allowed under Read Committed and RCSI, and both commonly break inventory, quota, and pricing workflows.

Inventory example with phantoms

Consider logic that checks availability before reserving inventory.

public async Task<bool> TryReserveAsync()
{
    // First read: how many items are available?
    var availableCount = await _context.Items
        .Where(i => i.Available > 0)
        .CountAsync();

    // Simulate processing delay
    await Task.Delay(50);

    // Second read: fetch an item to reserve
    var item = await _context.Items
        .Where(i => i.Available > 0)
        .OrderBy(i => i.Id)
        .FirstOrDefaultAsync();

    if (availableCount == 0 || item == null)
        return false;

    item.Available--;
    await _context.SaveChangesAsync();
    return true;
}

Between the count and the second query:

• another transaction may insert a new row,
• delete a row,
• or change availability on an existing row.

The two reads can disagree even inside the same transaction. This is a true phantom read.

Serializable or Snapshot Isolation would prevent this. Read Committed and RCSI explicitly allow it.

Why this matters

Any logic that assumes “the result set stays stable while I decide” is unsafe unless:

• Serializable isolation is used,
• Snapshot Isolation is used,
• or the invariant is enforced explicitly with locks or concurrency checks.

4.3 The “Ghost” Anomaly: Write-Skew Under Snapshot Isolation

Write skew is a Snapshot Isolation–specific anomaly. Each transaction sees a consistent snapshot, but the database does not protect cross-row invariants. The result is logically invalid state that still commits successfully.

Domain rule example

“At least one doctor must always be on call.”

Concurrent execution demonstrating write skew

public async Task TakeOffCallAsync(int doctorId)
{
    await using var tx = await _context.Database.BeginTransactionAsync(
        IsolationLevel.Snapshot);

    var onCall = await _context.Doctors
        .Where(d => d.OnCall)
        .ToListAsync();

    if (onCall.Count <= 1)
        throw new InvalidOperationException("At least one doctor must remain on call.");

    var doctor = await _context.Doctors.FindAsync(doctorId);
    doctor.OnCall = false;

    await _context.SaveChangesAsync();
    await tx.CommitAsync();
}

Run concurrently:

await Task.WhenAll(
    TakeOffCallAsync(1),
    TakeOffCallAsync(2));

Each transaction:

• sees two doctors on call,
• passes validation,
• updates a different row,
• commits successfully.

The invariant is violated even though Snapshot Isolation is used.

Why this happens

Snapshot Isolation detects write conflicts per row, not across related rows. The database enforces consistency of data versions, not consistency of business rules.

4.4 Hands-On: Forcing a Lost Update With a Unit Test

Reproducing concurrency failures in tests makes them easier to reason about and easier to fix. The goal is not to simulate production scale, but to force overlapping execution.

Example test

[Test]
public async Task LostUpdate_ShouldOccur_UnderConcurrentWrites()
{
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseSqlServer(TestConfig.ConnectionString)
        .Options;

    // Seed data
    await using (var seed = new AppDbContext(options))
    {
        seed.Accounts.Add(new Account { Id = 1, Balance = 100 });
        await seed.SaveChangesAsync();
    }

    var tasks = Enumerable.Range(0, 20)
        .Select(_ => Task.Run(async () =>
        {
            await using var ctx = new AppDbContext(options);

            var account = await ctx.Accounts.FindAsync(1);
            account.Balance -= 1;

            // Intentionally using Update to demonstrate the flaw
            ctx.Update(account);
            await ctx.SaveChangesAsync();
        }));

    await Task.WhenAll(tasks);

    await using var verify = new AppDbContext(options);
    var result = await verify.Accounts.FindAsync(1);

    Console.WriteLine($"Final Balance: {result.Balance}");
    Assert.AreNotEqual(80, result.Balance);
}

Without optimistic concurrency or locking, the final balance is almost never correct. The test fails nondeterministically, which mirrors real production behavior.

4.5 Quick Reference: Anomalies, Isolation Levels, and .NET Mitigations

Anomaly	Prevented By	Typical .NET Mitigation
Dirty reads	Read Committed+	Avoid NOLOCK in logic
Non-repeatable reads	Repeatable Read, Snapshot	Snapshot isolation or retries
Phantom reads	Serializable, Snapshot	Serializable, Snapshot, or explicit locking
Lost updates	Serializable, Snapshot (same row)	RowVersion + retry loop
Write skew	Serializable only	Domain-level locks or redesign

This table is the mental model to keep in mind: isolation levels prevent some anomalies, but not all. The rest must be handled explicitly in application code.

---

5 When Transactions Lie: The Illusion of Database Safety

Transactions often feel like a safety boundary: if the commit succeeds, the system must be consistent. That assumption breaks down quickly in modern systems. Databases only protect the data they manage. Anything outside that boundary—message brokers, email systems, caches, HTTP responses, even other databases—operates independently.

This section focuses on the most common ways transactions “lie”: the database reports success, but the system as a whole is now in an inconsistent or ambiguous state.

5.1 The Dual Write Problem: Updating the Database vs. Sending a Message

When an operation must update the database and publish an event to a broker such as MassTransit or Azure Service Bus, developers often reach for this pattern:

await using var tx = await _context.Database.BeginTransactionAsync();

await UpdateOrderAsync();
await _context.SaveChangesAsync();

await _bus.Publish(new OrderUpdated { Id = order.Id });
// UNSAFE: broker cannot roll back if commit fails

await tx.CommitAsync();

At first glance, this looks reasonable. The message is published “inside” the transaction, and the commit happens last. In reality, the message broker is completely unaware of the database transaction.

What can go wrong

1. Database commits, publish fails The state change exists, but no event is emitted. Downstream systems never see the update.

2. Publish succeeds, database rolls back A ghost event is published for state that never committed.

Both outcomes violate downstream assumptions and are extremely hard to clean up after the fact.

Why this happens

• SQL Server transactions cannot coordinate with message brokers.
• Cloud brokers do not support MSDTC.
• Even if they did, distributed two-phase commits destroy throughput and availability.

The practical fix: transactional outbox

Instead of publishing directly, persist the outgoing message in the same database transaction and publish it asynchronously afterward. The full implementation is covered in Section 7, but the key idea is simple: the database is the source of truth for both state changes and outgoing messages.

5.2 Post-Commit Failures: When Success Still Isn’t Success

This failure mode appears in synchronous request–response flows. The code looks correct:

await using var tx = await _context.Database.BeginTransactionAsync();

await ProcessPaymentAsync();
await _context.SaveChangesAsync();
await tx.CommitAsync();

return Ok();

The hidden assumption is:

“If CommitAsync() returned, the request succeeded.”

Now consider this sequence:

1. CommitAsync() succeeds and flushes to disk.
2. The process crashes before returning the HTTP response.
3. The client retries the request.

From the client’s perspective, the operation may or may not have happened. From the server’s perspective, the operation already committed.

Consequences

• The same operation may execute twice.
• Unique constraints or domain rules may be violated.
• External systems (payments, provisioning, notifications) may be triggered multiple times.

This is an idempotency problem

The system needs a way to distinguish “this is a retry” from “this is a new request.” Payment systems, booking engines, and provisioning workflows all solve this with an idempotency key.

Section 7.1 provides a complete implementation of the idempotent handler pattern, including schema and retry-safe handling. The important takeaway here is that post-commit crashes are normal, and correctness depends on designing for them.

5.3 Side-Effect Leakage: Why Sending Emails Inside a Transaction Is a Trap

A very common mistake is triggering side effects inside a transaction under the assumption that everything will either succeed or fail together.

await using var tx = await _context.Database.BeginTransactionAsync();

order.Status = "Confirmed";
await _context.SaveChangesAsync();

await _email.SendOrderConfirmation(order.Id); // External I/O

await tx.CommitAsync();

Failure modes

• Email sends, commit fails → customer is notified about an order that does not exist.
• Commit succeeds, process crashes before email → customer never receives confirmation.
• Slow email provider → transaction stays open longer, increasing lock contention.

The corrected pattern

Commit the database work first, then schedule side effects using an outbox or resilient messaging.

await using var tx = await _context.Database.BeginTransactionAsync();

order.Status = "Confirmed";
await _context.SaveChangesAsync();
await tx.CommitAsync();

// Side effects AFTER commit, with retry/outbox
await _outbox.ScheduleAsync(
    new SendConfirmationEmail(order.Id));

The email is now decoupled from the transaction. It can be retried, delayed, or recovered independently without corrupting database state.

5.4 Reading Your Own Writes: CQRS and the Extended “Transaction Lie”

CQRS separates write models from read models. A command writes authoritative state, publishes an event, and a projection updates later. This creates another subtle “transaction lie”: the write committed, but the system does not yet reflect the change everywhere.

This is another case where a successful transaction commit does not mean the system reflects the change—the lie extends beyond the write database into materialized views.

Example

await _orderService.CreateOrderAsync(request);

// Immediately query read model
var dto = await _readDb.Orders.FindAsync(orderId);

return dto; // May be null or stale

Why this happens

• Projections update asynchronously.
• Message delivery is not instantaneous.
• Read models trade freshness for scalability.

Practical mitigations

One simple fix is to return authoritative data directly from the write model:

var result = await _orderService.CreateOrderAsync(request);

var order = await _context.Orders.FindAsync(result.OrderId);
return new OrderDto(order);

Other strategies include versioned projections, cache invalidation, or explicitly signaling eventual consistency to clients.

Where this fits architecturally

At this point, teams typically choose between:

• Outbox + idempotency (most common)
• Sagas / compensating transactions for long-running workflows

This article focuses on the outbox and idempotency approach, but sagas are a valid alternative when workflows span multiple services and require explicit rollback behavior across steps.

---

6 Defensive Guardrails: Optimistic and Pessimistic Concurrency

Isolation levels reduce some anomalies, but they do not eliminate concurrent modification. In real systems, concurrency conflicts are normal, especially under load. Defensive guardrails—optimistic locking, pessimistic locking, and selective automation—are what turn concurrency from a source of corruption into a controlled failure mode.

This section focuses on how to apply those guardrails correctly in EF Core and SQL Server, and when to choose one approach over the other.

6.1 Optimistic Locking: Using RowVersion and Concurrency Tokens in EF Core

Optimistic locking assumes that conflicts are relatively rare. Instead of blocking access up front, the system allows concurrent work and checks at commit time whether the data changed underneath you. SQL Server supports this through the rowversion type, which EF Core maps to a concurrency token.

Entity configuration using attributes

public class Order
{
    public int Id { get; set; }
    public decimal Amount { get; set; }

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

Equivalent configuration using Fluent API

modelBuilder.Entity<Order>()
    .Property(o => o.RowVersion)
    .IsRowVersion()
    .IsConcurrencyToken();

Choose one approach:

• [Timestamp] is simple and explicit.
• Fluent API is preferred if you want to keep domain entities free of persistence attributes.

Both result in the same behavior.

Why this works

EF Core includes the original RowVersion value in the UPDATE predicate:

UPDATE Orders
SET Amount = @Amount
WHERE Id = @Id
  AND RowVersion = @OriginalRowVersion;

If another transaction modified the row, zero rows are updated. EF Core detects this and throws DbUpdateConcurrencyException. No blocking occurs, and the conflict is detected precisely at the write boundary.

Optimistic locking works best when:

• conflicts are infrequent (often < ~5%)
• retries are cheap
• invariants are local to a single row
• low latency matters more than immediate exclusivity

It also aligns well with retry-based resilience and distributed workflows.

6.2 Handling DbUpdateConcurrencyException: Retry, Merge, or Abort

Detecting a conflict is only half the problem. The real work is deciding what to do next. EF Core raises DbUpdateConcurrencyException when an update affects zero rows due to a version mismatch.

Pattern 1: Automatic retry (corrected)

Retries work when updates are additive or commutative. The important detail is that the stale entity must be discarded before retrying.

public async Task UpdateAmountWithRetry(int orderId, decimal delta)
{
    for (int attempt = 0; attempt < 3; attempt++)
    {
        var order = await _context.Orders.FindAsync(orderId);
        order.Amount += delta;

        try
        {
            await _context.SaveChangesAsync();
            return;
        }
        catch (DbUpdateConcurrencyException)
        {
            // Detach stale entity so the next iteration re-reads fresh state
            _context.Entry(order).State = EntityState.Detached;
        }
    }

    throw new InvalidOperationException("Failed after concurrent update retries.");
}

Without detaching (or recreating the DbContext), the retry would reuse the same stale RowVersion and fail again.

Pattern 2: “User-wins” merge

Some scenarios require merging changes instead of retrying blindly.

catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();

    var mergedValues = ResolveConflict(
        entry.CurrentValues,
        databaseValues);

    entry.OriginalValues.SetValues(databaseValues);
    entry.CurrentValues.SetValues(mergedValues);

    await _context.SaveChangesAsync();
}

This pattern is appropriate for collaborative editing or workflows where multiple actors update different fields. It requires explicit merge logic and careful testing.

Pattern 3: Abort and prompt

When conflicts affect pricing, inventory, or financial correctness, aborting and asking the caller to retry intentionally is often the safest option.

Rule of thumb:

• Independent changes → retry
• Semantically conflicting changes → merge or abort
• Never ignore concurrency exceptions

6.3 Pessimistic Locking: Using UPDLOCK, ROWLOCK, and SQL Server Hints

Optimistic locking detects conflicts after the fact. Pessimistic locking prevents them up front by acquiring exclusive access. SQL Server supports this through locking hints, which should be used sparingly.

Acquiring an update lock safely

var item = await _context.Items
    .FromSqlRaw(
        "SELECT * FROM Items WITH (UPDLOCK, ROWLOCK) WHERE Id = {0}",
        id)
    .SingleAsync();

UPDLOCK ensures that:

• other writers cannot update the row
• readers cannot later escalate to conflicting locks

ROWLOCK reduces the risk of lock escalation.

Note: FromSqlRaw with positional parameters is safe. If interpolating values directly, prefer FromSqlInterpolated to avoid accidental SQL injection if the pattern is copied incorrectly.

Example: atomic inventory reservation

var item = await _context.Items
    .FromSqlRaw(
        "SELECT * FROM Items WITH (UPDLOCK, HOLDLOCK) WHERE Id = {0}",
        id)
    .SingleAsync();

if (item.Available <= 0)
    return false;

item.Available--;
await _context.SaveChangesAsync();
return true;

HOLDLOCK approximates serializable behavior for that key range, preventing phantoms while the transaction is open.

Use pessimistic locking when:

• conflicts are frequent
• retries are expensive or unsafe
• the invariant is safety-critical (payments, capacity limits)

6.4 Interceptors and Automatic Lock Injection (With Caveats)

EF Core interceptors allow you to influence generated SQL centrally. This can be useful for enforcing locking behavior without scattering raw SQL across the codebase.

Corrected interceptor example for SQL Server

public class UpdateLockInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        if (ShouldApplyLock(eventData) &&
            command.CommandText.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
        {
            // Simplified example: append locking hints
            command.CommandText = command.CommandText
                .Replace("FROM", "FROM WITH (UPDLOCK, ROWLOCK)");
        }

        return base.ReaderExecuting(command, eventData, result);
    }
}

Important caveats

• SQL Server does not support FOR UPDATE; it uses WITH (UPDLOCK, …).
• Modifying CommandText directly is fragile.
• This simplified approach works only for straightforward SELECT statements.
• Production implementations should parse SQL properly or constrain usage to known query shapes.

Registration

services.AddDbContext<AppDbContext>(options =>
    options.AddInterceptors(new UpdateLockInterceptor()));

Interceptors are powerful, but they require discipline. Treat them as infrastructure code, not a shortcut for avoiding explicit design decisions.

Choosing Between Optimistic and Pessimistic Concurrency

A simple decision framework:

Situation	Prefer
Conflict rate < ~5%	Optimistic
Retries are cheap	Optimistic
High contention	Pessimistic
Safety-critical invariant	Pessimistic
User-facing latency sensitive	Optimistic
Limited capacity or stock	Pessimistic

Neither approach is “better” universally. The goal is to make concurrency behavior explicit, predictable, and aligned with business risk.

---

7 Engineering for Resilience: Idempotency and the Outbox

Resilience is not about avoiding failures. It is about designing systems where retries, duplicates, and partial execution do not corrupt state. Databases alone cannot provide this guarantee because message brokers, HTTP clients, background workers, and schedulers all operate outside a single transactional boundary.

Idempotency, transactional outboxes, deterministic identifiers, and disciplined retry policies are the tools that close this gap. Together, they turn unreliable execution into predictable behavior.

7.1 The Idempotent Handler Pattern: Preventing Double-Processing Safely

Message brokers such as MassTransit or Azure Service Bus may deliver the same message more than once. Network retries, broker restarts, and consumer crashes make duplicate delivery normal, not exceptional. The handler must therefore guarantee exactly-once effects, even if execution happens more than once.

Schema for idempotency tracking

CREATE TABLE MessageConsumers
(
    MessageId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    ProcessedAt DATETIME2 NOT NULL
);

The primary key is critical. It is what turns race conditions into safe failures.

Correct, race-safe handler pattern

A naïve “check then insert” approach is not atomic. Two concurrent handlers can both pass the existence check before either inserts. The fix is to rely on the database’s uniqueness constraint and execute the logic inside a transaction.

public async Task Handle(OrderPlaced message)
{
    await using var tx = await _context.Database.BeginTransactionAsync();

    try
    {
        // Record processing intent first
        _context.MessageConsumers.Add(new MessageConsumer
        {
            MessageId = message.Id,
            ProcessedAt = DateTime.UtcNow
        });

        await _context.SaveChangesAsync();

        // Business logic executes only if insert succeeded
        await ProcessOrderAsync(message);

        await tx.CommitAsync();
    }
    catch (DbUpdateException)
    {
        // Unique constraint violation means message was already processed
        await tx.RollbackAsync();
    }
}

Here, idempotency is enforced by the database, not timing assumptions. If two handlers race, one insert succeeds and the other fails cleanly.

7.2 Implementing the Transactional Outbox

The transactional outbox eliminates the dual-write problem by storing outbound messages in the same transaction as domain changes. Publishing becomes a separate concern handled asynchronously.

Outbox table example

CREATE TABLE OutboxMessages
(
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    Type NVARCHAR(200) NOT NULL,
    Payload NVARCHAR(MAX) NOT NULL,
    CreatedAt DATETIME2 NOT NULL,
    Sent BIT NOT NULL DEFAULT 0
);

Writing to the outbox atomically

await using var tx = await _context.Database.BeginTransactionAsync();

order.Status = "Completed";
await _context.SaveChangesAsync();

_context.OutboxMessages.Add(new OutboxMessage
{
    Id = Guid.NewGuid(),
    Type = nameof(OrderCompleted),
    Payload = JsonSerializer.Serialize(new OrderCompleted(order.Id)),
    CreatedAt = DateTime.UtcNow
});

await _context.SaveChangesAsync();
await tx.CommitAsync();

At this point, both the state change and the intent to publish are durable.

Background dispatcher (with important caveat)

public async Task DispatchAsync()
{
    var messages = await _context.OutboxMessages
        .Where(m => !m.Sent)
        .OrderBy(m => m.CreatedAt)
        .ToListAsync();

    foreach (var msg in messages)
    {
        await _bus.Publish(msg.ToEvent());
        msg.Sent = true;
    }

    await _context.SaveChangesAsync();
}

If the dispatcher crashes after publishing but before setting Sent = true, the message may be published again. This is expected. The system relies on consumer idempotency (Section 7.1) to handle duplicates safely.

For higher guarantees, production systems often use an UPDATE … OUTPUT pattern or row-level locking to atomically claim messages before publishing.

Using MassTransit’s built-in outbox

Frameworks can remove much of this boilerplate. With MassTransit and EF Core:

cfg.AddEntityFrameworkOutbox<AppDbContext>(o =>
{
    o.UseSqlServer();
    o.QueryDelay = TimeSpan.FromSeconds(1);
});

This integrates outbox persistence and dispatch directly into the messaging pipeline, reducing the risk of custom implementation errors.

7.3 Deterministic Identifiers for Idempotent Requests

When clients retry HTTP requests, the server needs a stable identifier to recognize duplicates. This is commonly called an idempotency key. Deterministic identifiers ensure that retries map to the same logical operation.

Important note on hashing

Using MD5 directly is not recommended. It is cryptographically broken and flagged by security scanners. Additionally, new Guid(hash) does not produce a proper UUIDv5—it simply packs bytes into a GUID.

Below is a simplified example using SHA-256 and truncation. For strict RFC 4122 compliance, a dedicated UUIDv5 implementation should be used.

Simplified deterministic GUID example

public static Guid CreateDeterministic(string input)
{
    using var sha = SHA256.Create();
    var hash = sha.ComputeHash(Encoding.UTF8.GetBytes(input));

    Span<byte> guidBytes = stackalloc byte[16];
    hash.AsSpan(0, 16).CopyTo(guidBytes);

    return new Guid(guidBytes);
}

Applying the pattern

var requestId = CreateDeterministic($"{userId}:{clientProvidedKey}");

var existing = await _context.Requests.FindAsync(requestId);
if (existing != null)
    return existing.Response;

var result = await ProcessAsync();

_context.Requests.Add(new RequestLog(requestId, result));
await _context.SaveChangesAsync();

return result;

This ensures:

• retries return the same result
• side effects execute only once
• duplicate requests are safe by construction

7.4 Polly Resilience Pipelines: Targeted, Safe Retries

Retries are powerful, but only when they are selective. Retrying every database exception is dangerous and can amplify failures.

Refined retry configuration

builder.Services.AddResiliencePipeline("db", pipeline =>
{
    pipeline.AddRetry(new RetryStrategyOptions
    {
        MaxRetryAttempts = 3,
        BackoffType = DelayBackoffType.Exponential,
        Delay = TimeSpan.FromMilliseconds(100),
        ShouldHandle = args =>
        {
            if (args.Outcome.Exception is DbUpdateConcurrencyException)
                return true;

            if (args.Outcome.Exception is SqlException sql)
            {
                return sql.Number switch
                {
                    1205 => true,   // Deadlock
                    -2   => true,   // Timeout
                    _    => false
                };
            }

            return false;
        }
    });
});

Only transient, retry-safe failures are retried. Constraint violations, syntax errors, and permission issues fail fast.

Correct usage with modern Polly APIs

Polly v8 pipelines expect a Func<CancellationToken, ValueTask>:

public class OrderService
{
    private readonly ResiliencePipeline _pipeline;

    public OrderService(ResiliencePipelineProvider provider)
        => _pipeline = provider.GetPipeline("db");

    public ValueTask SaveAsync(Func<CancellationToken, ValueTask> action, CancellationToken ct)
        => _pipeline.ExecuteAsync(action, ct);
}

Why this matters

Retries must:

• be intentional
• be bounded
• work with concurrency tokens and idempotency
• never hide real bugs

When combined with optimistic concurrency and idempotent handlers, retry pipelines turn transient failures into routine, recoverable events.

---

8 The Architect’s Decision Matrix: Choosing Your Strategy

Isolation levels, concurrency control, and state management are not implementation details. They shape how a system behaves under load, during failures, and across deployments. The challenge for architects and tech leads is not knowing what tools exist, but knowing when each tool is appropriate.

This section turns the earlier theory and failure modes into concrete decision guidance you can apply during design and code review.

8.1 When to Use Snapshot vs. Read Committed

Most confusion around transactions in .NET comes down to picking the wrong isolation level for the problem. Rather than thinking in terms of “stronger is better,” it helps to match isolation to the shape of the workload.

Practical decision matrix

Scenario	Recommended Isolation	Concurrency Strategy
Simple CRUD, low contention	Read Committed (RCSI)	Optimistic + retry
Multi-row invariants	Snapshot	Optimistic + write-skew guard
Financial or safety-critical single-row updates	Read Committed + UPDLOCK	Pessimistic
Reporting alongside OLTP	Snapshot	Read-only, no locks

How to read this table

• Read Committed (RCSI) works best when operations are short, conflicts are rare, and correctness can be enforced with retries.
• Snapshot Isolation is appropriate when multiple reads must agree with each other, but occasional write conflicts are acceptable.
• Pessimistic locking is reserved for cases where retries are dangerous or invariants must never be violated, even temporarily.

Many mature systems use a hybrid approach: Read Committed by default, Snapshot for specific workflows, and targeted pessimistic locks only where the business risk justifies them.

8.2 Designing for “Statelessness”: Moving Logic Out of Locks

Locking scales poorly because it assumes coordination through blocking. Modern systems scale by assuming retries, duplicates, and reordering—and making those safe.

Designing for statelessness does not mean avoiding state. It means avoiding implicit state tied to timing and locks.

Core principles

1. Model operations as repeatable commands, not one-time actions.
2. Record intent, not just the final outcome.
3. Assume retries will happen and design for them.
4. Detect conflicts explicitly instead of blocking preemptively.
5. Push invariants into domain logic, not lock timing.

Correct example: replacing pessimistic locks with optimistic concurrency

Instead of:

SELECT * FROM Inventory WITH (UPDLOCK) WHERE Id = @id;

Use explicit versioning with a concurrency token.

public class InventoryItem
{
    public int Id { get; set; }
    public int Available { get; set; }

    [ConcurrencyCheck]
    public int Version { get; set; }
}

Then update with retries:

await _pipeline.ExecuteAsync(async ct =>
{
    var item = await _context.Items.FindAsync(new object[] { id }, ct);

    if (item.Available <= 0)
        throw new InvalidOperationException("Out of stock");

    item.Available--;
    item.Version++;

    await _context.SaveChangesAsync(ct);
});

Here, the version field is meaningful because EF Core enforces it as a concurrency token. Conflicts are detected, not overwritten, and retries become safe.

This approach trades blocking for explicit conflict handling, which scales better under load and integrates naturally with idempotency and retries.

8.3 Benchmarking the Cost of Isolation

Isolation is not free. It affects latency, throughput, memory usage, and operational stability. Measuring these costs is the only way to make informed trade-offs.

Metrics worth tracking

• Average and p95 transaction latency
• Deadlock frequency
• Lock wait time
• TempDB version store growth (Snapshot, RCSI)
• Retry counts from optimistic concurrency

Tooling that helps in .NET environments

• BenchmarkDotNet for micro-benchmarks around transaction scopes and EF Core operations.
• Application Insights or OpenTelemetry for production latency, retries, and dependency failures.
• EF Core DbCommandInterceptor to capture query durations and lock-heavy statements.
• Load generators like wrk for coarse-grained concurrency testing.

wrk -t5 -c50 -d30s http://localhost/api/update

Typical patterns observed

• Serializable often multiplies latency under contention.
• Repeatable Read degrades throughput due to long-lived locks.
• Snapshot improves read concurrency but shifts cost to TempDB and retries.
• Read Committed (RCSI) offers the best throughput but tolerates anomalies.

Benchmarks turn isolation from a philosophical debate into a data-backed decision.

8.4 Final Checklist: Questions for Transactional Code Reviews

1. What isolation level does this code actually run under? Defaults differ across environments, especially with RCSI enabled.

2. Are any external side effects executed inside the transaction? If so, how are partial failures handled?

3. How does this code prevent or detect lost updates? Look for concurrency tokens, explicit locks, or retries.

4. Is this operation safe to execute more than once? If not, idempotency is missing.

5. What happens during deployment or failover? Azure SQL failovers abort in-flight transactions. Does the code handle transient errors like connection resets or SQL error 40613?

6. Are failures observable and actionable? Silent retries or swallowed exceptions hide correctness bugs until they become data corruption.

These questions catch most transactional bugs long before they reach production.

Closing Thoughts

ACID is a tool, not a guarantee. Atomicity, consistency, and durability usually behave as expected, but isolation is a spectrum with real trade-offs. SQL Server and .NET give you powerful primitives, but they do not enforce correctness for you.

Reliable systems are built by acknowledging where transactions stop helping—and designing explicitly for the gaps. When isolation levels, concurrency control, idempotency, and retries are treated as architectural choices instead of defaults, transactions stop lying and start doing exactly what you expect.