Database Transactions: Keeping Multi-Step Writes Correct

“Understand atomicity, rollbacks, isolation and concurrent database writes.”

Database Transactions

A transaction groups multiple database operations into one logical unit.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 'A';

UPDATE accounts
SET balance = balance + 100
WHERE id = 'B';

INSERT INTO ledger (
  source_account,
  destination_account,
  amount
)
VALUES ('A', 'B', 100);

COMMIT;

When something fails:

ROLLBACK;

Concurrency

Multiple transactions may execute simultaneously.

Sometimes rows need explicit locking.

SELECT balance
FROM accounts
WHERE id = $1
FOR UPDATE;

Keep Transactions Short

Long transactions hold locks and connections for longer.

Note

A database transaction cannot undo an email already sent or an external payment that already succeeded.

Cross-system workflows may need outbox, idempotency or compensating actions.

Finished this article?

Mark it done to update your series progress.