SQL Transactions

Understanding SQL Transactions

A transaction defines a boundary around database work that must succeed together. Transferring money requires both a debit and a credit: committing only the debit would leave an incorrect result. In PostgreSQL, BEGIN opens an explicit transaction block, COMMIT accepts its changes, and ROLLBACK discards its uncommitted changes. Statements still execute individually inside that boundary, and your own session can inspect their effects before deciding whether to commit.

Atomicity means the transaction's database changes are accepted together or discarded together. It does not mean every statement automatically accomplishes your intention. An UPDATE that matches no rows is normally successful SQL. A transfer can therefore commit an incorrect result unless the application checks that both accounts exist and that the expected rows were changed.

Consistency means preserving the rules that define valid data. Constraints such as primary keys, foreign keys, and CHECK conditions enforce declared rules; transaction logic must enforce additional business requirements. Isolation governs interaction with concurrent transactions. PostgreSQL does not expose another transaction's uncommitted changes, but this does not imply that every query inside a transaction sees an identical snapshot. At the default Read Committed level, successive queries can see changes committed by others between those queries.

Durability means that a successful commit is intended to survive a crash, under the database's durability configuration and storage assumptions. It is not a substitute for backups. Nor does committing database work automatically coordinate an external action, such as sending an email or charging a payment service. Those effects require separate application design because a database rollback cannot unsend an email.

PostgreSQL executes every statement within a transaction. Without an explicit block, individual successful statements normally commit separately, although client libraries can manage boundaries automatically. Check your client's behavior rather than assuming that consecutive statements form one unit.

Savepoints provide checkpoints inside an open transaction. ROLLBACK TO discards changes made after a named checkpoint while retaining earlier work. It neither commits those earlier changes nor makes them visible to other sessions. A later full ROLLBACK still discards the entire transaction. This distinction makes savepoints useful for correcting optional steps without abandoning valid work already completed.

Practical walkthrough

Use the example in a PostgreSQL session with permission to create temporary tables. Save it as transaction_demo.sql and run it through psql using: psql -X -qAt -v ON_ERROR_STOP=1 -f transaction_demo.sql. Supply your usual connection options if needed. These flags suppress startup customization and command tags, produce unaligned rows without headings, and stop execution on SQL errors. The output shown contains exactly the two final query rows.

First, create a temporary accounts table. It belongs to this session and disappears when the connection closes, avoiding changes to an existing accounts table. Balances are integer currency units, so the arithmetic is exact. A primary key makes each identifier unique; a CHECK constraint rejects negative balances. Neither constraint alone guarantees a valid transfer.

Next, begin a transaction and subtract 100 from account 1. Establish a savepoint after that debit. The first credit deliberately uses the wrong amount: 150 instead of 100. Rolling back to the savepoint removes that credit but preserves the debit. Apply the correct credit of 100, then commit both retained updates together.

Start another transaction and delete account 2. A full rollback cancels this deletion. Finally, select the accounts in identifier order, ensuring deterministic output. Account 1 holds 400 and account 2 holds 300; their combined balance remains 700.

This demonstration assumes known account identifiers and no competing writers. In an application, check affected-row counts and handle concurrency explicitly. Keep transactions short: waiting for human confirmation while a transaction remains open can prolong locks and delay other work.

Worked example

CREATE TEMP TABLE transaction_demo_accounts (
    id integer PRIMARY KEY,
    balance integer NOT NULL CHECK (balance >= 0)
);

INSERT INTO transaction_demo_accounts (id, balance)
VALUES (1, 500), (2, 200);

BEGIN;
UPDATE transaction_demo_accounts
SET balance = balance - 100
WHERE id = 1;

SAVEPOINT after_debit;

UPDATE transaction_demo_accounts
SET balance = balance + 150
WHERE id = 2;

ROLLBACK TO SAVEPOINT after_debit;

UPDATE transaction_demo_accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;

BEGIN;
DELETE FROM transaction_demo_accounts WHERE id = 2;
ROLLBACK;

SELECT id, balance
FROM transaction_demo_accounts
ORDER BY id;

Expected output

1|400
2|300

After the debit, this session sees balances of 400 and 200. The incorrect credit temporarily changes the second balance to 350. ROLLBACK TO restores it to 200 without restoring the first balance to 500, because the debit preceded the savepoint. The corrected credit produces 400 and 300, which COMMIT accepts. The later DELETE is valid SQL, but ROLLBACK cancels its effect. This illustrates that rollback is also a deliberate control decision, not merely an automatic response to errors. Because the table is temporary, this example demonstrates transaction behavior within one session, not cross-session visibility or durable storage after disconnecting.

Common mistakes and how to avoid them

• Assuming an explicit transaction makes incorrect SQL logically correct.

  Validate business conditions and affected-row counts before committing. Missing recipients can produce zero-row updates without errors; rollback if either transfer update does not affect exactly one expected account.

• Continuing normally after a PostgreSQL statement error.

  An error generally leaves the transaction aborted. Use full ROLLBACK, or ROLLBACK TO a savepoint established before the failed statement, before continuing useful work.

• Using ROLLBACK to undo an already committed change.

  Rollback affects the current uncommitted transaction, not completed transactions. Correct committed mistakes through a carefully validated compensating transaction, accounting for any intervening changes.

• Treating stronger isolation as a free correctness switch.

  Choose isolation based on concurrent access requirements. Serializable execution can reject conflicting work; applications must be prepared to retry the whole transaction, not just its last statement.

Review questions

Further reading

• PostgreSQL transaction tutorial