Hello, I'm Maneshwar. I'm working on FreeDevTools online currently building **one place for all dev tools, cheat codes, and TLDRs* — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.*

From Journals to Transactions: How SQLite Executes Work Safely

Yesterday, we completed the storage and journaling story, rollback journals, statement journals, and master journals and saw how SQLite preserves correctness across crashes, statement failures, and multi-db commits.

Today, we move one layer up.

Journals explain how SQLite restores correctness after something goes wrong.
Transactions explain how SQLite prevents things from going wrong in the first place even when multiple statements, users, or processes operate concurrently.

A db system exists to apply operations to stored data, protect that data from concurrent access, and recover to consistent states after failures.

SQLite does all of this using transactions, file locking, and page level journaling, while keeping the system simple enough to live inside a single library.

Transactions as the Execution Abstraction

SQLite executes all db work inside transactions.

Transactions are the abstraction that lets SQLite guarantee the ACID properties:

• Atomicity — all or nothing
• Consistency — constraints preserved
• Isolation — controlled concurrency
• Durability — results survive crashes

SQLite supports flat transactions only.
There is no true nested transaction model (SAVEPOINTs simulate nesting, but internally they are handled differently).

System (Implicit) Transactions

SQLite operates in autocommit mode by default.

That means:

• Applications do not need to explicitly begin or commit transactions.
• SQLite automatically wraps each SQL statement in an appropriate transaction.
• These are called system, implicit, or auto transactions.

Applications never see them.
They only see statement results.

How SQLite Classifies Statements

SQLite distinguishes statements by intent:

SELECT statements

• Executed inside a read-transaction
• No db pages are modified

Non-SELECT statements (INSERT / UPDATE / DELETE / DDL)

• Start in a read-transaction
• Automatically upgrade to a write-transaction when modification begins

At the end of statement execution:

• The transaction is committed if successful
• Or aborted if an error occurs

All of this happens transparently.

Atomic vs Incremental Execution

This is where SQLite’s execution model gets interesting.

Non-SELECT Statements: Fully Atomic

Non-SELECT statements are executed atomically:

• SQLite takes an internal mutex at the start
• Executes the entire statement without interruption
• Releases the mutex only after completion

No other statement can interleave execution in the middle.

This guarantees:

• statement-level atomicity
• clean rollback using the statement journal if needed

SELECT Statements: Incremental Execution

SELECT statements behave differently.

They are not executed atomically end-to-end.

Instead SQLite takes a mutex to initialize execution and produces rows incrementally then it releases the mutex between rows

This allows:

• multiple SELECT statements to interleave
• one SELECT to pause while another runs
• non-SELECT statements to execute during SELECT pauses

At any moment, several SELECT statements may be:

• partially executed
• waiting on locks
• or yielding control between rows

This design enables concurrency without multi threaded chaos.

The Role of Mutexes (The Critical Section Guardian)

A mutex (mutual exclusion lock) protects critical internal structures inside SQLite.

Think of it as:

“Only one execution path may manipulate this internal state at a time.”

SQLite uses mutexes to:

• protect parser state
• guard VDBE execution structures
• serialize write operations
• ensure internal consistency

Key Properties of SQLite Mutex Usage

• Non-SELECT statements hold the mutex for their entire execution
• SELECT statements acquire and release the mutex repeatedly
• Mutexes are not db locks
• They protect in process engine state, not files

This distinction matters:

• Mutexes control execution
• File locks control concurrency across processes

Isolation Without Illusions

Although SELECT statements interleave internally, SQLite guarantees:

• A read-transaction never observes partial writes
• A write-transaction cannot modify a table currently being read
• Readers are insulated from writers using page-level locks

This is why:

• read- and write-transactions cannot manipulate the same table simultaneously
• SQLite achieves isolation without complex MVCC machinery

The illusion of serial execution is preserved.

My experiments and hands-on executions related to SQLite will live here: lovestaco/sqlite

References:

SQLite Database System: Design and Implementation. N.p.: Sibsankar Haldar, (n.d.).

👉 Check out: FreeDevTools

Any feedback or contributors are welcome!

It’s online, open-source, and ready for anyone to use.

⭐ Star it on GitHub: freedevtools