Explicit Locking, Deadlocks, and Linux Lock Primitives 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 post, we explored how SQLite manages locks internally, the different lock states, compatibility rules, and how the pager escalates locks to guarantee serializable isolation.

All of that machinery operates implicitly: the application does not usually decide when or how locks are acquired.

Today’s learning builds on that foundation by answering three important questions:

1. Can an application influence when SQLite acquires locks?
2. How does SQLite avoid deadlocks despite strict locking?
3. How are SQLite’s logical locks mapped onto Linux file locks?

Explicit Locking in SQLite

SQLite is fundamentally an implicitly locked system. The pager module decides:

• when a database file must be locked
• which lock mode is required
• when locks should be upgraded or released

However, SQLite does expose limited control to applications through variants of the BEGIN TRANSACTION command.

These variants allow the application to hint SQLite about its locking intent before any database access occurs.

Consider the following transaction:

BEGIN EXCLUSIVE TRANSACTION;

INSERT INTO table1 VALUES (100);
INSERT INTO table2 VALUES (20, 100);
UPDATE table1 SET x = x + 1 WHERE y > 10;
INSERT INTO table3 VALUES (1, 2, 3);

COMMIT TRANSACTION;

Here, the application explicitly requests an exclusive transaction at the very beginning.

BEGIN EXCLUSIVE TRANSACTION

When BEGIN EXCLUSIVE executes successfully, SQLite immediately acquires an exclusive lock on all database files involved in the connection including the main database and any attached databases.

This has strong implications:

• No other thread or process can read or write those database files
• No new transactions can begin on those files
• The transaction is guaranteed to never block on locks later

In effect, the application is saying:

“I am going to need full control. Give it to me now or fail immediately.”

BEGIN IMMEDIATE TRANSACTION

BEGIN IMMEDIATE is slightly less aggressive.

Instead of an exclusive lock, SQLite immediately acquires a reserved lock on all relevant database files.

This guarantees:

• No other transaction can write
• No other transaction can successfully execute BEGIN IMMEDIATE or BEGIN EXCLUSIVE
• Existing and new read-transactions may continue unhindered

At this point, the transaction becomes a semi write-transaction.

It has secured the right to write in the future, but it does not block readers.

This is often a sweet spot:

• Writers are serialized early
• Readers are not penalized
• Lock contention is reduced later during commit

BEGIN DEFERRED TRANSACTION (Default)

If an application executes:

BEGIN TRANSACTION;

SQLite defaults to deferred mode.

In this mode:

• No locks are acquired at BEGIN
• A shared lock is obtained only when a database file is first read
• A reserved lock is obtained only when a write is attempted
• An exclusive lock is acquired only when pages are flushed to disk

Because lock acquisition is postponed, another transaction may:

• write to the database
• commit successfully
• block the deferred transaction later

As a result, deferred transactions may fail mid-execution with SQLITE_BUSY.

This is why SQLite documentation warns developers:

BEGIN does not guarantee write access unless IMMEDIATE or EXCLUSIVE is used.

Deferred mode optimizes for concurrency but shifts complexity to error handling.

Commit Failures and SQLITE_BUSY

In non-exclusive transactions (DEFERRED and IMMEDIATE), writes are not fully isolated from concurrent readers.

During COMMIT, SQLite may need to upgrade to an exclusive lock.

If shared locks are still held by other transactions:

• the commit fails with SQLITE_BUSY
• the transaction remains active
• the application may retry the commit later

This retry-based design is intentional and ties directly into SQLite’s deadlock prevention strategy.

Deadlock and Starvation

Locking solves isolation, but it introduces a new danger: deadlock.

This circular wait is a classic deadlock.

How SQLite Avoids Deadlocks

SQLite prevents deadlocks by design.

• All lock requests are non-blocking
• If a lock cannot be acquired immediately, SQLite returns SQLITE_BUSY
• SQLite retries only a finite number of times
• Retry count is configurable via busy_timeout
• Default retry count is zero

Starvation: A Theoretical Risk

While deadlock is impossible, starvation is theoretically possible.

A transaction may:

• repeatedly fail to acquire a lock
• continually receive SQLITE_BUSY
• never make progress due to constant contention

SQLite delegates starvation handling to the application:

• retry with backoff
• serialize access externally
• abort and retry later

In practice, starvation is rare unless the workload is highly write-contended.

Linux Lock Primitives Used by SQLite

SQLite’s logical locks are implemented using native Linux file locks.

On Linux, SQLite uses POSIX advisory locks by default. These locks are:

• enforced by the kernel
• advisory (cooperating processes must honor them)
• associated with file regions, not file contents

Linux supports only two lock modes:

• Read lock (shared)
• Write lock (exclusive)

To avoid confusion:

• SQLite SHARED → Linux read lock
• SQLite EXCLUSIVE → Linux write lock

A write lock excludes all other locks, both read and write.

Ephemeral Nature of File Locks

File locks are not persisted.

• They are kernel memory structures
• They are automatically released if a process crashes or exits
• They do not survive system failures

This is why SQLite must rely on journals and WAL files for crash recovery locks alone cannot protect durability.

Where This Leaves Us

At this point, the locking story is complete:

• SQLite offers explicit hints (DEFERRED, IMMEDIATE, EXCLUSIVE)
• Deadlocks are prevented by non-blocking lock acquisition
• Starvation is possible but manageable
• Logical locks are mapped onto Linux POSIX locks
• Durability is enforced separately through journaling

In the next continuation, we will see:

• Translation from SQLite locks to native file locks
• Engineering issues with native locks

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