Transactions, Isolation Levels and Locks

In lessons 0 to 5 one person ran one query at a time. A real shop has many sessions writing at once: two buyers for the last pair of headphones, a transfer that crashes halfway, a job queue with ten workers. This lesson reproduces each problem with two real psql sessions so you can see it happen, and then shows the fix. For the theory of ACID (atomicity, consistency, isolation, durability) and the full list of anomalies, read System Design, Lesson 21; for how an API uses transactions see Backend, Lesson 07; for the Prisma side see Full-Stack Q&A, Lesson 08. The tables are the shop of lesson 4, plus an accounts table for money moves.

Words used in this lesson

• A transaction is a group of statements treated as one unit. It ends with COMMIT (keep the changes) or ROLLBACK (throw them away).
• An isolation level is a rule for how much one transaction sees of the changes of others running at the same time.
• A snapshot is a frozen view of which rows are visible. PostgreSQL keeps old row versions (MVCC, multi-version concurrency control), so readers and writers do not block each other.
• An anomaly is a wrong result caused by concurrency. Names used below: non-repeatable read, lost update, write skew.
• A lock is a claim on a row or table. Others who want a conflicting claim must wait.
• A deadlock is a cycle of waiting: A waits for B and B waits for A.
• A serialization failure is an error that PostgreSQL raises to stop an anomaly. The right response is to retry the whole transaction.
• Idempotent means doing it twice has the same effect as doing it once.
• Dead rows are old row versions nobody needs any more. VACUUM cleans them up.

BEGIN, COMMIT, ROLLBACK: all or nothing

Take two accounts: Asha has 10,000 cents (100.00) and Ben has 5,000.

CREATE TABLE accounts (id integer PRIMARY KEY, owner text NOT NULL,                       balance_cents integer NOT NULL CHECK (balance_cents >= 0));INSERT INTO accounts VALUES (1, 'Asha', 10000), (2, 'Ben', 5000);

A transfer of 3,000 is two updates. Without BEGIN, psql is in autocommit mode: each statement is its own tiny transaction, committed at once. So if the program dies between the two statements:

UPDATE accounts SET balance_cents = balance_cents - 3000 WHERE id = 1;-- the app crashes here, the second statement never runsSELECT id, owner, balance_cents FROM accounts ORDER BY id;

 id | owner | balance_cents ----+-------+---------------  1 | Asha  |          7000  2 | Ben   |          5000

Three thousand cents have vanished. This is the atomicity problem. Wrap both updates in a transaction:

BEGIN;UPDATE accounts SET balance_cents = balance_cents - 3000 WHERE id = 1;UPDATE accounts SET balance_cents = balance_cents + 3000 WHERE id = 2;SELECT balance_cents FROM accounts ORDER BY id;   -- 7000, 8000ROLLBACK;SELECT balance_cents FROM accounts ORDER BY id;   -- 10000, 5000 again

Inside the transaction you see your own changes. After ROLLBACK nothing happened. If the connection dies before COMMIT, PostgreSQL rolls back for you, so the half-done state is never visible to anyone else. Replace ROLLBACK with COMMIT and both changes become permanent together.

An error aborts the whole transaction

A constraint (lesson 4) is a good safety net. Moving 20,000 from Asha, who has 10,000, inside BEGIN; ... COMMIT; gives:

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_cents_check"DETAIL:  Failing row contains (1, Asha, -10000).ERROR:  current transaction is aborted, commands ignored until end of transaction block

After the first error, PostgreSQL puts the transaction in an aborted state. Every later statement is refused, and a COMMIT is turned into a ROLLBACK (the COMMIT really did a rollback). Balances were unchanged. You cannot "ignore the error and carry on" in PostgreSQL, unless you use a savepoint.

Savepoints: undo part of a transaction

A savepoint is a bookmark inside a transaction. ROLLBACK TO SAVEPOINT name undoes everything after the bookmark and keeps the transaction alive. Example: build order 1, then try to add 5 headphones when only 1 is in stock (the CHECK (stock >= 0) from lesson 4 will refuse):

BEGIN;INSERT INTO orders (customer_id) VALUES (1);INSERT INTO order_items VALUES (1, 1, 2, 350);SAVEPOINT try_headphones;INSERT INTO order_items VALUES (1, 3, 5, 5900);UPDATE products SET stock = stock - 5 WHERE id = 3;ROLLBACK TO SAVEPOINT try_headphones;SELECT * FROM order_items;COMMIT;

ERROR:  new row for relation "products" violates check constraint "products_stock_check"DETAIL:  Failing row contains (3, Headphones, audio, 5900, -4). order_id | product_id | quantity | unit_price_cents ----------+------------+----------+------------------        1 |          1 |        2 |              350

The headphone row and the stock change are gone, the notebook line stays, and the order commits. ORMs use savepoints for "nested transactions".

Isolation levels in one table

PostgreSQL has four names, but three behaviours:

READ UNCOMMITTED behaves as Read Committed in PostgreSQL, so a dirty read (seeing uncommitted data) never happens. Set the level with BEGIN ISOLATION LEVEL REPEATABLE READ;. Below, sessions A and B run side by side, in the order shown.

Read Committed: a non-repeatable read

A non-repeatable read means the same query, twice in one transaction, gives two answers because someone else committed in between.

A| BEGIN;                          -- SHOW transaction_isolation says: read committedA| SELECT balance_cents FROM accounts WHERE id = 1;   -- 10000B| UPDATE accounts SET balance_cents = balance_cents - 2500 WHERE id = 1;   -- autocommitA| SELECT balance_cents FROM accounts WHERE id = 1;   -- 7500A| COMMIT;

Session A, still inside its transaction, saw 10,000 and then 7,500. In a report this can be a bug: a total that does not match the rows below it, because the numbers came from different moments. It is fine for many simple requests, and it is the default.

Repeatable Read: one frozen view

Same steps, but A starts with BEGIN ISOLATION LEVEL REPEATABLE READ;:

A| BEGIN ISOLATION LEVEL REPEATABLE READ;A| SELECT balance_cents FROM accounts WHERE id = 1;   -- 10000B| UPDATE accounts SET balance_cents = balance_cents - 2500 WHERE id = 1;A| SELECT balance_cents FROM accounts WHERE id = 1;   -- still 10000A| UPDATE accounts SET balance_cents = balance_cents - 1000 WHERE id = 1;A| ERROR:  could not serialize access due to concurrent updateA| ROLLBACK;   -- a new transaction now sees 7500

Two things happened. First, A's second read still says 10,000: it sees the snapshot from its first statement, not B's commit. Second, when A then tried to update the row that B changed after A's snapshot, PostgreSQL refused with a serialization failure instead of silently building on stale data. Under Read Committed the same update would simply have run on the new value. So with Repeatable Read your code must be ready to retry (see below).

Lost update, and the fix with FOR UPDATE

A lost update is the classic race. Two buyers both read stock = 10. The app computes the new value itself (10 - 3 and 10 - 4) and writes it back:

(both sessions in BEGIN)A| SELECT stock FROM products WHERE id = 1;   -- 10B| SELECT stock FROM products WHERE id = 1;   -- 10A| UPDATE products SET stock = 10 - 3 WHERE id = 1;B| UPDATE products SET stock = 10 - 4 WHERE id = 1;     -- waits for AA| COMMIT;B| COMMIT;                                             -- B's update ran after A committedB| SELECT stock FROM products WHERE id = 1;   -- 6

Seven were sold (3 + 4) and stock should be 3, but it is 6. A's sale was lost: B overwrote it with a number computed from a stale read. There are two good fixes.

Fix 1: let the database do the arithmetic. UPDATE products SET stock = stock - 4 WHERE id = 1 AND stock >= 4 is atomic. Under Read Committed the second session waits for the row lock, then re-reads the latest value. A row count of 0 means "not enough stock". This is the best fix whenever the new value is a simple function of the old one.

Fix 2: lock the row when you read it. SELECT ... FOR UPDATE locks the rows it returns until the end of the transaction:

(both sessions in BEGIN)A| SELECT stock FROM products WHERE id = 1 FOR UPDATE;   -- 10B| SELECT stock FROM products WHERE id = 1 FOR UPDATE;      -- B waits hereA| UPDATE products SET stock = stock - 3 WHERE id = 1;A| COMMIT;B|      7                                                  -- B's SELECT returns only now, with the new valueB| UPDATE products SET stock = stock - 4 WHERE id = 1 RETURNING stock;   -- 3B| COMMIT;

B waited at its SELECT ... FOR UPDATE until A committed, then read the fresh 7; final stock is 3. Use this when the app must read, think, then write. Keep the locked section short.

Write skew: Repeatable Read is not enough

Write skew is subtler. Two transactions each read the same set of rows, each decide "it is safe", and each update a different row. Each update alone is fine; together they break a rule. The standard story: a hospital needs at least one doctor on call. Both doctors are on call and both ask to go off.

CREATE TABLE oncall (doctor text PRIMARY KEY, on_call boolean NOT NULL);INSERT INTO oncall VALUES ('Asha', true), ('Ben', true);

Under Repeatable Read (both sessions run BEGIN ISOLATION LEVEL REPEATABLE READ; first):

A| SELECT count(*) FROM oncall WHERE on_call;   -- 2B| SELECT count(*) FROM oncall WHERE on_call;   -- 2A| UPDATE oncall SET on_call = false WHERE doctor = 'Asha';B| UPDATE oncall SET on_call = false WHERE doctor = 'Ben';A| COMMIT;B| COMMIT;A| SELECT * FROM oncall ORDER BY doctor;   -- Asha f, Ben f

Both committed. Nobody is on call. There was no error because the two updates touched different rows, so no row-level conflict exists. Repeatable Read cannot see the rule that links them. The same steps with ISOLATION LEVEL SERIALIZABLE:

A| UPDATE oncall SET on_call = false WHERE doctor = 'Asha';B| UPDATE oncall SET on_call = false WHERE doctor = 'Ben';A| COMMIT;B| COMMIT;B| ERROR:  could not serialize access due to read/write dependencies among transactionsB| DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.B| HINT:  The transaction might succeed if retried.A| SELECT * FROM oncall ORDER BY doctor;   -- Asha f, Ben t

PostgreSQL's Serializable level (called SSI, serializable snapshot isolation) tracks which transactions read what others wrote. It saw that A and B each read data the other changed, a pattern that cannot happen in any one-at-a-time order, and cancelled B. When B retried (BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) ...), it got count 1, so its code takes the "refuse" branch. B ran again against the new state and the rule held.

The retry loop

Retry the whole transaction, from BEGIN, on error code 40001 (serialization_failure) or 40P01 (deadlock_detected), never just the failed statement. The helper I ran with Node and pg:

async function withTransaction(fn, { isolation = 'SERIALIZABLE', maxTries = 5 } = {}) {  for (let attempt = 1; ; attempt++) {    const client = await pool.connect();    try {      await client.query(`BEGIN ISOLATION LEVEL ${isolation}`);      const result = await fn(client, attempt);      await client.query('COMMIT');      return result;    } catch (err) {      await client.query('ROLLBACK').catch(() => {});      const retryable = err.code === '40001' || err.code === '40P01';      if (!retryable || attempt >= maxTries) throw err;      console.log(`  attempt ${attempt} failed with ${err.code}, retrying`);      await new Promise(r => setTimeout(r, 20 * attempt + Math.random() * 20));    } finally {      client.release();    }  }}

Two calls ran at once; each reads the count and goes off call only if two or more are on (a 100 ms pause after the read makes the race certain). Output:

  attempt 1 failed with 40001, retryingAsha: now off call, attempt 1Ben: refused (only 1 on call), attempt 2[ { doctor: 'Asha', on_call: false }, { doctor: 'Ben', on_call: true } ]

Ben's second attempt saw one doctor and refused. Rules for the loop: limit the tries, add a small random delay, and keep no side effects outside the database inside (an email would be sent twice). Many ORMs do not retry for you.

Rule of thumb: Read Committed for most requests (with atomic UPDATEs and FOR UPDATE); Repeatable Read for reports that need one consistent picture; Serializable when a rule spans several rows and you cannot lock them, and you will write the retry loop. For write skew you can also lock all the rows you read, or turn the rule into a constraint (unique index, exclusion constraint) so the database enforces it for every session.

Row locks, table locks, and what waits for what

An UPDATE or DELETE takes a row lock on each row it changes. A second writer to the same row waits until the first commits or rolls back. Plain reads never wait. Besides rows, every statement takes a lock on the table. I looked at what session A held after one UPDATE inside a transaction:

(Query: SELECT locktype, relation::regclass, mode, granted FROM pg_locks WHERE pid = pg_backend_pid(), filtered to relation and transactionid locks.)

   locktype    |      rel      |       mode       | granted ---------------+---------------+------------------+--------- relation      | pg_locks      | AccessShareLock  | t relation      | accounts      | RowExclusiveLock | t relation      | accounts_pkey | RowExclusiveLock | t transactionid |               | ExclusiveLock    | t(4 rows)

An UPDATE takes RowExclusiveLock on the table and each index. That sounds scary but it is a weak table lock: many sessions can hold it at once, so many updates can run in parallel. The row lock itself is not listed in pg_locks (it is stored in the row; waiters queue on the transactionid of the transaction that owns it). A plain SELECT takes AccessShareLock, the weakest of all.

Other table locks, from the documentation: SHARE UPDATE EXCLUSIVE (VACUUM, CREATE INDEX CONCURRENTLY) lets reads and writes continue; SHARE (plain CREATE INDEX) blocks writes; ACCESS EXCLUSIVE (most ALTER TABLE, DROP, TRUNCATE, VACUUM FULL) conflicts with everything, even SELECT. I observed the first two modes above and the ACCESS EXCLUSIVE wait below, not each other mode.

The lock queue: why a small ALTER TABLE can freeze a table

ACCESS EXCLUSIVE conflicts with everything, and lock requests are served in line. So a waiting ALTER TABLE blocks everyone behind it, even plain reads. I reproduced this with three sessions. A opened a transaction and ran a harmless SELECT count(*) (holding AccessShareLock, and not committing). B ran ALTER TABLE accounts ADD COLUMN note text. Then C, a third psql with \timing on, ran a plain SELECT count(*) FROM accounts. While they waited, pg_stat_activity showed:

In pg_stat_activity both B (ALTER TABLE ...) and C (SELECT count(*) ...) showed wait_event_type = Lock, wait_event = relation.

C's innocent SELECT finally returned Time: 2241.485 ms: it waited 2.2 seconds, only because it stood behind B's waiting ALTER, and B waited for A. When A committed, B ran, then C. On a busy production table, one forgotten open transaction plus one ALTER TABLE stops every query on that table. This is the main reason lesson 7 uses lock_timeout for migrations.

You can fail fast instead of waiting. With SET lock_timeout = '1s' the ALTER gave up (A held its lock; I ran this):

ERROR:  canceling statement due to lock timeout

SKIP LOCKED: a job queue in one query

A common job: many workers must each take a different queued job. If all use SELECT ... FOR UPDATE, they queue up behind the first job. SKIP LOCKED says "skip rows that are locked by someone else":

CREATE TABLE jobs (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,                   payload text NOT NULL, status text NOT NULL DEFAULT 'queued');INSERT INTO jobs (payload) SELECT 'email-' || g FROM generate_series(1, 5) g;

Two workers, each in a transaction, each asking for two jobs:

A| BEGIN;B| BEGIN;A| SELECT id, payload FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED;A|   1 | email-1A|   2 | email-2B| SELECT id, payload FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED;B|   3 | email-3B|   4 | email-4B| SELECT id FROM jobs WHERE id = 1 FOR UPDATE NOWAIT;B| ERROR:  could not obtain lock on row in relation "jobs"

B did not wait: it took jobs 3 and 4. The last line shows NOWAIT, which raises an error at once instead of waiting for a locked row. The usual one-statement worker claims a job and marks it running:

WITH next AS (  SELECT id FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED)UPDATE jobs SET status = 'running' FROM next WHERE jobs.id = next.idRETURNING jobs.id, jobs.payload;

It returned 3 | email-3 (jobs 1 and 2 were already done). If a worker crashes inside its transaction, the claim rolls back and the job is queued again. For work that takes minutes, mark the job running with a timestamp and let a sweeper requeue stale ones, instead of holding a transaction open. SKIP LOCKED gives an incomplete view on purpose: use it for queues, not reports.

Deadlocks

A deadlock happens when two transactions take the same locks in opposite order. Here two transfers run at once: A moves money from account 1 to 2, B from 2 to 1.

A| BEGIN;B| BEGIN;A| UPDATE accounts SET balance_cents = balance_cents - 100 WHERE id = 1;   -- A locks row 1B| UPDATE accounts SET balance_cents = balance_cents - 100 WHERE id = 2;   -- B locks row 2A| UPDATE accounts SET balance_cents = balance_cents + 100 WHERE id = 2;   -- A waits for BB| UPDATE accounts SET balance_cents = balance_cents + 100 WHERE id = 1;   -- B waits for A: a cycleA| ERROR:  deadlock detectedA| DETAIL:  Process 18549 waits for ShareLock on transaction 793; blocked by process 18548.A| Process 18548 waits for ShareLock on transaction 792; blocked by process 18549.A| HINT:  See server log for query details.A| CONTEXT:  while updating tuple (0,17) in relation "accounts"B| UPDATE 1

Nobody can proceed, so after deadlock_timeout (default 1 second; I checked SHOW deadlock_timeout) PostgreSQL checks for a cycle, picks one victim (here A), and cancels it. B then continued. The error code is 40P01, and the retry loop above handles it. Two ways to avoid deadlocks:

1. Always lock in the same order. For a transfer, lock the lower account id first. I ran this: both sessions did SELECT id FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE. B simply waited for A's COMMIT and then got both rows. No deadlock.
2. Keep transactions short and touch as few rows as possible, so the window is small.

Advisory locks, briefly

An advisory lock is a lock on a number you choose, with no table or row behind it. PostgreSQL only keeps the book; your code gives it meaning. Use it for "only one instance runs this cron job" or "only one process migrates tenant 42":

A| SELECT pg_try_advisory_lock(42);   -- tB| SELECT pg_try_advisory_lock(42);   -- f   (A holds it)A| SELECT pg_advisory_unlock(42);     -- tB| SELECT pg_try_advisory_lock(42);   -- t

There are two lifetimes: session level (pg_advisory_lock, held until unlock or disconnect) and transaction level (pg_advisory_xact_lock, released at commit or rollback). Prefer the second: behind a transaction-mode connection pool, a session-level lock sticks to a connection that is handed to someone else.

Long transactions hurt

An open transaction is cheap to start and expensive to forget. Three costs:

1. It blocks cleanup (vacuum). When rows are updated, the old versions stay as dead rows until no transaction could still need them. VACUUM removes dead rows, but only those older than the oldest open snapshot. I tested: a table hot of 1,000 rows, session A open in REPEATABLE READ after one read, then 20 rounds of UPDATE hot SET n = n + 1 on all rows (autovacuum off for the test). VACUUM (VERBOSE) hot said:

tuples: 0 removed, 21000 remain, 20000 are dead but not yet removable      (A still open)tuples: 20000 removed, 1000 remain, 0 are dead but not yet removable       (after A rolled back)

Twenty thousand dead rows, none removable, because A's old snapshot might still want them. Tables and indexes bloat (93 pages for 1,000 live rows). In production this is the classic "table suddenly slow and huge": one forgotten transaction from days ago. 2. It holds locks until the end, and keeps the ALTER TABLE queue problem above alive. 3. It occupies a connection from a small pool (Backend, Lesson 07). A pool of 20 with 20 idle-in-transaction sessions is an outage.

The worst form is idle in transaction: the app did BEGIN, then went off to call an HTTP API with the transaction open. Never do network calls inside a transaction. To find culprits (the output is from a session I left idle):

SELECT pid, state, now() - xact_start AS xact_age, left(query, 30) AS last_queryFROM pg_stat_activity WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()ORDER BY xact_start;

  pid  |        state        |    xact_age     | last_query -------+---------------------+-----------------+------------ 18909 | idle in transaction | 00:00:03.214152 | SELECT 1;

As a safety net I set idle_in_transaction_session_timeout = '1s' (a test value; use 30 seconds to a few minutes in real life). After BEGIN; SELECT 1; and two seconds of silence, the next statement got FATAL: terminating connection due to idle-in-transaction timeout. Set it per role (ALTER ROLE app SET ...) and also consider statement_timeout.

Idempotency and retries from the app

Retries are everywhere: your retry loop, the HTTP client, the user double-clicking "Pay". The network can also lose the answer: the server committed, the client never heard, so it sends again. If the operation is not idempotent, the money moves twice.

The fix is an idempotency key: the client sends the same unique id on every retry, and the database stores it with a UNIQUE constraint, in the same transaction as the money move:

CREATE TABLE payments (  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, idempotency_key text NOT NULL UNIQUE,  from_account int NOT NULL, to_account int NOT NULL, amount_cents int NOT NULL CHECK (amount_cents > 0));

The core of the Node function (BEGIN, this insert, two updates, COMMIT):

const ins = await c.query(  `INSERT INTO payments (idempotency_key, from_account, to_account, amount_cents)   VALUES ($1,$2,$3,$4) ON CONFLICT (idempotency_key) DO NOTHING RETURNING id`,  [key, from, to, cents]);if (ins.rowCount === 0) { await c.query('ROLLBACK'); return 'duplicate, nothing done'; }// ...otherwise move the money in this same transaction, then COMMIT

I sent the same request req-123 twice at the same time, then a third time:

before [{"id":1,"balance_cents":10000},{"id":2,"balance_cents":5000}][ 'moved 3000, payment 1', 'duplicate, nothing done' ]duplicate, nothing doneafter  [{"id":1,"balance_cents":7000},{"id":2,"balance_cents":8000}]

Three requests, one transfer. Even the two simultaneous ones were safe: the second insert waited for the first transaction, saw the conflict, and did nothing. ON CONFLICT is covered in lesson 7. A real API also returns the original response on a duplicate. The same rule applies inside a retry loop: no emails or payment-provider calls there. Write an "outbox" row in the transaction and let a worker send it after the commit.

Try it

Use the accounts table (balances 10,000 and 5,000) and two psql windows.

1. Show a non-repeatable read: in window A BEGIN; and read a balance, in B update it, read again in A. Repeat with BEGIN ISOLATION LEVEL REPEATABLE READ;. What differs?
2. Run SELECT * FROM accounts WHERE id = 1 FOR UPDATE; in a transaction in both windows. What does the second do, and how do you make it fail instead of wait?
3. Reproduce a deadlock between two transfers, then change the update order so it cannot happen.
4. A teammate says "I set everything to SERIALIZABLE, so we are safe". What do you ask?

Answers

1. With Read Committed the second read in A shows the new balance. With Repeatable Read A keeps seeing the old one until it ends.
2. It hangs until the first commits or rolls back. Use FOR UPDATE NOWAIT (error at once) or SET lock_timeout.
3. Window A: update account 1 then 2. Window B: update 2 then 1, with both sessions open. After about a second one gets ERROR: deadlock detected. Fix: both always update the lower id first, or lock both rows first with ORDER BY id FOR UPDATE.
4. "Do you retry on 40001?" and "what is the throughput under contention?" Serializable gives correct results but raises serialization errors, so without a retry loop the failures reach users, and heavy contention can cause many retries.

Interview questions

Key takeaways

• A transaction is all-or-nothing. After any error inside it, you must roll back (or roll back to a savepoint).
• Read Committed (the default) sees new data at every statement. Repeatable Read freezes the view. Serializable also stops write skew, by raising errors you must retry.
• Do arithmetic in SQL (SET stock = stock - 1 WHERE stock >= 1), or lock with FOR UPDATE, never "read, compute in the app, write".
• Retry the whole transaction on 40001 and 40P01, with a limit, a short random wait, and no outside side effects inside.
• SKIP LOCKED makes a queue. Lock rows in a fixed order to avoid deadlocks. ACCESS EXCLUSIVE locks queue everything behind them, so use lock_timeout for DDL.
• Keep transactions short. Long ones block vacuum, hold locks and eat connections. Set idle_in_transaction_session_timeout.
• Use idempotency keys with a UNIQUE constraint so retries cannot repeat money moves.

What's next

Lesson 7: PostgreSQL Features and Safe Migrations. You now know how concurrent sessions behave. Next: JSONB, full-text search and arrays, upserts with INSERT ... ON CONFLICT (used above for idempotency), and how to change a live schema without taking the site down, using the lock rules from this lesson. The earlier lesson 5 covers the indexes that migrations create.