Transactions and Concurrency

In the SQL for Beginners series every statement you have run took effect on its own: the moment chapter 7 finished an UPDATE, that row was changed for good, with no way to take it back. Chapter 21 of this 24-chapter course introduces transactions — a group of statements that succeeds or fails as one — plus the vocabulary you need for what happens when more than one connection works on the same database.

ACID, in shop terms

Every serious database promises four things, and it is worth translating each one into our running example before touching the syntax:

• Atomicity — all or nothing. An orders row and its order_items rows land together, or neither does. There is no state where the order exists but its lines do not.
• Consistency — the rules from chapter 8 hold everywhere. A status outside the CHECK list, a NULL where NOT NULL was promised, or a dangling foreign key from chapter 9 aborts the whole transaction rather than storing something illegal.
• Isolation — concurrent transactions do not see each other’s half-finished work. While one connection is halfway through its updates, another never observes the intermediate mess.
• Durability — once COMMIT returns, the rows survive a crash or a power cut. SQLite has already flushed the change to disk by the time you see the prompt again.

SQLite delivers all four with a single database file plus a rollback journal beside it: the journal records the original image of every page before it is overwritten, and file locks decide who may proceed. No server process, no configuration — which is also why the concurrency rules in the last section of this chapter are simpler than a client/server database’s.

BEGIN, COMMIT and ROLLBACK

Without an explicit transaction, SQLite runs in autocommit mode: each statement is wrapped in its own invisible transaction. BEGIN opens one by hand, and everything after it is provisional until you choose:

BEGIN;
UPDATE customers SET city = 'Pune' WHERE id = 1;
SELECT first_name, city FROM customers WHERE id = 1;
ROLLBACK;
SELECT first_name, city FROM customers WHERE id = 1;

Expected output:

Aarav|Pune
Aarav|Mumbai

The first line proves the change is visible inside the transaction; the second proves ROLLBACK undid it completely, leaving the sample data untouched. Swap ROLLBACK for COMMIT and the change is durable instead:

BEGIN;
UPDATE customers SET city = 'Pune' WHERE id = 1;
COMMIT;
SELECT first_name, city FROM customers WHERE id = 1;
-- put the sample data back so later chapters stay consistent
UPDATE customers SET city = 'Mumbai' WHERE id = 1;

Expected output: Aarav|Pune — the row really was written. The final restore statement runs in autocommit mode, so the six-customer sample set is exactly as it was.

From application code the same three verbs come from the driver instead of the prompt: PDO offers $pdo->beginTransaction(), $pdo->commit() and $pdo->rollBack(), while Laravel wraps it all in DB::transaction(function () { /* ... */ });, which commits when the closure returns and rolls back when it throws. The syntax changes in chapter 23; the discipline does not.

The point of a transaction is a group of statements. Here an order and its line items are written together, and a ROLLBACK in between leaves both counts exactly where they started:

BEGIN;
INSERT INTO orders (customer_id, order_date, status)
    VALUES (1, '2026-01-12', 'paid');
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (7, 1, 1, 499);
ROLLBACK;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM order_items;

Expected output:

6
8

Delete the ROLLBACK; and you get seven orders and nine items; keep it and you get six and eight. Nothing in between is possible — that is atomicity, and it is the reason every multi-statement write in application code is wrapped in exactly this way.

Savepoints: undo one part

Sometimes only one branch of a transaction should be undone. A savepoint is a bookmark you can roll back to without ending the transaction or discarding everything before it:

BEGIN;
UPDATE customers SET city = 'Pune' WHERE id = 1;
SAVEPOINT sp1;
UPDATE customers SET city = 'Surat' WHERE id = 2;
SELECT id, city FROM customers WHERE id IN (1, 2) ORDER BY id;
ROLLBACK TO sp1;
SELECT id, city FROM customers WHERE id IN (1, 2) ORDER BY id;
ROLLBACK;

Expected output:

1|Pune
2|Surat
1|Pune
2|Delhi

Between the two SELECTs only the second update was undone, so Pune survives the savepoint while Surat is replaced by the original Delhi. The closing ROLLBACK takes back what happened before the savepoint too, which is why the sample data is untouched afterwards.

Three things that can go wrong

Before choosing any engine’s settings, name the failures you are protecting against. Each one is a story about two connections — call them Alice and Bob:

• Dirty read — Alice inserts an order and has not committed yet; Bob reads it and starts printing a packing slip. Alice changes her mind and rolls back. Bob has acted on a row that never existed.
• Non-repeatable read — Bob reads Priya’s city twice inside one report. Between the two reads Alice commits an UPDATE, so the report says Delhi on page one and Pune on page two — the same row gave two answers.
• Phantom read — Bob counts the orders and gets 6; Alice inserts a seventh and commits; Bob counts again and gets 7. No row changed — a new one simply appeared, like a phantom, between his two reads.

Each level of the SQL standard closes some of these doors, and every engine maps onto them differently.

Isolation levels

The standard defines four levels, trading correctness for throughput. The lower the level, the more concurrency — and the more anomalies you accept:

You ask for a level differently in each engine. MySQL defaults to REPEATABLE READ and changes it with SET TRANSACTION ISOLATION LEVEL READ COMMITTED; (issued before BEGIN; add SESSION to make it stick for the connection). PostgreSQL defaults to READ COMMITTED and takes SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; inside the transaction. SQLite has no such statement at all — its default already prevents dirty reads, because a reader only ever sees committed data, and the only way to see someone else’s uncommitted rows is the opt-in PRAGMA read_uncommitted, which works only in shared-cache mode and is not something you want in production.

SQLite’s single-writer model

The concurrency rule to internalise: one writer at a time, for the whole database file — not per table, not per row. Readers do not block readers, but a second writer gets SQLITE_BUSY until the first one commits or rolls back. You can watch it happen from one terminal:

# a connection that holds the write lock open for two seconds...
{
  echo "BEGIN IMMEDIATE; UPDATE customers SET city = 'Pune' WHERE id = 1;"
  echo "SELECT 'lock held';"
  sleep 2
  echo "ROLLBACK;"
} | sqlite3 shop.db &
sleep 0.5
# ...while a second connection tries to write
sqlite3 shop.db "UPDATE customers SET city = 'Surat' WHERE id = 2;"  # blocked

Expected output: (the second command fails while the first still holds the lock)

lock held
Error: stepping, database is locked (5)

Two details in that script matter. BEGIN IMMEDIATE asks for the write lock up front instead of taking it halfway through — the polite form for writers, because two connections that both start with BEGIN, read, and then try to write can otherwise deadlock and both fail. And the error was instant because the default wait was zero; the standard remedy is a timeout, which the pragma reports back when you set it:

sqlite3 shop.db "PRAGMA busy_timeout = 5000;"

Expected output: 5000 — the number of milliseconds a blocked statement will now wait before giving up. For heavier read traffic, PRAGMA journal_mode = WAL; switches to write-ahead logging, where one writer and many readers work side by side; the single-writer rule stays exactly the same. Three settings are worth knowing by name:

• delete — the default: the rollback journal sits beside the database during a transaction and is removed on commit. Simple, portable and crash-safe.
• wal — writes go to a separate log while readers keep using the main file, so reads overlap the writer. Unlike most pragmas this one is remembered: the database stays in WAL mode between connections.
• memory — the journal lives in RAM. Fast, and useless against a power cut — fine for test fixtures, wrong for shop data.

Common mistakes

• Forgetting the COMMIT — every statement sits uncommitted, so the connection holds the write lock and the data disappears when it closes. Commit on success, roll back on failure.
• Multi-statement writes with no transaction — an order inserted without its line items is exactly the inconsistency ACID exists to prevent. Wrap every pair of dependent statements in BEGIN…COMMIT.
• Holding a transaction open across slow work — computing, rendering or calling an API inside a transaction pins the lock for seconds. Read, compute, then write in one short transaction.
• Assuming another engine’s defaults travel with you — PostgreSQL’s READ COMMITTED, MySQL’s REPEATABLE READ and SQLite’s single writer are three different contracts. Read the isolation level when you switch databases.
• Ignoring SQLITE_BUSY — “database is locked” under load is a configuration problem, not bad luck. Set busy_timeout, write with BEGIN IMMEDIATE, and keep transactions short.

Practice checklist

1. Run the rollback demo and confirm the second SELECT still says Mumbai.
2. Repeat the two-insert demo without the ROLLBACK, check the counts become 7 and 9, then restore the sample data.
3. Replay the savepoint script and predict each line of output before you run it.
4. Run the lock script and capture the database is locked error, then add PRAGMA busy_timeout = 5000; to the second connection and compare.
5. Move one customer’s city inside a transaction together with a second update, roll the whole thing back, and prove both rows kept their original values.

What's next?

Transactions keep a database honest while it is running; design keeps it honest before the first row arrives. Next you shape the schema itself: Database Design and Normalization walks a deliberately messy table through 1NF, 2NF and 3NF.