One train at a time: a guide to database locks in Postgres

Hi! I’m Areeb, a Product Engineer at Sequence.

On the 1st of January 2024, on a cold evening in London, I watched the premiere of Mr. Bates vs. The Post Office [1]. It dramatises the scandal in which hundreds of sub-postmasters were wrongly prosecuted for theft, fraud and false accounting because of a faulty computer system called Horizon. A system meant to be the infallible guardian of the truth, instead it twisted the fate of so many.

Long after the credits, I kept thinking about the trust we put in everyday things. We trust that when our trains run, only one train is on the track. We trust that when we deposit money, the balance is correct after. We trust that a canal boat can work its way through a lock without the water draining out beneath it.

The cure for all of these is simple, database locks. What follows are five images: a railway token, a lost transaction, a canal lock, two stubborn goats, and a bundle of notched sticks. Every one of them is the same idea in different clothes.

What you’ll learn

Database locks: what they are, when to use them, and when not to.

The concepts below are about distributed systems and are therefore applicable to different databases and systems. I’ll be focusing on Postgres in this post because it’s our primary datastore here at Sequence.

A brass token on a single track

Here is a problem that took Victorians years to solve. A single-track railway has one rail running between two towns, and needs to travel both ways along it. Two trains entering from opposite ends are only a timetable away from a collision. That timetable is a promise, kept by people.

So the railways stopped relying on judgement and made the permission physical. For each section of track there existed exactly one token: a heavy brass staff. A driver was forbidden to enter the section of track unless they were physically holding that token. And there was only one for each stretch of the track.

When they reached the far end they would hand it over, and only then could a train travel the other way. Tyer's electric tablet machines [2] made two trains on a track impossible: the instrument at one end would not release a second tablet while the first was still out.

The token is not a physical barrier, and it is not a copy of the track. It is permission to act, made into an object you can hold. While you hold it, the section is yours, and no one else may enter. The danger, two trains on one track, does not go away; it simply cannot happen.

A database is like a single-track railway with thousands of trains, and a database lock is its brass token.

The lost fifty pounds

I spent my first four years as an engineer on read-heavy systems, where I almost never had to think about locks. Two requests rarely touched the same row at the same instant, and when they did, the database quietly sorted it out. Then I joined Sequence, where they often do, and the assumption broke.

Here is the plainest version of how it breaks.

Your bank balance is £100. Two requests arrive at almost the same instant: a £50 inbound transfer and a £50 refund. The first reads the balance: £100. The second, a moment later, also reads £100, because the first hasn't finished. The first adds fifty and writes £150. The second, working from the £100 it read, adds fifty and writes £150 as well. You are now fifty pounds poorer than the arithmetic says you should be, and no error was raised, no alarm rang.

This is a lost update. To prevent it, we need a brass token: a database lock.

Before the first request reads the balance, it takes the token for that row; in SQL, SELECT ... FOR UPDATE. It now holds the row until it finishes its work and commits. The second request asks for the same token, is told to wait, and stands at the signal until the first hands it back. Then it reads, and now it reads £150, not £100, and adds its fifty to the right number. The fifty pounds is safe, because the second train waited.

The clearest way to see the lock at work is two psql sessions. The first takes the token and holds it; the second wants the same row and must wait:

-- SESSION A
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;   -- reads 100, now holds the row

-- SESSION B (a moment later)
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;   -- blocks: A is holding the token

-- SESSION A continues
UPDATE accounts SET balance = 150 WHERE id = 42;
COMMIT;                                                  -- token handed back here

-- SESSION B unblocks; its SELECT now returns 150, not 100
UPDATE accounts SET balance = 200 WHERE id = 42;
COMMIT;

Without FOR UPDATE, both sessions read 100, both write 150, and one £50 deposit vanishes without a trace. With it, session B cannot read the balance until session A commits, so it reads the true 150 and adds its fifty on top. The lock did one thing, and it was enough: it made B wait, and it held the row until COMMIT.

We needed the lock only because of the gap in time between reading and writing. Close that gap by reading, checking, and writing in one statement, and the engine holds the token for the briefest possible instant:

-- a deposit needs no explicit lock at all
UPDATE accounts
SET balance = balance + 50
WHERE id = 42;

-- a withdrawal that must never overdraw: the check lives inside the statement
UPDATE accounts
SET balance = balance - 50
WHERE id = 42 AND balance >= 50;

If the balance is too low, the WHERE clause matches nothing, zero rows change, and the withdrawal simply does not happen. No overdraft, and not a lock in sight. This stays correct even under Read Committed isolation [3], the setting Postgres uses by default: if two of these hit the same row at once, the second waits for the first to commit, then re-reads the fresh balance before subtracting its own fifty. The arithmetic always runs on the latest number, never a stale one.

Reach for FOR UPDATE when the new value depends on the old one in a way a single statement cannot express. Reach for the single statement whenever one will do.

So that is what a lock is: not something that hides your data, but permission to act on a piece of it, held for a stretch of time, enforced by the database. It comes in two variants. A write lock is the single brass token: one holder, everyone else waits. A shared lock is gentler: several readers may hold it at once, so they don’t block each other, but they will ensure any writes are blocked to the row until they are done.

In SQL the gentler token is FOR SHARE:

-- many sessions may hold this at once and all may read;
-- any attempt to write the row must wait until they are all done
SELECT credit_limit FROM customers WHERE id = 7 FOR SHARE;

You want it when a row must stay still while you depend on it. A customer's credit limit must not move while you insert the order that relies on it, but you are not the one changing the row.

The canal lock

The token showed what a lock is; the canal shows what it protects. And what it protects is not really the resource. It is a rule.

Consider the lock on a canal. A pound lock [4] cannot have both its gates open at once. You are sealed in the chamber while the water rises; the upper gate stays shut until the lower one is closed and the levels agree. The mechanism exists to enforce an invariant, never both gates open, because the moment that rule breaks, the canal empties and the boats sit in mud.

A database lock does the same work for rules like a ledger's debits and credits must always sum to zero; a seat must never be sold to two people; a counter must never skip a number. The lock is how you keep "never" true while several hands reach for the same thing at the same time.

Reach for a lock when two overlapping operations would otherwise break an invariant. That is the question to ask each time: what becomes untrue if these two run at once? If the answer is "nothing," you very probably do not need a lock at all.

The goats on the bridge

Every lock so far has helped; this one shows how locks can trap each other.

Older than the railways is a fable, old enough to be Aesop's [5], about two goats who meet halfway across a plank bridge too narrow for either to pass. Neither will back up. They lower their heads, they push, and they both fall into the river and drown. A deadlock.

It happens like this. Transaction A holds a lock on row 1 and now wants row 2. Transaction B holds row 2 and now wants row 1. Each is waiting for something the other will not release until it finishes, and neither can finish. Two goats, one bridge, heads down. Left alone they would wait forever.

Here the database plays executioner. It spots the deadlock, picks one goat, and shoots it: it kills that transaction, rolls it back, and raises a deadlock error. The survivor crosses. That leaves you two jobs: catch the error and retry the loser, and, better still, stop the standoff from forming at all.

Here is the standoff expressed as SQL:

-- session A                              -- session B
BEGIN;                                    BEGIN;
UPDATE accounts SET ... WHERE id = 1;     UPDATE accounts SET ... WHERE id = 2;
-- holds 1, now wants 2                   -- holds 2, now wants 1
UPDATE accounts SET ... WHERE id = 2;     UPDATE accounts SET ... WHERE id = 1;
-- both block until Postgres notices the cycle, then it kills one:
-- ERROR: deadlock detected (SQLSTATE 40P01)

The cure is to make both goats cross the same way: sort the rows you mean to lock and always take the lower id first, whichever way the work is flowing.

-- BOTH transfers do this, whichever way the money is flowing:
-- take the lower id first, then the higher.
BEGIN;
SELECT 1 FROM accounts WHERE id = 1 FOR UPDATE;   -- lower id first
SELECT 1 FROM accounts WHERE id = 2 FOR UPDATE;   -- then the higher
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;
-- no deadlock: every transaction takes id 1 before id 2, so no cycle can form

Because every transaction now takes id 1 before id 2, no two can each hold the row the other waits for, and the cycle cannot form.

And because a deadlock is something the database expects you to survive rather than prevent perfectly, the loser simply tries again. When Postgres kills a transaction it raises a specific error, deadlock detected, and the same is true of the optimistic conflict we will meet shortly. The pattern is always the same: catch that error, wait a few milliseconds, and run the whole transaction again from the top with fresh data. A retry or two is almost always enough.

Holding the token and wandering off

This is where people trip up. Eager to avoid the lost update, you start adding locks where they do not belong.

A lock is a closed gate. While you hold it, everyone who wants that row queues behind you. For a few milliseconds, that is exactly what you want. It becomes a disaster the instant you hold the token and wander off. Picture the driver who pockets the brass staff and strolls into the station cafe for lunch. He is not on the track. He is doing no harm to himself. He has also stopped the entire line, in both directions, until he comes back.

The software version of going for lunch is holding a lock across something slow that you do not control: a network call, a human, or a slow query you could have run beforehand. Do not do it. Take the token, do the one small thing, give it back. Keep your transactions short or the system may grind to a halt.

Most of the time, leave it alone

Every image so far has reached for the token; this section is about the times you should not. Most of the time you do not need it, and reaching for it only makes your system slower and more fragile.

The first reason is read operations. A well-built database does not make readers queue behind writers. Postgres uses a mechanism called multi-version concurrency control (MVCC), where a reader sees a consistent snapshot of the data rather than queuing behind whoever is writing it. The writer does not block the reader; the reader does not block the writer; nobody waits. So a plain query needs no lock. If you find yourself locking rows merely in order to read them, stop and ask why you don't trust the snapshot, because the snapshot is almost certainly fine.

In SQL, a report takes no locks at all. It asks for a consistent snapshot once and reads everything against it, while writers carry on untouched. (The default isolation level, Read Committed, refreshes that snapshot on every statement; Repeatable Read freezes it for the whole transaction, which is what we want when several reads must agree with each other.)

-- one frozen snapshot for the whole report, no locks taken
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT sum(total)  FROM orders  WHERE month = '2026-05';
SELECT sum(amount) FROM refunds WHERE month = '2026-05';   -- sees the same snapshot as the line
                                                           -- above, even if others commit between
COMMIT;

The second reason returns us to history, and to a way of never holding the token at all.

For centuries the English Treasury recorded debts on tally sticks [6]: notched lengths of hazel, split lengthwise so that creditor and Crown each kept a half. Neither half meant anything alone. At settlement you brought the two together, and if the notches and the grain did not match, the deal was void: someone had tampered, and you began again.

That is the analogue version of optimistic locking. Instead of holding a gate shut for the whole time you are thinking, you note the version of the row, go away and do your work, and at the moment you commit you check that the version still matches: UPDATE ... WHERE id = ? AND version = ?. If the update touches zero rows, someone got there first; you tear up your work and retry. You hold no lock across your think-time, and contention costs you only on the rare occasions it truly occurs. It is the right tool when collisions are unlikely. It is the wrong tool when they are constant, because then everyone is forever redoing work, like two clerks repeatedly drafting the same letter and tearing it up.

In SQL it is a read that takes no lock, followed by a write that is permitted only if the version still matches what you read:

-- 1. read the latest version
SELECT id, balance, version FROM accounts WHERE id = 42;   -- say version comes back as 7

-- 2. ... compute the new balance while holding nothing at all ...

-- 3. commit the work only if no one moved the row in the meantime
UPDATE accounts
SET balance = ?, version = version + 1
WHERE id = 42 AND version = 7;

-- rows affected = 0  ->  someone got there first; re-read and try again

If that UPDATE changes zero rows, someone committed ahead of you: re-read and try again. It is the same retry we used for the deadlock, and it means the same thing. Someone reached the row first, so start over with fresh data.

You never held a lock while thinking; the row stayed free for everyone else, and you paid a price only on the rare occasion of a real collision.

Two common patterns

Two uses of locking come up again and again.

The first is making sure exactly one copy of a job runs, even when three instances of your service wake up at midnight and all reach for it. A Postgres advisory lock uses a key provided by your application: you pick a number and ask for it, and either you get it or you don't.

BEGIN;
SELECT pg_try_advisory_xact_lock(42);   -- t: you hold it. f: another session already does
-- if it returned t, run the nightly close here; COMMIT releases the lock automatically.
-- if it returned f, another instance is already running it, so do nothing.
COMMIT;

The second is handing out work from a queue so that ten workers take ten different jobs and not one of them waits behind another. FOR UPDATE SKIP LOCKED is a brass token that says: if this row is already taken, do not queue for it; step over it and take the next free one.

SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED      -- ignore rows another worker has already claimed
LIMIT 1;

Wrap that select in a transaction, mark the chosen row as running, and commit. Ten workers can run it at the same instant and each gets a different job, because any row another worker is already holding is skipped rather than waited on.

Both keep the discipline from earlier: hold the token only long enough to do one small thing.

When to reach for the token, and when not

Reach for a lock when you must read a value and then write a new one based on it, and you cannot express the change in a single breath. Reach for one when money, stock, or seats are on the line and a lost update is a real-world wrong, not merely an untidy number. Reach for one when an invariant spans several rows and has to hold true across all of them at the same instant.

Leave the lock alone when you are only reading; trust the snapshot. Leave it alone when contention is low and a version column will scale better than a queue of waiting writers. Leave it alone, above all, when the database can do the whole thing atomically in one statement:

UPDATE accounts SET balance = balance - 10 WHERE id = 1 AND balance >= 10 reads, checks, and writes in a single indivisible motion, and the engine takes and releases the lock itself in the smallest possible instant, leaving you no read then write gap to mishandle. Prefer this to taking the token by hand.

And leave it alone when a constraint would do the job better: to stop two users registering the same email, you do not lock the table and check; you put a unique index on the column and let the second insert fail.

A constraint is a permanent rule the database keeps for you; a lock is a temporary one you have to remember to take, correctly, every single time.

The constraint SQL is shorter than the sentence describing it:

CREATE UNIQUE INDEX users_email_key ON users (lower(email));

Then you simply attempt the insert. If two requests race in with the same email, one succeeds and the other comes back with a unique-violation error, which your code turns into a tidy "that email is already taken". No lock, and no read then check window for two simultaneous requests to slip through. The database refuses the duplicate for you, the same way, forever.

The semaphore [7], the counter in computing that limits how many may use a resource at once, was named by Dijkstra after the railway signal; interlocking [8], after the Victorian signal-box levers built so a signalman could not send two trains at each other. The whole language of stopping concurrent things from colliding stemmed from the railway, because the railway is where we first learned, the hard way, that the cure is not to move faster or to trust harder, but to let some stretches of track take one train at a time.

Which brings me back to that cold January evening. The sub-postmasters learned the hardest possible way what happens when a system of record is trusted more than it has earned. Horizon was a far more tangled failure than a lost update, but it rhymes with one: a computer quietly insisting on a number that was not true, and people paying for it. The everyday version is far smaller, a balance off by fifty pounds, but a ledger that holds real money has no room for a number that is quietly wrong. And is exactly what our system is built to prevent. A database lock is one of the guards against it, written in software. Use it where the track is truly single. Everywhere else, let the trains run.

Core Takeaways

• A lock is permission to write data: writes are queued, and one finishes before the next begins. Take it with SELECT ... FOR UPDATE and hold it until COMMIT.
• It helps to avoid the lost update: Under Postgres's default isolation, two requests can both read £100 and both write £150, and one deposit disappears with no error and no alarm.
• Prefer one atomic statement to taking a lock by hand: do the read, check, and write in a single indivisible motion. Reach for FOR UPDATE only when the new value depends on the old in a way one statement cannot express.
• Most of the time, leave it alone: Reads need no lock; trust the snapshot. When collisions are rare, an optimistic version column beats a queue of waiting writers. When a rule must always hold, a unique constraint beats remembering to lock by hand.
• Hold it briefly, and order it: Don’t hold a lock across a network call, a person, or a slow query. Always acquire locks in the same order, lowest id first, to avoid deadlock, and retry the loser.
• The test: what becomes untrue if these two operations run at once? If the answer is nothing, you almost certainly do not need a lock.

If these types of problems sound interesting to you… we're hiring across New York and London! And we'd love to hear from you 🙌

References for the curious

Ref [1]: Mr. Bates vs. The Post Office, ITV, first broadcast 1 January 2024. In the Post Office Horizon scandal, more than 700 sub-postmasters were prosecuted between 1999 and 2015 on the strength of accounting shortfalls that the Horizon system reported but that frequently were not real. It is now widely regarded as one of the most widespread miscarriages of justice in British history.

Ref [2]: Tyer's Electric Train Tablet, the instrument that made the single-line token tamper-proof: a tablet could not be drawn from one end while another was still out. https://en.wikipedia.org/wiki/Tyer's_Electric_Train_Tablet

Ref [3]: Read Committed, is the default isolation level in PostgreSQL. When a transaction runs on this isolation level, a SELECT query sees only data committed before the query began and never sees either uncommitted data or changes committed during query execution by concurrent transactions. (However, the SELECT does see the effects of previous updates executed within this same transaction, even though they are not yet committed.) Notice that two successive SELECTs can see different data, even though they are within a single transaction, when other transactions commit changes during execution of the first SELECT. https://www.postgresql.org/docs/7.2/xact-read-committed.html

Ref [4]: A pound lock holds a boat in a sealed chamber between two gates while the water is raised or lowered to meet the next stretch of canal. Pound locks are over a thousand years old; the mitre gates that make them practical are often credited to Leonardo da Vinci in the 1490s. en.wikipedia.org/wiki/Lock_(water_navigation)

Ref [5]: The two stubborn goats meeting on a narrow bridge is a fable in the Aesopic tradition: neither will give way, both push, and both are lost in the river. en.wikipedia.org/wiki/Aesop's_Fables

Ref [6]: Split tally sticks were the English Exchequer's record of debt for some six centuries, until 1826. A notched hazel stick was split lengthwise so each party kept a half, and the two had to match at settlement, which made them tamper-evident. en.wikipedia.org/wiki/Tally_stick

Ref [7]: In computing, a semaphore is a counter that limits how many things may use a resource at once: set it to one and it behaves like a lock, set it to ten and it lets ten through. Edsger Dijkstra introduced it in the 1960s and named it after the railway signal. en.wikipedia.org/wiki/Semaphore_(programming)

Ref [8]: Railway interlocking, developed in Victorian signal boxes, mechanically linked the signal and point levers so a signalman physically could not set two conflicting routes at once. The name carried into computing for mechanisms that stop inconsistent states arising. en.wikipedia.org/wiki/Interlocking