Skip to Content
Cloud Engineering Academy
6. Databases & Data Architecture

Transactions, isolation levels and consistency

Compare SQL Server locking and snapshot isolation with PostgreSQL MVCC and DynamoDB read consistency, then write .NET code that moves money correctly under concurrency on RDS, Aurora and DynamoDB.

advanced⏱ 2 h 🟢 FreeSAA-C03DVA-C03DEA-C01

Assumes: .NET 10 LTS · Npgsql 10.0.3 · EF Core 10 · AWS SDK for .NET v4 (AWSSDK.DynamoDBv2 4.1.0.2) · PostgreSQL 17 · Region us-east-1. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this lesson you can:

  • Explain the four ANSI isolation levels and the anomalies each one allows (dirty read, non-repeatable read, phantom, lost update, write skew) in plain words.
  • Compare how SQL Server (locking and row versioning), PostgreSQL on RDS/Aurora (MVCC) and DynamoDB (item-level atomicity, eventual vs strong reads) behave under the same concurrent workload.
  • Predict what a reader sees on an RDS read replica, an Aurora reader, a DynamoDB eventually consistent read and a global table replica.
  • Write a .NET 10 transfer that never loses an update, using three techniques: an atomic conditional UPDATE, pessimistic SELECT … FOR UPDATE, and SERIALIZABLE with a retry loop.
  • Choose the right technique for a banking workload and defend the choice in a design review.

Prerequisites

  • Module 6 overview — the database family and where each store fits
  • The earlier lessons in this module on RDS, Aurora and EF Core (listed in the module overview)
  • Docker Desktop on Windows, the .NET 10 SDK, and optionally the AWS CLI v2 with the learn-sandbox profile
  • Comfortable with BEGIN TRAN, COMMIT, ROLLBACK and SqlTransaction from your day job

Why this matters in banking

Harbourline Bank runs a "pay a friend" feature in its mobile app. Two requests arrive within the same millisecond for the same current account: a £60 card payment and a £50 transfer. The balance is £100. Both requests read £100, both decide "enough money", both write their new balance. One write overwrites the other. The customer has spent £110 from £100 and the ledger shows only one of the movements. That is a lost update, and it is the most common concurrency defect in banking code.

On SQL Server you may never have seen it, because the default locking behaviour and the way your team wrote stored procedures (UPDLOCK, HOLDLOCK, UPDATE … WHERE Balance >= @Amount) hid it. When the same code moves to PostgreSQL on Amazon RDS or Aurora, the rules change subtly: readers never block writers, REPEATABLE READ means something different, and SERIALIZABLE fails transactions instead of waiting. When part of the workload moves to DynamoDB, there are no multi-statement sessions at all, and a plain read may return data that is a fraction of a second old.

Auditors and regulators do not care which database you chose. They care that the ledger balances. This lesson gives you a precise vocabulary and tested code so the ledger balances on every AWS store you use.

The idea in plain language

A transaction is a group of changes that succeed or fail together. ACID describes the promise:

  • Atomicity — all or nothing.
  • Consistency — the database moves from one valid state to another (constraints hold).
  • Isolation — concurrent transactions do not see each other's half-finished work, to a degree you choose.
  • Durability — once committed, it survives a crash.

Isolation is the dial. Turn it all the way up (serializable) and every transaction behaves as if it ran alone, but you pay in waiting or retries. Turn it down and you get more throughput but must accept certain anomalies.

Consistency in distributed databases means something else: "if I read right after a write, do I see the write?" A replica that is 50 milliseconds behind gives you eventual consistency: it will catch up, but not instantly.

Analogy: A bank branch with one ledger book. Serializable means only one clerk may hold the book at a time. Read committed means clerks may glance at the book while someone else is writing, but only at lines that are already inked. Snapshot means every clerk gets a photocopy of the book as it was when they started; when they hand back their changes, the head clerk checks nobody else changed the same lines. A read replica is a copy of the book in another branch, couriered over every few seconds.

The anomalies, defined once

AnomalyWhat happensBanking example
Dirty readYou read another transaction's uncommitted change, which is then rolled backA balance check sees a deposit that later fails
Non-repeatable readYou read the same row twice in one transaction and get different valuesA statement job reads the balance at the start and end and they disagree
PhantomYou run the same range query twice and new rows appearSum of today's transactions changes during the reconciliation
Lost updateTwo transactions read, compute and write the same row; one write disappearsThe £110-from-£100 story above
Write skewTwo transactions read overlapping data, each writes a different row, and together they break a rule neither broke aloneJoint account rule "combined balance must stay ≥ 0": both holders withdraw from their own sub-account at once

What problem each AWS store solves — and its limits

StoreUnit of atomicityDefault isolationStrongest optionSharp edges
RDS for SQL ServerMulti-statement transactionREAD COMMITTED with shared locks (RCSI off unless you turn it on)SERIALIZABLE (key-range locks) or SNAPSHOTBlocking and deadlocks; RCSI changes behaviour silently
RDS for PostgreSQL / Aurora PostgreSQLMulti-statement transactionREAD COMMITTED with MVCCSERIALIZABLE (Serializable Snapshot Isolation, SSI)Serialization failures (SQLSTATE 40001) must be retried; long transactions bloat tables
RDS / Aurora read replicasRead-onlyWhatever the replica applies, asynchronously—Replica lag; SERIALIZABLE is not available on a PostgreSQL hot standby
DynamoDBSingle item (or up to 100 items with TransactWriteItems)Eventually consistent reads by defaultStrongly consistent reads on the base table; serializable transactionsNo strong reads on global secondary indexes; transactions are not cross-Region

The important honesty point: no AWS service makes your code correct for you. RDS and Aurora give you the same engine semantics as PostgreSQL or SQL Server anywhere else. Multi-AZ, failover and replicas add new consistency questions on top.

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
SQL Server READ COMMITTED (locking)PostgreSQL READ COMMITTED (MVCC) on RDS/AuroraPostgreSQL readers never take shared locks, so they never block writers. Each statement sees a fresh snapshot
READ_COMMITTED_SNAPSHOT ON (RCSI) — the default in Azure SQL DatabasePostgreSQL READ COMMITTEDClosest match. Code tested on Azure SQL Database usually behaves similarly on PostgreSQL
SNAPSHOT isolation (ALLOW_SNAPSHOT_ISOLATION ON)PostgreSQL REPEATABLE READBoth are snapshot isolation; both raise an update-conflict error instead of overwriting (SQL Server error 3960, PostgreSQL 40001)
SERIALIZABLE with key-range locksPostgreSQL SERIALIZABLE (SSI)SQL Server waits (and may deadlock); PostgreSQL lets you run and then aborts one transaction at commit or on conflict
WITH (UPDLOCK, ROWLOCK)SELECT … FOR UPDATESame intent: lock the row you will change
rowversion / [Timestamp] byte[] in EF CorePostgreSQL xmin system column mapped as [Timestamp] uint in NpgsqlNo extra column needed in PostgreSQL
Always On readable secondaryRDS read replica / Aurora readerAsynchronous; you must design for lag
Cosmos DB consistency levels (Strong, Bounded staleness, Session, Consistent prefix, Eventual)DynamoDB: eventually consistent or strongly consistent reads per requestOnly two choices per read in DynamoDB; strong reads cost twice as much and only on the base table
TransactionScope with MSDTCNo distributed transactions across RDS and DynamoDBUse the outbox pattern or idempotent steps instead (Module 8)

Architecture

How a Harbourline payment touches several consistency boundaries:

Read-your-writes rule: anything a customer just changed (their balance after a payment, a new beneficiary) is read from the writer or with a strongly consistent read. Reports, statements and dashboards may use replicas and eventually consistent reads.

Security-boundary view

Isolation is also a security control: a dirty read can leak data that was never meant to exist (a rolled-back credit), and a lost update is a financial integrity failure. Integrity requirements in PCI DSS and the GDPR principle of accuracy both depend on it. The controls are in three places:

Never rely on isolation alone: put a CHECK constraint on the balance and reconcile daily.

How each engine actually behaves

SQL Server (on RDS or anywhere)

  • READ COMMITTED (default, RCSI off): readers take short shared locks. A reader waits for a writer holding an exclusive lock. No dirty reads; non-repeatable reads, phantoms and lost updates are possible.
  • With READ_COMMITTED_SNAPSHOT ON, readers read the last committed version from tempdb's version store instead of waiting. On RDS for SQL Server you can turn it on with ALTER DATABASE … SET READ_COMMITTED_SNAPSHOT ON; it needs exclusive access to the database while it switches.
  • SNAPSHOT: the whole transaction sees the data as of its start. If you try to update a row someone else changed after your start, you get error 3960 and the transaction rolls back.
  • SERIALIZABLE: range locks stop phantoms. Correct, but prone to blocking and deadlocks (error 1205).

PostgreSQL (RDS for PostgreSQL and Aurora PostgreSQL)

PostgreSQL uses multi-version concurrency control (MVCC): an UPDATE writes a new row version and leaves the old one for transactions that still need it. VACUUM later cleans up.

  • READ UNCOMMITTED is accepted but behaves as READ COMMITTED. There are no dirty reads in PostgreSQL.
  • READ COMMITTED (default): each statement sees a snapshot taken when the statement started. An UPDATE that finds a row changed by a concurrent committed transaction re-checks its WHERE clause against the new version. That re-check is what makes UPDATE accounts SET balance = balance - 60 WHERE account_id = … AND balance >= 60 safe.
  • REPEATABLE READ: snapshot isolation for the whole transaction. In PostgreSQL it also prevents phantoms. Updating a row changed since your snapshot raises 40001 could not serialize access due to concurrent update.
  • SERIALIZABLE: snapshot isolation plus tracking of read/write dependencies. It detects write skew and aborts one transaction with 40001. You must retry.

Aurora PostgreSQL uses the PostgreSQL engine with a different storage layer. Isolation semantics on the writer are the same as community PostgreSQL. Aurora readers are hot standbys: they serve read-only queries, and SERIALIZABLE is not available there.

DynamoDB

  • Every single-item write (PutItem, UpdateItem, DeleteItem) is atomic, and a condition expression is evaluated atomically with it. That is DynamoDB's answer to the lost update.
  • Reads are eventually consistent by default. Set ConsistentRead = true for a strongly consistent read of the base table or a local secondary index. Global secondary indexes support only eventually consistent reads.
  • An eventually consistent read costs half a read capacity unit per 4 KB; a strongly consistent read costs one.
  • TransactWriteItems groups up to 100 actions, at most 4 MB in total, all-or-nothing, in one account and one Region. Transactions are serializable with respect to single-item reads and writes, but Query, Scan and BatchGetItem see them only as read-committed. Lesson 11, DynamoDB for banking correctness, goes deep.
  • Global tables replicate asynchronously by default (multi-Region eventual consistency); transactions are atomic only in the Region where they ran. AWS also offers a multi-Region strong consistency mode for global tables ⚠ VERIFY its current Region and feature constraints before designing around it.

Replicas and failover

TopologyReplicationWhat a reader may see
RDS Multi-AZ (one standby)Synchronous to a standby that serves no readsNot applicable — you cannot read the standby
RDS Multi-AZ DB cluster (two readable standbys)Semi-synchronous; standbys serve readsSmall lag on reader endpoint
RDS read replicaAsynchronousSeconds of lag under load; more during heavy writes
Aurora replicasShared storage; readers apply changes from the writer asynchronouslyTypically well under a second (AWS describes lag as usually under 100 ms ⚠ VERIFY for your workload)
DynamoDB eventually consistent readInternal replicasUsually up to date within a second

Step-by-step implementation

You will reproduce a lost update on PostgreSQL in Docker, then fix it three ways.

Start PostgreSQL 17 locally

The same engine major version is available on RDS and Aurora, so the behaviour you see here is the behaviour you get there.

docker run --name hl-m06-pg -e POSTGRES_PASSWORD=LocalOnly_ChangeMe1 -p 5432:5432 -d postgres:17

Create the schema

schema.sql
CREATE TABLE accounts (
  account_id  text PRIMARY KEY,
  currency    char(3) NOT NULL,
  balance     numeric(18,2) NOT NULL,
  CONSTRAINT balance_not_negative CHECK (balance >= 0)
);

CREATE TABLE ledger_entries (
  entry_id        bigserial PRIMARY KEY,
  account_id      text NOT NULL REFERENCES accounts(account_id),
  amount          numeric(18,2) NOT NULL,
  idempotency_key text NOT NULL UNIQUE,
  created_at      timestamptz NOT NULL DEFAULT now()
);

INSERT INTO accounts VALUES ('ACC-0000000001', 'GBP', 100.00),
                            ('ACC-0000000002', 'GBP', 0.00);
Get-Content schema.sql | docker exec -i hl-m06-pg psql -U postgres

The CHECK constraint is the safety net: even buggy code cannot drive the balance below zero.

Reproduce the problem in two psql sessions

Open two terminals with docker exec -it hl-m06-pg psql -U postgres.

TimeSession ASession B
t1BEGIN; SELECT balance FROM accounts WHERE account_id='ACC-0000000001'; → 100.00
t2BEGIN; same SELECT → 100.00
t3UPDATE accounts SET balance = 40.00 WHERE account_id='ACC-0000000001'; (app computed 100 − 60)
t4UPDATE accounts SET balance = 50.00 WHERE account_id='ACC-0000000001'; — waits for A's row lock
t5COMMIT;the update proceeds and writes 50.00
t6COMMIT; — final balance 50.00; £60 payment lost

The CHECK constraint did not help, because 50 is a valid number. The bug is the read-compute-write pattern.

Fix 1 — compute in the database with a guard

Replace the read-compute-write with one statement:

UPDATE accounts
SET balance = balance - 60.00
WHERE account_id = 'ACC-0000000001' AND balance >= 60.00
RETURNING balance;

Repeat the two-session test with -60 and -50. Session B waits, then re-evaluates its WHERE clause against A's committed row (balance 40), finds 40 >= 50 false, and updates zero rows. Your code treats "0 rows" as "insufficient funds".

Fix 2 — lock the row first (pessimistic)

When the decision needs more than one row or complex logic, lock what you will change:

BEGIN;
SELECT balance FROM accounts WHERE account_id = 'ACC-0000000001' FOR UPDATE;
-- application logic decides
UPDATE accounts SET balance = 40.00 WHERE account_id = 'ACC-0000000001';
COMMIT;

Session B's SELECT … FOR UPDATE waits until A commits, then sees 40.00. Lock accounts in a consistent order (for example, ascending account_id) to avoid deadlocks in transfers.

Fix 3 — SERIALIZABLE and retry

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE account_id = 'ACC-0000000001';
UPDATE accounts SET balance = 40.00 WHERE account_id = 'ACC-0000000001';
COMMIT;

Run in both sessions. The second one to write gets ERROR: could not serialize access due to concurrent update (SQLSTATE 40001). The application must roll back and run the whole transaction again from the start. The .NET code below does that.

AWS Console instructions

You do not need AWS for the exercises, but these are the places where isolation and consistency settings live:

  • RDS for SQL Server RCSI: not a console setting. Connect with SSMS or sqlcmd and run ALTER DATABASE [HarbourlineLedger] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE; in a maintenance window.
  • PostgreSQL default isolation: Console → RDS → Parameter groups → Create parameter group (family postgres17 or aurora-postgresql17), then edit default_transaction_isolation. Leave it at read committed unless you have a strong reason; set isolation per transaction in code instead.
  • Replica lag: Console → RDS → Databases → (your replica) → Monitoring and look at ReplicaLag (RDS) or AuroraReplicaLag (Aurora).
  • DynamoDB: there is no table-level consistency setting. In Console → DynamoDB → Explore items, the query panel has a Run with strong consistency option ⚠ VERIFY the exact label in the current console.

AWS CLI

# Read an item with the default (eventually consistent) read.
aws dynamodb get-item --table-name hl-m06-accounts \
  --key '{"pk":{"S":"ACC#ACC-0000000001"},"sk":{"S":"PROFILE"}}' \
  --profile learn-sandbox --region us-east-1

# The same read, strongly consistent (costs 1 RCU per 4 KB instead of 0.5).
aws dynamodb get-item --table-name hl-m06-accounts \
  --key '{"pk":{"S":"ACC#ACC-0000000001"},"sk":{"S":"PROFILE"}}' \
  --consistent-read --return-consumed-capacity TOTAL \
  --profile learn-sandbox --region us-east-1

# Check Aurora replica lag for the last hour (milliseconds).
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name AuroraReplicaLag \
  --dimensions Name=DBInstanceIdentifier,Value=hl-m06-aurora-reader-1 \
  --start-time "$(date -u -d '-1 hour' +%Y-%m-%dT%H:%M:%SZ)" --end-time "$(date -u +%Y-%m-%dT%H:%M:%SZ)" \
  --period 300 --statistics Maximum --profile learn-sandbox --region us-east-1

# Create a parameter group so you can inspect (not change) the default isolation.
aws rds describe-engine-default-parameters --db-parameter-group-family postgres17 \
  --query "EngineDefaults.Parameters[?ParameterName=='default_transaction_isolation']" \
  --profile learn-sandbox --region us-east-1

The DynamoDB commands need a table from the next lesson; the describe-engine-default-parameters call is read-only and free.

Infrastructure as Code

Isolation is mostly a code decision. What IaC should control is the guardrail: a CloudWatch alarm on replica lag, so readers that tolerate "a little stale" never silently become "very stale".

ReplicaLagAlarmStack.cs
using Amazon.CDK;
using Amazon.CDK.AWS.CloudWatch;
using Constructs;

namespace Harbourline.Infra;

public sealed class ReplicaLagAlarmStack : Stack
{
    public ReplicaLagAlarmStack(Construct scope, string id, IStackProps? props = null)
        : base(scope, id, props)
    {
        var lag = new Metric(new MetricProps
        {
            Namespace = "AWS/RDS",
            MetricName = "AuroraReplicaLag",
            DimensionsMap = new Dictionary<string, string>
            {
                ["DBInstanceIdentifier"] = "hl-m06-aurora-reader-1"
            },
            Statistic = "Maximum",
            Period = Duration.Minutes(1)
        });

        _ = new Alarm(this, "ReplicaLagHigh", new AlarmProps
        {
            AlarmName = "hl-m06-aurora-replica-lag-high",
            AlarmDescription = "Statement reads may be stale beyond the 2 s business tolerance",
            Metric = lag,
            Threshold = 2000, // milliseconds
            EvaluationPeriods = 5,
            DatapointsToAlarm = 3,
            ComparisonOperator = ComparisonOperator.GREATER_THAN_THRESHOLD,
            TreatMissingData = TreatMissingData.BREACHING
        });

        Tags.SetTag("Project", "cea-lab");
        Tags.SetTag("Lab", "m06-isolation");
        Tags.SetTag("Environment", "learn");
    }
}

TreatMissingData = breaching is deliberate: if the reader stops reporting, you want to know.

Complete code

A console app that runs the same transfer three ways against the Docker PostgreSQL from the steps above, with 50 concurrent transfers, and proves the final balance is correct.

Harbourline.Ledger.Concurrency.csproj
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
    <RootNamespace>Harbourline.Ledger.Concurrency</RootNamespace>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Npgsql" Version="10.0.3" />
  </ItemGroup>
</Project>
Program.cs
using System.Data;
using Npgsql;

namespace Harbourline.Ledger.Concurrency;

public static class Program
{
    // Local Docker only. On AWS, build this from Secrets Manager or IAM auth (lesson on encryption and access).
    private const string ConnectionString =
        "Host=localhost;Port=5432;Username=postgres;Password=LocalOnly_ChangeMe1;Database=postgres;Maximum Pool Size=60";

    public static async Task<int> Main(string[] args)
    {
        var mode = args.FirstOrDefault() ?? "atomic";
        await using var dataSource = NpgsqlDataSource.Create(ConnectionString);
        var ledger = new LedgerService(dataSource);

        await ResetAsync(dataSource, startingBalance: 1000.00m);

        // 50 concurrent debits of £30 against £1,000: exactly 33 should succeed (33 × 30 = 990).
        var tasks = Enumerable.Range(1, 50).Select(i => ledger.DebitAsync(
            accountId: "ACC-0000000001",
            amount: 30.00m,
            idempotencyKey: $"demo-{mode}-{i:D3}",
            mode: mode));

        var results = await Task.WhenAll(tasks);
        var balance = await ledger.GetBalanceAsync("ACC-0000000001");
        var succeeded = results.Count(r => r == DebitResult.Posted);

        Console.WriteLine($"Mode={mode} posted={succeeded} rejected={results.Count(r => r == DebitResult.InsufficientFunds)} balance={balance:0.00}");
        var expected = 1000.00m - succeeded * 30.00m;
        Console.WriteLine(balance == expected ? "Ledger consistent ✔" : $"LOST UPDATE ✘ expected {expected:0.00}");
        return balance == expected ? 0 : 1;
    }

    private static async Task ResetAsync(NpgsqlDataSource ds, decimal startingBalance)
    {
        await using var cmd = ds.CreateCommand(
            "DELETE FROM ledger_entries; UPDATE accounts SET balance = $1 WHERE account_id = 'ACC-0000000001';");
        cmd.Parameters.AddWithValue(startingBalance);
        await cmd.ExecuteNonQueryAsync();
    }
}

public enum DebitResult { Posted, InsufficientFunds, Duplicate }

public sealed class LedgerService(NpgsqlDataSource dataSource)
{
    private const int MaxAttempts = 5;

    public Task<DebitResult> DebitAsync(string accountId, decimal amount, string idempotencyKey, string mode) => mode switch
    {
        "naive" => NaiveAsync(accountId, amount, idempotencyKey),
        "atomic" => AtomicAsync(accountId, amount, idempotencyKey),
        "lock" => PessimisticAsync(accountId, amount, idempotencyKey),
        "serializable" => SerializableWithRetryAsync(accountId, amount, idempotencyKey),
        _ => throw new ArgumentOutOfRangeException(nameof(mode), mode, "Use naive, atomic, lock or serializable")
    };

    // BAD on purpose: read, compute in C#, write. Loses updates under concurrency.
    private async Task<DebitResult> NaiveAsync(string accountId, decimal amount, string key)
    {
        await using var conn = await dataSource.OpenConnectionAsync();
        await using var tx = await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted);

        var balance = await ReadBalanceAsync(conn, tx, accountId, forUpdate: false);
        if (balance < amount) { await tx.RollbackAsync(); return DebitResult.InsufficientFunds; }

        await Task.Delay(Random.Shared.Next(1, 5)); // widen the race window, like real network latency
        await using (var upd = new NpgsqlCommand("UPDATE accounts SET balance = $1 WHERE account_id = $2", conn, tx))
        {
            upd.Parameters.AddWithValue(balance - amount);
            upd.Parameters.AddWithValue(accountId);
            await upd.ExecuteNonQueryAsync();
        }
        if (!await InsertEntryAsync(conn, tx, accountId, -amount, key)) { await tx.RollbackAsync(); return DebitResult.Duplicate; }
        await tx.CommitAsync();
        return DebitResult.Posted;
    }

    // GOOD: one guarded UPDATE. PostgreSQL re-checks the WHERE clause after waiting for a concurrent writer.
    private async Task<DebitResult> AtomicAsync(string accountId, decimal amount, string key)
    {
        await using var conn = await dataSource.OpenConnectionAsync();
        await using var tx = await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted);

        await using var upd = new NpgsqlCommand(
            "UPDATE accounts SET balance = balance - $1 WHERE account_id = $2 AND balance >= $1", conn, tx);
        upd.Parameters.AddWithValue(amount);
        upd.Parameters.AddWithValue(accountId);
        if (await upd.ExecuteNonQueryAsync() == 0) { await tx.RollbackAsync(); return DebitResult.InsufficientFunds; }

        if (!await InsertEntryAsync(conn, tx, accountId, -amount, key)) { await tx.RollbackAsync(); return DebitResult.Duplicate; }
        await tx.CommitAsync();
        return DebitResult.Posted;
    }

    // GOOD: lock the row, then decide. Use when the decision needs several reads.
    private async Task<DebitResult> PessimisticAsync(string accountId, decimal amount, string key)
    {
        await using var conn = await dataSource.OpenConnectionAsync();
        await using var tx = await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted);

        var balance = await ReadBalanceAsync(conn, tx, accountId, forUpdate: true);
        if (balance < amount) { await tx.RollbackAsync(); return DebitResult.InsufficientFunds; }

        await using (var upd = new NpgsqlCommand("UPDATE accounts SET balance = $1 WHERE account_id = $2", conn, tx))
        {
            upd.Parameters.AddWithValue(balance - amount);
            upd.Parameters.AddWithValue(accountId);
            await upd.ExecuteNonQueryAsync();
        }
        if (!await InsertEntryAsync(conn, tx, accountId, -amount, key)) { await tx.RollbackAsync(); return DebitResult.Duplicate; }
        await tx.CommitAsync();
        return DebitResult.Posted;
    }

    // GOOD: naive-looking logic made safe by SERIALIZABLE plus a full-transaction retry on 40001/40P01.
    private async Task<DebitResult> SerializableWithRetryAsync(string accountId, decimal amount, string key)
    {
        for (var attempt = 1; ; attempt++)
        {
            try
            {
                await using var conn = await dataSource.OpenConnectionAsync();
                await using var tx = await conn.BeginTransactionAsync(IsolationLevel.Serializable);

                var balance = await ReadBalanceAsync(conn, tx, accountId, forUpdate: false);
                if (balance < amount) { await tx.RollbackAsync(); return DebitResult.InsufficientFunds; }

                await using (var upd = new NpgsqlCommand("UPDATE accounts SET balance = $1 WHERE account_id = $2", conn, tx))
                {
                    upd.Parameters.AddWithValue(balance - amount);
                    upd.Parameters.AddWithValue(accountId);
                    await upd.ExecuteNonQueryAsync();
                }
                if (!await InsertEntryAsync(conn, tx, accountId, -amount, key)) { await tx.RollbackAsync(); return DebitResult.Duplicate; }
                await tx.CommitAsync();
                return DebitResult.Posted;
            }
            catch (PostgresException ex) when (IsRetryable(ex) && attempt < MaxAttempts)
            {
                // Exponential backoff with jitter: 10, 20, 40, 80 ms plus up to 10 ms random.
                var delay = TimeSpan.FromMilliseconds(10 * Math.Pow(2, attempt - 1) + Random.Shared.Next(0, 10));
                await Task.Delay(delay);
            }
        }
    }

    private static bool IsRetryable(PostgresException ex) =>
        ex.SqlState is PostgresErrorCodes.SerializationFailure or PostgresErrorCodes.DeadlockDetected;

    public async Task<decimal> GetBalanceAsync(string accountId)
    {
        await using var cmd = dataSource.CreateCommand("SELECT balance FROM accounts WHERE account_id = $1");
        cmd.Parameters.AddWithValue(accountId);
        return (decimal)(await cmd.ExecuteScalarAsync() ?? throw new InvalidOperationException("Account not found"));
    }

    private static async Task<decimal> ReadBalanceAsync(NpgsqlConnection conn, NpgsqlTransaction tx, string accountId, bool forUpdate)
    {
        var sql = "SELECT balance FROM accounts WHERE account_id = $1" + (forUpdate ? " FOR UPDATE" : string.Empty);
        await using var cmd = new NpgsqlCommand(sql, conn, tx);
        cmd.Parameters.AddWithValue(accountId);
        return (decimal)(await cmd.ExecuteScalarAsync() ?? throw new InvalidOperationException("Account not found"));
    }

    // Returns false when the idempotency key already exists (duplicate request).
    private static async Task<bool> InsertEntryAsync(NpgsqlConnection conn, NpgsqlTransaction tx, string accountId, decimal amount, string key)
    {
        await using var cmd = new NpgsqlCommand(
            "INSERT INTO ledger_entries (account_id, amount, idempotency_key) VALUES ($1, $2, $3) ON CONFLICT (idempotency_key) DO NOTHING",
            conn, tx);
        cmd.Parameters.AddWithValue(accountId);
        cmd.Parameters.AddWithValue(amount);
        cmd.Parameters.AddWithValue(key);
        return await cmd.ExecuteNonQueryAsync() == 1;
    }
}

Run each mode:

dotnet run -- naive          # usually: LOST UPDATE ✘
dotnet run -- atomic         # Ledger consistent ✔ posted=33
dotnet run -- lock           # Ledger consistent ✔ posted=33
dotnet run -- serializable   # Ledger consistent ✔ (some attempts retried)

EF Core version: optimistic concurrency with xmin

When you use EF Core rather than raw SQL, the idiomatic fix is an optimistic concurrency token. In PostgreSQL, every row has a system column xmin (the ID of the transaction that last wrote it), so you need no extra column:

Account.cs
using System.ComponentModel.DataAnnotations;

namespace Harbourline.Ledger.Domain;

public sealed class Account
{
    public required string AccountId { get; init; }
    public required string Currency { get; init; }
    public decimal Balance { get; private set; }

    [Timestamp] // Npgsql maps a uint [Timestamp] property to the xmin system column.
    public uint Version { get; private set; }

    public void Debit(decimal amount)
    {
        if (amount <= 0) throw new ArgumentOutOfRangeException(nameof(amount));
        if (Balance < amount) throw new InvalidOperationException("Insufficient funds");
        Balance -= amount;
    }
}
DebitHandler.cs
using Microsoft.EntityFrameworkCore;

namespace Harbourline.Ledger.Application;

public sealed class DebitHandler(LedgerDbContext db)
{
    public async Task DebitAsync(string accountId, decimal amount, CancellationToken ct)
    {
        for (var attempt = 1; attempt <= 3; attempt++)
        {
            var account = await db.Accounts.SingleAsync(a => a.AccountId == accountId, ct);
            account.Debit(amount);
            try
            {
                // UPDATE … WHERE account_id = @id AND xmin = @originalVersion
                await db.SaveChangesAsync(ct);
                return;
            }
            catch (DbUpdateConcurrencyException) when (attempt < 3)
            {
                db.ChangeTracker.Clear(); // discard stale state and reload on the next loop
            }
        }
        throw new InvalidOperationException("Account is too busy; try again");
    }
}

Code explained line by line

  1. NpgsqlDataSource.Create(...) — the modern Npgsql entry point. It owns the connection pool; create it once per application (register it as a singleton in DI).
  2. Maximum Pool Size=60 — 50 concurrent tasks each need a connection. On RDS you would size this against max_connections and RDS Proxy (see the connection pooling lesson in this module).
  3. Enumerable.Range(1, 50).Select(...) with Task.WhenAll — fires 50 debits at once. This is the test that exposes races; single-threaded unit tests never do.
  4. expected = 1000 - succeeded * 30 — the invariant. If any update was lost, the balance is higher than the number of posted debits implies.
  5. NaiveAsync — the bug. ReadBalanceAsync without FOR UPDATE takes no lock, so two transactions read the same value. Task.Delay makes the race easy to reproduce, but it exists without it.
  6. AtomicAsync — balance = balance - $1 ... AND balance >= $1. PostgreSQL locks the row for the update; a concurrent updater waits, then re-evaluates the WHERE on the new committed version. Zero rows means insufficient funds.
  7. PessimisticAsync — FOR UPDATE takes a row lock at read time, so the second transaction waits before reading. Safe, but holds locks longer.
  8. IsolationLevel.Serializable — Npgsql issues BEGIN ISOLATION LEVEL SERIALIZABLE. The code is the naive logic, but PostgreSQL detects the conflict.
  9. catch (PostgresException ex) when (IsRetryable(ex) && attempt < MaxAttempts) — only retry on 40001 (serialization failure) and 40P01 (deadlock). Never retry on constraint violations or syntax errors.
  10. The retry re-opens the connection and re-runs the whole transaction, including the read. Retrying only the failing statement is wrong.
  11. ON CONFLICT (idempotency_key) DO NOTHING — combined with ExecuteNonQueryAsync() == 1, detects duplicates without raising an exception that would abort the transaction.
  12. [Timestamp] public uint Version — Npgsql's EF Core provider maps this to xmin. EF Core adds AND xmin = @original to the UPDATE and throws DbUpdateConcurrencyException if no row matched.
  13. db.ChangeTracker.Clear() — after a concurrency failure the tracked entity is stale; clearing forces a fresh read.

Choosing a technique

SituationTechniqueWhy
Single-row balance change with a simple ruleAtomic guarded UPDATEFewest round trips, no retries, no long locks
Transfer between two accountsGuarded UPDATE on each, in ascending account_id order, in one transactionConsistent lock order prevents deadlocks
Complex rule across several rows (joint-account limit, daily limit across cards)SERIALIZABLE with retry, or FOR UPDATE on a parent rowPrevents write skew
EF Core aggregate edited through a UI (beneficiary details)Optimistic xmin / rowversionUsers rarely collide; no locks held during think time
DynamoDB balanceUpdateItem with ConditionExpressionAtomic per item; see lesson 11
Read after the customer's own writeWriter endpoint or ConsistentRead = trueReplicas and default DynamoDB reads may lag
Monthly statements, dashboardsReader endpoint, eventually consistent readsCheaper, offloads the writer

Security considerations

⚠ Learning shortcut — not for production

The demo uses the postgres superuser with a password in a constant, against a local container. In AWS, the application connects as a dedicated role that has only SELECT, UPDATE on accounts and INSERT on ledger_entries (no UPDATE or DELETE on the ledger), with credentials from AWS Secrets Manager or IAM database authentication, over TLS (SSL Mode=VerifyFull with the RDS CA bundle).

  • Make the ledger append-only. Revoke UPDATE and DELETE on ledger_entries from the application role. Corrections are new reversing entries — that is both good accounting and good security.
  • Constraints are controls. CHECK (balance >= 0) (or >= -overdraft_limit) catches bugs that slip past isolation. Auditors like controls the database enforces.
  • Do not leak data through retries. Log the retry count and SQLSTATE, not the full row or the customer's details.
  • Long transactions are an availability risk. On PostgreSQL they block VACUUM and bloat tables; on SQL Server they hold locks and fill the version store. Set idle_in_transaction_session_timeout and statement_timeout in the RDS parameter group.
  • Stale reads can be a confidentiality problem. If a customer revokes a third party's access (Open Banking consent), the consent check must read from the writer or with a strongly consistent read, not a lagging replica.

Banking use case

Harbourline's Payments Service posts card payments into Aurora PostgreSQL and records idempotency keys in DynamoDB:

  1. The API receives POST /payments with an Idempotency-Key header.
  2. A DynamoDB conditional put (attribute_not_exists(pk)) claims the key. A duplicate fails the condition and returns the stored result (lesson 11).
  3. In Aurora, one READ COMMITTED transaction runs the guarded debit UPDATE and inserts the ledger entry.
  4. The mobile app's balance screen reads from the writer endpoint for 30 seconds after the customer's own payment (a "sticky to writer" flag in the session), then from the reader.
  5. The statements service always reads from the reader endpoint and shows "balances as at 23:59 yesterday", so lag is irrelevant.
  6. Reconciliation at 02:00 compares SUM(ledger_entries.amount) per account with accounts.balance and pages the on-call engineer on any difference.

Common mistakes

  • Read-compute-write in C# → it is natural with EF Core and invisible in single-user testing → push the arithmetic into a guarded UPDATE, or use a concurrency token.
  • Assuming PostgreSQL REPEATABLE READ behaves like SQL Server's → the names match, the semantics do not (PostgreSQL prevents phantoms and raises 40001 instead of blocking) → test under concurrency after migration.
  • Retrying only the failed statement → after 40001 the transaction is aborted → retry the whole unit of work from the first read.
  • Reading your own write from a replica → the reader endpoint is the "obvious" choice for GETs → route read-after-write to the writer.
  • Using a GSI for a balance check → GSIs are eventually consistent only → read the base table with ConsistentRead = true.
  • Turning on RCSI on RDS SQL Server without testing → code that relied on readers blocking now reads stale values → treat it as a behaviour change with regression tests.
  • Deadlocks in transfers → two transfers lock A→B and B→A → always lock in a stable order.

Troubleshooting

SymptomLikely causeFix
40001 could not serialize access due to concurrent updateREPEATABLE READ or SERIALIZABLE conflictExpected; retry the whole transaction with backoff
40001 could not serialize access due to read/write dependencies among transactionsSSI detected possible write skewRetry; if very frequent, narrow the transaction or add an index so SSI tracks fewer rows
40P01 deadlock detectedInconsistent lock orderLock rows in sorted key order; keep transactions short
25006 cannot execute UPDATE in a read-only transactionConnected to an Aurora reader or a read replicaUse the cluster (writer) endpoint for writes
SQL Server error 1205 on RDSDeadlock victimRetry; examine the deadlock graph from the system_health extended event session
SQL Server error 3960Snapshot update conflictRetry the transaction
Customer sees old balance right after payingRead served by replica or eventually consistent readWriter endpoint or ConsistentRead = true for read-after-write paths
ValidationException "Consistent reads are not supported on global secondary indexes"ConsistentRead = true on a GSI queryQuery the base table or accept eventual consistency
DbUpdateConcurrencyException on every savexmin not mapped, or entity reattached with a default versionMap [Timestamp] uint; load before modifying

Cost and cleanup

Everything in this lesson runs locally and is free. The optional CLI reads against a DynamoDB table use the always-free allowance. Strongly consistent reads consume twice the read capacity of eventually consistent reads, and transactional reads and writes consume twice the capacity of standard ones — that is a real cost driver at bank scale.

# Remove the local container and its data.
docker rm -f hl-m06-pg

If you deployed the replica-lag alarm, delete it (CloudWatch charges per alarm metric per month after the free allowance):

aws cloudwatch delete-alarms --alarm-names hl-m06-aurora-replica-lag-high --profile learn-sandbox --region us-east-1
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
  --query "ResourceTagMappingList[].ResourceARN" --profile learn-sandbox --region us-east-1

Exercises

  • 🟢 Guided: Run the console app in all four modes three times each. Record posted count, final balance and (for serializable) how many retries happened (add a counter). Explain why naive sometimes passes.
  • 🟡 Challenge: Implement TransferAsync(from, to, amount, key) with guarded updates on both accounts in one transaction. Prove with 100 concurrent random transfers between five accounts that the total money in the system never changes and no deadlock escapes.
  • 🔴 Live problem: After migrating the Joint Accounts service from SQL Server (SERIALIZABLE stored procedure) to Aurora PostgreSQL (EF Core, default settings), the fraud team reports three joint accounts where the combined balance went below the agreed overdraft limit on the same day. Nobody changed the business logic. Find the cause and propose the fix.

Assignment

Add a concurrency-tests folder to your portfolio repository: the console app, a Testcontainers-based xUnit v3 test that runs 50 concurrent debits against postgres:17 and asserts the invariant for each safe mode, and a one-page ADR "How Harbourline prevents lost updates on PostgreSQL" that lists the chosen technique per use case.

Solutions

Knowledge check

Interview questions

  • "What is a lost update and how do you prevent it?" — Two transactions read the same value, compute, and write; one overwrites the other. Prevent it with an atomic update in the database, a row lock (FOR UPDATE / UPDLOCK), optimistic concurrency (rowversion/xmin), or serializable isolation with retries.
  • "How does PostgreSQL's isolation differ from SQL Server's?" — PostgreSQL is MVCC-first: readers never block writers, REPEATABLE READ is snapshot isolation that also prevents phantoms, and SERIALIZABLE is optimistic (SSI) and aborts with 40001. SQL Server defaults to locking READ COMMITTED and its SERIALIZABLE blocks with range locks.
  • "When would you read from an Aurora replica, and when not?" — Replicas for reporting, statements and search; the writer for anything that must reflect the user's own recent change, consent checks and balance checks before money moves.
  • "Can you have a transaction across RDS and DynamoDB?" — No distributed transaction exists. Use idempotent steps with a transactional outbox or a saga, and reconcile.

Certification practice

Completion checklist

  • I can define dirty read, non-repeatable read, phantom, lost update and write skew with banking examples
  • I reproduced a lost update in two psql sessions
  • I ran the console app in all four modes and explained each result
  • I can say which reads at Harbourline must go to the writer or use ConsistentRead = true
  • I removed the hl-m06-pg container and any alarm I created

Key takeaways

  • Isolation is a dial; consistency across replicas is a separate question. Banking code must answer both.
  • PostgreSQL on RDS/Aurora is MVCC: no dirty reads, readers never block writers, and SERIALIZABLE aborts with 40001 — always retry the whole transaction.
  • The simplest fix for lost updates is a guarded atomic UPDATE … WHERE balance >= @amount; lock in a stable order for transfers.
  • Replicas and default DynamoDB reads are eventually consistent; read-after-write goes to the writer or uses strongly consistent reads (never on a GSI).
  • Back isolation with constraints, an append-only ledger and daily reconciliation.

Further reading

Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.

On this page