Transaction Control

Every query written in the previous topic was a single statement answering a single question. Work that changes data rarely fits in one statement. Lending a book requires two changes: opening a loan record and updating the book’s shelf status. If one happens and the other does not, the database describes a state that never actually existed — a loan that looks open while the book is still on the shelf, or the reverse.

The Relational Theory course introduced the ACID properties and defined atomicity as all-or-nothing. A transaction is what that definition becomes in practice: it turns several statements into a single unit the engine treats as indivisible.

Autocommit

Any statement run without an explicit transaction is its own transaction. This behavior is called autocommit: if the statement succeeds, its effect is permanent; if it fails, it is as though nothing happened.

rm -f k1.db
sqlite3 -box -header k1.db <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, on_shelf INT NOT NULL DEFAULT 1);
INSERT INTO book VALUES (1,'Lost Time',1),(2,'Silent House',1),(3,'Number Theory',1);
UPDATE book SET on_shelf = 0 WHERE id = 1;
SQL
sqlite3 -box -header k1.db 'SELECT id, title, on_shelf FROM book ORDER BY id;'
rm -f k1.db

┌────┬───────────────┬──────────┐
│ id │     title     │ on_shelf │
├────┼───────────────┼──────────┤
│ 1  │ Lost Time     │ 0        │
│ 2  │ Silent House  │ 1        │
│ 3  │ Number Theory │ 1        │
└────┴───────────────┴──────────┘

The first command created and updated the database, then ended; the second opened as a separate process and saw the change. Nothing here wrote a commit, because every statement committed on its own.

This mode suits isolated changes and is dangerous for changes that must travel together: if an error occurs between two statements, the first one is already permanent. Whether autocommit is on or off by default, and how it gets turned off, depends on the engine — some require writing BEGIN, others require flipping a setting. What never changes is that an explicit transaction must be closed with COMMIT or ROLLBACK.

Starting, Committing, and Rolling Back

An explicit transaction begins with BEGIN. Every change made after that point is visible only from inside that transaction; it is not permanent until COMMIT runs. ROLLBACK erases every change made since the transaction started:

rm -f k2.db
sqlite3 -box -header k2.db <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, on_shelf INT NOT NULL DEFAULT 1);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO book VALUES (1,'Lost Time',1),(2,'Silent House',1),(3,'Number Theory',1);

BEGIN;
INSERT INTO loan(book_id, member_id, pickup) VALUES (1, 4, '2024-03-11');
UPDATE book SET on_shelf = 0 WHERE id = 1;
SELECT 'in transaction' AS moment, (SELECT COUNT(*) FROM loan) AS loan_count,
       (SELECT on_shelf FROM book WHERE id = 1) AS book1_on_shelf;
ROLLBACK;

SELECT 'after rollback' AS moment, (SELECT COUNT(*) FROM loan) AS loan_count,
       (SELECT on_shelf FROM book WHERE id = 1) AS book1_on_shelf;
SQL
rm -f k2.db

┌────────────────┬────────────┬────────────────┐
│     moment     │ loan_count │ book1_on_shelf │
├────────────────┼────────────┼────────────────┤
│ in transaction │ 1          │ 0              │
└────────────────┴────────────┴────────────────┘
┌────────────────┬────────────┬────────────────┐
│     moment     │ loan_count │ book1_on_shelf │
├────────────────┼────────────┼────────────────┤
│ after rollback │ 0          │ 1              │
└────────────────┴────────────┴────────────────┘

Inside the transaction, the loan record existed and the book had left the shelf. After the rollback, both returned to their earlier state — not one at a time, but together. That is what atomicity means: a rollback leaves no partial result behind.

The SELECT inside the transaction sees its own changes even though they are not yet committed. This is a requirement of the transaction being consistent with itself; whether another connection can see these rows at the same time is a separate question, addressed in the isolation levels lesson.

Had COMMIT been written instead, both changes would have become permanent. Committing has a defined meaning for durability too: once COMMIT returns successfully, the changes survive even a power failure immediately afterward. How the engine guarantees this — the write-ahead log — is the subject of the Relational Database Administration course.

A Statement Error Does Not End the Transaction

A common assumption is that a statement failing inside a transaction automatically rolls the transaction back. That is not the standard behavior: the failing statement has no effect, the transaction stays open, and the decision belongs to the application.

The schema below carries a partial uniqueness index: a book cannot have more than one open loan at a time. A second attempt to lend it runs into that constraint:

rm -f k3.db
sqlite3 -box -header k3.db <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, on_shelf INT NOT NULL DEFAULT 1);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
CREATE UNIQUE INDEX loan_open ON loan(book_id) WHERE returned IS NULL;
INSERT INTO book VALUES (1,'Lost Time',0),(2,'Silent House',1);
INSERT INTO loan(book_id, member_id, pickup) VALUES (1, 4, '2024-03-11');

BEGIN;
INSERT INTO loan(book_id, member_id, pickup) VALUES (1, 7, '2024-03-12');
UPDATE book SET on_shelf = 0 WHERE id = 1;
SELECT 'in transaction after error' AS moment, COUNT(*) AS loan_count FROM loan;
ROLLBACK;
SELECT 'after rollback' AS moment, COUNT(*) AS loan_count FROM loan;
SQL
rm -f k3.db

Runtime error near line 8: UNIQUE constraint failed: loan.book_id (19)
┌────────────────────────────┬────────────┐
│           moment           │ loan_count │
├────────────────────────────┼────────────┤
│ in transaction after error │ 1          │
└────────────────────────────┴────────────┘
┌────────────────┬────────────┐
│     moment     │ loan_count │
├────────────────┼────────────┤
│ after rollback │ 1          │
└────────────────┴────────────┘

Three things are visible at once. First, the failing INSERT had no effect; the loan count stayed at 1. Second, the transaction did not close: the following statement kept running and the UPDATE was applied. Third, the rollback erased that UPDATE’s effect too.

The second point is a real source of bugs. Had the error message been ignored and COMMIT written, the book would have committed as off the shelf even though no loan had actually opened — exactly the inconsistency the design was meant to avoid. This is why application code checks the result of every statement and issues an explicit ROLLBACK when one fails.

The shape of the error message depends on the engine; the text here is a typical example naming the constraint and its kind. What does not change is that the violation is reported at the statement level and the transaction’s fate is left to the application. Some engines instead put the transaction into a locked state after an error, accepting only a rollback; this distinction needs to be known for the target engine.

When the Connection Drops

An uncommitted transaction is rolled back when the connection ends. This requires no explicit action from the application; it is a guarantee from the engine. The script below starts a transaction, inserts a row, and ends the process without writing COMMIT:

cat > interrupted.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync('interrupted.db');
db.exec(`
  CREATE TABLE IF NOT EXISTS loan(id INTEGER PRIMARY KEY, book_id INT,
                                  member_id INT, pickup TEXT);
`);
db.exec("BEGIN");
db.exec("INSERT INTO loan(book_id, member_id, pickup) VALUES (1, 4, '2024-03-11')");
console.log('row seen inside transaction:',
            db.prepare('SELECT COUNT(*) AS n FROM loan').get().n);
process.exit(0);
JS
rm -f interrupted.db
node interrupted.mjs
sqlite3 -box -header interrupted.db 'SELECT COUNT(*) AS reopened FROM loan;'
rm -f interrupted.db

row seen inside transaction: 1
┌──────────┐
│ reopened │
├──────────┤
│ 0        │
└──────────┘

The process saw the row inside its own transaction; the row was gone when the database file was reopened. The table definition did persist, because CREATE TABLE ran in autocommit mode before the transaction began.

This behavior shows that committing is a threshold: nothing is promised before COMMIT returns. On the application side, the matching discipline is that the code opening a transaction must close it on every exit path — error, early return, or interruption.

Setting the Transaction Boundary

How wide a transaction should be is a design decision under pressure from two directions.

Too narrow, and changes that must travel together end up in separate transactions, where an error in between leaves an inconsistency. The transaction’s boundary is the boundary of the business rule: in a “lend a book” operation, opening the record and updating the shelf status belong in the same transaction.

Too wide, and the transaction stays open for a long time; the resources it holds keep other connections waiting, and the cost of rolling it back grows. Holding an open transaction while waiting on user input is the best-known form of this mistake: the database can hold a lock for minutes while it waits for the user’s response.

How these two pressures get balanced will take concrete shape in the locking behavior lesson. For now, the rule is: a transaction spans the smallest set of statements the business rule requires, and once started, it does not wait on the outside world.

Summary

• Any statement run without an explicit transaction is its own transaction; this mode is called autocommit.
• In a transaction opened with BEGIN, changes are not permanent until COMMIT runs; ROLLBACK erases every change made during the transaction together.
• A statement error disables only that statement; the transaction stays open and the rollback decision belongs to the application — committing after an error makes a partial result permanent.
• An uncommitted transaction is rolled back when the connection ends.
• The transaction’s boundary is the business rule’s boundary; a transaction kept wider than necessary holds resources and keeps other connections waiting.

Next Step

Rollback in this lesson was a single “delete everything” button: the only option was to return to the start of the transaction. That is too blunt for batch work — if the third record out of a hundred books being loaded fails, discarding the first two is unnecessary. The next lesson defines intermediate stops inside a transaction, rolling back only past that stop, and how nested savepoints behave.