Two successful transactions, one impossible result

Two customers receive a confirmation email for seat 14C on the same flight. Both requests ran inside database transactions. Neither transaction failed. Neither application instance logged an error.

This is a hypothetical booking service. It runs on AWS in the way most production services do:

• Six application instances run as Amazon ECS tasks (containers managed by the Elastic Container Service). They are spread across three Availability Zones, which are separate data centres inside one AWS Region.
• An Application Load Balancer (ALB) receives every request from the internet and forwards it to one healthy task. Two requests from two customers can land on two different tasks in two different zones.
• The seats live in an Amazon Aurora PostgreSQL cluster. The cluster has one writer instance and two read replicas. AWS documents that the primary (writer) instance handles every data-changing statement, and that replicas serve read-only traffic; the cluster's reader endpoint spreads read connections across the replicas (Amazon Aurora endpoints).

The rule is simple: a seat can have at most one owner. Your first task is to find how two individually reasonable operations, on two different machines, broke that rule.

Read the evidence before changing anything

The first implementation does three things. It reads owner_id for seat 14C through the reader endpoint. It checks in application code that the value is NULL. It then writes the buyer's name through the writer endpoint. The two requests arrived 40 milliseconds apart (a teaching assumption). Here is the interleaving, with the stored owner shown as the writer instance holds it.

Each task also holds a process mutex (a lock that only one thread inside one process can hold at a time) around steps 1 to 3. The engineer who wrote it believed the mutex made the check and the write one operation.

This failure has a name: a lost update. It is a race condition between two transactions, which means the result depends on the order in which two independent operations happen to run. Transaction T1 (task a) and transaction T2 (task b) both evaluated the check owner_id IS NULL before either of them committed. Each check was true at the moment it ran. T2's write then replaced T1's write, and T1's update was lost. The diagram follows both transactions down to the row lock on the writer.

Synthesizing vector architecture diagram...

Notice where the database did its job and where it could not. The writer did serialise the two writes: T2's UPDATE waited until T1 committed, exactly as a row lock should make it wait. The lock protected the write. It did not protect the decision, because the decision ("the seat is free") was made in application code, from a replica, before the lock existed. When T2's turn came, its UPDATE carried no condition, so the engine had nothing to re-check. Both commits succeeded, and the second silently replaced the first.

The shape of this mistake has a name: check-then-act across disconnected operations. The application reads a value (SELECT owner_id). It decides in application code whether the value allows the action (if owner_id is NULL). It then runs a separate write (UPDATE ...). The read and the write are separate round trips, where a round trip is one request sent over the network and its response coming back. They may even go to different nodes, a replica for the read and the writer for the write, and run in different application tasks. In the gap between the two round trips, another request can read the same state and commit its own write before this one commits. That gap is what produces the lost update.

Three common first attempts all fail for the same reason. A process mutex protects one process. An in-memory set of "seats reserved so far" lives in one task, and six tasks have six sets. A check in an Amazon ElastiCache cache before the write is two separate operations (check, then write) on a copy of the data, so two tasks can both pass the check before either one writes. None of these places the decision at the place that holds the truth.

Make the decision at the authority, in one atomic step

The authority for a seat is the row on the Aurora writer instance. On Aurora PostgreSQL the cluster has exactly one writer instance; the replicas serve reads and are not the authority. Conflicting writes are resolved on the writer by the PostgreSQL engine's own locking and isolation rules, because Aurora PostgreSQL is PostgreSQL-compatible (Amazon Aurora PostgreSQL, Amazon Aurora endpoints). To prevent a lost update, the decision ("is the seat free?") and the action ("assign the seat") must be evaluated together, in one atomic unit, at the single source of truth: the writer instance. Three patterns obey that rule. Pattern A folds the condition into the single UPDATE and is the recommended default. Pattern B holds a row lock for the whole read, check and write, for logic that one WHERE clause cannot express, and blocks other transactions on that row until the transaction ends. Pattern C passes the version read earlier into the UPDATE's condition.

sqlCopy
-- Pattern A: atomic conditional UPDATE.
-- PostgreSQL, run through the Aurora cluster (writer) endpoint.
-- seats(flight_id, seat_no) is the primary key.
UPDATE seats
SET owner_id = :buyer
WHERE flight_id = :flight AND seat_no = '14C'
  AND owner_id IS NULL
RETURNING seat_no;

At PostgreSQL's default isolation level, Read Committed, the second updater of the same row waits until the first updater commits or rolls back. It then re-evaluates its WHERE clause against the updated version of the row (PostgreSQL transaction isolation). T2's update finds owner_id = 'A', so its condition is false and it updates zero rows. Only the winner receives a row. The application confirms a booking only after COMMIT returns. Zero returned rows means this attempt did not acquire the seat.

This mechanism is conditional mutation: the condition and the change are evaluated by the authority in one step, so there is no time between "is this allowed?" and "make it happen" in which another instance can act. A unique constraint on an active reservation can enforce the same invariant in a different schema. The number of ECS tasks no longer matters, because every task sends its decision to the same row on the same writer. Use pattern A when the whole rule fits in one row and one statement; its cost is one round trip, and it is the cheapest of the three patterns below.

Patterns B and C obey the same rule with the check in a different place.

sqlCopy
-- Pattern B: pessimistic locking.
-- The row lock is taken first; the check runs in the application while the lock is held.
BEGIN;
SELECT owner_id FROM seats
WHERE flight_id = :flight AND seat_no = '14C'
FOR UPDATE;
-- Application code: if owner_id is not NULL, ROLLBACK and report "taken".
UPDATE seats
SET owner_id = :buyer
WHERE flight_id = :flight AND seat_no = '14C';
COMMIT;

SELECT ... FOR UPDATE locks the row on the writer until the transaction ends, so a second transaction's FOR UPDATE waits and then sees the committed owner. Use pattern B when the decision needs several rows, or logic that one WHERE clause cannot express. Its cost is that every competing transaction waits in a queue behind the lock, and that the lock is held for the whole round trip to the application. Do not assume FOR UPDATE on an empty result locks a future row. If no reservation row exists yet, two transactions can both lock nothing and both insert. For that case, use a unique or exclusion constraint, a stable parent row such as the seat itself, or serializable isolation. Locking the rows that happen to exist is not the same as locking the condition.

sqlCopy
-- Pattern C: optimistic concurrency control with a version column.
-- The application read version = :expected_version earlier, from any endpoint.
UPDATE seats
SET owner_id = :buyer, version = version + 1
WHERE flight_id = :flight AND seat_no = '14C'
  AND version = :expected_version
RETURNING version;

Pattern C takes no lock during the read. The UPDATE succeeds only if nobody changed the row since the read, because any change would have raised version. Zero rows means a conflict: the application re-reads the row at the writer and decides again. Use pattern C when conflicts are rare and reads are many, for example a seat map that thousands of customers view and few customers buy. Its cost is a retry loop in the application, and wasted work on every conflict. Note that it is still a conditional mutation: the engine evaluates version = :expected_version and the write in one step. The read may come from a replica, because a stale read only causes a safe retry, never a wrong commit.

Name the distributed guarantee precisely, because every later design in this collection is checked the same way:

• Authority: the seat row on the Aurora writer. The replicas are derived copies, not the source of truth.
• Atomic boundary: one UPDATE statement in one transaction (patterns A and C), or one transaction holding a row lock (pattern B).
• Acknowledgement point: COMMIT returned to the task. The customer may be told "confirmed" only after that.
• Failure model: a task can crash at any step; a zone can lose network; a response can be lost between the writer and the task, or between the task and the customer.
• Clock assumption: none. The decision compares stored values, not timestamps.
• Recovery path: after any failure, the task reads the row at the writer and acts on what it finds.

The same decision in Amazon DynamoDB

Suppose the team stores seats in Amazon DynamoDB instead, a managed key-value database with no SQL transactions in the PostgreSQL sense. The same rule applies: the condition and the write must be one operation at the authority. DynamoDB provides this with a condition expression on the write. AWS documents that the condition is evaluated against the item identified by the request's key, and that the write is rejected when the condition is false (DynamoDB condition expressions). This is the UpdateItem request that pattern A becomes:

jsonCopy
{
  "TableName": "seats",
  "Key": {
    "FlightId": { "S": "HS101" },
    "SeatNo": { "S": "14C" }
  },
  "UpdateExpression": "SET OwnerId = :buyer",
  "ConditionExpression": "attribute_not_exists(OwnerId)",
  "ExpressionAttributeValues": {
    ":buyer": { "S": "customer-a" }
  }
}

The first task's request succeeds. The second task's request does not silently overwrite the item; it fails with ConditionalCheckFailedException (UpdateItem API reference). That exception is the DynamoDB equivalent of zero returned rows: this attempt did not acquire the seat.

One difference matters for the evidence stage. DynamoDB reads are eventually consistent by default, which means a read may not yet reflect a write that just completed. A read that must see the latest completed write sets ConsistentRead to true (DynamoDB read consistency). So a task that reads the item to decide, then writes without a condition, has the same lost update as the Aurora version that read from a replica. The read can be stale, and the write does not check.

The next rule does not fit in one row

The booking team adds a support rota. At least one of two engineers must remain on call. Both are currently on call. Two tasks, in two zones, each run one transaction. Each transaction counts the on-call engineers through its own snapshot (the consistent view of the data that the transaction sees), finds two, and marks its own engineer off call.

The one-row fix taught us where to put the decision. It did not prove that every invariant fits one row. Here are four ways to make the whole decision conflict:

Stable reads have a price

Multi-version concurrency control (MVCC) lets a normal read see a version of each row that fits its snapshot while another transaction creates newer versions. Visibility also depends on which transactions had committed when the snapshot was taken; it is not simply "choose the highest transaction ID below mine."

A read from an Aurora replica adds a second delay on top of the snapshot. AWS documents replica lag as usually much less than 100 milliseconds, and higher during periods with many writes (Aurora replication). A replica is therefore a good place to read a seat map for display and a wrong place to read a seat to decide. The database acknowledged a write the replica cannot read follows that gap when it grows.

A long-lived snapshot can pin old versions. Space reclamation falls behind, scans visit more dead versions, and the system slows even though the application is mostly reading. Watch transaction age, lock waits, dead tuples, and retry counts. Bound transaction duration before blaming storage hardware.

The anomaly names are useful only when you can produce the schedule:

The exact protection behind "Repeatable Read" depends on the engine. PostgreSQL prevents phantoms at that level but still allows serialization anomalies; Serializable adds dependency checking and may abort transactions. These are documented guarantees, not a universal isolation hierarchy (PostgreSQL transaction isolation). Aurora PostgreSQL is PostgreSQL-compatible, so the same documentation applies to transactions sent to the cluster endpoint.

Make a retry safe before making it automatic

An aborted serializable transaction must restart from its reads. Reusing the old decision and retrying only the final statement preserves the mistake. Keep external effects such as sending a receipt through Amazon Simple Email Service outside the retriable transaction; a database rollback cannot unsend a message.

Now test the conditional update against the network. Task a sends COMMIT for buyer A's update to the writer. The writer processes it. The response travels back across the zone boundary and is lost. Task a receives a network timeout.

For pessimistic locking, acquire multiple rows in a stable order to reduce deadlocks. Still handle deadlock victims: sorted locking is a protocol, not proof that every other writer follows it. Keep large deletes bounded; engines differ in lock escalation, and even without escalation a large transaction can create a large amount of log and cleanup work.

Key takeaway: never base a change on an observation that was not locked, that came from a previous statement, or that came from a read replica. Either combine the read, the check and the write into one conditional UPDATE statement (pattern A), or lock the target row inside one bounded database transaction with SELECT ... FOR UPDATE before evaluating the condition (pattern B). In DynamoDB, a ConditionExpression on the write is the equivalent of pattern A.

Mental model: a transaction boundary is a container, and the container lives at the authority. The invariant tells you what must be decided together; isolation and constraints determine whether the container protects it. Adding instances, zones, caches, or replicas never adds protection, because each of them is a copy of either the decision or the data. Only the authority can make a decision that every copy must respect.

Transfer: the last two units

A hypothetical warehouse service stores stock in an Aurora PostgreSQL cluster. Three AWS Lambda functions, started by three customer orders at the same moment, each execute UPDATE stock SET units = units - 1 WHERE units > 0 RETURNING units through the cluster endpoint. The warehouse has two units. Every function whose update returns a row then sends a shipment request to a carrier's HTTP API.

---

Source trail: 21-database-isolation-levels-acid-and-concurrency-anomalies. See the source map for consolidation and reference-retained material.