A transaction for moving credits between accounts

Moving 20 credits requires two changes: subtract 20 from one account and add 20 to another. A SQL transaction can publish those changes together or discard them together. It cannot decide that your second UPDATE found the intended recipient. You must turn that business requirement into a checked outcome.

This walkthrough is for readers comfortable with SELECT and UPDATE who are starting to write database-backed API operations. It uses PostgreSQL 17, two accounts and one kind of indivisible credit. There is no currency conversion, payment provider or authorization system. Run the examples in a disposable database with two independent psql sessions; keep session B in its normal autocommit mode.

Start with a total you can inspect

CREATE TABLE credit_accounts (
  id bigint PRIMARY KEY,
  credits bigint NOT NULL CHECK (credits >= 0)
);
INSERT INTO credit_accounts VALUES (1, 50), (2, 10);

The initial balances are 50 and 10, totaling 60. After a successful transfer they should be 30 and 30. If the transfer fails, they should remain 50 and 10. The primary key makes each account lookup identify at most one row. The nonnegative check protects an individual stored balance; it does not require the total across accounts to remain 60.

PostgreSQL’s transaction tutorial explains the all-or-nothing boundary around several statements. Statements outside an explicit transaction block also execute in transactions. Consequently, sending two separate successful updates in autocommit mode can commit the first before the second has even been attempted.

A transaction can successfully commit an incomplete transfer

Try this intentionally incomplete operation:

BEGIN;
UPDATE credit_accounts SET credits = credits - 20 WHERE id = 1;
UPDATE credit_accounts SET credits = credits + 20 WHERE id = 999
RETURNING id;
SELECT id, credits FROM credit_accounts ORDER BY id;
ROLLBACK;

The recipient update returns no identifier: account 999 does not exist. The following query nevertheless shows account 1 at 30 and account 2 at 10. No SQL error occurred. Both remaining balances satisfy their row constraints. If you replaced the final ROLLBACK with COMMIT, the database would store a total of 40. The displayed rollback restores the original 50 and 10 for the next example.

Atomicity groups the operations you actually execute. An update affecting zero rows is one successful SQL operation, even when your application needed one affected row. Catching exceptions alone therefore misses this failure. You also need to check the result of each required change.

Make both affected rows a condition of success

This small PL/pgSQL function keeps the checks next to the writes. Its inner BEGIN and END delimit a programming block; they do not start or commit an independent transaction.

CREATE FUNCTION transfer_credits(
  p_from bigint, p_to bigint, p_amount bigint
) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  IF p_from IS NULL OR p_to IS NULL OR p_amount IS NULL
     OR p_amount <= 0 OR p_from = p_to THEN
    RAISE EXCEPTION 'invalid transfer arguments'
      USING ERRCODE = '22023';
  END IF;

  UPDATE credit_accounts
  SET credits = credits - p_amount
  WHERE id = p_from AND credits >= p_amount;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'source missing or insufficient credits'
      USING ERRCODE = 'P0001';
  END IF;

  UPDATE credit_accounts
  SET credits = credits + p_amount
  WHERE id = p_to;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'destination missing'
      USING ERRCODE = 'P0002';
  END IF;
END;
$$;

The argument guard rejects missing inputs, nonpositive amounts and a transfer to the same account. The source update combines deduction with the sufficient-credit condition, using the stored value in the arithmetic. It avoids reading a balance into application memory and later writing back a stale calculated number.

After each update, FOUND tells this function whether at least one row was affected. With a primary-key predicate, that is exactly one or zero. Check it immediately: later SQL can replace its value. A missing source and an insufficient balance deliberately share one error here; a missing destination has another. These are database diagnostics, not an HTTP response contract or a permission check.

RAISE EXCEPTION turns the failed business requirement into an error. In this example there is no exception handler or savepoint recovery. If the destination is absent after the source was deducted, the function cannot leave that deduction committed. The caller must treat the operation as failed.

A SQL function is one reasonable place for this small rule. Application code can instead execute the same updates on one checked-out database connection, require an affected-row count of one after each, and roll back on either mismatch. That choice makes driver error handling and connection ownership part of the implementation. Sending BEGIN through one pooled connection and updates through another does not create one transaction.

Watch the commit boundary from two sessions

In session A, run:

BEGIN;
SELECT transfer_credits(1, 2, 20);
SELECT id, credits FROM credit_accounts ORDER BY id;

A sees 30 and 30 because a transaction can read its own writes. Leave it open. In session B, run:

SELECT id, credits FROM credit_accounts ORDER BY id;

B sees 50 and 10. Its ordinary read does not wait for A’s updated rows, and it cannot see A’s unpublished intermediate changes. This behavior follows PostgreSQL’s Read Committed rules: the query reads committed data as of the statement’s snapshot. The assumption here is that no other writer is changing these accounts.

Now finish A:

COMMIT;

Repeat B’s query. It now sees 30 and 30. “After commit” means a new statement with an appropriate snapshot; it does not update an already returned result or force a long-running older snapshot to refresh.

View full-size image ↗

The transaction owner can inspect its work before publication. The second connection sees the complete committed pair.

The function itself contains no commit. Calling it without an explicit BEGIN still makes its writes part of that single statement’s implicit transaction. An outer transaction is useful when the transfer and another database change must succeed together, such as inserting an audit record. An unrelated HTTP call would remain outside that database guarantee.

Successful statements can still be rolled back

Starting from the committed 30 and 30, run in A:

BEGIN;
SELECT transfer_credits(1, 2, 20);
SELECT id, credits FROM credit_accounts ORDER BY id;
ROLLBACK;
SELECT id, credits FROM credit_accounts ORDER BY id;

The first result is 10 and 50. The second is 30 and 30. Nothing failed inside the function; the caller chose to discard the entire open transaction. Rollback restores the earlier database state, rather than performing a new opposite-direction transfer.

Next, try a missing recipient:

BEGIN;
SELECT transfer_credits(1, 999, 20);
ROLLBACK;
SELECT id, credits FROM credit_accounts ORDER BY id;

The function reports P0002, and after rollback the balances remain 30 and 30. In interactive psql, issue the rollback after the expected error. A script configured to stop on its first error will stop before the remaining commands; its caller must handle cleanup. In a driver, roll back the failed transaction before reusing or returning that connection.

Without savepoint recovery, an error in an explicit transaction leaves it aborted: another ordinary query is rejected until rollback. Earlier changes in that same transaction are discarded too. Changes committed by a previous transaction are unaffected. This distinction matters when deciding which writes belong inside your unit of work.

View full-size image ↗

An affected-row check decides whether the business step happened. The transaction boundary decides whether successful steps become committed state.

Test the boundary before building an endpoint around it

The local PostgreSQL 17.11 checks also exercised a recipient already at the maximum bigint value. Adding 20 raises an overflow error and preserves the source balance. PostgreSQL’s integer types have finite ranges. Storage types also do not replace input validation: a caller can explicitly cast numeric 1.5 to bigint, producing 2 before the function receives it. Define your API’s accepted representation separately.

Two real connections tested competing deductions from a source with 50 credits. A held a 40-credit transfer open; B’s 20-credit transfer waited. After A committed, B rechecked the source condition and failed for insufficient credits. When A rolled back instead, B succeeded. That verifies this guarded arithmetic against those orderings. It does not establish freedom from deadlocks: opposite-direction transfers can acquire the two accounts in conflicting orders. PostgreSQL’s locking documentation describes that separate problem.

Finally, a successful database transaction does not make a repeated request harmless. Starting from 50 and 10, two calls with the same arguments both succeed and leave 10 and 50. Brandur Leach’s 6 September 2017 article on transactions and idempotent APIs distinguishes database work, request retries and external effects. This function supplies neither an idempotency key nor an external-effect protocol. Losing the commit response also does not establish that the commit failed; blindly repeating the transfer is unsafe.

For a reproducible exercise, start with fresh 50/10 balances and predict three results: transferring all 50, transferring 51, and transferring 20 to account 999. Verify both accounts after each isolated trial. Then close a connection while its successful transfer remains uncommitted and check from the other session that the original balances remain. Those checks test the actual database boundary; production authorization, audit history, retry policy and durability configuration still require their own design and evidence.