Skip to content

Path 1 · lesson 3 of 8

Intermediate30 minPostgreSQL 18.7 · Apache Cassandra 5.0.9 (both run in Docker for this lesson)

Transactions and isolation

The anomalies concurrent transactions cause, which each Postgres isolation level prevents, and what Cassandra offers instead: one partition and compare-and-set.

What you will learn

  • Recognise a lost update and write skew in application code
  • Choose between an atomic update, a row lock, Repeatable Read and Serializable for a given race
  • Handle serialization failures by retrying the whole transaction
  • Explain what Cassandra isolates, and use a lightweight transaction for compare-and-set

Before you start

The concept

A transaction groups several reads and writes so that they succeed or fail together. Isolation is the part that says what a transaction can see of other transactions running at the same time. Perfect isolation would make concurrent transactions behave as if they ran one after another. Databases offer weaker levels by default, because perfect isolation costs waiting or retries.

Postgres isolation levelDirty readNon-repeatable readPhantom readSerialization anomaly
Read Committed (default)Not possiblePossiblePossiblePossible
Repeatable ReadNot possibleNot possibleNot possible in PostgresPossible
SerializableNot possibleNot possibleNot possibleNot possible
From the PostgreSQL 18 documentation. Postgres accepts Read Uncommitted but runs it as Read Committed.

Cassandra has no multi-statement transactions in 5.0. Each write to one partition is applied in isolation, a logged batch eventually applies all of its writes or none, and lightweight transactions (an IF clause on a write) give compare-and-set on one partition using the Paxos consensus protocol.

Why it exists

Most concurrency bugs in business systems are not crashes. They are a balance that is slightly wrong, a seat sold twice, a rota with nobody on it. They pass every test that runs one request at a time and appear only under real load, and the data they corrupt is already committed. Knowing which anomaly your isolation level allows is how you find them in a design review instead of an incident.

The engines differ because of where the data lives. A Postgres transaction runs on one primary, which can see every lock and every snapshot. A Cassandra cluster has no single place that sees all writes; coordinating across partitions would need consensus on every write, so Cassandra coordinates only when you ask, one partition at a time.

How it works inside

How the lab makes races repeatable

Races are hard to show because they depend on timing. The lab drives two real Postgres sessions, A and B, from one script with the dblink extension, one statement at a time, so the interleaving is the same on every run. Each scenario starts from a balance of 100; A withdraws 50 and B withdraws 30. The right answer is 20.

Lost update: read, compute, write back

The most common pattern in application code: read the balance, subtract in the application, write the new value. Under Read Committed, the Postgres default, both sessions read 100 and each writes its own answer.

Database outputLost update under Read Committed

Real database output
 b_writes_100_minus_30
-----------------------
 UPDATE 1

 a_writes_100_minus_50
-----------------------
 UPDATE 1

 a_commits
-----------
 COMMIT

 final_balance
---------------
            50
Both reads returned 100. A's write replaced B's: the final balance is 50, and B's withdrawal of 30 vanished with no error anywhere.

The cheapest fix is to stop computing in the application. SET balance = balance - 50 asks the database to read and change the row in one step; under Read Committed, an update that waits for another transaction's lock re-reads the newest version of the row before applying.

Database outputThe same race with an atomic update

Real database output
 b_subtracts_30
----------------
 UPDATE 1

 a_subtracts_50
----------------
 UPDATE 1

 a_commits
-----------
 COMMIT

 final_balance
---------------
            20
20, correct, with the same default isolation level.

Repeatable Read: a fixed snapshot, and a refusal

Under Repeatable Read a transaction sees a snapshot taken at its first statement and keeps seeing it. If it then tries to change a row someone else changed and committed since, Postgres refuses instead of overwriting.

Database outputA reads again after B's update, then tries to write

Real database output
 a_reads_again
---------------
           100

 a_update |                           a_error
----------+-------------------------------------------------------------
 ERROR    | ERROR:  could not serialize access due to concurrent update

 final_balance
---------------
            70
A still sees 100 though B has committed 70: that is the snapshot. A's update is rejected, so nothing is lost; B's 70 stands and A must retry from the start, reading 70.

Write skew: the anomaly Repeatable Read allows

Two doctors are on call. The rule: a doctor may go off call if at least one other doctor stays on. Alice and Bob each check ("2 on call, fine"), and each updates their own row. No row is written twice, so Repeatable Read sees no conflict.

Database outputWrite skew under Repeatable Read

Real database output
 alice_commit | alice_error
--------------+-------------
 COMMIT       | OK

 bob_commit | bob_error
------------+-----------
 COMMIT     | OK

 doctors_on_call_now
---------------------
                   0
Both commits succeed and nobody is on call: a state no serial order of the two transactions could produce.

Serializable tracks what each transaction read as well as what it wrote. When it finds a cycle of read-write dependencies, it cancels one of the transactions.

Database outputThe same two transactions under Serializable

Real database output
 bob_commit |                                      bob_error
------------+--------------------------------------------------------------------------------------
 ERROR      | ERROR:  could not serialize access due to read/write dependencies among transactions+
            | DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt. +
            | HINT:  The transaction might succeed if retried.

 doctors_on_call_now
---------------------
                   1
Alice commits, Bob's commit is refused, one doctor stays on call. When Bob retries, he sees one doctor on call and the rule stops him.

SELECT ... FOR UPDATE: wait instead of retry

When you must read, decide in the application and write back, lock the row as you read it. The second session waits for the first to commit, then reads the new value.

Database outputB waits on A's row lock

Real database output
 application_name | state  | wait_event_type |  wait_event
------------------+--------+-----------------+---------------
 session_b        | active | Lock            | transactionid
Session B, blocked in pg_stat_activity, waiting for A's transaction to end.

Database outputAfter A commits, B reads 50 and writes 20

Real database output
 b_reads_after_waiting
-----------------------
                    50

 b_writes_50_minus_30
----------------------
 UPDATE 1

 b_commits
-----------
 COMMIT

 final_balance
---------------
            20
Correct, at the price of B waiting. Under load, every withdrawal from the same account queues behind the one before it.

Cassandra: last write wins, unless you ask

The same read-compute-write in Cassandra loses an update in the same way. There is no error to catch: each write simply carries a timestamp, and the newest one wins.

Database outputLost update in Cassandra

Real database output
B wrote 100 - 30 = 70
A wrote 100 - 50 = 50
Final:
 balance
---------
      50
Same result as Postgres under Read Committed: B's withdrawal is gone.

A lightweight transaction adds a condition to the write. Cassandra applies it only if the condition holds, and returns [applied] with the current value when it does not.

Database outputCompare-and-set with IF

Real database output
B: UPDATE ... SET balance = 70 IF balance = 100
 [applied]
-----------
      True
A: UPDATE ... SET balance = 50 IF balance = 100
 [applied] | balance
-----------+---------
     False |      70
A retries with the balance it was shown: SET balance = 20 IF balance = 70
 [applied]
-----------
      True
Final:
 balance
---------
      20
A's write is refused and tells A the balance is now 70; A recomputes and succeeds. 20, correct. This is the Cassandra equivalent of retrying after a serialization failure.

The documentation warns that lightweight transactions have "a non-negligible performance cost, because Paxos is used". The trace shows why. A plain update:

Read traceA plain update, traced

Real database output
Determining replicas for mutation
Appending to commitlog
Adding to accounts memtable
Request complete
One commit log append, one memtable write.

Read traceA lightweight transaction, traced (one node)

Real database output
Appending to commitlog
Adding to paxos memtable
Reading existing values for CAS precondition
CAS precondition is met; proposing client-requested updates for 3c5f2ea2-e283-11f1-d76a-eb25578a697c
A Paxos prepare round, then a read of the current value, then propose and commit rounds, each written to the commit log: four commit-log appends in this trace instead of one. On a cluster with three replicas, each round is a network round trip to a quorum of them.

Architecture

Drawing diagram…

Decision flowchart. If the database can do the arithmetic, use an atomic update. Otherwise in Postgres use SELECT FOR UPDATE for one row or Serializable with retries for a rule across rows; in Cassandra use a lightweight transaction for one partition, or redesign so the invariant fits in one partition.

flowchart TB
  R["Race: two writers, one value"]
  R --> Q1{"Can the database do the arithmetic?"}
  Q1 -->|yes| A1["Atomic update: SET x = x - n"]
  Q1 -->|no, the app must decide| Q2{"Which engine?"}
  Q2 -->|Postgres, one row| A2["SELECT ... FOR UPDATE: wait"]
  Q2 -->|Postgres, rule across rows| A3["Serializable: retry on 40001"]
  Q2 -->|Cassandra, one partition| A4["Lightweight transaction: IF, retry if not applied"]
  Q2 -->|Cassandra, across partitions| A5["Redesign so the invariant lives in one partition"]
Choosing a defence against a concurrent update.
  • The cheapest defence is not to read at all: an atomic update needs no lock held across the application round trip and no retries.
  • A row lock makes the losers wait; Serializable and lightweight transactions make the losers retry. Waiting is simpler; retrying wastes less time when conflicts are rare.
  • An invariant across rows (at least one doctor on call) is invisible to row locks on the rows you write. Only Serializable, or locking the rows you read, catches it.

Step by step

Drawing diagram…

Sequence diagram. Alice and Bob each count two doctors on call and each sets themselves off call. Alice commits successfully. Bob's commit fails with error 40001 because of read/write dependencies. Bob retries, counts one doctor on call and stays on.

sequenceDiagram
  participant A as Alice's transaction
  participant DB as Postgres (Serializable)
  participant B as Bob's transaction
  A->>DB: count doctors on call
  DB-->>A: 2
  B->>DB: count doctors on call
  DB-->>B: 2
  A->>DB: set alice off call
  B->>DB: set bob off call
  A->>DB: COMMIT
  DB-->>A: OK
  B->>DB: COMMIT
  DB-->>B: ERROR 40001, read/write dependencies
  B->>DB: retry: count doctors on call
  DB-->>B: 1, so Bob stays on call
Write skew caught by Serializable, as in the lab.
  1. Neither transaction wrote a row the other wrote. Repeatable Read would have committed both.
  2. Serializable remembers that each transaction read rows the other then changed. That cycle means no serial order explains both results, so one transaction is cancelled.
  3. The retry is not optional: the application must run the whole transaction again, including the read, so the decision is made on fresh data.

In a real system

  • Look for read-compute-write in code review. An ORM that loads an entity, changes a field and saves it is exactly the lost-update pattern; under Read Committed it loses writes under concurrency.
  • Prefer atomic updates and constraints. balance = balance - $1 WHERE balance >= $1, a unique constraint, or a CHECK constraint enforce rules the database can see, with no retries.
  • If you use Repeatable Read or Serializable, build the retry first. The documentation says applications must retry the whole transaction on SQLSTATE 40001; without that code, a higher isolation level turns silent corruption into user-facing errors.
  • Keep transactions short. Locks and snapshots are held until commit; a transaction that waits on an HTTP call holds them for that long.
  • In Cassandra, use lightweight transactions sparingly, for uniqueness (INSERT ... IF NOT EXISTS) and the occasional compare-and-set, and design tables so one invariant lives in one partition.

Wrong, then right

A wallet service handles withdrawals. The first version loads the balance, checks it in Java and saves the new value, inside a Spring transaction at the default isolation level.

Incorrect implementation

WalletService.javaJava
@Transactionalpublic void withdraw(long accountId, int amount) {    Account acc = accounts.findById(accountId).orElseThrow();    if (acc.getBalance() < amount) {        throw new InsufficientFunds();    }    acc.setBalance(acc.getBalance() - amount);   // computed in the application    accounts.save(acc);                           // UPDATE accounts SET balance = ?}

Why it is written this way

  • Read Committed, the default, does not stop two withdrawals reading the same balance.
  • Both pass the funds check, and the second save overwrites the first.

Why it fails

  • Two withdrawals of 50 and 30 from 100 can end at 50 or 70 instead of 20, exactly as the lab showed. No exception, no log line.
  • The funds check can also be bypassed: two withdrawals of 80 from 100 both see 100, both pass, and the account pays out 160.

Corrected implementation

WalletService.javaJava
@Transactionalpublic void withdraw(long accountId, int amount) {    // One statement: the database reads, checks and writes the row atomically.    int updated = jdbc.update(        "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",        amount, accountId, amount);    if (updated == 0) {        throw new InsufficientFunds();    }}

Why it is written this way

  • The arithmetic and the funds check happen in the database, on the newest version of the row.
  • A concurrent withdrawal waits for the row lock, then re-evaluates balance >= ? against the committed value.
  • Zero rows updated means the funds check failed (or the account does not exist).

Trade-offs

  • It works because the whole rule fits in one row and one statement. A rule that spans rows (a daily limit across many transactions) needs SELECT ... FOR UPDATE on a row that represents the rule, or Serializable with retries.
  • Concurrent withdrawals from one hot account still queue on its row lock; for very hot accounts that becomes the bottleneck.

Configuration

The settings that decide isolation and conflict behaviour. Defaults are those of PostgreSQL 18 and Cassandra 5.0.

default_transaction_isolation (Postgres)Default read committed
What it does
The isolation level of transactions that do not set one.
When to change it
Raise it per transaction (BEGIN ISOLATION LEVEL SERIALIZABLE) where you need it and have retries, rather than globally.
lock_timeout (Postgres)Default 0 (no limit)
What it does
How long a statement waits for a lock before failing.
When to change it
Set it for application roles (a few seconds) so a stuck transaction causes errors instead of a pile-up of waiting connections.
idle_in_transaction_session_timeout (Postgres)Default 0 (no limit)
What it does
Ends sessions that sit idle inside an open transaction.
When to change it
Set it (for example 60s) so a forgotten transaction cannot hold locks and snapshots indefinitely.
SERIAL consistency (Cassandra, per request)Default SERIAL
What it does
The consistency of the Paxos rounds of a lightweight transaction: SERIAL across data centres, LOCAL_SERIAL within one.
When to change it
Use LOCAL_SERIAL in a multi-data-centre cluster when the invariant only needs to hold within one data centre; it avoids cross-region round trips.

Hands-on exercise

Reproduce each anomaly and its fix

Run the lab and see, on real Postgres 18 and Cassandra 5.0, a lost update, its atomic-update fix, Repeatable Read refusing a write, write skew passing Repeatable Read and caught by Serializable, a row lock making a session wait, and a Cassandra lightweight transaction refusing a stale write.

Steps · 0 of 5 done

What can go wrong

Higher isolation without a retry loop
Symptom
After switching to Serializable, users see occasional 500 errors under load.
Cause
Serialization failures (SQLSTATE 40001) are expected behaviour at this level, and nothing retries them.
Fix
Wrap the whole transaction, reads included, in a retry with a small number of attempts and jitter. Retry only on 40001 (and 40P01 deadlock).
Retrying only the failed statement
Symptom
Retries succeed but the data is still wrong.
Cause
The retry reused values read in the failed transaction. The decision was made on stale data.
Fix
Retry from BEGIN, re-reading everything.
Mixing lightweight and plain writes on the same data
Symptom
A Cassandra compare-and-set occasionally succeeds against a value that was already overwritten.
Cause
Plain writes do not take part in Paxos, so they can interleave with lightweight transactions without being seen by them.
Fix
If a column is protected by lightweight transactions, write it only with lightweight transactions.
Long transactions holding locks
Symptom
Requests pile up waiting; pg_stat_activity shows many sessions with wait_event_type Lock.
Cause
A transaction holding a row lock is waiting on something slow, such as an external API call.
Fix
Do slow work outside the transaction; set lock_timeout and idle_in_transaction_session_timeout.

How to troubleshoot

Sessions are waiting and requests time out

Check

SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid), state, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';
How to read it
pg_blocking_pids names the session each waiter is stuck behind. Follow the chain to the first one, often idle in transaction.
Errors with SQLSTATE 40001 in the application logs

Check

Count them per endpoint, and check whether those endpoints retry.
How to read it
Expected under Repeatable Read and Serializable. A steady low rate with retries is healthy; a high rate means hot rows or long transactions.
Cassandra lightweight transactions are slow or time out

Check

nodetool proxyhistograms (the CAS Read and CAS Write latency columns); trace a slow one with TRACING ON
How to read it
Contention on the same partition makes Paxos rounds retry. Many clients compare-and-setting one partition is a design problem, not a tuning problem.

Key takeaways

  • Read Committed, the Postgres default, allows lost updates from read-compute-write code; atomic updates avoid them with no retries.
  • Repeatable Read gives a stable snapshot and refuses to overwrite a newer committed row, but allows write skew across rows.
  • Serializable prevents all anomalies by cancelling transactions; it is only correct with a whole-transaction retry on SQLSTATE 40001.
  • SELECT ... FOR UPDATE makes competitors wait instead of retry.
  • Cassandra 5.0 isolates writes within one partition; a lightweight transaction (IF) gives compare-and-set through Paxos, at several times the work of a plain write.

Practical check

Small scenarios, one decision each. Pick what you would do, then read why.

Scenario 1 of 3

A ticketing service sells seats. Code reads remaining for an event, checks it is above zero, and writes remaining - 1. Under a flash sale, more tickets are sold than seats exist. Postgres runs at the default isolation level.

What is the simplest correct fix?

Scenario 2 of 3

A booking system lets a room be booked if no existing booking overlaps the requested time. Two requests for overlapping times arrive together; both check, both see no overlap, both insert. The transactions run under Repeatable Read.

Why did Repeatable Read not stop this?

Scenario 3 of 3

A Cassandra table stores user accounts by username. Sign-up must not let two people claim the same username, which is the partition key.

Which approach fits?

References

References: PostgreSQL 18 documentation: Transaction isolation ↗ · PostgreSQL 18 documentation: Explicit locking (row-level locks) ↗ · PostgreSQL 18 documentation: SELECT, the locking clause (FOR UPDATE) ↗ · PostgreSQL 18 documentation: Serialization failure handling ↗ · PostgreSQL 18 documentation: Client connection defaults (lock_timeout, idle_in_transaction_session_timeout) ↗ · PostgreSQL 18 documentation: dblink ↗ · Ports and Grittner, Serializable Snapshot Isolation in PostgreSQL (VLDB 2012) ↗ · Apache Cassandra 5.0 documentation: Data manipulation (IF conditions, BATCH) ↗ · Apache Cassandra 5.0 documentation: Guarantees (lightweight transactions and Paxos) ↗ · Apache Cassandra CEP-15: General Purpose Transactions (Accord) ↗ · Report a problem