Correctness Under Concurrency: Invariants, Isolation Levels and the Bugs Transactions Don’t Prevent

A corporate card has a R1,000 limit and R800 has already been spent. Two R150 authorisations arrive at approximately the same time.

Both transactions read R800. Both determine that another R150 fits within the limit. Both approve the authorisation. Both commit successfully.

Together, they authorise R300 while only R200 was available.

Nothing crashed. No message was duplicated. No transaction rolled back. The database may have behaved exactly according to its configured transaction isolation level, yet the business invariant was violated.

This is the uncomfortable part of database concurrency: a successful commit proves that the database accepted a transaction under its configured rules. It does not, by itself, prove that the concurrent system is correct.

Correctness begins with defining the business invariants, identifying the executions that can violate them, and choosing the weakest coordination mechanism that is still strong enough to preserve them. Database isolation levels matter, but they are only part of that reasoning.

Correctness Starts With Business Invariants

Concurrency discussions often begin too low in the stack.

• Which isolation level should we use?
• Should this query use FOR UPDATE?
• Should the operation retry?
• Would a distributed lock solve it?

Those questions are impossible to answer well until the system states what must remain true.

An invariant is a property that every permitted execution must preserve. It is not merely a validation rule at an API boundary. It describes the states and transitions the business considers valid.

available_stock >= 0

approved_spend + pending_spend <= card_limit

at_least_one_doctor_on_call = true

account_balance >= reserved_balance

The distinction between an operation and an invariant matters. Approving R150 is valid when viewed against a balance of R800 and a limit of R1,000. Approving another R150 is also valid against that same observation. The two decisions are individually reasonable and collectively invalid.

That gives us three different questions:

• Is each operation valid using the state it observed?
• Is the final committed state valid?
• Is the concurrent history that produced that state valid?

A system can pass the first test and fail the other two. It can also finish with a superficially valid state after allowing an invalid decision along the way. Correctness is therefore about permitted histories, not only valid-looking rows.

In the previous article on reliable multi-service workflows, I argued that system design should begin with the business invariants that must survive failure. The same rule applies before we even leave a single database.

Atomicity Does Not Mean Isolation

ACID is useful shorthand, but it is often treated as one indivisible promise. Its properties answer different questions.

Atomicity asks whether a transaction happened completely or not at all. If a transaction updates three rows and fails before commit, atomicity prevents a partially committed result.

Isolation asks what concurrent transactions may observe and how their reads and writes may interact. A transaction can be perfectly atomic while making a decision from state that became stale before it committed.

Consider the spending-limit execution:

T1                                  T2
read spent = 800
                                    read spent = 800
check 800 + 150 <= 1000
                                    check 800 + 150 <= 1000
approve 150
                                    approve 150
commit
                                    commit

Each transaction may atomically write its own authorisation record. There is no partial transaction to recover. The defect is that two decisions used observations that were not coordinated strongly enough to protect the shared limit.

A transaction boundary groups work. It does not automatically serialize the business decisions inside that boundary.

This is why wrapping a read, an application-level check and a write in BEGIN and COMMIT is not a complete concurrency strategy. The result depends on the isolation semantics, the statements used, the rows or predicates involved, and the behavior of concurrent transactions.

Lost Updates Are the Easy Concurrency Bug

The lost update is the concurrency anomaly most engineers meet first.

Suppose an account balance is 100. Two transactions adjust it concurrently:

T1                                  T2
read balance = 100
                                    read balance = 100
calculate 100 - 10 = 90
                                    calculate 100 - 20 = 80
write balance = 90
                                    write balance = 80
commit
                                    commit

The expected balance is 70. The final balance is 80 because the second write replaced the result of the first. One update was lost.

For this particular invariant, the strongest fix may also be the simplest: stop calculating the new value in application memory.

UPDATE accounts
SET balance = balance - :amount
WHERE id = :id;

The database performs the read and mutation as one statement. Concurrent updates to the same row are coordinated by the database rather than by stale application values.

That does not make atomic updates a universal solution. They work when the decision and mutation can be expressed against one row or one directly updateable set. They do not automatically protect an invariant defined over multiple rows, a changing predicate, an aggregate query, or state owned by another system.

Lost updates are useful because they reveal the problem clearly. The harder anomalies are the ones in which concurrent transactions update different rows and never create an obvious write-write conflict.

Conditional Writes Are More Powerful Than They Look

The card-limit decision can often be converted from a read-check-write sequence into a conditional mutation.

UPDATE card_limits
SET spent = spent + :amount
WHERE card_id = :card_id
  AND spent + :amount <= spending_limit;

The application approves the authorisation only when the statement affects one row. If it affects zero rows, the limit does not permit the additional amount.

The important improvement is not fewer network calls, although that helps. The condition and mutation are evaluated by the same authority as one database operation. Another transaction cannot slip between the application check and the write.

This is a broadly useful pattern:

When possible, turn the invariant into the condition of the write.

Examples include reserving available inventory, advancing a state machine only from an expected state, claiming an unowned job, or deducting funds only when the remaining balance stays valid.

UPDATE inventory
SET available = available - :quantity
WHERE product_id = :product_id
  AND available >= :quantity;

UPDATE jobs
SET status = 'running',
    worker_id = :worker_id
WHERE id = :job_id
  AND status = 'pending';

Conditional writes move correctness away from an observation that can immediately become stale and towards an atomic state transition. They are often preferable to introducing explicit locks because the database already knows how to coordinate the conflicting writes.

Their limit is structural. The invariant must fit into the statement and the transactional authority evaluating it. When it depends on an absence, a set of rows, or a predicate whose matching set can change, row-level conditional updates may not be enough.

Write Skew Is the More Interesting Failure

Write skew exposes the weakness of reasoning only about conflicting writes.

Consider two doctors, A and B. Both are currently on call. The invariant is that at least one doctor must remain on call.

Transaction T1 reads both rows, sees that B is available, and marks A off call. At the same time, T2 reads both rows, sees that A is available, and marks B off call.

Initial state
A = on call
B = on call

T1                                  T2
read A = on, B = on
                                    read A = on, B = on
confirm B remains on
                                    confirm A remains on
update A = off
                                    update B = off
commit
                                    commit

Final state
A = off call
B = off call

The transactions update different rows. There is no conventional lost update. Each write can succeed without overwriting the other, yet together they violate the invariant.

The invariant is predicate-level: the set of rows satisfying on_call = true must never be empty. Protecting one known row is not the same as protecting the truth of that predicate while concurrent transactions change which rows match it.

Write skew appears in less theatrical forms throughout production systems:

• two reservations consume capacity calculated from the same aggregate
• two administrators each remove the other administrator's final permission
• two workers claim different records while violating a shared quota
• two transfers independently rely on the same available collateral
• two configuration changes each assume the other safety control remains enabled

These operations can pass code review because every transaction appears internally consistent. The error exists only in the concurrent history.

Why Snapshot Isolation Still Allows Write Skew

Snapshot isolation gives each transaction a consistent view of committed data from a particular point in time. That is a strong and useful guarantee. A transaction does not see a collection of rows changing underneath individual reads.

But a consistent snapshot is not necessarily a serializable execution.

In the doctor example, both transactions can read the same valid snapshot. Each updates a different row, so neither necessarily encounters a direct write conflict. Both can commit even though no serial execution would allow that result.

If T1 had run completely before T2, then T2 would have seen A off call and would not have taken B off call. If T2 had run first, T1 would have seen B off call. The final outcome cannot be explained by either serial order. That is a serialization anomaly.

Database terminology requires care here. Labels such as READ COMMITTED, REPEATABLE READ, SNAPSHOT and SERIALIZABLE do not imply identical implementation details across products.

In PostgreSQL, Repeatable Read is implemented using snapshot isolation. It provides a stable transaction snapshot and prevents several familiar anomalies, but PostgreSQL explicitly documents that serialization anomalies remain possible. PostgreSQL Serializable adds monitoring for read-write dependencies and aborts transactions when allowing all of them to commit could produce a result inconsistent with every serial ordering.

Other databases may map the same labels to different locking or multiversion behavior. Some expose Snapshot Isolation separately. Some strengthen or weaken particular levels beyond the minimum described by the SQL standard. Engineers must verify the guarantees of the database and version they actually operate, not reason from the label alone.

The practical question is not whether an isolation level sounds strong. It is whether the executions permitted by that implementation can violate the invariant.

What Serializable Isolation Actually Guarantees

Serializable isolation promises that the effect of committed concurrent transactions is equivalent to some serial ordering of those transactions.

It does not mean that only one transaction physically executes at a time. It does not mean there is one global database lock. It does not mean the application will never need to retry.

A database can provide serializability through strict locking, optimistic conflict detection, multiversion concurrency control, predicate tracking, or a combination of techniques. The implementation may permit transactions to run concurrently and then reject one when their combined dependencies cannot be serialized safely.

For the on-call example, a serializable database must prevent both conflicting transactions from committing. One may succeed while the other receives a serialization failure. The rejected transaction must restart from the beginning and make its decision from a new state.

This retry is part of the contract, not an incidental database error.

attempt transaction
        |
        v
serialization failure?
   |             |
  yes            no
   |             |
retry whole      commit result
transaction

Application code must retry the complete transaction, including every read that informed its decisions. Retrying only the final statement can preserve stale reasoning and recreate the problem.

Serializable isolation is powerful when an invariant depends on multiple rows or predicates and the database can observe all relevant reads and writes. It also has costs: dependency tracking, aborted work under contention, retry complexity, and potentially surprising failures when traffic patterns change.

The right conclusion is not to use Serializable everywhere or nowhere. Use it when the invariant requires a serializable history and a simpler enforcement mechanism cannot express the rule safely.

Pessimistic Concurrency Control Coordinates Before the Write

Pessimistic locking assumes that a conflict is important or likely enough to coordinate before proceeding.

In PostgreSQL, a row can be selected for update:

BEGIN;

SELECT balance
FROM accounts
WHERE id = :account_id
FOR UPDATE;

UPDATE accounts
SET balance = :new_balance
WHERE id = :account_id;

COMMIT;

Another transaction attempting to update or acquire a conflicting lock on that row must wait until the lock is released. This can make a read-check-write sequence safe when all competing operations lock the same authoritative row before making their decisions.

Pessimistic concurrency control works well when:

• the rows that represent the invariant are known
• conflicts are common or expensive
• transactions are short
• contention is moderate
• callers can tolerate waiting

Its costs are equally concrete:

• blocked transactions consume time and resources
• inconsistent lock ordering can cause deadlocks
• long transactions increase latency and contention
• one hot row can serialize otherwise independent work
• locking the wrong rows creates confidence without protection

The last point matters. Locking both doctor rows protects the example because all participants coordinate on the complete relevant set. Locking only the row each transaction intends to change does not protect the cross-row invariant.

Locks also operate inside one database authority. A FOR UPDATE lock cannot prevent an external payment provider or another database from changing its own state.

A lock is not free correctness. It trades concurrency for explicit coordination.

Optimistic Concurrency Control Detects Stale Decisions

Optimistic concurrency control allows transactions or requests to proceed without holding a lock for the full decision period. It detects whether the state changed before accepting the write.

A common implementation uses a version column:

id        balance        version
42        1000.00        17

The application reads the record and version, computes the next state, and submits a conditional update:

UPDATE accounts
SET balance = :new_balance,
    version = version + 1
WHERE id = :account_id
  AND version = :expected_version;

If one row is affected, the version was still current and the update succeeded. If zero rows are affected, another operation changed the record. The caller must retry from fresh state, resolve the conflict, or report it.

Optimistic concurrency control is attractive when:

• contention is relatively low
• holding database locks across application work would be expensive
• conflicts can be detected using a clear version boundary
• operations can be retried safely
• surfacing a conflict to a user is acceptable

It is not automatically weaker than pessimistic locking. The two approaches coordinate at different times. Pessimistic control prevents a competing change before it happens. Optimistic control allows the work and rejects a stale write when it attempts to commit.

Both approaches can fail when the versioned or locked object does not represent the full invariant. A version on each doctor row would not detect write skew if each transaction updates a different doctor. One shared schedule version could, but that data-model decision deliberately creates a common coordination point.

Optimistic vs Pessimistic Concurrency Control

The choice is not simply performance versus correctness. Either approach can be correct or incorrect depending on the invariant and implementation.

If conflicts are rare, optimistic control avoids paying coordination cost on every operation. If conflicts are frequent and expensive work happens before detection, pessimistic locking can prevent repeated wasted effort.

Neither should be selected from habit. Measure contention, understand retry cost, and prove that the coordinated resource actually covers the invariant.

Retries Change the Correctness Problem

Serializable transactions, optimistic concurrency failures and deadlocks can all produce legitimate retries. Once a transaction may execute more than once, retry safety becomes part of correctness.

A pure local transaction is comparatively straightforward. Roll it back, start again, repeat the reads, and attempt the writes against current state.

The situation changes when code inside the retry boundary performs an external effect:

• charging a card
• sending an email
• calling another service
• appending to an external ledger
• publishing a message outside the local transaction

The database can roll back its own rows. It cannot unsend an email or reverse an API call merely because the transaction later encounters a serialization failure.

begin transaction
read state
call payment provider        succeeds externally
update local state
commit                       serialization failure
retry transaction
call payment provider        duplicate charge

This is where concurrency control intersects with business-level idempotency: once an operation may legitimately execute more than once, its externally visible effect must still occur at most once from the business's point of view.

Keep external side effects outside retryable database closures where possible. Record the durable intention locally, commit it with the business state, and perform the external work through an outbox or workflow with a stable operation identity. If an external call must occur synchronously, use the provider's idempotency mechanism and persist enough evidence to reconcile an ambiguous outcome.

Retrying is not error handling around correctness. It is one of the executions the correctness design must support.

Database Constraints Are Stronger Than Application Checks

Consider user registration implemented as an application check:

if email_is_not_taken(email):
    insert_user(email)

Two requests can both observe that the email is absent before either inserts it. The check is true when evaluated and obsolete immediately afterwards.

A unique constraint expresses a different kind of rule:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE
);

Now the database refuses to commit a state containing duplicate email values. The application still needs to handle the constraint violation, but it no longer carries sole responsibility for coordinating concurrent inserts.

This leads to a durable engineering principle:

Put an invariant as close as possible to the authority capable of enforcing it atomically.

Database constraints are valuable because every writer is subject to them, including background jobs, migrations and future application code that may forget an earlier validation convention.

Different constraints protect different shapes of state:

• NOT NULL ensures required values exist
• CHECK protects conditions within a row
• UNIQUE protects uniqueness across rows
• foreign keys protect references between related tables
• exclusion constraints can prevent overlapping ranges or other conflicting combinations in PostgreSQL

Constraints do not replace useful application validation. Application checks provide better messages and avoid work that is already known to be invalid. The database constraint remains the final authority under concurrency.

Not Every Invariant Fits in a Constraint

Constraints are strongest when the database can directly reject an invalid state. Many important rules do not fit cleanly into a built-in constraint.

Examples include:

• aggregate limits across a changing collection of rows
• the requirement that at least one member of a set remains active
• state derived from several tables and time windows
• a payment state owned by an external provider
• capacity shared across services
• a workflow that must eventually reach one of several terminal outcomes

Even database-specific capabilities have boundaries. In PostgreSQL, a CHECK constraint is expected to depend on the new or updated row rather than query other table data. Cross-row rules may require a different model, a unique or exclusion constraint, explicit locking, or Serializable execution.

Sometimes the best fix is to redesign the data so the invariant has one authoritative representation. A card limit and its consumed amount in one row support an atomic conditional update. A schedule aggregate with one version can create an optimistic concurrency boundary for several assignments.

That concentration of authority has a performance cost, but it also makes the correctness rule enforceable. Distributing every fact for scalability and then rebuilding strong coordination around it can be more expensive than keeping the invariant together.

When one database owns the relevant state, the available tools include constraints, conditional writes, locks, optimistic versions and stronger isolation. When no single database owns the invariant, the problem has moved beyond database concurrency control.

Hot Rows Can Create Accidental Serialization

A logically correct design can still fail operationally if every request contends for one row.

100 concurrent workers
        |
        v
one account limit row
        |
        v
effective serialization

Global counters, wallet balances, inventory rows and account-level limit records are natural coordination points. Updating one row is attractive because it gives the database a clear authority. Under high contention, it can also create a queue of transactions waiting for the same lock or repeatedly failing optimistic checks.

The first response should not be to remove coordination and hope reconciliation repairs the damage. The invariant still exists.

Possible designs depend on what the business genuinely requires:

• partition the invariant when independent partitions are valid
• reserve capacity in bounded allocations or escrow buckets
• narrow the duration and scope of held locks
• use a queue when deliberate serialization matches the domain
• use sharded counters when an exact instantaneous total is not required
• move slow work outside the transaction
• reject or shed work when waiting would be harmful

Escrow-style allocation is particularly useful when a global quantity can be divided safely. Instead of every worker contending for the global stock total, each partition receives a bounded amount it may allocate locally. The sum of allocated rights never exceeds the global capacity.

This is more complex than one locked row. That complexity should be earned by measured contention, not introduced preemptively.

Correctness and throughput are not opposing goals, but preserving a global invariant requires coordination somewhere. The architecture can move, partition or batch that coordination. It cannot wish it away.

Where Database Transactions Stop Helping

Even Serializable isolation protects only state inside the authority of that transactional system.

Suppose a workflow reserves funds in PostgreSQL and then charges a customer through an external payment provider:

+------------------------+
| PostgreSQL             |
| SERIALIZABLE           |
|                        |
| protects this state    |
+-----------+------------+
            |
            | no shared atomic commit
            v
+------------------------+
| Payment provider       |
+------------------------+

The PostgreSQL transaction cannot atomically control the provider. A commit in one system and failure in the other still creates a partial outcome. The same limit applies across Service A's database and Service B's database.

The coordination progression is useful:

application check
        |
conditional write
        |
database constraint or lock
        |
serializable transaction
        |
multiple transactional owners
        |
distributed workflow

Once an invariant spans independently committed authorities, tools such as transactional outboxes, idempotent operations, sagas, compensation, durable workflow state and reconciliation become relevant. They do not extend one database transaction across the world. They preserve intent and progress while each authority commits locally.

The article on dual writes, outboxes, sagas and reconciliation develops that workflow model in detail. The key boundary here is simple: serializability can order transactions observed by one serializable authority. It cannot order state transitions that authority does not own.

A Practical Framework for Protecting Invariants

Start with the invariant, not the concurrency primitive. Then ask what authority owns the relevant state and which concurrent history would make the rule false.

This is guidance rather than universal law. Real designs often combine mechanisms.

A unique constraint may protect the final state while optimistic concurrency gives the API a clearer conflict response. A conditional update may reserve capacity while an outbox publishes the resulting event. A serializable transaction may protect local workflow state while idempotency protects a payment request outside the database.

The useful discipline is to explain why every mechanism exists:

• which invariant it protects
• which concurrent execution it prevents or detects
• what failure the caller sees
• whether retrying is safe
• where its authority ends

If the design cannot answer those questions, adding another lock or increasing the isolation level is unlikely to make it understandable.

Correctness Is About Histories, Not Just Final States

Databases make it natural to inspect current state. Concurrency correctness often requires asking how that state was reached.

A spending-limit system might eventually reconcile its total to R1,000 by reversing one R150 authorisation. The final number is valid. The history is not necessarily valid if the system promised both merchants that their transactions were approved at a time when only one should have been.

Likewise, an inventory count can return to zero after a later correction while two customers have already received confirmations for the last item. A unique row can exist after a cleanup job deletes its duplicate, even though downstream systems observed both records.

The relevant facts include:

• what each operation observed
• which operations overlapped
• which decision became externally visible
• what ordering the system promised
• whether compensation restores the business meaning or only the data shape

This is why audit records, operation identifiers and state-transition histories matter in high-consequence systems. They are not substitutes for concurrency control. They provide the evidence needed to understand whether the permitted history matched the invariant and to resolve the cases that cannot be repaired automatically.

Testing must also explore histories. A unit test that runs T1 and then T2 exercises one serial order. It does not demonstrate safety under overlap. Useful concurrency tests coordinate execution at known points, create competing reads before either write, and assert both the final state and the accepted outcomes.

The strongest test is not that both requests returned success. It is that every successful combination corresponds to a history the business permits.

Use the Weakest Coordination That Preserves the Invariant

Correctness is not a property obtained automatically by wrapping code in a database transaction.

It comes from defining the invariant, identifying the concurrent executions that threaten it, placing enforcement at the strongest useful authority, choosing an appropriate coordination mechanism, making retries safe, and knowing where the transactional boundary ends.

Atomic updates prevent some lost updates. Conditional writes combine a decision and mutation. Constraints make the database reject invalid states. Pessimistic locking coordinates before conflict. Optimistic concurrency detects stale decisions. Serializable isolation rejects histories that cannot be ordered safely. Each mechanism protects a different shape of invariant and imposes a different operational cost.

Use the weakest coordination mechanism that is still strong enough to preserve the invariant. Weaker than that is incorrect. Stronger than necessary can create blocking, retries, contention and complexity without buying additional safety.

Once no single transactional authority owns the invariant, the problem has stopped being purely a database-concurrency problem. It has become a distributed-systems problem, and correctness must be carried across boundaries through durable intent, idempotency, workflow state and reconciliation.

Further Reading

• PostgreSQL transaction isolation documentation
• PostgreSQL explicit locking documentation
• PostgreSQL constraint documentation