Readers, Writers, Locks, and Why SQLite Has One Writer at a Time

Learning outcomes

Chapter 8 gave one connection a correct transaction model. Concurrency begins when another connection opens the same database file while the first one is still reading or writing. SQLite must preserve isolation and atomicity across those independent actors, and it does so through the pager, journal/WAL machinery, and operating-system file locks. This lesson starts in the default rollback-journal model because its lock transitions make the single-writer rule easiest to see.

One file, two actors: what exactly is concurrent?

Two Python connections in one process, two browser threads, two command-line shells, and two unrelated programs can all reach the same SQLite file. They are not the same operational situation, even though the pager ultimately has to coordinate compatible file access.

A common debugging mistake is to collapse all four into “SQLite is locked.” Name the layer first. A database can be perfectly healthy while an application violates its own driver threading rules, or while two valid processes simply contend for the one writer.

The pager is the concurrency coordinator

SQLite’s pager is the layer that treats the database as fixed-size pages and coordinates caching, transactions, journal recovery, and file locking. In rollback-journal mode, developers usually need only a high-level picture of four lock states. These are database-file lock states—not row locks.

Why “many readers, one writer” is a real engine rule

Many readers are compatible because they can all observe a stable committed database state. Two independent writers are harder: both could attempt to modify the same pages, journal state, schema, or freelist. SQLite serializes write transactions for a database file rather than exposing row-level write locks. In rollback mode, a writer can begin while readers still exist, but it must eventually reach a commit phase compatible with those readers finishing.

Connection A                         Connection B
------------                         ------------
BEGIN; SELECT ...
SHARED lock  ----------------------> reader active

                                    BEGIN IMMEDIATE;
                                    RESERVED / writer intent
                                    UPDATE ... (uncommitted)

A still reading  ------------------> writer may not finish exclusive commit yet
END A read                           |
                                    v
                                    commit can complete

Rule: readers may overlap portions of a write transaction,
      but there is still only one writer for the database file.

Rollback mode is not “the whole database is always exclusively locked”

The phrase “SQLite locks the whole database” is too crude to be useful. The lock is file-oriented, but rollback-mode version 3 deliberately delays the most exclusive phase so readers can coexist with a writer for part of the write transaction. The important application-level rule is narrower: only one connection can own the write transaction for a database file at a time, and commit may need readers to clear.

Controlled two-shell experiment

Use a disposable directory and one database file. The two shell windows must open the same resolved path. Set zero wait time so the conflict is visible immediately.

sqlite3 concurrency-lab.dbPRAGMA journal_mode=DELETE;CREATE TABLE counter(id INTEGER PRIMARY KEY, value INTEGER NOT NULL);INSERT INTO counter(id,value) VALUES(1,0);.timeout 0

Leave shell A open and start an explicit writer:

BEGIN IMMEDIATE;UPDATE counter SET value=value+1 WHERE id=1;SELECT value FROM counter WHERE id=1;-- A sees 1 inside its uncommitted transaction.-- Do not COMMIT yet.

Open shell B on the same file:

sqlite3 concurrency-lab.db.timeout 0SELECT value FROM counter WHERE id=1;-- expected in rollback mode while A is uncommitted: 0BEGIN IMMEDIATE;-- expected: database is locked / SQLITE_BUSY

Now execute COMMIT; in A and retry BEGIN IMMEDIATE; in B. B should now acquire the writer position. The database was not corrupt and no “unlock command” was missing; B encountered ordinary contention.

A second rollback-mode timeline: a reader can delay commit

To expose the reader/writer relationship, let A hold an explicit read transaction while B writes. B can obtain writer intent and modify its private transactional state, but commit may need to wait/fail while A keeps a conflicting read lock.

BEGIN;SELECT value FROM counter WHERE id=1;-- Keep this transaction open.

.timeout 0BEGIN IMMEDIATE;UPDATE counter SET value=value+1 WHERE id=1;COMMIT;-- With A still holding the read transaction, COMMIT can report SQLITE_BUSY.

End A with COMMIT; or ROLLBACK;, then let B retry its commit if the transaction is still active. This is why an interactive shell left sitting after BEGIN can be a real concurrency participant.

Network filesystems are not “just another path”

SQLite relies on VFS/operating-system locking behaving correctly. The official locking documentation warns that network filesystem locking has historically been unreliable on some NFS and Windows-network configurations. WAL has an even stricter same-host shared-memory requirement that Lesson 3 will cover. Do not diagnose a shared-network deployment as if it were two local processes until you have verified the filesystem semantics SQLite requires.

Diagnose before tuning

When an application reports “database is locked,” capture state before adding a giant timeout or changing journal mode.

Reproducible lab and cleanup

Repeat the two-shell writer conflict three times: first with a zero timeout, then after making the first transaction shorter, and finally after moving any artificial sleep/user interaction outside the transaction. Record who held the transaction and for how long. Do not leave a shell sitting inside BEGIN after the lab.

-- In both shells, ensure no explicit transaction remains.ROLLBACK;  -- harmless only if a transaction is active; otherwise expect an error.quit-- Delete concurrency-lab.db only after both processes have closed it.

Production judgment and bridge

The one-writer rule is not a defect to “turn off.” It is part of SQLite’s concurrency architecture. Your job is to keep write transactions small, identify contention precisely, choose the appropriate journal mode, and decide whether the workload still fits a single-file embedded database. Lesson 2 turns the observed writer collision into an explicit busy-wait and retry policy.

Authoritative references

• SQLite — File Locking And Concurrency In SQLite Version 3.
• SQLite — Transaction.
• SQLite — Result and Error Codes.
• SQLite — How To Corrupt An SQLite Database File.