Path 1 · lesson 3 of 8
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 level | Dirty read | Non-repeatable read | Phantom read | Serialization anomaly |
|---|---|---|---|---|
| Read Committed (default) | Not possible | Possible | Possible | Possible |
| Repeatable Read | Not possible | Not possible | Not possible in Postgres | Possible |
| Serializable | Not possible | Not possible | Not possible | Not possible |
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
---------------
50The 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
---------------
20Repeatable 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
---------------
70Write 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
---------------------
0Serializable 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
---------------------
1SELECT ... 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 outputapplication_name | state | wait_event_type | wait_event ------------------+--------+-----------------+--------------- session_b | active | Lock | transactionid
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
---------------
20Cassandra: 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 outputB wrote 100 - 30 = 70
A wrote 100 - 50 = 50
Final:
balance
---------
50A 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 outputB: 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
---------
20The 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 outputDetermining replicas for mutation Appending to commitlog Adding to accounts memtable Request complete
Read traceA lightweight transaction, traced (one node)
Real database outputAppending 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
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"]- 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
- Neither transaction wrote a row the other wrote. Repeatable Read would have committed both.
- 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.
- 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
@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
@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 UPDATEon 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 you should see
A final balance of 50 for the lost update and 20 for each fix; a serialization error for the Repeatable Read write; zero doctors on call under Repeatable Read and one under Serializable, with whichever transaction commits second cancelled; locking the read rows makes the second transaction wait and then see one doctor on call. In Cassandra, the stale lightweight transaction returns [applied] False with the current balance, and its trace has several Paxos steps and commit-log appends where the plain update has one.
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_activityshows 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_timeoutandidle_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_pidsnames the session each waiter is stuck behind. Follow the chain to the first one, oftenidle 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