Lock Management in SQLite

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.*

In the previous posts, we examined how SQLite structures transactions from system transactions to user transactions, savepoints, and statement subtransactions.

All of that machinery exists for one central reason: isolation.

When multiple transactions operate concurrently, the database must ensure that each one behaves as if it were executing alone.

Achieving this illusion is the responsibility of lock management.

Isolation, Concurrency Control, and Serializability

Any system that allows more than one concurrent transaction must prevent transactions from interfering with one another in unsafe ways.

In the database literature, several closely related terms are used to describe this problem:

• Concurrency control — the practical problem to be solved
• Serializability — the theoretical property we aim to achieve
• Locking — one of the mechanisms used to enforce it

This relationship was famously articulated by Gray and Reuter:
Concurrency control is the problem, serializability is the theory, and locking is the mechanism.

Isolation is the “I” in ACID, and it comes in multiple strengths read committed, repeatable read, cursor stability, and serializable. Among these, serializable isolation is the strictest, ensuring that the outcome of concurrent transactions is equivalent to some serial execution order.

SQLite implements serializable isolation, but it does so with a deliberately simple and conservative design.

SQLite’s Philosophy: Simple Locks, Strong Guarantees

SQLite does not use shared memory to coordinate concurrency across processes because it must work reliably across platforms and deployment models, it relies exclusively on native operating system file locking primitives.

SQLite follows strict two-phase locking (2PL):

• Locks are acquired during execution
• Locks are released only when the transaction completes

This guarantees serializable execution.

SQLite Lock Types

From the perspective of a single transaction, a database file can be in one of five SQLite defined locking states.

These are logical locks managed by SQLite, not raw OS locks.

1. NO LOCK

This is the default state when a transaction begins.

• The transaction holds no locks
• It cannot read or write the database
• Other transactions may read or write as permitted

2. SHARED LOCK

A shared lock allows reading from the database file.

• Any number of transactions may hold shared locks simultaneously
• This enables multiple concurrent read transactions
• While shared locks exist, no writes are allowed

This is the standard lock state for read only transactions.

3. RESERVED LOCK

A reserved lock signals intent to write in the future.

• Only one reserved lock may exist
• It may coexist with multiple shared locks
• Other transactions may continue reading
• No other transaction may acquire another reserved, pending, or exclusive lock

At this stage, the transaction is a semi write transaction, it plans to write but hasn’t yet modified the database file.

4. PENDING LOCK

A pending lock is an internal transitional lock.

• Indicates that a transaction wants to write immediately
• Existing shared locks may remain
• New shared or reserved locks are blocked
• At most one pending lock may exist

The pending lock exists solely to drain readers before acquiring an exclusive lock.

5. EXCLUSIVE LOCK

The exclusive lock grants full control.

• Allows both reading and writing
• Only one exclusive lock may exist
• No other lock of any type may coexist

Once acquired, the transaction becomes a full write transaction, and changes can be written back to the database file.

Lock Compatibility Matrix

SQLite enforces strict compatibility rules between locks. Conceptually, this is represented by the following matrix:

This matrix ensures:

• Readers do not block readers
• Writers are serialized
• No writer can sneak in once an exclusive lock is pending

Lock Acquisition Protocol

Lock acquisition in SQLite is managed by the pager module, not by SQL execution directly.

The pager is responsible for ensuring safe access to database pages.

Initial State

Every transaction starts in the NO LOCK state.

Reading the Database

Before reading the first page:

• The pager acquires a SHARED lock
• The transaction becomes a read-transaction
• It may read any number of pages

Once reading completes, the lock is released and the transaction returns to NO LOCK.

Preparing to Write

Before modifying any page:

• The pager acquires a RESERVED lock
• The transaction becomes a semi write-transaction
• Modifications are made only in cache
• No database file changes are visible yet

This allows reads to continue uninterrupted while preparing writes.

Writing to Disk

Before flushing modified pages:

1. The pager requests an EXCLUSIVE lock
2. The lock manager may first grant a PENDING lock
3. Once all shared locks are released, the lock is upgraded to EXCLUSIVE
4. Pages are written back to disk

At this point, the transaction is a full write transaction, and changes become visible.

SQLite Lock Manager Internals

On Unix-like systems, SQLite implements locking using two core functions:

• sqlite3OsLock
• sqlite3OsUnlock

These functions are defined in os_unix.c and provide the abstraction between SQLite’s logical lock states and the underlying OS file locks.

The pager uses:

• sqlite3OsLock to acquire or upgrade locks
• sqlite3OsUnlock to downgrade or release locks

When a database connection is closed, all locks are released automatically, ensuring no orphaned locks remain.

Why SQLite’s Locking Works So Well

Despite its simplicity, SQLite’s locking model provides:

• True serializable isolation
• Predictable performance
• Minimal deadlock complexity
• Robust behavior across platforms

By trading fine-grained locking for clarity and correctness, SQLite achieves reliability that scales surprisingly well for its intended use cases.

In the next continuation, we’ll go deeper into:

• Explicit locking
• Deadlock and starvation
• Linux file locking primitives (fcntl, POSIX locks, and their subtleties)

That’s where the OS and SQLite finally meet head on.

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