Chapter 08 · Transactions, Atomicity, Journaling, and Savepoints
Autocommit, Implicit Transactions, and Explicit BEGIN / COMMIT / ROLLBACK
Build a correct SQLite transaction mental model by contrasting autocommit, implicit transactions, and explicit atomic units of work with a failure-prone inventory transfer.
Learning outcomes
The prerequisite SQL course introduced transactions as units of
work. SQLite adds an important implementation fact:
there is no non-transactional read or write path. If you do not write BEGIN, SQLite still starts a
transaction automatically around database access. This lesson
turns that fact into a practical workflow for protecting a
business invariant.
Explain autocommit as connection state rather than “transactions are off.”
Distinguish automatically started implicit transactions from explicit BEGIN/COMMIT units.
Build a multi-statement operation that is all-or-nothing.
Observe what a normal constraint failure does inside an explicit transaction.
Recover predictably with ROLLBACK instead of guessing transaction state.
Protect a stock-transfer invariant in the FieldNotes domain.
The misleading mental model: “I did not write BEGIN, so there was no transaction”
SQLite automatically starts a transaction for commands that access the database when no transaction is already active. In the common one-statement case, that implicit transaction finishes when the statement finishes. This behavior is often called autocommit mode: after an implicit transaction completes successfully, SQLite is again ready to commit the next automatically started transaction.
It means the connection is not currently held inside an explicit transaction boundary. A single INSERT can still be atomic even when you never typed BEGIN.
| Situation | What SQLite does | Risk to remember |
|---|---|---|
One UPDATE, no BEGIN |
Starts an implicit write transaction and commits it when the statement finishes. | Fine for a business operation that truly is one statement. |
| Three related writes, no BEGIN | Each finished statement can become its own committed transaction. | A failure after statement 1 can leave a half-finished business operation. |
BEGIN ... several statements ...
COMMIT
|
Keeps one explicit transaction open across the statements. | Your code must decide how to handle every error path. |
| Connection closes with an open transaction | Uncommitted transaction is rolled back. | Closing is not a substitute for deliberate error handling. |
Build a disposable inventory invariant
FieldNotes technicians carry spare parts between sites. For one SKU, the invariant is simple: a transfer should move quantity from one location to another and record one request ID. The total stock must not change merely because the transfer was attempted.
DROP TABLE IF EXISTS transfer_event;DROP TABLE IF EXISTS part_stock;CREATE TABLE part_stock( site_code TEXT NOT NULL, sku TEXT NOT NULL, qty INTEGER NOT NULL CHECK(qty >= 0), PRIMARY KEY(site_code, sku)) WITHOUT ROWID;CREATE TABLE transfer_event( request_id TEXT PRIMARY KEY, sku TEXT NOT NULL, from_site TEXT NOT NULL, to_site TEXT NOT NULL, qty INTEGER NOT NULL CHECK(qty > 0));INSERT INTO part_stock VALUES('PLANT-A','FILTER-01',10),('PLANT-B','FILTER-01',3);-- Seed a request ID so a later duplicate will fail.INSERT INTO transfer_event VALUES('TX-100','FILTER-01','PLANT-A','PLANT-B',1);SELECT SUM(qty) AS total_filters FROM part_stock;-- expected: 13Unsafe version: related statements commit independently
Suppose the application decrements the source first, then writes the transfer event, then increments the destination. With no explicit transaction, the first UPDATE can commit before the duplicate request ID is discovered.
UPDATE part_stockSET qty = qty - 4WHERE site_code='PLANT-A' AND sku='FILTER-01';-- This fails: TX-100 already exists.INSERT INTO transfer_event(request_id,sku,from_site,to_site,qty)VALUES('TX-100','FILTER-01','PLANT-A','PLANT-B',4);-- An application that stops on the error never reaches this statement.UPDATE part_stockSET qty = qty + 4WHERE site_code='PLANT-B' AND sku='FILTER-01';After the first UPDATE commits, PLANT-A has 6. If execution stops at the failed INSERT, PLANT-B remains 3, so the total has fallen from 13 to 9. SQLite correctly made each individual statement atomic; the application chose the wrong business transaction boundary.
Atomic version: BEGIN, verify, COMMIT—or ROLLBACK
Reset the lab, then run the operation inside one explicit transaction. The duplicate INSERT still fails, but the source decrement remains only an uncommitted change until the application decides what to do.
BEGIN;UPDATE part_stockSET qty = qty - 4WHERE site_code='PLANT-A' AND sku='FILTER-01';-- Expected constraint error: duplicate request_id.INSERT INTO transfer_event(request_id,sku,from_site,to_site,qty)VALUES('TX-100','FILTER-01','PLANT-A','PLANT-B',4);-- Inspecting now, on the same connection, can still show the earlier UPDATE.SELECT site_code, qtyFROM part_stockWHERE sku='FILTER-01'ORDER BY site_code;-- Normalize the failure path explicitly.ROLLBACK;SELECT site_code, qtyFROM part_stockWHERE sku='FILTER-01'ORDER BY site_code;-- expected after rollback: PLANT-A=10, PLANT-B=3A normal uniqueness violation uses the default ABORT conflict behavior: the failing statement is backed out, but earlier statements in the explicit transaction are not automatically erased. That is why “an error happened” is not enough information to infer the final transaction state.
What can a statement failure do to the surrounding transaction?
Constraint errors usually have well-defined conflict-algorithm behavior, which Chapter 5 covered. Lower-level failures such as disk-full, I/O, interruption, or out-of-memory are different: current SQLite documentation says SQLite attempts to undo the failing statement and keep the transaction, but in some circumstances must roll back the whole transaction. Applications should inspect the returned result and normalize the failure path deliberately.
| Failure class | Typical lesson-level interpretation | Application habit |
|---|---|---|
| UNIQUE/CHECK/NOT NULL with default ABORT | Failing statement is undone; explicit transaction normally remains active. | Do not accidentally COMMIT earlier work after catching the error. |
ON CONFLICT ROLLBACK |
Conflict can roll back the transaction. | Know the schema/statement conflict policy. |
SQLITE_FULL, SQLITE_IOERR,
SQLITE_INTERRUPT, SQLITE_NOMEM
|
SQLite may preserve the transaction or may cancel it, depending on where the failure occurred. | Inspect the result/driver transaction state and issue a defensive rollback when appropriate. |
| Process crash before COMMIT | Open transaction cannot be treated as committed. | Rely on SQLite recovery, then verify application intent after restart. |
Successful transfer: verify the invariant before COMMIT
Use a fresh request ID. The transfer is not complete merely because each statement returned success; verify the expected target count and invariant while still inside the transaction.
BEGIN;UPDATE part_stockSET qty = qty - 4WHERE site_code='PLANT-A' AND sku='FILTER-01' AND qty >= 4;SELECT changes() AS source_rows; -- expected: 1INSERT INTO transfer_event(request_id,sku,from_site,to_site,qty)VALUES('TX-101','FILTER-01','PLANT-A','PLANT-B',4);UPDATE part_stockSET qty = qty + 4WHERE site_code='PLANT-B' AND sku='FILTER-01';SELECT changes() AS destination_rows; -- expected: 1SELECT SUM(qty) AS total_filters FROM part_stock; -- expected: 13COMMIT;
If source_rows is 0, perhaps the stock was already
consumed or the SKU/site predicate was wrong. A row count is a
diagnostic signal, not permission to continue blindly. In
application code, treat unexpected counts as a reason to roll
back.
Implicit transaction completion depends on statement completion
At the C API level, an automatically started transaction is committed when the last active statement finishes; prepared statements finish reliably when reset/finalized, and an open incremental BLOB handle also counts as unfinished work. Higher-level drivers wrap these details, but this explains why a connection can remain “busy with a statement” longer than a simple SQL transcript suggests.
Python, Node.js, Java, .NET, ORMs, and mobile frameworks may add their own transaction APIs or legacy defaults. Learn SQLite’s engine semantics first, then verify how your specific driver maps begin/commit/rollback and autocommit state.
Reproducible lab: prove the invariant, not just the syntax
Run three cases on a disposable database: successful transfer, duplicate request failure followed by explicit rollback, and insufficient-stock attempt where the guarded UPDATE changes zero rows. Record the two site quantities, total quantity, and event count after each case.
Transaction checkpoint
Answer from engine state, not from what you hoped the code did.
- When you omit BEGIN, does SQLite perform writes outside transactions?
- Why can three individually atomic statements still produce a non-atomic business operation?
- After a normal UNIQUE failure inside BEGIN, is it safe to assume the entire transaction vanished?
- What should an application do when an expected one-row UPDATE reports zero rows?
- What invariant did the transfer lab protect?
Review the answers
SQLite automatically starts transactions around database access. Separate autocommitted statements have separate commit boundaries, so a later failure cannot undo an earlier committed statement. A normal ABORT-style constraint failure usually cancels only that statement, so the caller must choose rollback or another recovery path. An unexpected row count should be treated as a failed precondition. The lab protected conservation of total FILTER-01 stock and one logical event per request ID.
Production judgment and bridge
Define the business invariant first, then place the smallest set of database changes that must succeed or fail together inside one explicit transaction. Avoid leaving decisions to accidental autocommit boundaries. Lesson 2 adds the next dimension: when SQLite attempts to acquire write capability and how that timing changes contention behavior.