11.1 Transaction Boundaries, ACID, and Failure Semantics
Metadata Card
- Prerequisites: Chapter 4, WAL and Recovery; Chapter 8, Executor
- Keywords: Transaction boundary, ACID, constraints, WAL, commit, rollback, savepoint
- Code Language: SQL (PostgreSQL)
A transaction defines how a state change commits, fails, and recovers. In an account transfer, executing both UPDATE statements is not enough: the debit and credit must take effect together to preserve the total. The database can check a balance; choosing the right recipient remains the application's job.
Run the following examples in order in an empty PostgreSQL 16+ practice database. Only the savepoint example deliberately raises an error. Interactive psql lets you continue after that error; scripts using ON_ERROR_STOP must handle that step separately. Start with non-negative balances:
CREATE TABLE account (
account_id bigint PRIMARY KEY,
balance numeric(18, 2) NOT NULL,
CONSTRAINT account_balance_nonnegative CHECK (balance >= 0)
);
INSERT INTO account (account_id, balance)
VALUES (1, 500.00), (2, 200.00);Transaction Boundaries Determine What Must Succeed Together
A transfer operation includes a deduction and a credit. If these two statements are each committed independently, and the first succeeds while the second fails, the system ends up in a state that violates business rules.
BEGIN;
UPDATE account
SET balance = balance - 100.00
WHERE account_id = 1;
UPDATE account
SET balance = balance + 100.00
WHERE account_id = 2;
COMMIT;Balances are now (1, 400.00) and (2, 300.00). This minimal example assumes both accounts exist; the final example implements the missing-account and insufficient-balance checks.
A true transaction boundary should also encompass critical data such as transfer records and idempotency keys that must all take effect together. However, it should not include operations like waiting for user input, network calls, or long-running computations. The longer a transaction is, the longer it holds snapshots, locks, old versions, and database connections.
ACID Is Four Distinct Responsibilities
Atomicity: Only Complete State Transitions Are Committed
An aborted transaction must not leave its modifications as committed facts. Explicit ROLLBACK must have the same effect. Some statement errors can be recovered through savepoints without aborting the entire transaction. Implementation varies: InnoDB uses undo records to support rollback; PostgreSQL relies on tuple versions and transaction commit status to determine visibility, and its crash recovery primarily replays WAL logs, without performing a textbook-style "undo" of uncommitted disk values, as in traditional undo/redo models.
Atomicity does not govern external side effects. If a transaction has already sent an email, deducted funds from a third-party account, or written to an independent system, a local ROLLBACK cannot reverse those actions. Cross-boundary consistency requires mechanisms like outbox patterns, idempotent consumers, compensation actions, or distributed transaction protocols.
Consistency: Define Invariants First, Then Enforce Them
The consistency in ACID does not mean "replica consistency" nor that the database can infer all business rules. It means that a transaction must transform the database from one state that satisfies a set of invariants to another state that also satisfies those same invariants.
Databases excel at enforcing declarable constraints:
PRIMARY KEYandUNIQUE: identity and uniqueness;NOT NULLandCHECK: single-row or expressible domain constraints;FOREIGN KEY: referential integrity;- Appropriate isolation levels or explicit locks: for invariants across rows or across queries.
Still, the application must define correct state transitions and verify affected row counts. For example, a deduction should perform the balance check within the same atomic update, not by reading first and then writing.
BEGIN;
UPDATE account
SET balance = balance - 100.00
WHERE account_id = 1
AND balance >= 100.00;
ROLLBACK;ROLLBACK preserves the practice balances here. A real transfer must inspect the UPDATE row count. If zero rows are affected, it means either the account doesn’t exist or the balance is insufficient. The application must then abort any subsequent credit. The database does not automatically interpret these business implications.
Isolation: What Intermediate States and Concurrency Errors Can Appear
Isolation answers the question: when multiple transactions execute concurrently, which intermediate states and concurrency anomalies can occur. It does not mean "all transactions run sequentially" nor is it a simple on/off switch. SQL isolation levels provide different guarantees, and databases may implement them using locks, MVCC, or SSI among other mechanisms.
Even a standard SELECT using snapshot isolation without blocking writes can still result in two write transactions competing for the same row. Serializable transactions may also be actively terminated by the database. Correct applications must handle waiting, deadlocks, and retryable errors appropriately.
Durability: Commit Confirmation Binds to Failure Models
Durability guarantees that, under the failure model declared by the database, any transaction that has been confirmed as committed will not be lost due to a crash. A common path is to first persist sufficient logging before allowing dirty data pages to be written back to disk.
This guarantee depends on configuration and infrastructure:
- When PostgreSQL sets
synchronous_committooff, clients may receive a success response before a database or operating-system crash, potentially losing recent transactions that have not reached durable storage. Database consistency can be restored, but the promise that "committed transactions never disappear" is relaxed. - In replicated systems, "durability" must specify whether only the primary node has written to disk or whether synchronous replicas have also acknowledged the commit.
- If storage hardware falsely reports disk flush completion, the database cannot compensate with software-level configuration to achieve physical guarantees.
Therefore, don’t just state “enabling WAL ensures durability.” Instead, document the commit strategy, replica acknowledgment conditions, and acceptable data loss windows.
Failure Goes Beyond COMMIT and ROLLBACK
In PostgreSQL, if a statement within a transaction fails, the transaction typically enters a failed state; subsequent statements will not execute until a ROLLBACK or a rollback to a savepoint established before the error.
BEGIN;
SAVEPOINT before_optional_step;
INSERT INTO account (account_id, balance) VALUES (1, 10.00);
-- Primary key conflict, transaction entered error state
ROLLBACK TO SAVEPOINT before_optional_step;
-- Transaction back online
UPDATE account
SET balance = balance + 10.00
WHERE account_id = 2;
COMMIT;After this commit, account 2 holds 310.00 and account 1 still holds 400.00. Savepoints exist only within the original transaction. When a connection ends, the server rolls back an ordinary transaction that remains uncommitted. Losing the connection while waiting for a COMMIT response is different: the commit may already have succeeded, so reconcile using the business request ID before retrying.
Also keep in mind objects that don't fully participate in transaction rollbacks. PostgreSQL sequence number generation doesn't reclaim values when the calling transaction rolls back, so gaps in auto-incremented IDs are normal and shouldn't be used to infer data loss.
A Checkable Transfer
This PostgreSQL PL/pgSQL anonymous block checks the affected row counts in executable code. Either failed condition raises an exception, preventing the debit from committing alone. It still uses fixed accounts and an amount and does not implement business request deduplication.
BEGIN;
DO $$
DECLARE
affected bigint;
BEGIN
PERFORM account_id FROM account
WHERE account_id IN (1, 2)
ORDER BY account_id FOR UPDATE;
UPDATE account SET balance = balance - 100.00
WHERE account_id = 1 AND balance >= 100.00;
GET DIAGNOSTICS affected = ROW_COUNT;
IF affected <> 1 THEN
RAISE EXCEPTION 'source missing or insufficient balance';
END IF;
UPDATE account SET balance = balance + 100.00
WHERE account_id = 2;
GET DIAGNOSTICS affected = ROW_COUNT;
IF affected <> 1 THEN
RAISE EXCEPTION 'destination missing';
END IF;
END $$;
COMMIT;
SELECT account_id, balance FROM account ORDER BY account_id;After following this page in order, expect (1, 300.00) and (2, 410.00). In a separate practice transaction, remove the recipient and run the transfer again: the exception must also undo the debit. Execute ROLLBACK after the error before checking the result.
Fixed lock ordering reduces the likelihood of deadlocks, but retry logic must not be removed. A more complete implementation should also include a unique business request ID to prevent clients from resubmitting the same transfer after a timeout.
Section Checkpoint
- Can distinguish atomicity within a database from external side-effect compensation.
- Can assign business invariants to constraints, SQL conditions, and isolation mechanisms.
- Can explain the relationship between WAL, flush strategies, and durability guarantees.
- Can handle transaction error states, savepoints, and affected row counts.
References
The next section breaks down multiple transactions into read and write operations, and determines whether a concurrent execution is equivalent to any particular serial order.