Transactions, ACID and savepoints

A transaction is a group of SQL statements that the database treats as one unit of work: either every change in it becomes permanent together, with COMMIT, or none of them does, with ROLLBACK or a crash. Moving 500 from Asha’s wallet to Ravi’s takes two UPDATE statements, one debit and one credit. Run them as one transaction and nobody can ever see, or be left with, the debit without the credit. The four letters ACID name what that promise contains: atomicity, consistency, isolation and durability. Every engine also runs a statement typed on its own as a tiny transaction, which is called autocommit. Where PostgreSQL, MySQL and SQLite differ most is what happens after an error in the middle of a transaction, and the difference decides whether a failed transfer can quietly create money.

Each SQL example below runs on SQLite 3.49.1, which sql.js 1.14.2 brings to your browser, and one example is a short Python program, recorded with Python 3.14.8 and its sqlite3 module.

A transfer is two statements

The wallet ledger has one row per account. A CHECK constraint says that no balance may go below zero, the database’s version of “no overdrafts”. BEGIN opens the transaction, the two updates move the money and COMMIT makes both changes permanent at the same moment:

-- A tiny wallet ledger: two accounts, balances in rupees. No balance may go below zero.
CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  holder  TEXT    NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Asha', 800), (2, 'Ravi', 1000);

-- Move 500 from Asha to Ravi: two statements, one unit of work.
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

SELECT id, holder, balance FROM accounts ORDER BY id;
SELECT sum(balance) AS total FROM accounts;

┌────┬────────┬─────────┐
│ id │ holder │ balance │
├────┼────────┼─────────┤
│ 1  │ Asha   │ 300     │
│ 2  │ Ravi   │ 1500    │
└────┴────────┴─────────┘
┌───────┐
│ total │
├───────┤
│ 1800  │
└───────┘

Asha goes from 800 to 300 and Ravi from 1000 to 1500, and the total stays at 1800. That last number is worth printing in every money example: a transfer moves money, so the sum of all balances must not change.

The spelling varies a little. PostgreSQL and MySQL accept START TRANSACTION as well as BEGIN, and START TRANSACTION is the form the SQL standard uses. SQLite writes BEGIN or BEGIN TRANSACTION, accepts END as another name for COMMIT, and lets you say how early the transaction takes its write lock, with BEGIN DEFERRED, BEGIN IMMEDIATE or BEGIN EXCLUSIVE; a later lesson of this module uses that.

ROLLBACK: as if nothing happened

ROLLBACK ends the transaction and throws away every change made since BEGIN. Until then, the session that made the changes already sees them, which is how a program can check its own work before deciding:

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  holder  TEXT    NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Asha', 800), (2, 'Ravi', 1000);

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- Inside the transaction, this session already sees its own changes.
SELECT id, holder, balance FROM accounts ORDER BY id;
ROLLBACK;

-- ROLLBACK threw both changes away.
SELECT id, holder, balance FROM accounts ORDER BY id;

┌────┬────────┬─────────┐
│ id │ holder │ balance │
├────┼────────┼─────────┤
│ 1  │ Asha   │ 300     │
│ 2  │ Ravi   │ 1500    │
└────┴────────┴─────────┘
┌────┬────────┬─────────┐
│ id │ holder │ balance │
├────┼────────┼─────────┤
│ 1  │ Asha   │ 800     │
│ 2  │ Ravi   │ 1000    │
└────┴────────┴─────────┘

The first result shows Asha at 300 and Ravi at 1500, the transfer as this session sees it. After ROLLBACK, both are back at 800 and 1000. Other sessions never saw the middle state at all: what they may see of work that is still in progress is the subject of the next lesson, on isolation levels.

Autocommit: one statement, one transaction

Without BEGIN, each statement runs in a transaction of its own, which commits by itself when the statement ends. All three engines start in this autocommit mode: PostgreSQL and SQLite wrap every statement outside BEGIN this way, and MySQL does so because its autocommit variable is 1 by default. In MySQL, SET autocommit = 0 turns it off for the session, and then every statement joins an open transaction until you write COMMIT or ROLLBACK.

Autocommit still makes each statement atomic. A statement that changes many rows changes all of them or none, even when the problem is in the last row:

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  holder  TEXT    NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Asha', 300), (2, 'Ravi', 1000), (3, 'Meena', 650);

-- No BEGIN: the statement is a transaction of its own. Charging every account a
-- fee of 400 would take Asha to -100, so the whole statement fails...
UPDATE accounts SET balance = balance - 400;

-- ...and no row changed, not even the two that could pay.
SELECT id, holder, balance FROM accounts ORDER BY id;

┌────┬────────┬─────────┐
│ id │ holder │ balance │
├────┼────────┼─────────┤
│ 1  │ Asha   │ 300     │
│ 2  │ Ravi   │ 1000    │
│ 3  │ Meena  │ 650     │
└────┴────────┴─────────┘

Runtime error near line 10: CHECK constraint failed: balance >= 0

Charging a fee of 400 would take Asha below zero, so SQLite rejects the whole UPDATE, and Ravi and Meena, who could both pay, keep their old balances too. One statement is the smallest transaction there is, and one statement that does all of a job is often the safest way to do it, as the exercise at the end of this lesson shows.

ACID: four promises

• Atomicity. All of the transaction’s changes take effect, or none does. A ROLLBACK, an error that aborts the transaction or a crash before COMMIT all end the same way: as if the transaction had never started. Each engine has its own machinery for it: PostgreSQL marks the transaction as aborted, so the row versions it wrote are never seen, InnoDB applies its undo log, and SQLite restores pages from its rollback journal or ignores the unfinished part of its write-ahead log.
• Consistency. At COMMIT, the data obeys every rule the schema declares: NOT NULL, UNIQUE, CHECK and foreign keys. Most constraints are checked as each statement runs; PostgreSQL and SQLite can also postpone a foreign-key check to COMMIT when it is declared DEFERRABLE INITIALLY DEFERRED. Rules that live only in the application, such as “a transfer never changes the total”, hold only if each transaction is written correctly. The database keeps the rules it was told about; the rest is your code’s job.
• Isolation. Transactions running at the same time do not trip over each other’s half-finished work. How much of each other they may see is set by the isolation level, and the levels differ more than their names suggest. That is the next lesson.
• Durability. Once COMMIT returns, the change survives a crash or a power cut. Before answering, the engine writes the change to a log on disk and waits for the disk to confirm: PostgreSQL’s write-ahead log, InnoDB’s redo log with innodb_flush_log_at_trx_commit at its default of 1, and SQLite’s journal with PRAGMA synchronous at FULL. Each engine lets you relax this for speed, and then the last commits before a crash can be lost.

When a statement fails inside a transaction

Here the engines part ways. Asha holds only 300 and the transfer asks for 500. The debit breaks the CHECK constraint, the credit works, and the script then commits:

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  holder  TEXT    NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Asha', 300), (2, 'Ravi', 1000);

-- Asha holds only 300, so the debit breaks the CHECK constraint.
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

SELECT id, holder, balance FROM accounts ORDER BY id;
SELECT sum(balance) AS total FROM accounts;

┌────┬────────┬─────────┐
│ id │ holder │ balance │
├────┼────────┼─────────┤
│ 1  │ Asha   │ 300     │
│ 2  │ Ravi   │ 1500    │
└────┴────────┴─────────┘
┌───────┐
│ total │
├───────┤
│ 1800  │
└───────┘

Runtime error near line 10: CHECK constraint failed: balance >= 0

SQLite rejected the debit, and only the debit. The transaction stayed open, the credit went through, and COMMIT made it permanent: Ravi now has 1500, Asha still has 300, and the total has grown from 1300 to 1800. Five hundred rupees were created from nothing. This is SQLite’s documented default for a constraint error: it backs out the changes of the statement that failed, keeps the changes of earlier statements and leaves the transaction active.

MySQL behaves the same way. MySQL 8.4 rejects the debit with error 3819 (Check constraint 'accounts_chk_1' is violated.), keeps the transaction open, and a COMMIT keeps the credit. InnoDB treats a duplicate key the same way: it rolls back the statement, not the transaction.

PostgreSQL does the opposite. After any error, the whole transaction is marked as failed. Every later statement in it is refused with ERROR: current transaction is aborted, commands ignored until end of transaction block, and when you finally send COMMIT, PostgreSQL answers ROLLBACK: nothing of the transaction is kept, neither the debit nor the credit. A script with the same four statements leaves both balances untouched.

So the same script is safe on one engine and creates money on the other two. Never rely on the engine’s choice: decide yourself what happens after an error.

Roll back on any error

In application code, the rule is short: when any statement of a transaction fails, roll the whole transaction back and report the failure. The Python program below runs the same failed transfer twice, once with code that logs the error and carries on, and once with code that rolls back:

"""A transfer from application code, two ways: the error ignored, and the error rolled back.

Asha holds 300 and asks to send 500 to Ravi. The CHECK constraint rejects the debit.
"""
import sqlite3


def open_wallet():
    # autocommit=True: the program sends BEGIN, COMMIT and ROLLBACK itself.
    con = sqlite3.connect(":memory:", autocommit=True)
    con.executescript("""
        CREATE TABLE accounts (
          id      INTEGER PRIMARY KEY,
          holder  TEXT    NOT NULL,
          balance INTEGER NOT NULL CHECK (balance >= 0)
        );
        INSERT INTO accounts VALUES (1, 'Asha', 300), (2, 'Ravi', 1000);
    """)
    return con


DEBIT = "UPDATE accounts SET balance = balance - ? WHERE id = ?"
CREDIT = "UPDATE accounts SET balance = balance + ? WHERE id = ?"


def transfer_ignoring_errors(con, src, dst, amount):
    """Wrong: logs the failed statement and commits whatever else worked."""
    con.execute("BEGIN")
    for sql, args in ((DEBIT, (amount, src)), (CREDIT, (amount, dst))):
        try:
            con.execute(sql, args)
        except sqlite3.IntegrityError as e:
            print(f"  logged and ignored: {e}")
    con.execute("COMMIT")


def transfer(con, src, dst, amount):
    """Right: any error rolls the whole transaction back, then reaches the caller."""
    con.execute("BEGIN")
    try:
        con.execute(DEBIT, (amount, src))
        con.execute(CREDIT, (amount, dst))
    except Exception:
        con.execute("ROLLBACK")
        raise
    con.execute("COMMIT")


def show(con):
    rows = con.execute("SELECT holder, balance FROM accounts ORDER BY id").fetchall()
    total = sum(balance for _, balance in rows)
    print("  " + ", ".join(f"{holder} {balance}" for holder, balance in rows) + f" (total {total})")


print("Ignoring the error:")
con = open_wallet()
transfer_ignoring_errors(con, 1, 2, 500)
show(con)

print("Rolling back on any error:")
con = open_wallet()
try:
    transfer(con, 1, 2, 500)
except sqlite3.IntegrityError as e:
    print(f"  transfer refused: {e}")
show(con)

Ignoring the error:
  logged and ignored: CHECK constraint failed: balance >= 0
  Asha 300, Ravi 1500 (total 1800)
Rolling back on any error:
  transfer refused: CHECK constraint failed: balance >= 0
  Asha 300, Ravi 1000 (total 1300)

The first version reproduces the bug of the SQL script: the error is logged, the commit keeps the credit, and the total grows to 1800. The second wraps both statements in try, sends ROLLBACK from the except branch and re-raises, so the caller learns that the transfer was refused and the total stays at 1300. Nothing in that function depends on which engine is underneath, which is the point.

SQLite adds one more reason for the explicit ROLLBACK. A few errors, such as a full disk (SQLITE_FULL), an I/O error (SQLITE_IOERR) or running out of memory (SQLITE_NOMEM), sometimes roll the whole transaction back by themselves and sometimes only the failed statement, so its manual recommends answering them with a ROLLBACK of your own: if the transaction has already ended, that ROLLBACK fails with an error that does no harm. Most database libraries also offer a helper that does this for you, such as a with block or a transaction decorator; read what it does on an error before you trust it.

Savepoints: undo part of a transaction

Sometimes a transaction should survive the failure of one of its parts. A batch import should skip a bad record and keep the good ones, all in one transaction. A savepoint marks a point inside the transaction: SAVEPOINT name sets it, ROLLBACK TO name undoes everything after it while keeping what came before, and RELEASE name forgets the mark and keeps the work. Each payment below is two statements, a payment row and a debit, under its own savepoint:

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  holder  TEXT    NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
CREATE TABLE payments (id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL, amount INTEGER NOT NULL);
INSERT INTO accounts VALUES (1, 'Asha', 300), (2, 'Ravi', 1000);

-- A batch of three bill payments. Each is a payment row plus a debit, under its own
-- savepoint, so a payment the balance cannot cover leaves neither behind.
BEGIN;

SAVEPOINT p1;
INSERT INTO payments VALUES (1, 2, 400);
UPDATE accounts SET balance = balance - 400 WHERE id = 2;
RELEASE p1;

SAVEPOINT p2;
INSERT INTO payments VALUES (2, 1, 450);
UPDATE accounts SET balance = balance - 450 WHERE id = 1;
ROLLBACK TO p2;
RELEASE p2;

SAVEPOINT p3;
INSERT INTO payments VALUES (3, 1, 200);
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
RELEASE p3;

COMMIT;

SELECT id, account_id, amount FROM payments ORDER BY id;
SELECT id, holder, balance FROM accounts ORDER BY id;

┌────┬────────────┬────────┐
│ id │ account_id │ amount │
├────┼────────────┼────────┤
│ 1  │ 2          │ 400    │
│ 3  │ 1          │ 200    │
└────┴────────────┴────────┘
┌────┬────────┬─────────┐
│ id │ holder │ balance │
├────┼────────┼─────────┤
│ 1  │ Asha   │ 100     │
│ 2  │ Ravi   │ 600     │
└────┴────────┴─────────┘

Runtime error near line 20: CHECK constraint failed: balance >= 0

The second payment, 450 from Asha’s 300, fails its debit. ROLLBACK TO p2 then also removes its payment row, which had been inserted successfully, so the table never records a payment that was not paid. The first and third payments commit: Ravi is left with 600 and Asha with 100. A real import program issues ROLLBACK TO only when a step fails; this script writes it where the failure happens.

In PostgreSQL, a savepoint is the only way to carry on after an error inside a transaction: ROLLBACK TO SAVEPOINT clears the failed state and the transaction continues from the mark. The psql client can do this for you with \set ON_ERROR_ROLLBACK on, which quietly sets a savepoint before every statement. MySQL has the same three statements, and so does SQLite, where a SAVEPOINT issued outside any transaction starts one and releasing that outermost savepoint commits it.

Changing the schema inside a transaction

Statements that create or change tables are called DDL, and the engines disagree on whether they belong to a transaction:

CREATE TABLE accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL);

-- In SQLite, CREATE TABLE is part of the transaction like any other change.
BEGIN;
INSERT INTO accounts VALUES (1, 'Asha');
CREATE TABLE audit_log (id INTEGER PRIMARY KEY, note TEXT);
INSERT INTO audit_log (note) VALUES ('opened account 1');
ROLLBACK;

SELECT count(*) AS accounts FROM accounts;
SELECT name FROM sqlite_schema WHERE type = 'table' ORDER BY name;

-- A transaction cannot start inside another one.
BEGIN;
BEGIN;
COMMIT;

┌──────────┐
│ accounts │
├──────────┤
│ 0        │
└──────────┘
┌──────────┐
│   name   │
├──────────┤
│ accounts │
└──────────┘

Runtime error near line 15: cannot start a transaction within a transaction

In SQLite, the ROLLBACK removed the new audit_log table along with the inserted rows; PostgreSQL behaves the same way for almost all DDL, which makes it safe to run a whole schema migration as one transaction. MySQL does not: CREATE TABLE, ALTER TABLE and the other DDL statements cause an implicit commit, first of the work before them, then of themselves. The same script on MySQL keeps Asha’s row and the new table, and its ROLLBACK has nothing left to undo.

The last three lines show a second difference. A transaction cannot contain another one. SQLite refuses the inner BEGIN with an error, and PostgreSQL answers it with a warning, there is already a transaction in progress, and carries on. MySQL silently commits the open transaction and starts a new one, so a nested START TRANSACTION commits half-done work. For nesting, use savepoints.

How the engines differ

Your turn

-- Move 500 from account 1 to account 2, or change nothing when account 1 cannot pay.
-- This version fails the CHECK constraint when account 1 holds less than 500.
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

SELECT id, balance FROM accounts ORDER BY id;

# Sample tests: each one builds a small accounts table, runs your SQL on it and compares the balances it returns,
# in order.
tests:
  - name: moves 500 when account 1 can pay
    ordered: true
    setup: |
      CREATE TABLE accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL CHECK (balance >= 0));
      INSERT INTO accounts VALUES (1, 'Asha', 800), (2, 'Ravi', 1000);
    expected:
      columns: [id, balance]
      rows:
        - [1, 300]
        - [2, 1500]
  - name: changes nothing, without an error, when account 1 holds less than 500
    ordered: true
    setup: |
      CREATE TABLE accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL CHECK (balance >= 0));
      INSERT INTO accounts VALUES (1, 'Asha', 300), (2, 'Ravi', 1000);
    expected:
      columns: [id, balance]
      rows:
        - [1, 300]
        - [2, 1000]
  - name: moves exactly the whole balance when account 1 holds 500
    ordered: true
    setup: |
      CREATE TABLE accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL CHECK (balance >= 0));
      INSERT INTO accounts VALUES (1, 'Asha', 500), (2, 'Ravi', 0);
    expected:
      columns: [id, balance]
      rows:
        - [1, 0]
        - [2, 500]
  - name: leaves every other account alone
    ordered: true
    setup: |
      CREATE TABLE accounts (id INTEGER PRIMARY KEY, holder TEXT NOT NULL, balance INTEGER NOT NULL CHECK (balance >= 0));
      INSERT INTO accounts VALUES (1, 'Asha', 900), (2, 'Ravi', 100), (3, 'Meena', 50);
    expected:
      columns: [id, balance]
      rows:
        - [1, 400]
        - [2, 600]
        - [3, 50]

Interview questions

Fresher · What does each letter of ACID promise?

Atomicity: all of a transaction’s changes take effect, or none does, whether it ends with ROLLBACK, an error or a crash. Consistency: at commit, the data obeys every rule the schema declares, such as NOT NULL, UNIQUE, CHECK and foreign keys; rules known only to the application hold only if each transaction is written correctly. Isolation: concurrent transactions do not see each other’s unfinished work, to the degree the isolation level promises. Durability: once COMMIT returns, the change survives a crash, because the engine has written it to a log on disk first. A transfer shows all four: the debit and the credit happen together, no balance goes negative, a report running at the same time never sees money in flight, and the transfer is still there after a restart.

Fresher · A statement inside a transaction fails. What happens to the transaction in PostgreSQL, MySQL and SQLite?

PostgreSQL marks the whole transaction as failed: it refuses every later statement with “current transaction is aborted”, and a COMMIT rolls everything back. MySQL with InnoDB and SQLite undo only the failed statement and keep the transaction open, so a later COMMIT keeps every statement that worked. That is how a transfer whose debit failed a CHECK constraint can still commit its credit. In PostgreSQL, ROLLBACK TO SAVEPOINT is the way to continue after an error. Whatever the engine, application code should roll back as soon as any statement of a transaction fails.

Key takeaways

• A transaction makes several statements one unit: COMMIT keeps all their changes and ROLLBACK keeps none.
• Without BEGIN, every statement commits on its own, and each single statement is atomic by itself.
• ACID: all or nothing, the schema’s rules hold at commit, concurrent work stays apart, and committed work survives a crash.
• After an error, PostgreSQL aborts the whole transaction, while MySQL and SQLite undo only the failed statement and let COMMIT keep the rest. Roll back on any error in your own code.
• Savepoints undo part of a transaction. DDL is transactional in PostgreSQL and SQLite, but MySQL commits implicitly around it, and a second START TRANSACTION there commits the first.