Chapter 08 · Transactions, Atomicity, Journaling, and Savepoints
DEFERRED, IMMEDIATE, and EXCLUSIVE Transactions
Choose BEGIN DEFERRED, IMMEDIATE, or EXCLUSIVE by understanding when SQLite attempts to acquire write capability and how journal mode changes reader behavior.
Learning outcomes
All three BEGIN modes define transaction-start behavior, not different kinds of data consistency. The useful question is: when does this connection ask SQLite for write capability, and what happens if another connection already owns or needs conflicting access?
Explain DEFERRED as the default “wait until first access” mode.
Explain IMMEDIATE as starting a write transaction at BEGIN time.
Explain how EXCLUSIVE differs from IMMEDIATE in rollback-journal mode and why they are equivalent in WAL mode.
Observe a read transaction attempting to upgrade to a writer.
Reproduce SQLITE_BUSY with two independent connections on a disposable file.
Choose a mode from workload intent rather than folklore.
One database file can have many readers, but only one write transaction
SQLite allows multiple connections to hold read transactions concurrently. A write transaction is exclusive in a different sense: only one connection can be the writer at a time. The BEGIN mode controls how early your connection tries to become that writer.
| Mode | At BEGIN | First SELECT | First write | Rollback-mode reader effect |
|---|---|---|---|---|
BEGIN / BEGIN DEFERRED | Sets up an explicit transaction boundary but defers actual database transaction until access. | Starts a read transaction. | Starts or upgrades to a write transaction if possible. | Readers coexist until locking needs change. |
BEGIN IMMEDIATE | Attempts to start a write transaction immediately. | Runs inside that write transaction. | Already has writer status. | Other readers can generally continue while the writer has not reached an exclusive phase. |
BEGIN EXCLUSIVE | Starts write transaction immediately. | Runs inside it. | Already a writer. | In rollback modes, prevents other connections from reading while active. |
DEFERRED: delay the decision until the workload reveals itself
BEGIN DEFERRED is the default. The BEGIN statement itself does not immediately access the database. If the first real statement is a SELECT, the connection starts as a reader. If a later write appears, SQLite tries to upgrade that read transaction to a write transaction.
BEGIN DEFERRED;SELECT qty FROM part_stockWHERE site_code='PLANT-A' AND sku='FILTER-01';-- The transaction began as a read transaction.UPDATE part_stockSET qty = qty - 1WHERE site_code='PLANT-A' AND sku='FILTER-01';-- This must upgrade to a write transaction; it can fail with SQLITE_BUSY.COMMIT;This is useful when an operation may turn out to be read-only. The tradeoff is that contention can appear at the later write, after your application has already performed reads and perhaps computation.
IMMEDIATE: move writer contention to the beginning
BEGIN IMMEDIATE tries to start a write transaction immediately. If another connection already owns the write transaction, BEGIN itself can return SQLITE_BUSY. That front-loads an important failure mode before the application has done the rest of its transactional work.
BEGIN IMMEDIATE;-- If BEGIN succeeds, this connection is the current writer.SELECT qty FROM part_stockWHERE site_code='PLANT-A' AND sku='FILTER-01';UPDATE part_stock SET qty = qty - 1WHERE site_code='PLANT-A' AND sku='FILTER-01';COMMIT;The keyword describes transaction/locking timing. The pager still controls when dirty pages and journal content reach files. Lesson 3 follows that storage path.
EXCLUSIVE: journal mode changes what the word means for readers
BEGIN EXCLUSIVE also starts a write transaction immediately. In WAL mode, SQLite documents EXCLUSIVE and IMMEDIATE as equivalent. In rollback-journal modes, EXCLUSIVE additionally prevents other database connections from reading while the transaction is active.
| Journal mode | IMMEDIATE | EXCLUSIVE |
|---|---|---|
| DELETE/TRUNCATE/PERSIST rollback modes | Starts writer early; existing/other readers can coexist during portions of the transaction. | Starts writer early and excludes other readers for the transaction. |
| WAL | Starts writer early. | Same transaction behavior as IMMEDIATE. |
This chapter uses rollback mode to make the distinction observable, then only previews WAL. Chapter 9 develops the WAL concurrency model in depth.
Controlled two-connection experiment
Use two terminals, two CLI processes, or a small test program against one disposable file. Set busy waiting to zero so the lock conflict appears immediately instead of waiting.
PRAGMA journal_mode=DELETE;PRAGMA busy_timeout=0;BEGIN IMMEDIATE;UPDATE part_stock SET qty=qtyWHERE site_code='PLANT-A' AND sku='FILTER-01';-- Leave this transaction open temporarily.PRAGMA busy_timeout=0;BEGIN IMMEDIATE;-- expected: database is locked / SQLITE_BUSY while A holds the write transaction.Now roll back A, retry B, and observe that it succeeds. The lesson is not “retry forever”; the lesson is that contention is a normal, observable state requiring a bounded policy. Chapter 9 covers busy handlers/timeouts and retry design.
Upgrade experiment: DEFERRED can discover contention late
Reset both connections. Have A start DEFERRED and read. Have B start IMMEDIATE and become the writer. Then ask A to UPDATE. A now needs to upgrade its read transaction, but B already owns the writer position, so A can receive SQLITE_BUSY.
BEGIN DEFERRED;SELECT qty FROM part_stockWHERE site_code='PLANT-A' AND sku='FILTER-01';-- Keep A open as a reader.BEGIN IMMEDIATE;UPDATE part_stockSET qty=qtyWHERE site_code='PLANT-B' AND sku='FILTER-01';-- Keep B open as writer.UPDATE part_stockSET qty=qty-1WHERE site_code='PLANT-A' AND sku='FILTER-01';-- expected under this contention: SQLITE_BUSYROLLBACK;Observe EXCLUSIVE in rollback mode—then compare WAL only if supported
In rollback mode, hold BEGIN EXCLUSIVE on connection A and attempt a SELECT from B with zero timeout. B should be blocked. If your build accepts PRAGMA journal_mode=WAL, repeat after switching a disposable database to WAL: EXCLUSIVE and IMMEDIATE are then equivalent and WAL readers use a different concurrency mechanism.
WAL is part of normal SQLite builds but can be omitted at compile time. If PRAGMA journal_mode=WAL returns another mode, record the result and skip the optional WAL comparison; Chapter 9 will make capability checks explicit.
Selection guide: choose from intent and contention
| Workload shape | Reasonable starting choice | Why |
|---|---|---|
| Mostly read; write is conditional or rare | DEFERRED | Avoid claiming writer status unless needed. |
| You know the unit of work must write and prefer early contention detection | IMMEDIATE | Writer conflict appears at transaction start instead of after useful work. |
| Rollback-mode maintenance where intentionally excluding readers is acceptable | EXCLUSIVE, cautiously | It blocks readers and should be justified operationally. |
| WAL workload | DEFERRED or IMMEDIATE based on intent | EXCLUSIVE offers no extra transaction behavior over IMMEDIATE in WAL. |
Lock-timing checkpoint
Predict where contention appears.
- What is the default BEGIN mode?
- If DEFERRED begins with SELECT, what kind of transaction starts?
- Where can IMMEDIATE report SQLITE_BUSY?
- When does EXCLUSIVE differ from IMMEDIATE?
- Why is a two-connection test more informative than memorizing lock names?
Review the answers
DEFERRED is the default. A first SELECT starts a read transaction. BEGIN IMMEDIATE can report SQLITE_BUSY at BEGIN if another writer is active. EXCLUSIVE differs from IMMEDIATE in non-WAL journaling modes by excluding readers; they are equivalent in WAL mode. A two-connection experiment exposes the timing and application-visible failure instead of reducing concurrency to vocabulary.
Production judgment and bridge
Choose a BEGIN mode to control when contention is discovered, not as a generic performance switch. Keep the transaction short regardless of mode. Lesson 3 goes below SQL and follows rollback-journal atomic commit through the pager so you can distinguish lock timing, atomicity, and durability.