A transaction lets related database changes commit together or be discarded together. Debiting an account, crediting another, and recording a ledger entry belong to the same decision when the operation requires all three. PostgreSQL provides that boundary through BEGIN, COMMIT, and ROLLBACK. The application must still place the correct statements inside it: atomic execution does not discover a forgotten credit or invent a missing business check.

Decide what belongs to one unit

Without an explicit transaction block, ordinary psql autocommit runs each statement in its own transaction. A successful debit can therefore commit before a later ledger insert fails. Explicitly grouping the writes changes the failure boundary. Other transactions do not see the group’s uncommitted intermediate table changes, and rollback discards its transactional changes rather than relying on compensating updates issued afterward.

Atomicity is distinct from isolation and durability. A committed unit can still embody an incorrect concurrent decision if its isolation or locking protocol is insufficient. The persistence guarantees of an acknowledged commit also depend on the database’s durability configuration. None of those questions is answered solely by finding a BEGIN in the code.

Every PostgreSQL statement already executes in a transaction, including reads. An explicit read transaction can have a purpose, such as selecting a repeatable snapshot or using transaction-local settings. The relevant decision is the required boundary, not a rule that all read transactions are useless overhead.

When it breaks

In PostgreSQL, an unhandled SQL error normally leaves an explicit transaction block aborted. Further ordinary statements fail until the client rolls back, or rolls back to an applicable earlier savepoint. Disabling a client’s stop-on-error behavior does not restore the server transaction. Returning that connection to a pool without cleaning up can make the next request inherit the failure state.

A server-reported rejection and a lost commit acknowledgement are different evidence. If a connection fails while the client awaits COMMIT, the client may not know whether the database committed before communication was lost. Blindly repeating a payment can duplicate it. A durable operation identifier and outcome reconciliation address that ambiguity; ordinary rollback semantics do not prove what happened on an unreachable server.

Check it quickly

Run the following in a fresh psql -X -v ON_ERROR_STOP=1 session on PostgreSQL 18 using a disposable database. Temporary tables keep the account identifiers local to the session. Error stopping is disabled only for two deliberate failures so their SQLSTATE values can be observed, then enabled again.

\set ON_ERROR_STOP on
CREATE TEMP TABLE accounts (id integer PRIMARY KEY,balance integer CHECK(balance>=0));
CREATE TEMP TABLE ledger (id integer PRIMARY KEY,amount integer CHECK(amount>0));
INSERT INTO accounts VALUES (1,100),(2,100);
BEGIN;
UPDATE accounts SET balance=balance-20 WHERE id=1;
UPDATE accounts SET balance=balance+20 WHERE id=2;
INSERT INTO ledger VALUES (1,20);
COMMIT;
SELECT id,balance FROM accounts ORDER BY id;
BEGIN;
UPDATE accounts SET balance=balance-30 WHERE id=1;
\set ON_ERROR_STOP off
INSERT INTO ledger VALUES (1,30);
\echo duplicate_error :SQLSTATE
SELECT balance FROM accounts WHERE id=1;
\echo aborted_error :SQLSTATE
\set ON_ERROR_STOP on
ROLLBACK;
SELECT id,balance FROM accounts ORDER BY id;
SELECT count(*) AS ledger_rows FROM ledger;
DO $$ BEGIN
  IF (SELECT array_agg(balance ORDER BY id) FROM accounts) IS DISTINCT FROM ARRAY[80,120]
     OR (SELECT count(*) FROM ledger) <> 1
     OR (SELECT amount FROM ledger WHERE id=1) IS DISTINCT FROM 20 THEN
    RAISE EXCEPTION 'partial transfer survived';
  END IF;
END $$;
DROP TABLE ledger,accounts;

The first transaction commits balances of 80 and 120 and one ledger row. The second transaction debits another thirty, then attempts to reuse ledger identifier one. PostgreSQL 18.6 reports 23505 for that uniqueness violation. The following ordinary read reports 25P02, showing that the transaction is aborted even though the client continues sending commands.

After rollback, the balances are still 80 and 120, with one ledger row. The second debit did not survive. The assertion checks the entire relevant state, rather than assuming that seeing an error proves rollback worked. The final drop removes the temporary fixture explicitly; closing the session would also remove its temporary tables.

This is a controlled atomicity experiment, not a complete money-transfer service. A real operation must verify that intended accounts exist, affected-row counts match expectations, amounts obey the business contract, and concurrent decisions are protected. Otherwise a transaction can atomically commit the wrong set of changes.

Keep recovery inside the contract

Savepoints allow selected work to be undone while preserving earlier transactional changes. They are useful when that partial outcome is intentional; swallowing a required ledger failure and committing the debit would violate this operation’s contract. Exceptions caught inside database procedural blocks can also establish a smaller rollback scope, so examine the actual handling path.

Sequence advancement and external actions such as sending mail are not undone by rolling back table writes. Keep irreversible external effects out of an attempt that may be retried, or coordinate them through a durable protocol. Short, explicit transactions make the commit decision easier to reason about, while checked results and a clear recovery path establish that the decision still means what the application intends.

Sources