SQL Transactions

A transaction is a group of one or more SQL statements that the database treats as a single, indivisible unit of work. Either every statement in the group succeeds and its changes become permanent, or none of them do — the database is left exactly as it was before the transaction started. Transactions are what let you safely move money between two bank accounts, update inventory while placing an order, or make any change that touches multiple rows or tables where a partial update would corrupt your data.

Overview: How Transactions Work

Without transactions, every statement you run is committed to disk the instant it finishes — this is called autocommit mode, and it is the default in almost every SQL database, including SQLite. Autocommit is fine for a single, independent statement, but it becomes dangerous when a logical operation requires several statements to succeed together. If you transfer money by running two separate UPDATE statements (debit one account, credit another) and the application crashes between them, you have destroyed money — one account was debited, but the other was never credited.

A transaction solves this by wrapping those statements in a boundary. You mark the start with BEGIN TRANSACTION, run your statements, and finish with either COMMIT (make the changes permanent) or ROLLBACK (undo everything since BEGIN). While the transaction is open, the database engine tracks every change you make in a log — SQLite uses either a rollback journal or a write-ahead log (WAL) — so that if you roll back, or if the power fails mid-transaction, the engine can restore the database to its pre-transaction state.

Well-behaved transactions follow the ACID properties:

  • Atomicity — all statements in the transaction succeed, or none of them do.
  • Consistency — the database moves from one valid state to another; constraints (foreign keys, checks, uniqueness) are never violated at commit time.
  • Isolation — concurrent transactions don’t see each other’s uncommitted changes.
  • Durability — once COMMIT succeeds, the change survives a crash or power loss.

Syntax

BEGIN TRANSACTION;
  -- one or more SQL statements
COMMIT;

-- or, to undo everything since BEGIN:
BEGIN TRANSACTION;
  -- one or more SQL statements
ROLLBACK;
Keyword Purpose
BEGIN TRANSACTION Opens a new transaction. SQLite also accepts plain BEGIN; MySQL and PostgreSQL commonly use START TRANSACTION.
COMMIT Permanently applies every change made since BEGIN.
ROLLBACK Discards every change made since BEGIN, as if the transaction never happened.
SAVEPOINT name Creates a named checkpoint inside an open transaction that you can roll back to without discarding the whole transaction.
ROLLBACK TO name Undoes changes made after the named savepoint, but keeps the transaction open.
RELEASE name Removes a savepoint once you no longer need to roll back to it (does not commit).

Examples

Example 1: A simple transfer (COMMIT)

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL,
    balance REAL NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (id, owner, balance) VALUES
    (1, 'Alice', 500.00),
    (2, 'Bob', 200.00);

BEGIN TRANSACTION;

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

COMMIT;

SELECT id, owner, balance FROM accounts ORDER BY id;

Result:

id owner balance
1 Alice 350.0
2 Bob 350.0

Both UPDATE statements run inside the same transaction. Neither change is visible to other connections until COMMIT runs — at that point both updates become permanent together. $150 leaves Alice’s balance and the same $150 lands in Bob’s; the total money in the table never changes.

Example 2: Undoing a mistake (ROLLBACK)

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL,
    balance REAL NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (id, owner, balance) VALUES
    (1, 'Alice', 500.00),
    (2, 'Bob', 200.00);

BEGIN TRANSACTION;

INSERT INTO accounts (id, owner, balance) VALUES (3, 'Carol', 100.00);

-- On second thought, Carol's account should not be created yet
ROLLBACK;

SELECT id, owner, balance FROM accounts ORDER BY id;

Result:

id owner balance
1 Alice 500.0
2 Bob 200.0

The INSERT for Carol ran successfully inside the transaction, but because the transaction was rolled back instead of committed, it is as if that statement never executed. Only Alice and Bob remain — the table looks exactly as it did before BEGIN.

Example 3: Partial undo with SAVEPOINT

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL,
    balance REAL NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (id, owner, balance) VALUES
    (1, 'Alice', 500.00),
    (2, 'Bob', 200.00);

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance + 50.00 WHERE id = 1;  -- give Alice a bonus

SAVEPOINT before_bob_bonus;
UPDATE accounts SET balance = balance + 50.00 WHERE id = 2;  -- oops, Bob shouldn't get one
ROLLBACK TO before_bob_bonus;
RELEASE before_bob_bonus;

COMMIT;

SELECT id, owner, balance FROM accounts ORDER BY id;

Result:

id owner balance
1 Alice 550.0
2 Bob 200.0

A SAVEPOINT lets you undo part of a transaction without throwing away everything. Alice’s bonus stays applied because it happened before the savepoint; Bob’s mistaken bonus is undone by ROLLBACK TO before_bob_bonus, and the whole transaction is still committed cleanly at the end.

How It Works Step by Step (Under the Hood)

When you run BEGIN TRANSACTION, the engine does not immediately touch the main database file for every statement that follows. Instead:

  • 1. BEGIN — the engine acquires a lock and starts recording changes in a separate log (SQLite’s rollback journal or WAL file), rather than overwriting the original data pages directly.
  • 2. Each statement runs — INSERT/UPDATE/DELETE modify pages in memory and record enough information in the log to reverse the change if needed. Other connections still see the pre-transaction data.
  • 3. Constraint checks — CHECK, NOT NULL, UNIQUE, and foreign key constraints are enforced statement-by-statement (and, for deferred foreign keys, at commit time).
  • 4. COMMIT — the engine flushes the changed pages to the real database file and marks the transaction durable. Once this returns successfully, the data survives a crash.
  • 4′. ROLLBACK (instead) — the engine discards the log without ever touching the main file, so the database is untouched.

This is also where isolation levels come in — they control how much of one transaction’s in-progress work another concurrent transaction is allowed to see:

Isolation Level What It Prevents
READ UNCOMMITTED Nothing — other transactions may see uncommitted (“dirty”) changes. Rarely used.
READ COMMITTED Dirty reads. Each statement sees only committed data, but repeated reads within the same transaction may differ.
REPEATABLE READ Dirty reads and non-repeatable reads. Rows you’ve already read won’t change under you.
SERIALIZABLE All of the above, plus phantom rows — transactions behave as if run one at a time.

SQLite doesn’t expose these as a tunable setting the way PostgreSQL, MySQL, or SQL Server do — it uses file-level locking to give effectively serializable behavior by default (a writer blocks other writers until it commits or rolls back). In client/server databases like PostgreSQL or MySQL, choosing a stricter isolation level buys correctness at the cost of more blocking and possible retries; choosing a looser one buys concurrency at the cost of subtle read anomalies.

Common Mistakes

Mistake 1: Starting a transaction inside another transaction

BEGIN TRANSACTION;
BEGIN TRANSACTION;

SQLite (like most databases) does not support truly nested transactions. Calling BEGIN while a transaction is already open raises an error such as “cannot start a transaction within a transaction”. If you need a checkpoint inside a transaction you already opened, use SAVEPOINT instead:

BEGIN TRANSACTION;
SAVEPOINT sp1;
-- do work you might want to undo
RELEASE sp1;
COMMIT;

Mistake 2: Rolling back when there’s nothing to roll back

ROLLBACK;

Running ROLLBACK without an open transaction fails with an error like “cannot rollback – no transaction is active”. This typically happens when application code assumes a transaction is open because an earlier statement failed silently, or a previous COMMIT already closed it. Always pair every BEGIN with exactly one COMMIT or ROLLBACK, and only issue the latter when you know a transaction is actually open:

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

Mistake 3: Leaving transactions open too long

A transaction that stays open while waiting on user input, a slow external API call, or an unrelated long-running query holds locks the entire time, blocking other writers and sometimes readers. This is one of the most common causes of application-wide slowdowns in production databases. Keep the time between BEGIN and COMMIT/ROLLBACK as short as possible — gather all the data you need first, then open the transaction only to make the writes.

Best Practices

  • Keep transactions short — do all your reads and computation outside the transaction, then open it only to perform the writes.
  • Always pair BEGIN with a COMMIT or ROLLBACK in every code path, including error-handling branches — an uncaught exception should still trigger a rollback.
  • Use SAVEPOINT when you need to undo part of a multi-step transaction without discarding work that already succeeded.
  • Never run DDL (like dropping a table) and unrelated business data changes in the same transaction unless you understand your database’s specific rules — some engines implicitly commit before DDL.
  • Wrap multi-row or multi-table writes in a transaction even if your database defaults to autocommit — don’t rely on single-statement atomicity for logically related changes.
  • Test your rollback path deliberately, not just your happy path — simulate the failure and confirm the database ends up unchanged.
  • In high-concurrency systems, watch for lock contention and deadlocks; keep the order in which you touch tables consistent across transactions to reduce deadlock risk.

Practice Exercises

  • Exercise 1: Create a products table with columns id, name, and stock. Insert one row with stock = 10. Write a transaction that decreases the stock by 3 and then commits. What is the final stock value?
  • Exercise 2: Using the same products table, open a transaction, decrease stock by 20 (even though only 10 are in stock), then roll back instead of committing. Confirm the stock is still the original value afterward.
  • Exercise 3: Write a transaction with two savepoints: apply an update, create SAVEPOINT a, apply a second update, create SAVEPOINT b, apply a third update, then roll back to SAVEPOINT a. Which of the three updates remain after you commit?

Summary

  • A transaction groups multiple SQL statements into one all-or-nothing unit of work.
  • BEGIN TRANSACTION opens a transaction; COMMIT makes its changes permanent; ROLLBACK undoes them entirely.
  • Transactions guarantee the ACID properties: atomicity, consistency, isolation, and durability.
  • SAVEPOINT and ROLLBACK TO let you undo part of a transaction without discarding everything.
  • Databases track in-progress changes in a log (journal or WAL) so they can be discarded on rollback or replayed after a crash.
  • Isolation levels control how much of an in-progress transaction other concurrent transactions can see; SQLite enforces strict isolation through file locking by default.
  • Keep transactions short, always pair BEGIN with a matching COMMIT/ROLLBACK, and test your rollback paths deliberately.