Transactions and ACID

A transaction groups statements into one unit of work: either all of them take effect or none do. The classic example is a bank transfer, which needs a debit and a credit to succeed together.

The examples use a small accounts table, created in Example 1.

Example 1: BEGIN and COMMIT

A transfer of 100 from Asha to Ben. Both updates become visible together at COMMIT.

CREATE TABLE accounts (
  id      int PRIMARY KEY,
  owner   text NOT NULL,
  balance numeric(10,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Asha', 500), (2, 'Ben', 300);

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

SELECT * FROM accounts ORDER BY id;

 id | owner | balance 
----+-------+---------
  1 | Asha  |  400.00
  2 | Ben   |  400.00
(2 rows)

Other sessions never see the state after the first UPDATE and before the second. Two sessions show this (Session A changes a balance inside a transaction, Session B reads while it is open, on a fresh table with balances 500 and 300):

-- Session A (uncommitted change)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Session B (sees the old value)
SELECT * FROM accounts ORDER BY id;

 id | owner | balance 
----+-------+---------
  1 | Asha  |  500.00
  2 | Ben   |  300.00
(2 rows)

-- Session A
COMMIT;

-- Session B (sees the committed value)
SELECT * FROM accounts ORDER BY id;

 id | owner | balance 
----+-------+---------
  1 | Asha  |  400.00
  2 | Ben   |  300.00
(2 rows)

Session B sees no change until Session A commits.

Example 2: ROLLBACK

ROLLBACK discards every change made since BEGIN.

BEGIN;
UPDATE accounts SET balance = balance - 250 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;
ROLLBACK;

SELECT balance FROM accounts WHERE id = 1;

 balance 
---------
  150.00
(1 row)

 balance 
---------
  400.00
(1 row)

Inside the transaction the balance is 150.00. After ROLLBACK it is back to 400.00.

Example 3: Atomicity when a statement fails

In PostgreSQL an error inside a transaction puts it into an aborted state. Every later statement is rejected until ROLLBACK, so a half-finished transfer cannot be committed by accident.

BEGIN;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
SELECT * FROM accounts;
COMMIT;

SELECT * FROM accounts ORDER BY id;

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, Asha, -600.00).
ERROR:  current transaction is aborted, commands ignored until end of transaction block
 id | owner | balance 
----+-------+---------
  1 | Asha  |  400.00
  2 | Ben   |  400.00
(2 rows)

The credit succeeded, the debit violated CHECK (balance >= 0), the SELECT was rejected as the transaction was already aborted, and COMMIT on an aborted transaction performs a rollback. The final SELECT shows both balances unchanged: the credit to Ben was undone too.

MySQL differs: after a failed statement the transaction stays open, and only that one statement is rolled back, so the application decides whether to continue.

Example 4: Savepoints

A savepoint marks a point inside a transaction. ROLLBACK TO undoes work back to the mark and keeps the transaction usable.

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
ROLLBACK TO after_debit;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

SELECT * FROM accounts ORDER BY id;

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, Asha, -4650.00).
 id | owner | balance 
----+-------+---------
  1 | Asha  |  350.00
  2 | Ben   |  450.00
(2 rows)

The failing statement is undone by ROLLBACK TO after_debit, the 50 debit before the savepoint is kept, and the transfer completes. Savepoints are how a driver's "nested transaction" support works.

Example 5: Autocommit

Outside BEGIN, every statement is its own transaction and commits immediately. txid_current() returns the current transaction ID, so comparing it across statements shows how many transactions ran.

SELECT txid_current() AS t1 \gset
SELECT txid_current() = :t1 AS same_transaction;

BEGIN;
SELECT txid_current() AS t2 \gset
SELECT txid_current() = :t2 AS same_transaction;
COMMIT;

 same_transaction 
------------------
 f
(1 row)

 same_transaction 
------------------
 t
(1 row)

In autocommit mode the second statement runs in a new transaction (f); inside BEGIN both statements share one (t).

Client libraries differ: psql and most server-side tools default to autocommit, while JDBC and Python DB-API drivers such as psycopg2 start a transaction implicitly and wait for an explicit commit(). MySQL exposes the setting as autocommit (default 1).

Example 6: ACID and the mechanism behind each letter

MySQL InnoDB uses the undo log for rollback (atomicity), the redo log for durability, and row locks plus MVCC for isolation.

Example 7: Consistency is enforced by constraints

The database guarantees only the rules it knows about. A rule expressed as a constraint holds for every transaction; a rule kept only in application code can be bypassed.

BEGIN;
UPDATE accounts SET balance = -1 WHERE id = 2;
ROLLBACK;

ALTER TABLE accounts ADD CONSTRAINT owner_not_blank CHECK (length(trim(owner)) > 0);

BEGIN;
UPDATE accounts SET owner = '   ' WHERE id = 1;
ROLLBACK;

ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (2, Ben, -1.00).
ERROR:  new row for relation "accounts" violates check constraint "owner_not_blank"
DETAIL:  Failing row contains (1,    , 350.00).

Both updates are rejected, so no committed state can contain a negative balance or a blank owner. "Total money is unchanged by a transfer" cannot be a single-row constraint, so it depends on the transaction being atomic and correctly written.

Example 8: Durability, WAL and synchronous_commit

At commit, PostgreSQL writes a commit record to the WAL and flushes it to disk (fsync) before reporting success. Data pages are written later; after a crash, replay of the WAL restores them.

SHOW fsync;
SHOW synchronous_commit;
SHOW wal_level;

SELECT pg_current_wal_lsn() AS before_lsn \gset
INSERT INTO accounts VALUES (3, 'Chen', 10);
SELECT pg_current_wal_lsn() > :'before_lsn' AS wal_advanced;

 fsync 
-------
 on
(1 row)

 synchronous_commit 
--------------------
 on
(1 row)

 wal_level 
-----------
 replica
(1 row)

 wal_advanced 
--------------
 t
(1 row)

The insert advanced the WAL position, which is the record that makes the row durable.

synchronous_commit trades durability for latency:

It can be set per transaction:

BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO accounts VALUES (4, 'Dina', 20);
COMMIT;

fsync = off is different: it risks data corruption after a crash and is only for disposable data.

Example 9: DDL is transactional in PostgreSQL

CREATE TABLE, ALTER TABLE, and DROP TABLE can be rolled back, which makes schema migrations atomic.

BEGIN;
CREATE TABLE audit_log (id serial PRIMARY KEY, note text);
ALTER TABLE accounts ADD COLUMN status text DEFAULT 'active';
SELECT to_regclass('audit_log') AS audit_log_inside;
ROLLBACK;

SELECT to_regclass('audit_log') AS audit_log_after;
SELECT column_name FROM information_schema.columns
WHERE table_name = 'accounts' ORDER BY ordinal_position;

 audit_log_inside 
------------------
 audit_log
(1 row)

 audit_log_after 
-----------------
 
(1 row)

 column_name 
-------------
 id
 owner
 balance
(3 rows)

After ROLLBACK the table is gone and the status column was never added.

Example 10: Transactions in application code

Three habits cause most transaction problems in applications.

Keep transactions short. An open transaction holds its locks and blocks vacuum from removing old row versions. A session that opened a transaction and is waiting on the application shows as idle in transaction:

-- Session A (application opens a transaction and then does other work)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;

 balance 
---------
  500.00
(1 row)

-- Session B (monitoring)
SELECT state, query FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();

        state        |                   query                    
---------------------+--------------------------------------------
 idle in transaction | SELECT balance FROM accounts WHERE id = 1;
(1 row)

-- Session A
ROLLBACK;

Do not make network calls, wait for user input, or sleep inside a transaction. Limit the damage with idle_in_transaction_session_timeout.

Retry on serialization failure. Under REPEATABLE READ and SERIALIZABLE, PostgreSQL can abort a transaction with SQLSTATE 40001 (serialization_failure); a deadlock aborts with 40P01 (deadlock_detected). The transaction was valid; running it again from the start usually succeeds. Each session below reads one account and updates the other:

-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance + 1 WHERE id = 2;

  sum   
--------
 500.00
(1 row)

-- Session B
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts WHERE id = 2;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;

  sum   
--------
 300.00
(1 row)

-- Session A
COMMIT;

-- Session B
COMMIT;

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

Session A committed. Session B was cancelled at COMMIT and its whole transaction was rolled back. The application retries it:

for attempt in range(5):
    try:
        with conn:                      # BEGIN ... COMMIT / ROLLBACK
            with conn.cursor() as cur:
                cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                transfer(cur, src, dst, amount)
        break
    except (SerializationFailure, DeadlockDetected):
        continue                        # retry the whole transaction
else:
    raise RuntimeError("giving up after 5 attempts")

The retry must re-run the whole transaction, including the reads, because the earlier reads may be stale.

Do not ignore errors. In PostgreSQL a failed statement aborts the transaction (Example 3). Code that catches the error and keeps issuing statements gets current transaction is aborted errors until it rolls back or uses a savepoint.

Interview points

• ACID stands for atomicity, consistency, isolation, durability. Be ready to name the mechanism: atomicity via the commit log and undo or MVCC, durability via WAL, isolation via MVCC and locks, consistency via constraints.
• The "C" in ACID is about the database rules holding after each transaction. It differs from the "C" in the CAP theorem, which is about replicas agreeing.
• A transaction that has not committed is invisible to other sessions under READ COMMITTED and stricter levels. See isolation levels.
• COMMIT is durable only after the WAL flush. synchronous_commit = off can lose the last transactions but never corrupts data.
• PostgreSQL DDL is transactional, MySQL and Oracle DDL is not. Migrations in PostgreSQL can be wrapped in one transaction.
• What autocommit is, and why a driver that disables it needs an explicit commit().
• Why long transactions are harmful: locks held, vacuum blocked, bloat. See locking and deadlocks.
• Which errors should be retried (40001, 40P01) and which should not (constraint violations).

Summary

• A transaction applies all of its changes at COMMIT or none of them after ROLLBACK; an error in PostgreSQL forces a rollback of the whole transaction unless a savepoint is used.
• ACID maps to concrete mechanisms: commit log (A), constraints (C), MVCC and locks (I), WAL flush (D).
• Statements outside BEGIN run in autocommit mode, one transaction each.
• synchronous_commit trades a small window of possible loss for lower commit latency; fsync = off risks corruption.
• Keep transactions short, retry the whole transaction on 40001 and 40P01, and remember that PostgreSQL DDL is transactional while MySQL and Oracle DDL is not.