Database transactions make many state changes easier to reason about, but transaction boundaries alone do not guarantee that every business invariant survives concurrency. A particularly subtle failure is write skew: two transactions read overlapping state, update different rows, and both commit even though their combined result violates a rule.

This anomaly matters because each transaction can look correct in isolation. The defect appears only when valid decisions are made from snapshots that become incompatible once both writes are accepted.

Serializable transaction isolation is the general database mechanism for preventing this class of outcome. The key is to identify invariants that span multiple records, understand which isolation level actually protects them, and handle serialization failures as a normal concurrency result.

Start with an invariant, not a transaction

Suppose a support system requires at least one engineer to remain on call.

The table is small:

engineer_id | on_call
------------+--------
A           | true
B           | true

The invariant is:

count(on_call = true) >= 1

Either engineer may go off call as long as another engineer remains available.

A transaction might implement that rule like this:

BEGIN;

SELECT COUNT(*)
FROM engineers
WHERE on_call = true;

UPDATE engineers
SET on_call = false
WHERE engineer_id = 'A';

COMMIT;

The application performs the update only when the count is greater than one.

With one transaction at a time, this is correct. With two concurrent transactions, the result can be different.

See the skew in a timeline

Assume transaction T1 changes engineer A and transaction T2 changes engineer B.

T1                              T2
-----------------------------------------------
read A=true, B=true
                                read A=true, B=true

count = 2                       count = 2
decision: A may leave           decision: B may leave

write A=false                   write B=false

commit                          commit

The final state is:

engineer_id | on_call
------------+--------
A           | false
B           | false

Neither transaction overwrote the other’s row. There is no classic lost update. Each transaction changed a distinct record.

The problem is that both decisions depended on the same multi-row predicate: at least one other engineer is on call.

Write skew is therefore best understood as an invariant failure across concurrent decisions, not merely as competing writes to one value.

Snapshot isolation can still permit the anomaly

Many database systems offer isolation based on a consistent snapshot. A transaction sees a stable view of committed data from a defined point in time, so repeated reads are protected from several common anomalies.

That is useful, but a consistent snapshot does not automatically serialize decisions.

In the example, both transactions can read a snapshot containing two on-call engineers. If the database checks only for conflicting writes to the same row, the updates do not conflict:

T1 writes row A
T2 writes row B

Both commits may therefore be accepted under an isolation mode that permits write skew.

This distinction is essential:

same-row conflict
    !=
cross-row invariant conflict

A database can prevent one transaction from overwriting another transaction’s row while still allowing two individually valid writes to create an invalid aggregate state.

Serializable isolation protects the outcome

Serializable isolation requires concurrent transactions to produce results equivalent to some serial execution.

For the on-call example, consider the possible serial orders.

If T1 runs first:

T1 sees A=true, B=true
T1 sets A=false
T1 commits

T2 then sees A=false, B=true
T2 cannot set B=false

If T2 runs first, the symmetric result applies. In either serial order, one engineer remains on call.

A serializable database must therefore prevent the concurrent execution from committing as if both transactions independently observed the original state. Depending on the database engine, it may block an operation, detect a dangerous dependency pattern, or abort one transaction at commit time.

The application-level contract is the same: one transaction may have to retry.

Treat serialization failure as expected control flow

Serializable isolation does not mean every transaction waits until it can commit. Many implementations use optimistic techniques and abort a transaction when its observed dependencies cannot be reconciled with a valid serial order.

Application code must be prepared for that result.

Conceptually:

attempt transaction
       |
       v
serialization conflict?
   /          \
 yes           no
  |             |
rollback       commit
  |
retry with a fresh transaction

A retry must execute the complete transaction again. Reusing values read by the failed attempt defeats the protection because the retry needs a new view of database state.

A service boundary can use a bounded retry loop:

def remove_from_on_call(engineer_id):
    for attempt in range(3):
        try:
            with db.serializable_transaction() as tx:
                active = tx.query_value(
                    "SELECT COUNT(*) FROM engineers WHERE on_call = true"
                )

                if active <= 1:
                    return {"status": "rejected"}

                tx.execute(
                    "UPDATE engineers SET on_call = false "
                    "WHERE engineer_id = ?",
                    [engineer_id],
                )

            return {"status": "updated"}

        except SerializationConflict:
            if attempt == 2:
                raise

    raise RuntimeError("unreachable")

The exact exception type and transaction API depend on the database driver. The important properties are bounded retries, a fresh transaction for every attempt, and no externally visible side effect before the database outcome is settled.

Keep side effects outside retryable transaction bodies

A retryable transaction body may run more than once. That makes irreversible side effects dangerous inside it.

Consider this sequence:

1. read database state
2. send email
3. update database
4. commit fails with serialization conflict
5. retry
6. send email again

The database can roll back its own writes, but it cannot retract an email, payment request, message already accepted by a broker, or call already processed by another service.

Keep the transaction focused on database work. Record durable intent in the same commit, then perform external effects after the commit through a mechanism designed for reliable delivery, such as an outbox.

serializable transaction
   |
   +-- update domain rows
   |
   +-- insert outbox record
   |
 commit
   |
 publisher sends external message

This structure makes transaction retries safe because failed attempts leave neither committed domain changes nor committed outbox records.

Row locks help only when they lock the decision

A common response to concurrency defects is to add SELECT ... FOR UPDATE. That can be correct, but only when the locked rows cover the state that determines the invariant.

If every transaction locks all on-call rows before deciding, concurrent changes can be serialized through those locks. Yet this approach can become fragile when the protected set is defined by a predicate rather than a fixed row.

For example:

SELECT engineer_id
FROM engineers
WHERE team_id = 7
  AND on_call = true
FOR UPDATE;

The application is protecting the proposition that the team has at least one on-call engineer. Depending on the database engine, isolation mode, indexes, and locking semantics, locking the rows currently matching a predicate may not protect against every concurrent change that alters the predicate result.

A useful design question is:

What concrete database conflict forces two transactions that can violate this invariant to coordinate?

If the answer is unclear, relying on incidental row locks is risky.

Serializable isolation lets the database reason about the read-write dependency structure rather than requiring application code to manually predict every conflicting row.

A sentinel row can turn a predicate into a direct conflict

Sometimes an invariant belongs naturally to a stable aggregate or parent record. In that case, transactions can coordinate through one row.

For the on-call example, a teams row can serve as the coordination point:

BEGIN;

SELECT id
FROM teams
WHERE id = 7
FOR UPDATE;

SELECT COUNT(*)
FROM engineers
WHERE team_id = 7
  AND on_call = true;

UPDATE engineers
SET on_call = false
WHERE engineer_id = 'A';

COMMIT;

Every transaction that changes the team’s on-call membership first locks the same team row. The multi-row invariant now has an explicit serialization point.

This can be effective when contention is modest and the aggregate boundary is real. It also makes the coordination policy visible.

The trade-off is reduced concurrency. All relevant updates queue behind the same lock even when some pairs of changes could safely proceed together.

Constraints are stronger when the invariant fits them

Before reaching for transaction isolation, check whether the database can express the invariant directly.

Unique constraints, foreign keys, exclusion constraints, and CHECK constraints are powerful because correctness does not depend on every caller remembering a protocol.

For example, a unique constraint is ideal for a rule such as:

at most one active reservation for a seat

A multi-row lower-bound rule such as “at least one engineer remains on call” is harder to encode with ordinary row-level constraints. That is the territory where transaction-level coordination becomes important.

A useful preference order is:

declarative database constraint
        |
        v
explicit single-row coordination
        |
        v
serializable transaction

This is not a strict hierarchy for every system. It is a prompt to choose the simplest mechanism that fully protects the invariant.

Test the concurrent schedule, not only the function

A sequential unit test can confirm the business rule and still miss write skew completely.

A concurrency test should create the dangerous schedule deliberately:

initial state: A=true, B=true

T1 reads count=2
T2 reads count=2

T1 attempts A=false
T2 attempts B=false

both transactions finish

assert count(on_call=true) >= 1

Use synchronization barriers in the test so both transactions complete their reads before either proceeds to commit. Without coordination, the test may pass repeatedly simply because the database happened to execute the transactions one after another.

For a serializable implementation, acceptable outcomes include:

T1 commits, T2 rejects after retry

or:

T2 commits, T1 rejects after retry

The test should assert the invariant, not a particular winner.

Also test the retry path directly. Serialization failures may be uncommon in routine development traffic, so a retry defect can remain hidden until production contention increases.

Watch the cost of stronger isolation

Serializable isolation protects more relationships between concurrent transactions, so it can increase blocking, aborts, or retries under contention.

That cost should be measured rather than guessed.

Track metrics such as:

transaction attempts
serialization failures
retry count
retry exhaustion
transaction duration
lock wait time

A rising serialization-failure rate can signal a hot invariant, transactions that stay open too long, or access patterns that create unnecessary dependency cycles.

Keep serializable transactions short. Do not wait for user input, remote services, or slow unrelated work while holding a transaction open. Read the state required for the decision, apply the database changes, and finish promptly.

Distinguish contention from correctness

It is tempting to lower isolation when retries appear expensive. That can trade visible contention for silent invariant damage.

First preserve the correctness rule. Then reduce contention by changing the shape of the work.

Options include:

  • shrinking transaction duration;
  • partitioning independent aggregates;
  • introducing an explicit coordination row;
  • moving unrelated reads outside the transaction;
  • reducing unnecessary writes;
  • redesigning a global invariant into smaller independent invariants where the domain permits it.

The goal is not maximum parallelism. The goal is the maximum safe parallelism consistent with the domain rule.

A practical review method

When a transaction reads several records and then writes based on their combined state, review it in this order:

  1. State the invariant as a sentence or predicate.
  2. Identify every read that contributes to the decision.
  3. Identify every concurrent write that can change the truth of that predicate.
  4. Check the actual isolation level used in production.
  5. Confirm that conflicting transactions cannot both commit an invalid combined result.
  6. If serializable isolation can abort work, retry the complete transaction from the start.
  7. Keep irreversible external effects outside the retryable body.
  8. Add a synchronized concurrency test that exercises the dangerous schedule.
  9. Monitor serialization conflicts and retry exhaustion after deployment.

The most important shift is from asking whether each write is valid to asking whether the committed set of concurrent decisions preserves the invariant.

Write skew hides in the gap between those two questions. Serializable transactions close that gap by requiring the database to accept only outcomes that correspond to a valid serial order.

Software Engineering Databases Concurrency Transactions