Production engineering workshop · 50 MIN

Database engineering · Transfer value without lost updates

Lock shared records in a consistent order and keep invariants in the database.

A transfer debits one account and credits another. Both changes must commit together. Acquire row locks in a stable ID order so competing transfers reduce deadlock risk, validate balance after locking and reject insufficient funds. Always use integer minor units or an explicit decimal policy for money. A retry must restart the entire transaction and use an idempotency key to prevent an uncertain commit being repeated.

First reproduce the normal path. Then force a failure at each boundary and inspect what remains true.

Read the example

CREATE TABLE accounts (id bigint PRIMARY KEY, balance bigint NOT NULL CHECK(balance >= 0));
INSERT INTO accounts VALUES (1,100),(2,0);
BEGIN;
SELECT id,balance FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
-- Application verifies both rows exist, amount > 0, and source balance >= amount.
UPDATE accounts SET balance=balance-20 WHERE id=1 AND balance>=20;
-- Application MUST assert the debit affected exactly one row before crediting.
UPDATE accounts SET balance=balance+20 WHERE id=2;
COMMIT;
SELECT SUM(balance) FROM accounts; -- 100
-- Parameterize values in an application; rollback on any failure.

Review the behavior in the stated project environment.

Your challenge

Create two disposable PostgreSQL accounts with balances 100 and 0. Implement a parameterized transfer service that locks both rows in ascending ID order, validates amount and balance, then updates both. Run parallel transfers and verify the conserved total and nonnegative balances.

Solution cost: Two indexed row operations plus lock waits. time · Constant application state; database log and MVCC retention vary. space

Common trap

Reading balance outside the lock and then updating can lose concurrent changes. A CHECK constraint alone cannot make a two-row transfer atomic.

CREATE TABLE accounts (id bigint PRIMARY KEY, balance bigint NOT NULL CHECK(balance >= 0));
INSERT INTO accounts VALUES (1,100),(2,0);
BEGIN;
SELECT id,balance FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
-- Application verifies both rows exist, amount > 0, and source balance >= amount.
UPDATE accounts SET balance=balance-20 WHERE id=1 AND balance>=20;
-- Application MUST assert the debit affected exactly one row before crediting.
UPDATE accounts SET balance=balance+20 WHERE id=2;
COMMIT;
SELECT SUM(balance) FROM accounts; -- 100
-- Parameterize values in an application; rollback on any failure.

Further reading: Official documentation