Writing data safely

Reading from a database is forgiving. Writing to it is not, because requests retry, processes crash halfway, and two of them arrive at the same instant. This section is the four mechanisms that keep a table correct through all of that: transactions, constraints, upserts and row locks. What they prevent is the quiet kind of failure, a double charge or a balance that lost an update, which careful application code cannot catch on its own because the check and the write are two separate moments.

All or nothing#

A transaction is a group of statements that either all take effect or none do. Without one, a process that crashes between two statements leaves the database in a state that neither statement intended: credits deducted with no usage recorded, or an order with no line items. Wrap anything that must stay consistent together, and note that a transaction is also the unit of isolation, so another connection sees nothing until it commits.

BEGIN;
UPDATE accounts SET credits = credits - 10 WHERE id = 7;
INSERT INTO usage (account_id, credits) VALUES (7, 10);
COMMIT;   -- both happen, or on error neither does

-- ROLLBACK undoes everything since BEGIN. Most client libraries do it for you
-- when the block raises:
-- with conn.transaction():
--     ...            # an exception here rolls back and re-raises

Let the database refuse bad data#

CREATE TABLE usage (
  id          bigserial PRIMARY KEY,
  account_id  bigint NOT NULL REFERENCES accounts(id),
  credits     int    NOT NULL CHECK (credits > 0),
  request_id  text   NOT NULL UNIQUE,     -- one row per request, however many retries
  created_at  timestamptz NOT NULL DEFAULT now()
);

Each of those does a specific job. PRIMARY KEY makes the row addressable and unique. REFERENCES accounts(id) is a foreign key: the database refuses a usage row for an account that does not exist, and refuses to delete an account that still has usage, which is what stops the orphan rows that break every later join. NOT NULL says the column must have a value. CHECK encodes a rule the type cannot, here that a charge is positive. UNIQUE is the one doing the real work below.

Application code has bugs, deploys race, and two services write the same table. Constraints do not forget, and they hold for every writer including the migration somebody runs by hand at midnight. A unique request id means a retried request cannot charge twice, and it means that whatever the application logic does.

Insert or update#

-- Retry is harmless: the second attempt inserts nothing and raises nothing.
INSERT INTO usage (account_id, credits, request_id)
VALUES (7, 10, 'req_8f2c')
ON CONFLICT (request_id) DO NOTHING;

-- Upsert: insert, or update what is already there. The excluded pseudo-table
-- holds the row you tried to insert.
INSERT INTO daily_cost (day, model, cost_usd)
VALUES ('2026-03-11', 'sonnet', 12.40)
ON CONFLICT (day, model) DO UPDATE
SET cost_usd = daily_cost.cost_usd + excluded.cost_usd;

-- Return what happened, so the caller knows without a second query.
INSERT INTO usage (account_id, credits, request_id)
VALUES (7, 10, 'req_8f2c')
ON CONFLICT (request_id) DO NOTHING
RETURNING id;    -- no row returned means it was already there

Two requests at once#

Two requests both read a balance of 10, both subtract 10, both write 0, and one charge disappears. Nothing errored and no code was wrong on its own; the problem is that reading and writing were two separate moments and something happened in between. This is the lost update, and there are three standard fixes.

time   request A                     request B
 1     SELECT credits -> 10
 2                                   SELECT credits -> 10
 3     UPDATE SET credits = 0
 4                                   UPDATE SET credits = 0
       one of the two charges has vanished, and nothing raised

-- 1. Do the arithmetic in the database. One statement, no gap.
UPDATE accounts SET credits = credits - 10
WHERE id = 7 AND credits >= 10
RETURNING credits;      -- no row returned means there were not enough credits

-- 2. Lock the row first, if you must read, decide, then write.
BEGIN;
SELECT credits FROM accounts WHERE id = 7 FOR UPDATE;  -- B blocks here until A commits
-- ... decide ...
UPDATE accounts SET credits = credits - 10 WHERE id = 7;
COMMIT;

-- 3. Optimistic: carry a version and fail if it moved.
UPDATE accounts SET credits = 0, version = version + 1
WHERE id = 7 AND version = 4;     -- 0 rows updated means someone else got there first

Locking works and costs you concurrency, so take locks in a consistent order across your codebase. Two transactions that lock account 7 then account 9, and account 9 then account 7, will deadlock; Postgres detects it and kills one with a deadlock detected error, which the application should retry rather than treat as a failure.