Transactions and ACID

Every statement in a transaction either fully succeeds (all changes applied) or fully fails (no changes applied). If any statement in the transaction raises an error, the database automatically rolls back all previous statements in that transaction. Partial commits are impossible — the database will never show a state where some statements of a transaction applied and others did not.

A transaction must bring the database from one consistent state to another consistent state. Consistency is defined by the constraints, rules, and invariants declared in the schema — NOT NULL, UNIQUE, CHECK, FOREIGN KEY constraints, and any application-level business rules. A transaction that would violate any constraint is rejected entirely.

Each transaction executes as if it were the only transaction running, even when hundreds of transactions run simultaneously. Changes made by an in-progress transaction are not visible to other transactions until the first transaction commits (at the default isolation level). This prevents one transaction from reading dirty, inconsistent intermediate states written by another in-flight transaction.

Once a transaction commits, its changes are permanently recorded — even if the database server crashes immediately after COMMIT returns. The database achieves this through write-ahead logging (WAL): changes are written to a persistent log before they are applied to the main data files. On restart after a crash, the database replays the WAL to recover any committed changes that had not yet been written to main storage.

ACID is the set of four properties that define the reliability guarantees of database transactions. Atomicity means a transaction is all-or-nothing — every statement in it succeeds and is committed, or no statement takes effect. Partial completion is impossible. Consistency means a transaction can only bring the database from one valid state to another — all constraints, foreign keys, and schema rules are enforced, and any transaction that would violate them is rejected. Isolation means concurrent transactions do not interfere with each other — each transaction sees a consistent view of data and changes from one transaction are not visible to others until committed. Durability means once a transaction commits, its changes survive permanently — even a server crash immediately after COMMIT cannot lose committed data, because changes are written to a write-ahead log before being applied.

Why they matter: without Atomicity, a payment system could deduct money from a customer without crediting the merchant if it crashes mid-operation. Without Consistency, a database could contain orphaned records (orders referencing non-existent customers). Without Isolation, two concurrent transactions could both read a stock count of 1, both decrement it, and leave the count at -1. Without Durability, a confirmed order could disappear after a server crash.

ACID properties are implemented through specific mechanisms: Atomicity via transaction rollback on error; Consistency via constraint checking at commit time; Isolation via locking and MVCC (Multi-Version Concurrency Control); Durability via write-ahead logging. These are not optional features — they are the foundational contract that makes relational databases trustworthy for financial, medical, and any critical application data.

COMMIT makes all changes made since the last BEGIN permanent. Once COMMIT executes, the changes are written to durable storage and become visible to all other sessions. The transaction ends and all locks acquired during it are released. COMMIT is irreversible — once committed, changes cannot be undone through a ROLLBACK (only through a new transaction that explicitly reverses the changes).

ROLLBACK undoes all changes made since the last BEGIN (or since the last SAVEPOINT if using ROLLBACK TO SAVEPOINT). The database reverts to the exact state it was in before the transaction started. All modified rows are restored to their pre-transaction values. All locks are released. A ROLLBACK can be issued explicitly (by the application when it detects an error or business rule violation) or automatically (by the database when it detects a constraint violation, deadlock, or crash recovery).

The practical distinction matters when a transaction contains multiple statements: if statement 3 of 5 fails, ROLLBACK undoes statements 1 and 2 as well — the database never shows a state where only some of the transaction's changes applied. This is the guarantee applications rely on for correctness. In autocommit mode (the default in most SQL clients), each individual statement is its own implicit transaction that commits immediately — there is nothing to roll back if a later statement fails. This is why multi-statement operations that must be atomic must always be wrapped in an explicit BEGIN...COMMIT block.

SQL defines four isolation levels representing a spectrum from lowest to highest isolation: READ UNCOMMITTED (allows dirty reads — reading uncommitted data from other transactions), READ COMMITTED (prevents dirty reads — only reads committed data; this is the PostgreSQL default), REPEATABLE READ (prevents dirty reads and non-repeatable reads — if a transaction reads a row twice, it gets the same value both times), and SERIALIZABLE (prevents all anomalies — equivalent to running transactions one at a time, no concurrency anomalies possible).

The default (READ COMMITTED) is appropriate for most OLTP workloads — web requests, API handlers, individual user operations. Each statement sees the latest committed data, preventing dirty reads while allowing good concurrency. Change to REPEATABLE READ when: a transaction reads the same data multiple times and must see consistent values throughout (a financial report that sums balances across multiple queries within the same transaction must use REPEATABLE READ or higher, or it could show inconsistent totals if accounts are updated between reads).

Change to SERIALIZABLE when: two concurrent transactions could make mutually inconsistent decisions based on data they each read. The classic example is two doctors both reading that a room is empty and both assigning a patient to it — at READ COMMITTED both see empty and both assign, resulting in double booking. At SERIALIZABLE, one transaction wins and the other sees a conflict and is forced to retry. Use SERIALIZABLE for inventory allocation, appointment booking, any "read then write based on the read" pattern where the read-to-write decision must be exclusive. The cost is lower throughput — the database detects more conflicts and forces retries. In PostgreSQL, SERIALIZABLE is implemented via SSI (Serializable Snapshot Isolation) which reduces the conflict rate compared to traditional lock-based serializable isolation.

A dirty read occurs when Transaction A reads data that Transaction B has written but not yet committed. If Transaction B subsequently rolls back, Transaction A has read data that effectively never existed — a "dirty" (invalid) read. The term "dirty" refers to the uncommitted write being read before it is confirmed as permanent.

Example: a loyalty point balance adjustment transaction starts, credits a customer 500 points (balance goes from 1,000 to 1,500), but has not yet committed. Another transaction reads the customer's balance to check eligibility for a premium offer — it sees 1,500 points and approves the offer. The first transaction then rolls back (perhaps the order that earned the points was cancelled) — the balance reverts to 1,000 points. The customer was approved for a premium offer based on points they do not actually have.

READ UNCOMMITTED allows dirty reads — it is the only isolation level that does. READ COMMITTED (and all higher levels) prevents dirty reads by ensuring a transaction only sees data from committed transactions. PostgreSQL does not support READ UNCOMMITTED — SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is silently promoted to READ COMMITTED. In practice, dirty reads are almost never acceptable — READ COMMITTED is the minimum viable isolation level for any application where data correctness matters. READ UNCOMMITTED is sometimes used for approximate analytics (counting rows where a small error is acceptable) to avoid lock contention, but this use case is rare.

A deadlock occurs when two or more transactions each hold a lock that the other needs, creating a circular wait that can never resolve. Transaction A holds a lock on Row 1 and wants Row 2. Transaction B holds a lock on Row 2 and wants Row 1. Neither can proceed. The database detects this cycle and automatically rolls back one of the transactions (typically the one that has done less work or holds fewer locks), allowing the other to proceed. The rolled-back transaction receives an error and must be retried by the application.

Deadlocks are not errors in the database — they are a natural consequence of concurrent transactions accessing shared data. The database handles them correctly. The application must handle the deadlock error by retrying the rolled-back transaction. The real question is how to minimise their frequency.

Prevention strategies: first, always acquire locks in a consistent order. If every transaction that needs to lock both Row 1 and Row 2 always locks Row 1 before Row 2, deadlocks between them are impossible (one will wait for the other to finish, not create a cycle). Second, keep transactions short — the longer a transaction holds locks, the higher the probability another transaction needs those same locks. Commit as soon as the work is done. Third, use SELECT FOR UPDATE SKIP LOCKED for queue-based processing — each worker picks a different row and never contends with other workers. Fourth, avoid user interaction within a transaction — never hold a database transaction open while waiting for a user to click a button (the transaction would hold locks for seconds or minutes, causing widespread contention). Fifth, use optimistic locking (version numbers or timestamps) instead of pessimistic locking (SELECT FOR UPDATE) when conflicts are rare — check the version number at UPDATE time and retry only if it changed.

Cause: A previous statement in the current transaction raised an error. PostgreSQL puts the transaction into an aborted state — all subsequent statements are ignored and return this error. The transaction cannot proceed in this state. This happens when, for example, a constraint violation or duplicate key error occurs mid-transaction and the application continues sending statements instead of issuing ROLLBACK.

Fix: Issue ROLLBACK immediately after detecting any error in a transaction: ROLLBACK; This ends the aborted transaction and returns the connection to a clean state. Then either retry the entire transaction from BEGIN or handle the error at the application level. In application code, always catch exceptions in the transaction block and execute ROLLBACK before re-raising or returning. Never re-use a database connection that has an aborted transaction — all queries on that connection will fail until ROLLBACK is issued.

Cause: Two concurrent transactions each hold a lock needed by the other, creating a circular wait. PostgreSQL's deadlock detector runs periodically, identifies the cycle, and rolls back one transaction (the 'victim') to break the deadlock. The victim transaction receives this error. The other transaction is allowed to proceed and complete.

Fix: The application must handle deadlock errors by retrying the entire transaction. Implement exponential backoff — wait a short random time before retrying to avoid immediately deadlocking again. To reduce deadlock frequency: ensure all transactions that access the same set of rows acquire locks in the same order (lock customer before merchant, not merchant before customer). Keep transactions as short as possible. Use SELECT FOR UPDATE only when genuinely needed. Consider whether SERIALIZABLE isolation (which handles some of these conflicts differently via SSI) would be more appropriate for the workload.

Cause: Two statements that must succeed together are running in autocommit mode — each commits independently. The UPDATE committed, then the server crashed or the application errored before the INSERT could run. The result is a partial state that violates application-level invariants.

Fix: Wrap all statements that must succeed together in an explicit transaction: BEGIN; UPDATE ...; INSERT ...; COMMIT; With a transaction, if the INSERT fails for any reason, the UPDATE is automatically rolled back. The database will never show a state where one committed and the other did not. Audit existing code for any multi-statement operations that lack explicit BEGIN/COMMIT and add them. Add a CHECK or trigger that enforces the business invariant at the database level if possible — a delivered order must have a payment record.

Cause: Using SERIALIZABLE isolation level. Two concurrent transactions read and wrote overlapping data in a way that is not equivalent to any serial ordering. PostgreSQL's SSI (Serializable Snapshot Isolation) detected a serialization anomaly and rolled back one transaction to preserve serializability. This is not a bug — it is the correct behaviour of SERIALIZABLE isolation.

Fix: The application must retry serialization failures. Implement a retry loop: catch the serialization error (error code 40001 in PostgreSQL), wait a brief random interval, and retry the entire transaction from BEGIN. Most serialization failures are transient — the retry will typically succeed because the conflicting transaction has already committed and the window of conflict is gone. If serialization failures are frequent, reconsider whether SERIALIZABLE is necessary — REPEATABLE READ or READ COMMITTED may be sufficient for the workload with appropriate application-level conflict detection.

Cause: A long-running transaction is holding row or table locks while performing slow work (large aggregations, waiting for external API responses, or — worst — waiting for user input). Any other transaction that needs those same rows must wait for the lock to be released. As the wait grows, query timeouts begin cascading across the application.

Fix: Keep transactions as short as possible. Do all computation, validation, and external calls OUTSIDE the transaction. Only open BEGIN when you are ready to immediately execute the DML statements. Never make network calls (API requests, file reads) inside a transaction. Never wait for user interaction inside a transaction. Set statement_timeout (per-session timeout) and lock_timeout (maximum wait for a lock) in PostgreSQL: SET lock_timeout = '5s' — queries that cannot acquire a lock within 5 seconds fail instead of waiting indefinitely, preventing cascading timeouts.

Write a complete transaction scenario for FreshCart's order fulfilment process. The scenario: a customer (customer_id = 1) places an order for 2 units of product_id = 1. Write the SQL transaction that: (1) verifies the customer exists and is active (loyalty_tier != null), (2) checks product_id = 1 is in stock, (3) inserts a new order into the orders table with store_id = 'ST001', total_amount = quantity × unit_price, order_status = 'Processing', (4) inserts the order line into order_items. Wrap all validation and DML in a BEGIN...ROLLBACK block (use ROLLBACK at the end since this is a playground — in production it would be COMMIT). Also show what a SAVEPOINT would look like between the order INSERT and the order_items INSERT, and write the SELECT queries that verify each step.