Use PostgreSQL transactions

Use BEGIN, COMMIT, and ROLLBACK around changes that form one unit.

BEGIN;

UPDATE accounts
SET balance = balance - 20
WHERE id = 1 AND balance >= 20;

UPDATE accounts
SET balance = balance + 20
WHERE id = 2;

COMMIT;

The transaction is atomic: both updates become visible together, or ROLLBACK discards them. Application code must also check that the debit updated exactly one row. Atomic execution does not make an incorrect business rule correct.

PostgreSQL’s default READ COMMITTED isolation gives each statement a snapshot of committed data when that statement begins. A second query in the same transaction can therefore see a row another transaction committed in between. Stronger isolation levels change what can be observed, but may abort a transaction that must then be retried.

Concurrent transactions can still block one another when they update the same rows or request conflicting locks. Acquire rows in a consistent order to reduce deadlocks. When PostgreSQL detects a deadlock, it aborts one participant; the application should retry the whole transaction only when the operation is safe to repeat.

After any statement error, the transaction is aborted until ROLLBACK or ROLLBACK TO SAVEPOINT. Do not catch the application exception and continue sending unrelated SQL through the same failed transaction.

Keep the boundary small. Validate and compute before BEGIN, make the related database changes, then commit. Do not wait for email, payment, user input, or another network service while holding locks and a pooled connection. For external effects that must follow a commit, an outbox table can record work in the same transaction for a separate worker to deliver.

Try it: open two psql sessions, update the same row without committing in the first, and observe the second wait. Use ROLLBACK in the first session and confirm which value remains.

Do not wait for email, payment, or another network service inside a database transaction.