Transactions and ACID

What is it? #

A transaction groups several statements into one all-or-nothing unit. Either every statement takes effect, or none of them do.

The classic case is a bank transfer: subtract from one account, add to another. If the second statement fails after the first succeeded, money has vanished. A transaction makes that impossible — the failure undoes the subtraction too.

You start one with BEGIN, make it permanent with COMMIT, or undo everything with ROLLBACK.

ACID is the four guarantees a transaction gives you: Atomicity, Consistency, Isolation and Durability. They sound abstract and are actually very concrete, which this lesson shows.

Think of it like this #

Moving furniture between two rooms with a rule: either every item arrives, or everything goes back exactly where it started. No half-moved state is ever allowed to be the final answer.

Halfway through, you drop a box. Instead of leaving the sofa in room B and the table in room A, you put everything back and report failure. The house is never left in a broken in-between state.

And while you are moving, nobody else sees the half-finished arrangement. They see either the old layout or the new one — never a chair floating in the hallway.

Simple example #

Transferring 500 from account A to account B.

Two statements: subtract from A, add to B. Between them, the total money in the system is temporarily wrong by 500. A transaction guarantees that nobody ever observes that moment, and that a crash in between leaves both accounts untouched.

Code #

-- ---------- The bank transfer ----------

BEGIN;                                      -- start the transaction

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;                                     -- both take effect, permanently

-- If the server crashed between the two UPDATEs, neither would be applied.
-- If the second failed, you would ROLLBACK and neither would be applied.

-- ---------- Rolling back ----------

BEGIN;
DELETE FROM orders WHERE placed_at < '2020-01-01';
SELECT count(*) FROM orders;      -- check what you are left with
ROLLBACK;                         -- changed your mind: nothing was deleted

-- This is THE safe way to try a risky statement.
-- Inside the transaction you can see the effect; ROLLBACK undoes it completely.

-- ---------- Checking a destructive change before committing ----------
-- !! This pattern is worth making a habit for any UPDATE or DELETE on real data.

BEGIN;

UPDATE orders SET status = 'archived'
WHERE placed_at < '2020-01-01';
-- UPDATE 41823        <-- did you expect ~41,000? If not, ROLLBACK now.

SELECT status, count(*) FROM orders GROUP BY status;   -- sanity-check the result

COMMIT;    -- only when the numbers look right
-- ROLLBACK;  -- otherwise

-- ---------- Savepoints: partial rollback ----------

BEGIN;
INSERT INTO customers (name, email) VALUES ('Asha', '[email protected]');

SAVEPOINT after_customer;                    -- a named point to return to

INSERT INTO orders (customer_id, total) VALUES (999, 100);   -- fails: no customer 999

ROLLBACK TO SAVEPOINT after_customer;        -- undo ONLY back to the savepoint
                                             -- the customer INSERT is still pending

INSERT INTO orders (customer_id, total)
VALUES (currval(pg_get_serial_sequence('customers','id')), 100);   -- correct one

COMMIT;    -- the customer AND the corrected order are both saved

RELEASE SAVEPOINT after_customer;   -- (optional) discard a savepoint you no longer need

---------- The aborted-transaction state ----------

If a statement fails inside a transaction, PostgreSQL marks the whole
transaction as aborted. Every further statement returns:

    ERROR: current transaction is aborted,
           commands ignored until end of transaction block

You cannot continue. You must ROLLBACK (or ROLLBACK TO SAVEPOINT).
This is deliberate: it stops you from committing a half-applied result.

---------- ACID, concretely ----------

A — ATOMICITY     All statements succeed, or none do.
                  The transfer never subtracts without adding.

C — CONSISTENCY   The database moves from one valid state to another.
                  Constraints (foreign keys, CHECK, UNIQUE) are enforced,
                  so a transaction cannot commit data that breaks the rules.

I — ISOLATION     Concurrent transactions do not see each other's
                  unfinished work. Others see the old balances or the
                  new ones, never the moment in between.

D — DURABILITY    Once COMMIT returns, the change survives a crash,
                  a power cut, or someone pulling the plug.
                  This is what the WAL exists for (see the WAL lesson).

-- ---------- Every statement is already a transaction ----------

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Without an explicit BEGIN, PostgreSQL wraps this in its own transaction
-- and commits it immediately. This is called "autocommit".
-- So a SINGLE statement is always atomic on its own.
-- You need BEGIN only when TWO OR MORE statements must succeed together.

-- ---------- Transactions and DDL ----------

-- PostgreSQL can roll back schema changes too, which many databases cannot:
BEGIN;
ALTER TABLE orders ADD COLUMN note text;
CREATE INDEX orders_note_idx ON orders (note);
ROLLBACK;        -- the column and the index are both gone

-- This makes migrations much safer: a failed migration leaves nothing behind.
-- EXCEPTION: CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

-- ---------- Finding transactions left open ----------

SELECT pid,
       now() - xact_start AS transaction_age,
       state,
       left(query, 60)    AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start IS NOT NULL
ORDER BY xact_start;

-- 'idle in transaction' for a long time is a REAL PROBLEM:
-- it holds locks and prevents VACUUM from cleaning up. See the VACUUM lesson.

How it works #

Atomicity is implemented through the write-ahead log. Changes are recorded there before they are applied, so if a crash interrupts a transaction, PostgreSQL knows on restart which transactions had committed and which had not, and undoes the incomplete ones. The WAL lesson covers this properly.

Consistency is where constraints do their work. A transaction that would leave a foreign key dangling or violate a CHECK cannot commit — PostgreSQL raises an error instead. This is why the table-design lesson insists on real constraints: they are the definition of "valid" that consistency is measured against.

Isolation means concurrent transactions do not observe each other's unfinished work. PostgreSQL achieves this with MVCC — multiversion concurrency control. Rather than overwriting a row, an update writes a new version of it. Each transaction sees the versions that were committed when it started, which is why readers never block writers and writers never block readers. It is also why old row versions accumulate and need cleaning up, which is the entire subject of the VACUUM lesson.

Durability means that once COMMIT returns, the change has been written to the WAL and flushed to disk. Power loss at that instant does not lose the transaction; on restart, PostgreSQL replays the WAL.

Autocommit is worth being clear about. Every single statement is already wrapped in its own transaction, so one UPDATE is atomic by itself. You need an explicit BEGIN only when two or more statements must succeed or fail together.

The aborted-transaction state confuses people the first time. After any error inside a transaction, PostgreSQL refuses every subsequent statement until you issue a ROLLBACK. That is a safety feature, not a malfunction: it makes it impossible to ignore a failure and commit a partially applied result anyway.

Savepoints provide partial rollback within a transaction. ROLLBACK TO SAVEPOINT undoes work back to a marked point while keeping everything before it and leaving the transaction usable. This also clears the aborted state, which is how you recover from a failed statement without discarding the whole transaction.

A genuinely useful PostgreSQL property: DDL is transactional. You can ALTER TABLE, CREATE INDEX and DROP inside a transaction and roll all of it back. Many databases commit schema changes immediately, leaving a failed migration half-applied. Here, a migration that fails cleanly leaves nothing behind. The exception is CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block.

Real-world use #

The habit worth adopting from this lesson is wrapping risky changes in a transaction and verifying before committing. BEGIN, run the UPDATE, read the reported row count, run a SELECT to confirm the result looks right, then COMMIT or ROLLBACK. It costs seconds and prevents the category of incident that otherwise requires restoring from backup.

Keep transactions short. A transaction left open holds locks and, more damagingly, prevents VACUUM from cleaning up row versions newer than its snapshot — across the entire database, not just the tables it touched. A connection sitting in idle in transaction for hours is a genuine production problem, which is why it is worth monitoring and why idle_in_transaction_session_timeout exists.

A specific application anti-pattern: BEGIN, then call an external API, then COMMIT. The transaction now stays open for however long that network call takes, with all the consequences above. Do the external work first, or after, but not inside.

Application frameworks usually manage transactions for you, and it is worth knowing exactly where the boundaries are. A common bug is code that appears to be in one transaction but is actually running several, so a partial failure leaves inconsistent data.

Retry logic matters for transactions that can fail due to concurrency — serialisation failures and deadlocks are normal under load and are safe to retry. A transaction built to be retried should be idempotent, which the isolation and locking lessons develop further.

Common mistakes #

• Leaving a transaction open in "idle in transaction" state, holding locks and blocking VACUUM globally.
• Making a network or API call inside a transaction, keeping it open for the duration of the call.
• Not knowing that an error aborts the whole transaction until you issue a ROLLBACK.
• Running destructive UPDATEs and DELETEs without BEGIN, so there is no chance to check before committing.
• Assuming a single statement needs an explicit transaction — autocommit already makes it atomic.

Practice #

Create an accounts table with two rows and a CHECK constraint that balance must be non-negative. Perform a transfer inside a transaction and commit it. Then attempt a transfer larger than the balance, watch the CHECK constraint abort the transaction, and confirm that further statements are refused until you ROLLBACK. Finally, use a savepoint: insert a customer, deliberately fail an order insert, roll back to the savepoint, insert a correct order, and commit — then verify the customer and the corrected order both exist.