Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 33 ยท Beyond Basics

Concurrency, Locking and WAL

How SQLite handles many readers and one writer: lock states, rollback journal vs WAL, SQLITE_BUSY, BEGIN IMMEDIATE, checkpoints and multi-process pitfalls.

SQLite has a reputation for being "single user". That is out of date. A single SQLite database file can serve many readers at the same moment and handles thousands of small transactions per second. What it does not do is let two connections write at the same time. At any instant at most one connection is changing the file; the others wait their turn.

Understanding this one rule makes most "database is locked" errors easy to reason about. This lesson explains how SQLite arbitrates access (locks), what changes in WAL mode, what SQLITE_BUSY really means, and the mistakes that bite programs with several threads or processes. Two processes and a real file cannot run inside a web page, so the multi-connection scenarios are explained with code and tables; the pieces that a single connection can show (transaction types and atomic updates) run live.

The one rule

Many readers Any number at the same time.
One writer Only one connection writes at once.
Others wait Or fail with SQLITE_BUSY.
1 / 2

Lock states in the default mode

In the default rollback-journal mode a connection moves through five lock levels on the database file. You never request these directly; SQLite takes them as your statements need them.

Lock Held by What others can do
none idle connection anything
SHARED every connection that is reading others can read; a writer can start preparing
RESERVED the one connection that intends to write others can still read; no second writer
PENDING the writer, waiting for readers to leave no new readers; existing readers finish
EXCLUSIVE the writer, while it writes the file nobody else can read or write

A write goes SHARED then RESERVED (changes are built in memory and the journal), then PENDING and EXCLUSIVE only for the short moment of the commit. So even in the default mode, writers block readers only briefly; the longer a write transaction stays open, the longer other writers wait, but readers mostly carry on until commit.

The table below is a small model of those rules, written as data so you can ask questions of it:

SQL A lock compatibility table

Press Run (or Ctrl + Enter) to see the result here.

This is a model, not SQLite itself, but the answers match the rules above. A reader is only ever turned away in the default mode, during PENDING and EXCLUSIVE. In WAL mode the writer never excludes readers.

SQLITE_BUSY: what the error means

When a connection needs a lock that another connection holds, SQLite does not queue it forever. It returns SQLITE_BUSY, shown by most drivers as "database is locked". You can soften this in two ways:

  1. Set a busy timeout. PRAGMA busy_timeout = 5000; (or the driver's own option) makes SQLite retry for up to 5 seconds before giving up. Every application with more than one connection should have one (see PRAGMAs and Tuning).
  2. Keep write transactions short. Never wait for the network, a user, or a slow computation while a write transaction is open.

There is also a sneaky case where waiting cannot help. Suppose two connections each start an ordinary (deferred) transaction, both read, and both then try to write. Each holds a SHARED lock that blocks the other from reaching EXCLUSIVE, so neither can proceed. SQLite detects the deadlock and fails one of them at once with SQLITE_BUSY, ignoring the timeout. The cure is to declare your intent up front:

SQL
BEGIN IMMEDIATE;   -- take the write lock now; wait (up to busy_timeout) if somebody else has it
-- read, decide, write
COMMIT;

Transaction types

BEGIN can be followed by three words, covered in Transactions. In terms of locks:

Statement Lock taken When to use
BEGIN / BEGIN DEFERRED none until the first statement; a read takes SHARED, a write RESERVED transactions that only read
BEGIN IMMEDIATE RESERVED at once transactions that will probably write; avoids the deadlock above
BEGIN EXCLUSIVE EXCLUSIVE at once (in WAL mode it behaves like IMMEDIATE) rare: maintenance that must lock everyone out

A single connection cannot show the other side of a lock, but it can show that these statements are accepted and that a transaction cannot be nested:

SQL BEGIN IMMEDIATE and no nesting

Press Run (or Ctrl + Enter) to see the result here.

The first block updates the counter and prints visits | 1 before committing. The second block reads it in a deferred transaction. The last statement fails with cannot start a transaction within a transaction. For nesting you use savepoints (SAVEPOINT name), also covered in the Transactions lesson.

Write the change so it needs no lock dance

Many concurrency problems disappear if the check and the change happen in one statement, because a single statement is atomic. Compare the two approaches to selling the last item in stock:

SQL
-- risky: two connections could both read stock = 1, then both write
SELECT stock FROM items WHERE id = 7;
-- (application decides 1 > 0 ...)
UPDATE items SET stock = stock - 1 WHERE id = 7;

-- safe: the condition and the change are one atomic statement
UPDATE items SET stock = stock - 1 WHERE id = 7 AND stock > 0;
-- then check how many rows changed: 1 = sold, 0 = sold out
SQL Check and change in one statement

Press Run (or Ctrl + Enter) to see the result here.

changes() reports 1 for the first sale and 0 for the second, and the final stock is 0. No matter how many connections run the same statement at the same moment, only one can win, and no application-side locking is needed. UPSERT and RETURNING shows other single-statement patterns.

WAL mode

Write-ahead logging changes where new data goes. Instead of copying old pages into a rollback journal and overwriting the database file, a writer appends changed pages to a second file, the -wal file. Readers look at the database file plus the part of the WAL that was committed when their read began. The result:

  • Readers do not block the writer, and the writer does not block readers. Each reader sees a consistent snapshot from the moment it started.
  • There is still only one writer. A second writer waits (or gets SQLITE_BUSY).
  • Commits are usually faster, since they are one sequential append and, with synchronous = NORMAL, need fewer flushes.

You turn it on once; it is stored in the file:

SQL
PRAGMA journal_mode = WAL;

A third file, -shm, is a shared-memory index of the WAL that all connections on the machine use to find the latest data. That is why WAL needs every connection to be on the same host and cannot work over a network file system.

Checkpoints

The WAL cannot grow forever. A checkpoint copies committed pages from the WAL back into the main database file so the WAL can be reused. SQLite checkpoints automatically when the WAL reaches about 1,000 pages (PRAGMA wal_autocheckpoint), and when the last connection closes it checkpoints and removes the -wal and -shm files. You can request one yourself:

SQL
PRAGMA wal_checkpoint(PASSIVE);    -- copy what you can without waiting for anybody
PRAGMA wal_checkpoint(TRUNCATE);   -- copy everything, wait for readers, then shrink the WAL to zero bytes

The returned row has three numbers: whether the checkpoint was blocked, the number of frames in the WAL, and how many were copied. A checkpoint cannot recycle pages that an active reader still needs. A reader that stays open for hours, for example a connection with an unfinished query, makes the WAL grow without bound. The cure is the same as for locks in general: keep read transactions short and finish or reset every statement.

What WAL costs

  • The database becomes three files; when you copy it by hand you must copy them together, or better, use the backup methods in Backup, VACUUM and Maintenance.
  • It needs shared memory and a local disk (no NFS or SMB shares).
  • Reads can be slightly slower when the WAL is large, as lookups check it first.
  • Switching modes needs exclusive access: other connections must be closed.

For most applications with concurrent readers, WAL is the better choice, and it is the usual first step when you hit "database is locked".

Multi-process and multi-thread pitfalls

  • Network file systems. SQLite relies on the operating system's file locks, which are unreliable on NFS and many SMB setups. Two machines sharing one database file over a network can corrupt it. Use a client-server database for that.
  • Forgotten statements. A SELECT that has not been fully read (or whose cursor was never closed) keeps a SHARED lock. This is the most common cause of mysterious lock errors: close cursors, use with blocks, or finalize statements.
  • Long write transactions. Do your slow work before BEGIN IMMEDIATE and keep the transaction to the writes.
  • Many writer connections. More connections do not make writes faster, since writes are serialized anyway. A common design is one dedicated writer connection (or a queue in front of it) and a small pool of readers.
  • Sharing a connection across threads or processes. Prefer one connection per thread. A connection must never be carried across a fork(); open a new one in the child.
  • Deleting or copying live files. Removing a -wal file or copying the main file while a writer is active can lose committed data or produce a corrupt copy.
  • SQLITE_LOCKED is not SQLITE_BUSY. SQLITE_BUSY means another connection holds a lock. SQLITE_LOCKED is an internal conflict inside one process, for example when you drop a table that a statement of the same connection is still reading.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How many connections can write to one SQLite database at the same instant?

    SQLite allows many concurrent readers but serializes writers, in both rollback-journal and WAL mode.
  2. 2. What is the main benefit of WAL mode?

    In WAL mode a writer appends to a log while readers keep using their snapshot, so they do not block each other. There is still one writer, and it needs a local file system.
  3. 3. Why should a transaction that will write usually start with BEGIN IMMEDIATE?

    With deferred transactions, two connections can each hold a read lock while waiting to write, and one must fail at once. IMMEDIATE takes the write intent first, so the other waits for the busy timeout.
  4. 4. What does PRAGMA busy_timeout = 5000 change when a lock is held by another connection?

    It is a retry window for acquiring locks, not a limit on how long a query may run.
  5. 5. Why can a long-running read transaction make the WAL file grow?

    Pages in use by a reader's snapshot must stay in the WAL, so the log cannot be reset until readers finish.

Summary

  • SQLite allows many readers but only one writer at a time; others wait for busy_timeout or fail with SQLITE_BUSY ("database is locked").
  • In the default mode the writer escalates SHARED, RESERVED, PENDING, EXCLUSIVE and blocks readers only around commit; use BEGIN IMMEDIATE for transactions that write.
  • Doing check-and-change in a single statement (UPDATE ... WHERE stock > 0, then changes()) avoids many races.
  • WAL mode appends changes to a log: readers and the writer stop blocking each other, commits are faster, and checkpoints move data back into the main file. It needs a local file system and long-lived readers can bloat the log.
  • Avoid network file systems, forgotten cursors, long write transactions, many writer connections, forking with open connections, and copying live database files.

Next: SQLite from Python, JavaScript and Other Languages shows how to use all of this from your programs.