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.
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, pessimisticSELECT … FOR UPDATE, andSERIALIZABLEwith 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-sandboxprofile - Comfortable with
BEGIN TRAN,COMMIT,ROLLBACKandSqlTransactionfrom 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
| Anomaly | What happens | Banking example |
|---|---|---|
| Dirty read | You read another transaction's uncommitted change, which is then rolled back | A balance check sees a deposit that later fails |
| Non-repeatable read | You read the same row twice in one transaction and get different values | A statement job reads the balance at the start and end and they disagree |
| Phantom | You run the same range query twice and new rows appear | Sum of today's transactions changes during the reconciliation |
| Lost update | Two transactions read, compute and write the same row; one write disappears | The £110-from-£100 story above |
| Write skew | Two transactions read overlapping data, each writes a different row, and together they break a rule neither broke alone | Joint 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
| Store | Unit of atomicity | Default isolation | Strongest option | Sharp edges |
|---|---|---|---|---|
| RDS for SQL Server | Multi-statement transaction | READ COMMITTED with shared locks (RCSI off unless you turn it on) | SERIALIZABLE (key-range locks) or SNAPSHOT | Blocking and deadlocks; RCSI changes behaviour silently |
| RDS for PostgreSQL / Aurora PostgreSQL | Multi-statement transaction | READ COMMITTED with MVCC | SERIALIZABLE (Serializable Snapshot Isolation, SSI) | Serialization failures (SQLSTATE 40001) must be retried; long transactions bloat tables |
| RDS / Aurora read replicas | Read-only | Whatever the replica applies, asynchronously | — | Replica lag; SERIALIZABLE is not available on a PostgreSQL hot standby |
| DynamoDB | Single item (or up to 100 items with TransactWriteItems) | Eventually consistent reads by default | Strongly consistent reads on the base table; serializable transactions | No 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 equivalent | What's different |
|---|---|---|
SQL Server READ COMMITTED (locking) | PostgreSQL READ COMMITTED (MVCC) on RDS/Aurora | PostgreSQL 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 Database | PostgreSQL READ COMMITTED | Closest match. Code tested on Azure SQL Database usually behaves similarly on PostgreSQL |
SNAPSHOT isolation (ALLOW_SNAPSHOT_ISOLATION ON) | PostgreSQL REPEATABLE READ | Both are snapshot isolation; both raise an update-conflict error instead of overwriting (SQL Server error 3960, PostgreSQL 40001) |
SERIALIZABLE with key-range locks | PostgreSQL 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 UPDATE | Same intent: lock the row you will change |
rowversion / [Timestamp] byte[] in EF Core | PostgreSQL xmin system column mapped as [Timestamp] uint in Npgsql | No extra column needed in PostgreSQL |
| Always On readable secondary | RDS read replica / Aurora reader | Asynchronous; you must design for lag |
| Cosmos DB consistency levels (Strong, Bounded staleness, Session, Consistent prefix, Eventual) | DynamoDB: eventually consistent or strongly consistent reads per request | Only two choices per read in DynamoDB; strong reads cost twice as much and only on the base table |
TransactionScope with MSDTC | No distributed transactions across RDS and DynamoDB | Use 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 withALTER 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 UNCOMMITTEDis accepted but behaves asREAD COMMITTED. There are no dirty reads in PostgreSQL.READ COMMITTED(default): each statement sees a snapshot taken when the statement started. AnUPDATEthat finds a row changed by a concurrent committed transaction re-checks itsWHEREclause against the new version. That re-check is what makesUPDATE accounts SET balance = balance - 60 WHERE account_id = … AND balance >= 60safe.REPEATABLE READ: snapshot isolation for the whole transaction. In PostgreSQL it also prevents phantoms. Updating a row changed since your snapshot raises40001 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 with40001. 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 = truefor 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.
TransactWriteItemsgroups 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, butQuery,ScanandBatchGetItemsee 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
| Topology | Replication | What a reader may see |
|---|---|---|
| RDS Multi-AZ (one standby) | Synchronous to a standby that serves no reads | Not applicable — you cannot read the standby |
| RDS Multi-AZ DB cluster (two readable standbys) | Semi-synchronous; standbys serve reads | Small lag on reader endpoint |
| RDS read replica | Asynchronous | Seconds of lag under load; more during heavy writes |
| Aurora replicas | Shared storage; readers apply changes from the writer asynchronously | Typically well under a second (AWS describes lag as usually under 100 ms ⚠ VERIFY for your workload) |
| DynamoDB eventually consistent read | Internal replicas | Usually 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:17Create the schema
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 postgresThe 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.
| Time | Session A | Session B |
|---|---|---|
| t1 | BEGIN; SELECT balance FROM accounts WHERE account_id='ACC-0000000001'; → 100.00 | |
| t2 | BEGIN; same SELECT → 100.00 | |
| t3 | UPDATE accounts SET balance = 40.00 WHERE account_id='ACC-0000000001'; (app computed 100 − 60) | |
| t4 | UPDATE accounts SET balance = 50.00 WHERE account_id='ACC-0000000001'; — waits for A's row lock | |
| t5 | COMMIT; | the update proceeds and writes 50.00 |
| t6 | COMMIT; — 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
sqlcmdand runALTER 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
postgres17oraurora-postgresql17), then editdefault_transaction_isolation. Leave it atread committedunless 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) orAuroraReplicaLag(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-1The 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".
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.
<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>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:
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;
}
}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
NpgsqlDataSource.Create(...)— the modern Npgsql entry point. It owns the connection pool; create it once per application (register it as a singleton in DI).Maximum Pool Size=60— 50 concurrent tasks each need a connection. On RDS you would size this againstmax_connectionsand RDS Proxy (see the connection pooling lesson in this module).Enumerable.Range(1, 50).Select(...)withTask.WhenAll— fires 50 debits at once. This is the test that exposes races; single-threaded unit tests never do.expected = 1000 - succeeded * 30— the invariant. If any update was lost, the balance is higher than the number of posted debits implies.NaiveAsync— the bug.ReadBalanceAsyncwithoutFOR UPDATEtakes no lock, so two transactions read the same value.Task.Delaymakes the race easy to reproduce, but it exists without it.AtomicAsync—balance = balance - $1 ... AND balance >= $1. PostgreSQL locks the row for the update; a concurrent updater waits, then re-evaluates theWHEREon the new committed version. Zero rows means insufficient funds.PessimisticAsync—FOR UPDATEtakes a row lock at read time, so the second transaction waits before reading. Safe, but holds locks longer.IsolationLevel.Serializable— Npgsql issuesBEGIN ISOLATION LEVEL SERIALIZABLE. The code is the naive logic, but PostgreSQL detects the conflict.catch (PostgresException ex) when (IsRetryable(ex) && attempt < MaxAttempts)— only retry on40001(serialization failure) and40P01(deadlock). Never retry on constraint violations or syntax errors.- The retry re-opens the connection and re-runs the whole transaction, including the read. Retrying only the failing statement is wrong.
ON CONFLICT (idempotency_key) DO NOTHING— combined withExecuteNonQueryAsync() == 1, detects duplicates without raising an exception that would abort the transaction.[Timestamp] public uint Version— Npgsql's EF Core provider maps this toxmin. EF Core addsAND xmin = @originalto theUPDATEand throwsDbUpdateConcurrencyExceptionif no row matched.db.ChangeTracker.Clear()— after a concurrency failure the tracked entity is stale; clearing forces a fresh read.
Choosing a technique
| Situation | Technique | Why |
|---|---|---|
| Single-row balance change with a simple rule | Atomic guarded UPDATE | Fewest round trips, no retries, no long locks |
| Transfer between two accounts | Guarded UPDATE on each, in ascending account_id order, in one transaction | Consistent 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 row | Prevents write skew |
| EF Core aggregate edited through a UI (beneficiary details) | Optimistic xmin / rowversion | Users rarely collide; no locks held during think time |
| DynamoDB balance | UpdateItem with ConditionExpression | Atomic per item; see lesson 11 |
| Read after the customer's own write | Writer endpoint or ConsistentRead = true | Replicas and default DynamoDB reads may lag |
| Monthly statements, dashboards | Reader endpoint, eventually consistent reads | Cheaper, 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
UPDATEandDELETEonledger_entriesfrom 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
VACUUMand bloat tables; on SQL Server they hold locks and fill the version store. Setidle_in_transaction_session_timeoutandstatement_timeoutin 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:
- The API receives
POST /paymentswith anIdempotency-Keyheader. - A DynamoDB conditional put (
attribute_not_exists(pk)) claims the key. A duplicate fails the condition and returns the stored result (lesson 11). - In Aurora, one
READ COMMITTEDtransaction runs the guarded debitUPDATEand inserts the ledger entry. - 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.
- The statements service always reads from the reader endpoint and shows "balances as at 23:59 yesterday", so lag is irrelevant.
- Reconciliation at 02:00 compares
SUM(ledger_entries.amount)per account withaccounts.balanceand 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 READbehaves like SQL Server's → the names match, the semantics do not (PostgreSQL prevents phantoms and raises40001instead of blocking) → test under concurrency after migration. - Retrying only the failed statement → after
40001the 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
| Symptom | Likely cause | Fix |
|---|---|---|
40001 could not serialize access due to concurrent update | REPEATABLE READ or SERIALIZABLE conflict | Expected; retry the whole transaction with backoff |
40001 could not serialize access due to read/write dependencies among transactions | SSI detected possible write skew | Retry; if very frequent, narrow the transaction or add an index so SSI tracks fewer rows |
40P01 deadlock detected | Inconsistent lock order | Lock rows in sorted key order; keep transactions short |
25006 cannot execute UPDATE in a read-only transaction | Connected to an Aurora reader or a read replica | Use the cluster (writer) endpoint for writes |
| SQL Server error 1205 on RDS | Deadlock victim | Retry; examine the deadlock graph from the system_health extended event session |
| SQL Server error 3960 | Snapshot update conflict | Retry the transaction |
| Customer sees old balance right after paying | Read served by replica or eventually consistent read | Writer endpoint or ConsistentRead = true for read-after-write paths |
ValidationException "Consistent reads are not supported on global secondary indexes" | ConsistentRead = true on a GSI query | Query the base table or accept eventual consistency |
DbUpdateConcurrencyException on every save | xmin not mapped, or entity reattached with a default version | Map [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-pgIf 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-1Exercises
- 🟢 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 whynaivesometimes 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 (
SERIALIZABLEstored 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
Typical results: naive posts more than 33 debits (or ends with a balance higher than expected); atomic and lock post exactly 33 and end at £10.00; serializable posts 33 with tens of retries.
naive sometimes passes because the race needs two transactions to read before either writes. With a small pool or fast machine the reads may serialise by chance. Increasing Task.Delay makes it fail reliably — a good reminder that "the test passed" is not proof of correctness for concurrency.
public async Task<DebitResult> TransferAsync(string from, string to, decimal amount, string key)
{
await using var conn = await dataSource.OpenConnectionAsync();
await using var tx = await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted);
// Lock both rows in a stable order to prevent deadlocks.
var ordered = new[] { from, to }.Order(StringComparer.Ordinal).ToArray();
await using (var lockCmd = new NpgsqlCommand(
"SELECT account_id FROM accounts WHERE account_id = ANY($1) ORDER BY account_id FOR UPDATE", conn, tx))
{
lockCmd.Parameters.AddWithValue(ordered);
await using var reader = await lockCmd.ExecuteReaderAsync();
while (await reader.ReadAsync()) { }
}
await using (var debit = new NpgsqlCommand(
"UPDATE accounts SET balance = balance - $1 WHERE account_id = $2 AND balance >= $1", conn, tx))
{
debit.Parameters.AddWithValue(amount);
debit.Parameters.AddWithValue(from);
if (await debit.ExecuteNonQueryAsync() == 0) { await tx.RollbackAsync(); return DebitResult.InsufficientFunds; }
}
await using (var credit = new NpgsqlCommand("UPDATE accounts SET balance = balance + $1 WHERE account_id = $2", conn, tx))
{
credit.Parameters.AddWithValue(amount);
credit.Parameters.AddWithValue(to);
await credit.ExecuteNonQueryAsync();
}
// Insert two ledger entries with keys $"{key}:D" and $"{key}:C", then commit.
await tx.CommitAsync();
return DebitResult.Posted;
}Why: without the ordered FOR UPDATE, a transfer A→B and a transfer B→A each lock their first row and wait for the other: a deadlock. The invariant test is SELECT SUM(balance) FROM accounts before and after — money is neither created nor destroyed.
Cause: write skew. Each holder's withdrawal reads both sub-account balances, checks the combined limit, and updates only its own row. Under SQL Server SERIALIZABLE, range locks made the second transaction wait. Under PostgreSQL READ COMMITTED (the EF Core default), both transactions see the old combined balance, each updates a different row, and no row-level conflict ever occurs. Even REPEATABLE READ would not catch it — only SERIALIZABLE (SSI) detects write skew.
Fix options:
- Run the withdrawal in
SERIALIZABLEwith a full-transaction retry on40001. - Or lock a shared parent row (
SELECT … FROM joint_accounts WHERE id = … FOR UPDATE) before reading the sub-accounts, so the two withdrawals serialise. - Add a reconciliation query that flags any joint account below its limit, and backfill the three cases with the operations team.
Also add a regression test with two concurrent withdrawals. The lesson for the migration programme: isolation-level behaviour is part of the migration scope, not an implementation detail.
Knowledge check
Answer: No. PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, and MVCC means readers only ever see committed row versions. Dirty reads are impossible.
Answer: READ_COMMITTED_SNAPSHOT ON (RCSI). Readers get the last committed version per statement and do not block — just like PostgreSQL READ COMMITTED.
Answer: The UPDATE takes a row lock. If another transaction changed the row, the second UPDATE waits, then re-evaluates its WHERE clause against the newly committed version. The arithmetic happens on the latest value, and the guard rejects the debit if funds are now insufficient.
Answer: Roll back and re-run the entire transaction from the beginning, with backoff and a retry limit. The transaction is already aborted; individual statements cannot be retried.
Answer: No. Global secondary indexes support only eventually consistent reads. Read the item from the base table with ConsistentRead = true.
Answer: Write skew. Only SERIALIZABLE (SSI) detects it.
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 READis snapshot isolation that also prevents phantoms, andSERIALIZABLEis optimistic (SSI) and aborts with40001. SQL Server defaults to lockingREAD COMMITTEDand itsSERIALIZABLEblocks 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
A developer's Lambda function writes a payment status item to a DynamoDB table and, in the same invocation, reads it back to build the response. Occasionally the read returns the previous status. What is the most cost-effective fix?
A. Enable DynamoDB Streams and read the new image from the stream.
B. Set ConsistentRead to true on the GetItem call.
C. Query a global secondary index that projects the status attribute.
D. Switch the table to on-demand capacity mode.
Answer: B. A strongly consistent read of the base table returns the latest committed write. Streams are asynchronous and add complexity (A). GSIs are eventually consistent only (C). Capacity mode does not affect consistency (D).
A banking application uses Aurora PostgreSQL. Customers report that immediately after transferring money, the balance page sometimes shows the old balance. Balance pages read through the reader endpoint. What should a solutions architect recommend?
A. Increase the number of Aurora replicas. B. Route reads that follow a customer's own write to the cluster (writer) endpoint. C. Enable Multi-AZ on the reader instances. D. Change the cluster to use synchronous replication to readers.
Answer: B. Aurora replicas apply changes asynchronously; read-after-write paths should use the writer. More replicas do not reduce lag (A). Multi-AZ is about availability, not freshness (C). Aurora has no option to make reader replication synchronous (D).
A data engineer runs a nightly job on RDS for PostgreSQL that reads all of a day's transactions twice: once to compute totals per account and once to write a summary. Rows inserted by late-arriving payments during the job cause the totals and summary to disagree. Which change fixes this with the least locking?
A. Run the job in a single REPEATABLE READ transaction.
B. Run each query with SELECT … FOR UPDATE.
C. Set default_transaction_isolation to read uncommitted.
D. Move the job to a read replica.
Answer: A. REPEATABLE READ gives the whole transaction one snapshot, so both reads see the same rows, without blocking writers. FOR UPDATE blocks the payment inserts' updates and does not stop new rows (B). PostgreSQL treats READ UNCOMMITTED as READ COMMITTED (C). A replica has the same per-statement snapshot problem (D) — though it would offload load, it does not fix consistency.
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-pgcontainer 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
SERIALIZABLEaborts with40001— 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.
EF Core on AWS
Choose the right EF Core provider for RDS and Aurora, make units of work retry-safe across failovers, and ship migrations from a pipeline as a one-shot job without locking a busy ledger table.
Amazon DynamoDB fundamentals
Model Harbourline customers, accounts and transactions in a single DynamoDB table: choose partition and sort keys from access patterns, pick a capacity mode, add a global secondary index, and query it from .NET 10 with the AWS SDK v4 against DynamoDB Local.