Transactions, Normalization, and Performance

Concurrency and Isolation

---

Transactions become more interesting once several users may act at the same time. Even when each individual transaction is well-formed, two transactions running concurrently can produce a result that neither one would have produced on its own.

This chapter shows the most common concurrency problems with examples.

Concurrency Is Hard

If two transactions touch related data at the same time, the final result may depend on how their statements interleave in time. Sometimes the interleaving is harmless. Sometimes it produces a state that neither transaction intended.

The hard part is that concurrency-related bugs usually do not appear in testing. With only one user, there is nothing to interleave with. The bug only appears in production, intermittently, under load, when two requests happen to land at the same time.

The Lost-Update Problem

Consider an accounts table where each row tracks a balance. Two transactions simultaneously try to increment the balance of an account by ten.

Without any concurrency protection:

time	transaction A	transaction B
t1	reads accounts.balance -> 50
t2		reads accounts.balance -> 50
t3	writes accounts.balance = 60
t4		writes accounts.balance = 60

Both transactions did their work correctly. Both observed the same starting value, both added ten, both wrote back. The final value is 60, even though two deposits of ten happened. One increment was lost.

This is the classic lost-update problem, and it appears whenever the pattern is “read a value, compute a new value in application code, write the new value back.” Each step is fine on its own; the interleaving is the bug.

Loading Exercise...

The same shape can appear in many domains:

• two students enroll in the last available spot of a course instance,
• two librarians process the same book return at the same time,
• two members try to borrow the last copy of the same book.

Each of these can produce wrong answers if the underlying operation is not protected.

Isolation as the Goal

Isolation is the transaction property that addresses these concerns. The goal of isolation is to make concurrent transactions behave, as far as the application can tell, as if they had run one at a time.

The strongest version of this is called serializability. A concurrent execution is serializable if its final effect is equivalent to some one-at-a-time ordering of the same transactions. Either A then B, or B then A — but not the half-and-half result that the lost-update example produced.

Loading Exercise...

Real systems do not run at full serializability all the time, because making every transaction wait for every other one would be very slow. Instead, they offer isolation levels that trade off safety for concurrency.

PostgreSQL’s Isolation Levels

PostgreSQL supports four isolation levels, set per transaction:

• READ UNCOMMITTED — in PostgreSQL, this behaves the same as READ COMMITTED (no truly dirty reads),
• READ COMMITTED — the default; each statement sees a snapshot taken at the moment that statement started,
• REPEATABLE READ — the whole transaction sees a snapshot taken when the transaction started; repeated reads return the same data,
• SERIALIZABLE — the strongest level; the system detects when concurrent transactions would produce a non-serializable result and rolls one of them back.

The default READ COMMITTED is fine for most things. It prevents the most jarring concurrency artifacts but does not prevent the lost-update problem.

The SERIALIZABLE level prevents lost updates and most other concurrency anomalies automatically, at the cost of more rollbacks when under contention, i.e., having plenty of traffic. It is a reasonable choice for work where correctness matters more than throughput, but the application that uses the database has to be prepared to retry a transaction when the serializable check rolls it back.

Loading Exercise...

Explicit Locking with SELECT FOR UPDATE

The most direct fix for the lost-update problem is to lock the row before reading it, so that no other transaction can read or modify the same row until the lock is released.

BEGIN;

SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
-- application reads the value, computes new value

UPDATE accounts SET balance = 60 WHERE id = 7;

COMMIT;

The FOR UPDATE clause tells the DBMS to lock the matched rows for the rest of the transaction. Any other transaction trying to SELECT ... FOR UPDATE or UPDATE the same row has to wait until the first transaction commits or rolls back.

With FOR UPDATE in place, the lost-update timeline becomes:

time	transaction A	transaction B
t1	reads balance -> 50 (locks row)
t2		tries to read with FOR UPDATE, waits
t3	writes balance = 60, commits
t4		wakes up, reads balance -> 60
t5		writes balance = 70, commits

Now the writes are sequential. The final value is 70, which is correct.

The cost is that transaction B waits. If many transactions all want to touch the same row, they line up behind each other. For low-contention rows this is invisible; for hot rows it can become a bottleneck.

Loading Exercise...

A Simpler Fix: Atomic Updates

Whenever possible, the cleanest fix is to push the increment into the database itself, so that no application-side computation is involved:

UPDATE accounts SET balance = balance + 10 WHERE id = 7;

This is one statement. The database handles it atomically. Even with several transactions running concurrently, each one increments by exactly ten, and no update is lost.

The reason this works is that the database performs the read and the write as a single operation, with appropriate internal locking. The application never sees the intermediate value, so there is no opportunity for a stale value to be written back.

When the new value can be expressed as a function of the old value in pure SQL, this pattern is almost always preferable to the read-compute-write pattern with explicit locking.

When the new value depends on application logic that SQL cannot express (a complex calculation, a call to an external service), the read-compute-write pattern is unavoidable, and explicit locking with FOR UPDATE is the right tool.

Loading Exercise...

Deadlocks

Locking solves one problem but introduces a new one: deadlock. A deadlock happens when two transactions each hold a lock that the other one wants.

time	transaction A	transaction B
t1	locks row 1	locks row 2
t2	tries to lock row 2, waits	tries to lock row 1, waits
t3	(waiting for B)	(waiting for A)

Neither transaction can proceed. Most DBMSs detect this situation and roll one of the transactions back, choosing one as the “victim.” The application receives an error and has to decide whether to retry.

Deadlocks are usually the result of acquiring locks in different orders in different code paths. The most reliable prevention is to always acquire locks in the same order across the application — for example, always lock the lower-numbered id first. This is not always practical, but it eliminates a large fraction of deadlocks where it can be applied.

For most projects, deadlocks are rare. Knowing that they can happen, and that retrying is the right response, is usually enough.

Loading Exercise...

Concurrency in Different Domains

The same patterns appear across domains.

In the recipe app, two users adding the same recipe to favorites at the same time is fine — the (user_id, recipe_id) primary key ensures one of the inserts will fail with a duplicate-key error. No explicit transaction work is needed; the constraint handles it.

In the library, two members trying to borrow the last copy of a book is the classic concurrency case. A naive implementation might:

1. read the available copies of the book,
2. pick the first one,
3. insert a loan referencing that copy.

If two members do this at the same time, both may pick the same copy and both may insert a loan. The system would then have two active loans for the same physical copy, which is impossible in reality.

Two fixes work here. First, lock the copy row with SELECT ... FOR UPDATE before inserting the loan. Second, add a unique constraint that says a copy can only have one active loan at a time:

CREATE UNIQUE INDEX one_active_loan_per_copy
ON loans (copy_id)
WHERE returned_at IS NULL;

The second fix uses indexes that we discuss later in this part. It prevents the bad state directly, regardless of how the application code is structured. One of the two concurrent inserts will succeed, the other will fail with a unique-violation error, and the application can show the second member a “sorry, that copy was just taken” message.

This is a good general pattern: when a concurrency invariant can be expressed as a constraint, doing so is more reliable than expressing it through application-side locking. Constraints are checked atomically by the database; locking depends on the application code being correct.

Loading Exercise...

When to Worry About Concurrency

Three signs that an operation needs concurrency consideration:

• it reads a value, computes something from it, and writes the result back,
• it depends on the count of related rows (such as “the last available copy”),
• it allocates a unique resource (a seat, an inventory item, a slot).

For all three, the safer defaults are atomic updates where possible, unique constraints where the invariant fits, and explicit locking where the application logic genuinely needs to read-compute-write across multiple steps.

Loading Exercise...

For operations that do not touch shared state in this way — inserting a new independent row, updating one user’s own data — concurrency usually is not a concern. The database handles the row-level atomicity automatically.

SQL Programming Exercise

The following SQL sequence practices the concrete defenses from this chapter: locking before read-compute-write and using an atomic update when the database can do the computation directly.

Note that the grader does not check that you use BEGIN, COMMIT, ROLLBACK, and SELECT ... FOR UPDATE correctly. It only checks the final state of the database. As the point of the exercise is to practice these techniques, do use BEGIN, COMMIT, and SELECT ... FOR UPDATE appropriately.

Loading Exercise...

Check Your Understanding

1. What does the lost-update problem look like, and why does it happen even when each transaction is well-formed?
2. When is SELECT ... FOR UPDATE needed, and when does an atomic update like UPDATE ... SET col = col + 1 solve the same problem more cleanly?
3. Why is preventing a concurrency problem with a unique constraint often more reliable than preventing it with application-side locking?