One letter was left undeveloped in the previous lesson. The A for atomicity, the C for consistency and the D for durability we opened right up; the I for isolation we stated in four lines and postponed. This is the postponement.

The reason for postponing it is that isolation is not like the other three. Atomicity admits no degrees: a transaction is atomic or it is not. Neither does durability: what is committed survives or it does not. Isolation, by contrast, is a dial. The SQL standard defines four positions, each management system implements the ones it wants and how it wants, and choosing the wrong position produces errors that do not show up on the development laptop, do not show up in the tests, show up on a Tuesday at eleven in the morning at the Central branch front desk and cannot be reproduced.

Because that is exactly what has happened this week at BiblioRed. Copy EJ-3081 of "The Map of Time" shows as on loan to two different members at the same time. The North branch's autumn reading club has 25 seats and 26 people signed up, even though the system checked capacity before registering anyone. And a published event has been left with no speaker at all, despite the application preventing the last one from being cancelled.

None of the three is a programming error in the usual sense. The code that produces them reads well, passes review and works perfectly when a single person runs it. All three are concurrency failures, and this lesson is about recognizing them, triggering them at will and fixing them.

Everything that follows is designed for you to reproduce. Open two terminals with psql connected to the same database. Throughout the lesson we will call them Session A and Session B, and each demonstration states in what order each statement has to be run. Running them in another order gives another result, and that is precisely the lesson.

1. Why concurrency is essential and why it is dangerous
2. The laboratory: two terminals and the initial state
3. Phenomenon 1: lost update
4. Phenomenon 2: dirty read
5. Phenomenon 3: non-repeatable read
6. Phenomenon 4: phantom read and the last seat
7. Phenomenon 5: write skew, the surprising one
8. The four isolation levels of the SQL standard
9. How the level is set and what PostgreSQL really does
10. Lock-based concurrency control
11. Explicit locks: FOR UPDATE, FOR SHARE, LOCK TABLE, SKIP LOCKED
12. Multiversion concurrency control (MVCC) and why VACUUM exists
13. Deadlocks: how they arise, how they are detected, how they are avoided
14. Optimistic versus pessimistic locking
15. The complete solution: the last seat in the reading club
16. Outside PostgreSQL: SQLite and the return of the problem in NoSQL

Let us start with the obvious, because it explains why "doing it one at a time" is not enough.

Why it is essential. BiblioRed has four front desks, a web catalog open to 12,000 members, a mobile application and several automated processes (due-date notices, report generation). If operations ran strictly one after another, each front desk would wait for all the others to finish. And not only that: while one transaction waits for the disk to confirm its fsync() —those milliseconds from section 12 of the previous lesson—, the processor would sit idle. Concurrency is what allows one operation's waiting time to be another's working time.

Why it is dangerous. That last row is the whole lesson. When two transactions touch the same data at the same time, the result may depend on the exact order in which their statements interleave. And you do not control that order: it is decided by the operating system's scheduler, by network latency and by which page happened to be in cache in that microsecond.

The classic formulation of the problem is this:

The goal of concurrency control is that the interleaved execution of several transactions produces the same result as some execution in which they had run one after another. That property is called serializability.

Notice the "some": no specific order is required. If A and I run at the same time, it is fine for the result to be that of "A and then I" or that of "I and then A". What is not fine is for it to be a result that no sequential order would have produced. When that happens, we have an anomaly.

Before triggering anything, we have to set the stage. Open two terminals:

A very handy trick: make each session identify itself in the prompt and always show the process number, which will be needed when we talk about locks.

And the starting state of the data we are about to abuse:

One free seat. One available copy. Everything you need to break the system.

Lost update. Two transactions read the same piece of data, both compute a new value from what they read, and both write. The second write overwrites the first, which is lost without a trace and without an error.

This is the EJ-3081 failure. The front desk application does the natural thing: it reads the status, checks that it is available, and lends it.

The two front desks at the Central branch serve members at the same time. Session A is desk 1, serving Marta Alsina (member 14). Session B is desk 2, serving Iván Pereda (member 15). Run in the order of the "step" column:

Check the damage:

Two open loans of the same physical copy. Marta has taken it home and Iván is at the front desk asking where his book is. There has been no error, no exception, no trace. Both transactions were atomic, consistent and durable. And the result is impossible.

A's check (t3) and A's write (t8) are separated in time, and in that gap B read. B made its decision with information that stopped being true before B acted. It is what is known as an unprotected read-modify-write sequence.

Notice an important detail: no sequential order produces this result. If A had run in full and then B, B would have read on_loan and would have rejected the loan. The other way round, the same. The result obtained corresponds to no serial execution: it is an anomaly in the strict sense of section 1.

It is important to understand why this happens despite the fact that a standalone UPDATE is safe. Compare:

The second form is truly atomic: the management system locks the row to update it and evaluates the condition against the most recent version. If another transaction has already set it to on_loan, the reply is:

And that UPDATE 0 is the signal the application must read as "somebody got ahead of me, cancel the operation". Checking the number of affected rows is the cheapest defense there is against lost updates, and it is surprising how much code ignores it.

Dirty read. A transaction reads data that another has modified but has not yet committed. If the other one does a ROLLBACK, the first has worked with data that never existed.

In a management system that allowed dirty reads, at t4 Session B would read paid and the report would count a payment that never happened.

In PostgreSQL, at t4 Session B reads pending. Always. There is no way to trigger a dirty read, not even by asking for one explicitly:

PostgreSQL accepts the READ UNCOMMITTED syntax for standard compatibility, but internally treats it as READ COMMITTED. The reason is its architecture: as we will see in section 12, multiversion concurrency control means a transaction always reads a committed version of every row. It is not that reading dirty data is forbidden: it is that there is no mechanism with which to do it.

This does not mean the phenomenon is a historical curiosity. Other management systems do allow it —SQL Server with READ UNCOMMITTED or the infamous WITH (NOLOCK), MySQL with READ UNCOMMITTED— and some people turn it on "so the reports do not block anything". It is a bad idea: besides reading data that may disappear, on some engines it can read duplicated rows or skip rows if the index is reorganized during the scan.

Non-repeatable read. A transaction reads a row, and on reading it again within the same transaction it gets different values, because another transaction modified and committed it in between.

BiblioRed's management asks for a report that first counts the pending fines and then adds up their amounts:

The same query, within the same transaction, has returned two different values. The report A is building mixes figures from two instants: if the first read fed a total and the second a breakdown, the total and the breakdown do not match. And whoever receives it will think there is a calculation error.

At REPEATABLE READ, PostgreSQL takes a snapshot of the database on the transaction's first statement, and all later reads see that snapshot, ignoring whatever others commit afterwards. It is exactly what a report needs: a coherent photograph of one instant.

Practical rule. Every report that runs more than one query and presents the results together should go inside a REPEATABLE READ transaction. It is free, it is one line, and it eliminates the whole "the numbers do not add up" family at the root.

Phantom read. A transaction runs a query with a condition, and on repeating it new rows appear that satisfy that condition and that another transaction inserted and committed in between. The difference from the non-repeatable read is that there the values of a row changed; here the set of rows changes.

This is the reading club failure, and it is subtler than the previous ones because the code that produces it looks impeccable.

The registration application does the following: it counts the occupied seats, checks that some are free, and inserts.

Remember the state: event 51, 25 seats, 24 occupied, one free. Marta Alsina (14) signs up from her phone while Iván Pereda (15) signs up at the North front desk.

26 people for 25 chairs. On the day of the reading club, somebody is left standing.

This is the point to internalize, because it is counterintuitive:

Counting before inserting protects you from nothing. The count is true at the instant it is taken and stops being true immediately afterwards. Between the SELECT and the INSERT there is a gap, and a whole transaction fits through that gap.

And there is something worse. With the non-repeatable read, raising to REPEATABLE READ was enough. Here no snapshot is enough, because the problem is not what A sees: it is that A and B are deciding about the same seat without knowing it. Not even a lock on the rows read would help, because the conflicting rows —the other one's registration— did not exist yet when the read happened. You cannot lock a row that does not exist. Hence the name "phantom".

The real solutions are covered in section 15, and there are three of them, with different consequences.

The SQL standard says that REPEATABLE READ permits phantom reads. PostgreSQL does not permit them: its REPEATABLE READ is really snapshot isolation, and the snapshot covers the whole database, so rows inserted afterwards are invisible. It is stricter than the standard.

But be careful with the conclusion: that solves the read phantom, not the last-seat problem. Under REPEATABLE READ, A would not see B's registration... and would still insert its own, and both COMMITs would succeed because there is no write conflict on the same row. The result would still be 26. This is the natural bridge to the next phenomenon.

Write skew. Two transactions read the same set of data, each decides something based on it, and each writes to different rows. Neither overwrites the other, so there is no detectable conflict, but together they violate a rule that individually they respected.

It is the phenomenon that surprises even people who have worked with databases for years, because it happens at REPEATABLE READ / snapshot isolation, which many people take to be "the safe level".

House rule: every published event must have at least one confirmed speaker. Event 51 has two: Nuria Bastos and an external speaker. When cancelling a speaker, the application checks that one is left.

Zero speakers in a published event. And both transactions were right when they decided.

Notice why the management system did not protest: A wrote to speaker 7's row, B to speaker 9's. There is no conflicting row. The mechanisms that detect lost updates work at row level, and here there are no two writes on the same row. The conflict is in the premise: both read a set the other was about to modify.

Other examples of the same pattern, so you recognize it when you see it:

The only defenses against write skew are:

1. SERIALIZABLE isolation (with retries, because it will abort transactions).
2. Materializing the conflict: forcing both transactions to write to the same row, even artificially —for example, locking the events row with SELECT ... FOR UPDATE before touching its speakers—.
3. A database constraint that expresses the rule, when possible. Here it is not directly possible (a CHECK cannot count rows in another table), which illustrates the limit of declarative constraints against rules that span several rows.

In section 15 we will apply exactly these three ideas to the last-seat problem.

The SQL:1992 standard defined four levels, precisely in terms of which phenomena they permit. This is the canonical table, the one you have to know:

The last two columns deserve a note: write skew does not appear in the 1992 standard. It was described later, when snapshot isolation became popular and it turned out that it satisfied the standard's table up to REPEATABLE READ and still permitted anomalies. We include it because in practice it is the one that causes most problems today.

And now the table that really matters when you work with PostgreSQL:

Three readings of this table:

• PostgreSQL is stricter than the standard at REPEATABLE READ: it forbids read phantoms, which the standard permits.
• The default level is READ COMMITTED, which permits three of the four phenomena. It is not an oversight: it is a deliberate balance between correctness and performance, and it means that the isolation level your application is running at today, if nobody has touched it, is the second weakest.
• The jump in guarantee is between REPEATABLE READ and SERIALIZABLE, and it is the most expensive one: SERIALIZABLE is the only level that eliminates write skew, and it does so by aborting transactions.

It lies in when the snapshot is taken:

That "write conflict" row is the one that surprises people in production. Under REPEATABLE READ:

A's transaction is left aborted and has to be retried in full. It is not a failure: it is the management system refusing to produce an anomaly. But if your application does not know how to retry, the user sees an error.

Three ways, from most local to most global:

Checking the current level:

You can also change the server default in postgresql.conf with default_transaction_isolation, but it is not advisable: it makes the application's behavior depend on a file that is probably not in the same repository as the code. The level is a decision of the code, and it should be visible in the code.

Two modifiers useful for reports:

• READ ONLY prevents writes and allows the management system some optimizations.
• DEFERRABLE, combined with SERIALIZABLE READ ONLY, makes the transaction wait until it can take a snapshot that guarantees it will never abort due to serialization. It is ideal for a long nightly report: it may take a while to get going, but it will not fail halfway after twenty minutes of work.

Historically, the first answer to the concurrency problem was the lock: if a transaction is going to use a piece of data, it reserves it and everybody else waits.

The idea is intuitive: many can read at once, but writing requires exclusivity. The compatibility table reads like this: if A holds an S lock on row 3081 and B asks for another S, B goes through. If B asks for X, B waits.

A lock can be taken on units of different sizes, and there is a clear trade-off:

Some management systems practice lock escalation: if a transaction accumulates too many row locks, they replace them with a table lock to save memory, with the side effect of blocking everybody. PostgreSQL does not escalate locks: it stores the lock mark in the row itself, so it can hold millions without spending server memory. It is a notable practical difference.

The protocol that guarantees serializability through locks is called two-phase locking (2PL), and its rule is deceptively simple:

A transaction has a growing phase, in which it can only acquire locks, and a shrinking phase, in which it can only release them. Once it has released the first lock, it cannot acquire any more.

In practice, almost all management systems use strict 2PL: exclusive locks are held until the COMMIT or the ROLLBACK. That guarantees nobody reads uncommitted changes and simplifies recovery.

And it explains the biggest operational consequence of all this: the longer a transaction lasts, the longer it holds its locks and the more people wait. It is the technical justification for the "short transactions" rule from lesson 06-01.

The price of 2PL is that transactions wait for one another, and that is where the deadlocks of section 13 come from.

To complete the picture, PostgreSQL has eight table-level lock modes. You do not have to memorize them, but you should know they exist and when it takes them:

That last row is the cause of half the service outages during deployments: an ALTER TABLE waiting behind a long query, and the whole request queue waiting behind the ALTER TABLE, including the SELECTs that worked a moment ago. Viewing the locks in progress:

The wait_event_type = Lock on the second row is the unmistakable signature of "I am waiting for somebody else".

Besides the automatic locks, SQL lets you ask for them by hand. It is the tool of pessimistic locking (section 14).

Locks the rows read as if they were about to be modified. Any other transaction that tries to modify them —or lock them— waits.

Row 3081 is now reserved. Reproduction with two sessions:

And there is the key: when B finally gets the row, it sees it with the new value. Its "is it available?" check is now correct, and it will reject the loan. This resolves the lost update of section 3 cleanly.

Shared lock: it prevents others from modifying the rows, but allows others to read them with FOR SHARE. It is used when you need to guarantee that a row does not change while you work with related data, without intending to modify it yourself.

There are also two gentler variants, FOR NO KEY UPDATE and FOR KEY SHARE, which PostgreSQL uses internally for foreign keys and which allow more concurrency. Knowing they exist is enough.

Locks the whole table. It is blunt and almost always disproportionate.

Its legitimate use is the nightly maintenance process that needs a table to sit still, or the step of a migration that reorganizes data. On the path of a user operation, never.

Two modifiers that change what happens when the row is taken:

NOWAIT is for giving the user a fast answer instead of leaving them waiting:

The application translates that error into "another front desk is handling this copy right now, please try again", which is infinitely better than a frozen screen.

SKIP LOCKED is the basis of work queues. BiblioRed has a process that sends the due-date notices; with several workers in parallel, each one must take different notices:

Each worker gets ten notices that no other one is processing, with no waiting and no duplicates. It is a pattern that replaces a message queue in many medium-sized systems, and it works surprisingly well.

Locking has a serious flaw: if writing requires exclusivity, readers get in the writers' way and vice versa. Management's monthly report, which scans three million loans, would block the front desk for its entire run.

The solution adopted by PostgreSQL, Oracle and InnoDB is multiversion concurrency control.

MVCC. The management system does not overwrite the data: every modification creates a new version of the row. Each transaction sees the version that was visible at the moment of its snapshot. That way, readers never block writers nor writers readers.

Every physical row carries two hidden columns:

You can see them:

Now an UPDATE, and we look again:

The ctid —the row's physical address— has changed from (12,7) to (12,41). The row has not been modified: a new one has been written somewhere else, and the old one has been marked with xmax = 90118. An UPDATE in PostgreSQL is, physically, an INSERT plus a marking of the previous version.

When a transaction reads, it applies a simple rule: a version is visible if its xmin corresponds to a transaction committed before my snapshot and its xmax is zero or corresponds to a transaction not committed in my snapshot.

This design has an unavoidable consequence. After a while in operation, the copies table contains the current version of every row and all the old versions that nobody can see any more. They are called dead tuples.

Dead tuples cost in three ways:

1. Disk space, which grows without stopping.
2. Read time: a scan of the table also reads the dead tuples and discards them one by one.
3. Consumption of transaction identifiers, which are finite.

To see it:

VACUUM scans the tables and marks as reusable the space of the dead tuples that no transaction can see any more.

Variants:

Under normal conditions you do not have to run it by hand: the autovacuum process does it on its own. But you have to know what happens if it does not run:

• Bloat: the table takes up several times what it should and queries slow down progressively. A loans table with 2 GB of useful data can end up taking 9 GB.
• Stale statistics, and with them bad execution plans (06-03).
• Transaction identifier exhaustion: PostgreSQL uses a 32-bit counter. If VACUUM does not "freeze" the old rows in time, the server stops completely to avoid data loss, with an unforgettable message: database is not accepting commands to avoid wraparound data loss. It is one of the few ways to take a PostgreSQL database out of service through operational neglect.

VACUUM's number one enemy is long transactions. A transaction open for three hours forces all the dead tuples created since then to be kept, because in theory that transaction might need them. It is another reason —the third by now— for transactions to be short. Spotting the culprits:

That three-hour idle in transaction is exactly the pattern lesson 06-01 asked you to avoid with idle_in_transaction_session_timeout.

Deadlock. Two or more transactions wait for each other in a cycle: A waits for a resource B holds, and B waits for a resource A holds. Without outside intervention, they would wait forever.

The classic case: two transactions that touch the same two rows in reverse order. In BiblioRed, a transfer of copies between the Central and North branches, run at the same time in both directions.

In Session A:

The wait-for graph the management system has built:

A cycle. When the detector finds a cycle, it picks a victim transaction —usually the one that has done least work— and aborts it. The other carries on and finishes fine.

It does not check for the cycle on every wait: that would be extremely expensive. When a transaction has been waiting for longer than deadlock_timeout (1 second by default), and only then, it builds the graph and looks for cycles.

Consequence: a deadlock costs at least one second before it is resolved. On a system with many deadlocks, that alone is a performance problem.

To investigate them, it is worth turning on logging of slow lock waits:

The first is the fundamental one, and it is easy to apply. The copy transfer rewritten:

With both sessions taking the locks in the same order, the cycle is impossible: the second waits for the first and finishes afterwards. There is waiting, but there is no deadlock.

And about retries: a deadlock is identified by SQLSTATE 40P01, and a serialization failure by 40001. Both are transient and retryable. A serious application catches them and retries with an increasing wait and some randomness, up to a maximum of three or five attempts.

The two general strategies for protecting a read-modify-write sequence. The difference lies in the starting assumption.

It is the standard pattern. You add a column to the table that is incremented on every modification, and the update is only applied if the version is still the one that was read.

The flow, applied to editing a BiblioRed event from the management panel:

If nobody has modified the event:

If another manager modified it while our user was thinking:

And that UPDATE 0 is the whole detection. The application shows "another user has modified this event; review the changes and save again" instead of silently overwriting somebody else's work.

This pattern solves the problem from section 15.2 of lesson 06-01: there is no open transaction while the human decides. The transaction lasts as long as an UPDATE.

To automate the increment so that nobody forgets it, a trigger —of the kind we saw briefly in 05-04:

Careful: with the trigger, the application must no longer write version = version + 1 in its UPDATE, but it must still put AND version = ? in the WHERE. The condition is the protection; the increment is just bookkeeping.

• Optimistic if there is a human being between the read and the write, or if conflicts are rare (editing member records, cataloging materials, managing events). It is the default in web applications.
• Pessimistic if contention is high and conflict is the norm (lending the last copy at peak hour), or if redoing the work is expensive.

Let us go back to the problem from section 6 and really solve it. State: event 51, 25 seats, 24 occupied, two members signing up at the same time.

There are three correct approaches. All three work; they are not equivalent.

The idea: turn capacity into a piece of data held in a single row, with a declared constraint. That way the conflict stops being a phantom and becomes a collision on the same row, which the management system knows how to resolve.

And the registration:

With two simultaneous sessions:

In Session B:

Going above 25 is impossible. It does not matter what the isolation level is, it does not matter what the client is, it does not matter if tomorrow somebody writes a script that inserts by hand: the constraint is in the database and it is always enforced.

Two technical details worth understanding:

• The UPDATE ... SET x = x + 1 is not an application-level read-modify-write: the management system locks the row, reads the current value and writes. Under READ COMMITTED, when B is unblocked it re-evaluates its UPDATE against the most recent version, so it adds to 25 and not to 24.
• Under REPEATABLE READ, instead of the CHECK error, B would get could not serialize access due to concurrent update. Also correct, but it requires a retry.

Cost: the event's row becomes a serialization point. All registrations for that event queue up on it. For a 25-seat reading club that is irrelevant; for selling 60,000 tickets in two minutes it would be a bottleneck.

The idea: lock the event's row before counting, so that only one transaction at a time can be deciding about that event.

Session B waits on its FOR UPDATE until A's COMMIT, then counts 25, sees there is no room and rejects cleanly.

Advantages: it requires no new columns or denormalization, and the capacity logic (which can be complex: companions, seats reserved for school groups, waiting list) stays in one place.

Drawbacks: the protection lives in the application code. If another program, another team or a maintenance script inserts into registrations without taking the lock, the guarantee disappears without anybody noticing. And you have to remember always to lock the same row and in the same order relative to other locks, or the deadlocks of section 13 come back.

The idea: ask the management system for the full guarantee and let it detect the conflict.

With the two sessions interleaved as in section 6, the first to commit succeeds and the second receives, at the COMMIT:

PostgreSQL's SSI engine has detected that B read a set of rows that A modified, and that the combined result corresponds to no serial execution. It aborts B.

Advantages: it is the only solution that resolves all the phenomena at once, including the write skew of section 7. The application code is written as if there were no concurrency, which is enormously easier to reason about.

Drawbacks:

• It forces you to implement retries. Without them, the user sees a cryptic error.
• It has a cost: the management system tracks the read/write dependencies of every transaction.
• The error arrives at the COMMIT, when all the work has already been done.
• And, like approach 2, it does not protect against a script that uses another isolation level.

Reasoned recommendation for BiblioRed: approach 1, complemented with approach 3 where necessary.

The decisive argument is the one in the first row. A business rule as hard as "no more than 25 people fit" cannot depend on every program that touches the database, today and five years from now, remembering to take a lock or ask for an isolation level. The chk_capacity constraint is in the schema, it is always enforced, and on top of that it documents itself: anybody reading the definition of events will see the rule written down. It is the same argument as section 21 of lesson 04-04 about which rules belong in the database.

The occupied_seats_total counter is a denormalization —it has to be kept consistent with registrations, with the precautions of 05-04, including the periodic check that it adds up—, and that is its price. It is paid gladly.

And for the rules that cannot be expressed as a single-row constraint —the "at least one speaker" of section 7, which spans several rows of another table— you use approach 3 with retry, because it is the only one that covers them.

Concurrency is the biggest difference between SQLite and a database server, and it is worth being clear about it so as not to choose badly.

BEGIN IMMEDIATE deserves a note: in SQLite, a normal BEGIN is deferred and does not take the write lock until the first write, which produces the classic "I read, I decide, I write and I am told the database is busy" with the work already done. If the transaction is going to write, start it with BEGIN IMMEDIATE and you will take the lock from the outset.

The practical conclusion: SQLite is excellent with one writer and many readers. For BiblioRed's four front desks writing at the same time, no. For the inventory application on a tablet, perfect.

You will remember from 03-04 that eventual consistency is the price of availability and partition tolerance. What is worth adding here is that eventual consistency does not eliminate the last-seat problem: it makes it worse.

The tools change name but respond to the same ideas:

• The read and write quorum (w + r > n) is a way of buying consistency in a distributed system, analogous to raising the isolation level.
• MongoDB's writeConcern: {w: "majority"} is the explicit request for that guarantee.
• Atomic document operations ($inc, findAndModify with a condition) are the equivalent of approach 1's UPDATE ... SET x = x + 1 WHERE ...: the check and the write in a single indivisible operation.

The underlying lesson, worth taking away: concurrency is not a problem of relational databases, it is a problem of reality. Changing technology does not eliminate it; it changes the tools you face it with and, almost always, shifts more responsibility onto the application code.

Counting before inserting and believing that protects you. It is this lesson's mistake. A whole transaction fits between the SELECT count(*) and the INSERT. If the rule is hard, express it as a constraint.

Not checking the number of affected rows. An UPDATE ... WHERE status='available' that returns UPDATE 0 is saying "somebody got ahead of me". Ignoring it turns a perfect protection into decoration.

Using REPEATABLE READ without retry logic. At READ COMMITTED a write conflict waits and carries on; at REPEATABLE READ it aborts. Raising the level without retries swaps a silent error for a noisy one, which is better, but it is still an error the user sees.

Believing SERIALIZABLE is "the safe level and that is it". It is safe and it obliges you to retry. Without retries it is not safer: it just fails more.

Assuming the default level is the strictest. It is READ COMMITTED, the second weakest. Check it with SHOW transaction_isolation; before reasoning about anything.

Keeping a transaction open while a human decides. It locks rows, prevents VACUUM and fixes nothing that optimistic locking does not fix better.

Accessing the same rows in different orders in different parts of the code. It is the recipe for a deadlock. Adopt a canonical order —by ascending primary key— and respect it everywhere.

Diagnosing a deadlock as a database failure. It is not: the database did exactly what it should by detecting it and breaking the cycle. The failure is in the code's access order.

Ignoring VACUUM until it hurts. Keep an eye on n_dead_tup and last_autovacuum in pg_stat_user_tables, and chase down long idle in transaction transactions, which are the usual reason autovacuum cannot do its job.

Using VACUUM FULL during service hours. It takes an ACCESS EXCLUSIVE lock: it blocks even SELECTs. It is a maintenance-window operation.

Final tip: always reproduce the failure before fixing it. Every phenomenon in this lesson can be triggered with two psql terminals in under a minute. A concurrency bug you cannot reproduce is a bug you do not know whether you have fixed.

For each of these three real BiblioRed situations, state which phenomenon of the five studied is occurring, why the READ COMMITTED level does not prevent it, and what the most appropriate defense is.

(a) Management's monthly report shows "312 overdue loans" in the headline and, three pages further down, a breakdown by branch that adds up to 315.

(b) The nightly process that moves little-used copies from Central to South, and another one that brings them from South to Central, hang and one of the two dies with an error after a second.

(c) The East branch must always keep at least two copies of material 907 (school reading list). It has three. Two librarians simultaneously process a transfer of one copy each to other branches; both check that two would be left and both confirm. In the end one is left.

Using two psql terminals, reproduce the double loan of copy EJ-3081 from section 3. Then rewrite the loan operation so that it is impossible for two front desks to lend the same copy, without changing the isolation level and without adding new columns. Write the complete transaction and state what the application must check and what message it must show the operator.

The library wants to allow companions at events: a member can sign up with up to 3 companions, and the registrations.occupied_seats column records the total number of seats that registration consumes (1 + companions). In addition, every event reserves 5 of its seats for school groups, which members cannot take.

With these two new rules, decide which of the three approaches from section 15 you would use, justify it, and write the SQL of the solution.

Solution 1

(a) Non-repeatable read.

The report runs two different queries within the same session. Under READ COMMITTED, each statement takes a new snapshot, so the second query sees three loans that fell due —or that were registered— between one and the other. Each figure is correct at its instant; together they are incoherent.

Defense: wrap the report in a transaction with a stable snapshot.

For a long report, SERIALIZABLE READ ONLY DEFERRABLE is even better: it guarantees it will not abort halfway.

(b) Deadlock.

Two transactions that update the same set of copies in reverse order. READ COMMITTED has nothing to do with it: deadlocks are independent of the isolation level, because they arise from the order in which locks are acquired, not from visibility. The error after a second is exactly deadlock_timeout doing its job.

Defense: a canonical access order. Have both processes lock the affected copies with SELECT ... FOR UPDATE ... ORDER BY copy_id at the start of the transaction, whatever the direction of the transfer. And retry on 40P01 as a safety net.

(c) Write skew.

The two librarians read the same set (the three copies of material 907 in East), each decides they can move a different one, and each writes to a different row. There is no row conflict, so no row-based mechanism detects it. Neither READ COMMITTED nor REPEATABLE READ prevents it: it is the phenomenon of section 7.

Defense, in order of preference:

1. Materialize the conflict: lock a common row before deciding, for example the material's or the branch's.

   BEGIN;
   SELECT 1 FROM materials WHERE material_id = 907 FOR UPDATE;
   SELECT count(*) FROM copies
   WHERE material_id = 907 AND branch_id = 4 AND status <> 'withdrawn';
   -- if count > 2, transfer
   COMMIT;
   

2. SERIALIZABLE with retry, which detects it in general without having to anticipate the case.

A CHECK is no use here: the rule counts rows in another table, and that is outside what a declarative row constraint can express.

Solution 2

The reproduction is the table from section 3. The solution without changing the level or the schema, with pessimistic locking on the copy:

The second session waits on the FOR UPDATE until the first one's COMMIT, and then reads on_loan, so it aborts with ROLLBACK.

An even better variant, which does not even require the application to check anything beforehand:

In the losing session:

What the application must check: the number of rows affected by the UPDATE. If it is 0, run ROLLBACK.

What it must show the operator: a message that reflects reality and tells them what to do. For example: "Copy EJ-3081 is no longer available: another front desk lent it a few seconds ago. Check other copies of the same title or create a reservation." No "Database error" and no numeric codes: the person at the front desk has a member standing in front of them.

An additional note: the second variant is preferable to the first because it does the check and the write in a single statement, holds the lock for less time and does not depend on the application remembering to compare the status it read.

Solution 3

Approach chosen: number 1, the constraint in the database, adapted to the two new rules.

Justification: the two new rules —seats for companions and the school reservation— make the capacity logic more complex, and therefore easier to get wrong somewhere in the code. The more complicated a rule is, the more arguments there are for it to live in a single place and not in every program that inserts registrations. Besides, both rules are still expressed over values in a single row of events, which is exactly what a CHECK can verify.

Marta's registration with two companions:

If the event has 25 seats, 5 reserved for schools and 18 already occupied by members, the operation fails because 18 + 3 > 20:

Observations about the solution:

• chk_occupied_seats guarantees that the denormalized occupied_seats column cannot drift away from companions. It is the discipline of 05-04 applied: a computed column must carry its check alongside it. In fact, here it would be even better to declare it as a generated column (GENERATED ALWAYS AS (companions + 1) STORED), as we saw in 04-04.
• The name of the constraint matters: chk_capacity_members appears literally in the error message, and the application can translate it into "There are not enough seats left for you and your companions".
• A periodic check that occupied_seats_total matches the real sum of confirmed registrations is still necessary, exactly as explained in 05-04 for summary tables.
• If in the future a rule appeared that spanned several rows of other tables —for example, "a member cannot be registered for two overlapping events"—, that would no longer fit in a CHECK, and you would have to go to approach 3 with SERIALIZABLE and retry. It is worth knowing where that border lies.

This lesson has demonstrated something uncomfortable: code that is correct for one user can be broken code for two. The three faults we started with —the copy lent twice, the 26 people in a club with 25 chairs, the published event with no speakers— did not come from programming errors in the usual sense. They came from an implicit assumption the real world does not respect: that nothing happens between reading and writing.

We have triggered the five phenomena with two terminals and understood them from the inside out. The lost update, whose cheapest defense is to put the condition inside the UPDATE itself and look at how many rows were affected. The dirty read, which in PostgreSQL simply cannot happen. The non-repeatable read, which ruins every report with more than one query and is cured with one line. The phantom read, which teaches the most important lesson of all: counting before inserting protects you from nothing, because you cannot lock a row that does not exist yet. And write skew, which happens at high isolation levels, is detected by no row-based mechanism, and is only solved with SERIALIZABLE or by materializing the conflict by hand.

We have seen the canonical table of the four levels and —more useful still— the table of what PostgreSQL actually does: that it has no real READ UNCOMMITTED, that its REPEATABLE READ is snapshot isolation and forbids phantoms the standard permits, that its default level is the second weakest, and that the real jump in guarantee is at SERIALIZABLE, which is paid for with aborted transactions and therefore with mandatory retry logic.

Then we went down into the machinery: shared and exclusive locks, granularity and the fact that PostgreSQL locks rows and never escalates; two-phase locking, which explains why a long transaction is a collective problem; explicit locks, with FOR UPDATE to protect a read, NOWAIT to answer fast instead of freezing the screen and SKIP LOCKED to share out a work queue among several processes. We have opened MVCC up until we saw xmin, xmax and ctid changing before our eyes, and we have understood its bill: the dead tuples, the VACUUM that cleans them up and what happens when it does not get there in time. We have triggered a deadlock, read its real message, drawn its wait-for graph and learned that the fundamental defense fits in one sentence: always access rows in the same order.

And we have solved the last-seat problem three times, only to discover that all three solutions work and only one really protects. Pessimistic locking and SERIALIZABLE live in the application code, and are therefore lost as soon as another program touches the database. The chk_capacity constraint lives in the schema, is always enforced and documents itself. It is the same conclusion we reached in 04-04 and in 05-04, and we can now state it as a principle: a rule that cannot be broken must live where it cannot be bypassed.

With this, BiblioRed's system is now correct under concurrency. What remains is for it to be fast. Because there is a problem pending since the module's first line: the listing of overdue loans by branch, the very one we have just used in the exercises, takes fourteen seconds now that the loans table has passed two million rows. And fourteen seconds at a front desk with a member standing there is an eternity.

Lesson 06-03, Indexes and Query Optimization, is the one that fixes that —and it is the lesson 05-04 explicitly pointed us to when we said that indexes are the first thing to try before denormalizing. We will see what an index is and how a B-tree turns millions of comparisons into four reads; what an index costs, because they are not free and that is why you do not index everything; unique, composite, partial and expression indexes, with the leftmost prefix rule that explains why the order of the columns changes everything; what to index and what not to, including the warning that PostgreSQL does not index foreign keys on its own; how to read an EXPLAIN ANALYZE execution plan line by line and what it means when the estimated rows look nothing like the real ones; and the complete case of those fourteen seconds, with its plan before, its diagnosis, the index that fixes it and its plan after.