Lock Management in Multithreaded SQLite Applications

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.

Yesterday, we looked at how SQLite solves in-process locking problems by introducing internal lock tracking structures like unixinodeinfo and unixFile.

That design ensured correctness when the same database file is opened multiple times within a single process.

Today’s reading pushes that discussion further by exposing what happens when threads enter the picture, and why SQLite had to make very specific design choices on Linux.

Multithreading and the Linux Locking Model

At first glance, multithreading should not fundamentally change file locking semantics.

After all, threads belong to the same process, and POSIX file locks are defined at the process level.

However, this assumption breaks down on Linux systems that used LinuxThreads.

Under LinuxThreads:

• Locks obtained by one thread cannot be overridden by sibling threads
• Only the owner thread is allowed to manipulate its locks
• This behavior is not POSIX-compliant

This created a serious mismatch between SQLite’s assumption that locks are process-wide and LinuxThreads’ thread-scoped lock ownership

As a result, two threads in the same process could each believe they held a compatible lock yet be unable to upgrade or release locks correctly

This inconsistency made correct lock management impossible.

SQLite’s Response: Drop LinuxThreads

Starting with SQLite 3.7.0, the SQLite team officially stopped supporting LinuxThreads.

Instead, SQLite relies on NPTL (Native POSIX Thread Library).

The File Closing Problem in Multithreaded Environments

Even with NPTL, multithreading introduces another subtle issue, file descriptor closing.

On Linux, when a file descriptor is closed all locks on that inode held by the process are released, it does not matter which thread acquired them and it does not matter which file descriptor was used

This behavior is correct from the kernel’s point of view, locks are process-owned, but it is dangerous for SQLite.

The Dangerous Scenario

Imagine:

• Thread T1 holds a SHARED lock via one unixFile
• Thread T2 holds a RESERVED lock via another unixFile
• Thread T1 closes its file descriptor

Linux will:

• release all locks on that inode
• including the RESERVED lock held by T2

From SQLite’s perspective, this would be catastrophic, locks disappear while transactions are still active.

Lazy File Closing: SQLite’s Only Viable Option

SQLite solves this using a technique called lazy file closing.

Instead of immediately calling close(fd):

• SQLite checks whether other unixFile objects on the same inode still hold locks
• If so, the close is deferred
• The file descriptor is placed into a deferred list

The file descriptor is only truly closed when all locks on that inode have been released and the last thread finishes with the file

This design:

• prevents accidental lock loss
• preserves correctness
• at the cost of temporarily holding extra file descriptors

The SQLite developers accepted this tradeoff because there is no safer alternative on Linux.

Note: Deferred file descriptors can later be reused if the file is reopened, reducing resource waste.

SQLite Lock APIs: The Pager–OS Boundary

So far, we’ve talked about what SQLite tracks. Now we look at how lock transitions actually occur.

SQLite exposes two internal APIs for lock management:

• sqlite3OsLock
• sqlite3OsUnlock

On Unix systems, these are implemented in os_unix.c and serve as the only gateway between SQLite’s logical lock system and native fcntl locks.

Today’s reading focuses on sqlite3OsLock.

The sqlite3OsLock API: Escalating Locks Safely

The function signature is:

int sqlite3OsLock(sqlite3_file *id, int locktype);

Where:

• id represents a database connection (sqlite3_file)
• locktype is the requested SQLite lock

Important constraints:

• Clients cannot request PENDING directly
• Locks can only be strengthened in this order:

NOLOCK → SHARED → RESERVED → PENDING → EXCLUSIVE

Downgrades are handled separately via sqlite3OsUnlock.

Compatibility Checks Against Peer Connections

If another connection in the same process holds a stronger lock:

• SQLite checks compatibility
• No native locks are touched yet

If the process-wide lock is:

• PENDING or stronger → incompatible → SQLITE_BUSY
• SHARED or RESERVED → limited upgrades may be allowed

This is where nShared bookkeeping becomes essential.

Internal-Only Lock Transitions

Some lock changes never touch the OS.

Example:

• Process already holds a SHARED lock
• Another connection requests SHARED

SQLite simply:

• increments nShared
• updates connection state
• avoids calling fcntl

This is how SQLite safely supports multiple readers in one process.

Lock Escalation: When Native Locks Are Needed

True lock escalation happens only when:

• the requesting connection already holds the strongest process-wide lock
• and needs to move to a higher level

Examples:

• SHARED → RESERVED
• RESERVED → EXCLUSIVE

In these cases:

• SQLite may temporarily acquire a PENDING lock
• waits for shared locks to drain
• attempts to obtain a native write lock
• fails fast if incompatible locks remain

If escalation fails, SQLite returns SQLITE_BUSY without blocking.

Why This Algorithm Matters

This layered algorithm guarantees that:

• Native locks are never accidentally overridden
• Thread-level operations remain consistent
• File descriptor closing does not break transactions
• Deadlocks remain impossible
• Lock state is always deterministic

SQLite achieves serializable isolation not by complexity, but by careful ordering, strict invariants, and aggressive refusal to block.

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