How to Avoid SQLite Locking Errors

SQLite in Production: The Single-Writer Reality (Part 5 of 5)

Part 5 of 5 in the series SQLite in Production.

The most dangerous SQLite production bug does not show up in development. It is a transaction that starts as a read, upgrades to a write, and then deadlocks against another connection doing the same thing. A developer on the SQLite User Forum described it in March 2026 as a race condition so rare that it never surfaced during development, yet it can silently drop writes in production and is nearly impossible to reproduce on demand. Two connections each run BEGIN, read some rows, and then both try to write. Both hold a read lock. Neither can upgrade, because the other will not release its read lock first. The result is database is locked, even when you set busy_timeout, because retrying does not help a deadlock. The fix is one word: IMMEDIATE.

Key Takeaways

• SQLite allows only one writer at a time, even in WAL mode; every write transaction holds a database-level lock for its full duration.
• The read-to-write transaction upgrade is the single most common source of database is locked errors in production; start any write transaction with BEGIN IMMEDIATE.
• A single dedicated writer connection plus a separate reader pool can be roughly 20x faster than one shared pool, because it removes SQLite-level lock contention entirely.
• Backups need planning: copy the main file, -wal, and -shm together, or use VACUUM INTO, and never rely on a bare cp of the .db file.
• Frequent full VACUUM is costly, locking the database for a whole rebuild; prefer auto_vacuum = INCREMENTAL with scheduled incremental_vacuum.
• Monitoring lock contention, WAL file size, and SQLITE_BUSY rates catches problems before they become outages.

The Single-Writer Reality: Locking Fundamentals

Every locking pitfall traces back to one architectural fact: SQLite is a single-writer database. The official file-locking documentation describes five lock states, from UNLOCKED through SHARED, RESERVED, PENDING, and EXCLUSIVE. Any number of readers can hold a SHARED lock at once, but only one connection can hold the EXCLUSIVE lock required to write. In the default rollback journal mode, a writer even blocks new readers during the commit phase. Enabling Write-Ahead Logging (WAL) mode lets readers proceed while the single writer appends to the -wal file, but it does not change the single-writer rule. Two writers still serialize; one commits, the other waits or fails.

Connection Pooling and Long Transactions

The default busy timeout is zero. If no busy handler is set, a write that cannot acquire a lock returns SQLITE_BUSY immediately, as documented in the SQLite busy timeout API. Setting PRAGMA busy_timeout = 5000 makes SQLite retry internally for up to five seconds before giving up, which is the single most important lock-related configuration. But it only helps when the lock is genuinely held by a fast, short transaction. It does nothing for a deadlock, which is why the deferred-transaction trap below is so dangerous.

-- The prod PRAGMA stack, applied on every connection
PRAGMA journal_mode = WAL; -- concurrent readers during writes
PRAGMA synchronous = NORMAL; -- safe in WAL mode, fewer fsync calls
PRAGMA busy_timeout = 5000; -- retry locked write for 5 seconds
PRAGMA cache_size = -64000; -- 64 MB page cache
PRAGMA foreign_keys = ON; -- off by default; set per connection
PRAGMA temp_store = MEMORY; -- keep temp tables in RAM

This stack matches the recommended defaults from the oneuptime production setup guide, and it is the baseline every production deployment should start from. As covered in Part 4 of this series, WAL plus synchronous=NORMAL is what turns a slow, fsync-heavy default into tens of thousands of writes per second by cutting the number of disk synchronization calls per commit. But configuration alone does not solve contention; transaction shape is what matters next.

The Deferred Transaction Deadlock

SQLite transactions come in three modes: DEFERRED, IMMEDIATE, and EXCLUSIVE. A plain BEGIN is DEFERRED: it acquires no lock up front and starts as a read transaction. The problem, as the March 2026 forum thread and the ten-thousand-meters deep dive both explain, is that a read transaction cannot always be upgraded to a write transaction. If another connection has already modified the database, the upgrade is impossible, and the write statement fails with SQLITE_BUSY immediately. busy_timeout does not help, because waiting makes no sense: the read snapshot is already invalid.

The scenario is a genuine deadlock. Two connections each hold a read lock. Each tries to write, and each needs the other to release its read lock first. Neither can progress, so SQLite returns database is locked and the app must retry from scratch. An anonymous forum participant called the logic behind transaction upgrades “highly surprising,” and a developer who had used SQLite for years admitted he had never known about the footgun until it hit his production code.

import sqlite3

def update_balance(conn, account_id, delta):
 # The fix: acquire write lock up front.
 # A plain BEGIN (DEFERRED) can deadlock when two connections
 # read first, then both try to write.
 conn.execute("BEGIN IMMEDIATE")
 try:
 conn.execute(
 "UPDATE accounts SET balance = balance + ? WHERE id = ?",
 (delta, account_id),
 )
 conn.commit()
 except sqlite3.OperationalError as exc:
 conn.rollback()
 if "database is locked" in str(exc):
 # Escalate to app-level retry queue with backoff.
 raise TimeoutError("write lock unavailable") from exc
 raise

The rule is simple: if a transaction may write at any point, start it with BEGIN IMMEDIATE. This acquires a reserved lock immediately, so the transaction cannot collide with another pending upgrade. The pecar production gotchas make the same point and add a second rule: only start a transaction when you actually need to write. Starting a transaction for a pure read and holding it open is how you accidentally block other writers. Keep write transactions as short as possible, and never hold one open across a network call or long computation.

BEGIN IMMEDIATE has a cost: it blocks other writers for the whole transaction, so a long write transaction becomes a serialization point. The answer is not to avoid IMMEDIATE but to keep the transaction short and batch writes inside it. The pecar guide notes that a Django + SQLite combination on a low-cost Hetzner instance sustained thousands of writes per second, with the framework, not SQLite, as the bottleneck. Short transactions are the lever that keeps that number high.

Connection Pooling and Long Transactions

Write contention is not only about how you open transactions; it is also about how you pool connections. A common instinct is to scale the connection pool to handle more concurrency, but with SQLite this backfires. In a February 2026 benchmark, Evan Schwartz, in PSA: Your SQLite Connection Pool Might Be Ruining Your Write Performance, measured the effect directly. Using one shared pool of many connections, he found the write workload took roughly 1.93 seconds at about 2,586 rows per second with a p99 latency of around 182 seconds. Using a single writer connection with all writes queued at the app level, the same workload finished in approximately 83 milliseconds at roughly 60,061 rows per second with a p99 of about 82 milliseconds. That is roughly a 20x improvement, achieved by removing SQLite-level lock contention entirely.

The mechanism is worth understanding. In an async app, a connection that holds a write lock and then awaits yields control to the runtime. The runtime schedules another task that also needs the lock, and that task contends with the parked writer. Under enough load, new writers keep arriving and the original writer never gets scheduled again. The result is lock starvation, visible in logs as “slow statement” warnings where the elapsed time sits right at the busy_timeout value. The fix, which Schwartz confirmed after retracting his initial proposals, is to mirror SQLite’s own architecture at the app level: one dedicated writer connection with writes queued, and a separate read-only pool for concurrent reads.

# Single writer connection + separate reader pool (conceptual)
writer = connect("app.db", read_only=False) # all writes queue here
reader_pool = [connect("app.db", read_only=True) for _ in range(8)]

def write_event(conn, payload):
 conn.execute("BEGIN IMMEDIATE") # single writer serializes in app
 conn.execute("INSERT INTO events (payload) VALUES (?)", (payload,))
 conn.commit()
# Note: prod use adds a bounded app-level queue so writes wait
# in memory instead of hammering the write lock.

Long write transactions compound the problem. A write transaction holds an EXCLUSIVE lock for its entire duration, from the first write statement until COMMIT. The ten-thousand-meters analysis is blunt that long write transactions are “killers of SQLite concurrency,” because a transaction that issues an insert, then does network work, then issues more SQL, blocks every other writer the whole time. The ten-thousand-meters benchmark also found that with 100 or more concurrent writers, throughput drops sharply or errors appear, and that wrapping writes in an app-level mutex keeps throughput stable at roughly 57,000 operations per second even at 256 threads. Serializing at the app level, rather than letting SQLite’s file lock coordinate, is a reliable pattern.

Backup Strategies That Avoid Corruption

Backing up SQLite is where many teams corrupt their data without realizing it. The naive approach is to copy the .db file with cp while the database is live. In rollback journal mode this can copy a half-written file. In WAL mode it is worse: committed data may live in the -wal file, not the main file, so copying only .db produces a stale or inconsistent snapshot. The pecar guide warns that copying the file directly “can corrupt the backup,” and the Oldmoe backup-strategies writeup details why: in WAL mode you must copy the main file, -wal, and -shm together, ideally inside a deferred transaction so the WAL is not deleted mid-copy.

The recommended online backup method is VACUUM INTO, which creates a consistent, self-contained copy without blocking writers. It does not interfere with other writes and it produces a compact backup that is fast to restore. The oneuptime guide and the pecar guide both recommend it for production. The SQLite backup API, exposed as the .backup command in the sqlite3 shell, is another solid option: it copies pages incrementally, handles concurrent writes by re-reading modified pages, and produces a consistent snapshot. Both beat a bare cp.

For remote durability, Oldmoe notes that writing backups to replicated object storage protects against whole-machine failure, and that copy-on-write filesystems such as ZFS or Btrfs make local backups nearly free by deduplicating unchanged pages. The trade-off is complexity: each method adds a tool to run and monitor. A pragmatic production setup pairs VACUUM INTO or .backup to a local file with a scheduled sync of that file to object storage, which gives both fast local restore and remote durability.

Schema Design and the Cost of VACUUM

Schema design shapes both concurrency and disk usage, and the biggest hidden cost is VACUUM. SQLite’s default auto_vacuum mode is NONE: when you delete rows, freed pages go to an internal freelist and the file never shrinks. Henrique Faria documented this disk-space trap with a striking example: a monitoring database had grown to 6.9 GB while holding only 37 MB of actual data, because cache entries and completed jobs had been churned out over time and the file retained its historical peak size.

Running a full VACUUM to reclaim that space is expensive. It rebuilds the entire database into a temporary file and then swaps it in, which locks the database for the duration and requires enough free disk for a full copy. On Faria’s 6.9 GB file, a one-time VACUUM took about 45 seconds and blocked writes the whole time. That is not something you want running on a schedule in production, especially during a deploy.

The better design is auto_vacuum = INCREMENTAL, which tracks freed pages in a pointer map and shrinks the file only when you explicitly run PRAGMA incremental_vacuum(N). This avoids the per-commit overhead of FULL mode and gives you control over when space is reclaimed. Faria’s pattern is to run a scheduled job that executes PRAGMA incremental_vacuum(1000) (roughly 4 MB per run at the default 4 KB page size) on an hourly timer. The operation is bounded, does not lock the database for 45 seconds, and keeps the file close to its real data size.

-- Check how much space is reclaimable
SELECT page_count * page_size AS total_bytes,
 freelist_count * page_size AS free_bytes
FROM pragma_page_count, pragma_page_size, pragma_freelist_count;
-- If free_bytes is large relative to total_bytes, reclaim on schedule:
PRAGMA auto_vacuum = INCREMENTAL; -- must be set before tables exist,
 -- or run one VACUUM to restructure
PRAGMA incremental_vacuum(1000); -- reclaim up to 1000 pages (~4 MB)

There is a catch: auto_vacuum must be set before the first table is created, or you must run one full VACUUM to restructure the file into the pointer-map format that FULL and INCREMENTAL use. Plan for this in your schema bootstrap, not after the database is large. Schema design also affects concurrency directly: the pecar guide recommends splitting churn-heavy tables across separate database files when one writer is not enough, so each file can write in parallel. That is a valid pattern, but the guide is honest that before going down that road you should consider whether a client-server database would make the app code simpler.

Monitoring for Early Detection

Monitoring is what turns a latent problem into an early warning. The most important signals are SQLITE_BUSY error rates, WAL file size, and lock-state behavior. The oneuptime guide and SQLite hardening resources agree on which metrics matter. If SQLITE_BUSY appears in app logs more than occasionally, you are either hitting the single-writer ceiling or holding transactions too long. A -wal file that grows far larger than the main database signals checkpoint starvation, usually from a long-running reader pinning an old snapshot.

Checkpoint starvation deserves special attention because it is silent. In WAL mode, a checkpoint can only merge frames older than the oldest active reader snapshot. If a long-lived reader holds a snapshot open while writers keep appending, the WAL cannot be truncated and grows without bound until it exhausts disk and raises SQLITE_FULL. The fix is to manage checkpointing explicitly in a background thread or process, using PRAGMA wal_checkpoint(PASSIVE) on a schedule, rather than relying on the default auto-checkpoint heuristic. The Micrologics WAL optimization guide recommends this for high-write servers.

-- Health checks to run on schedule
PRAGMA journal_mode; -- should return wal
PRAGMA wal_checkpoint(PASSIVE); -- merge WAL frames, don't block
PRAGMA freelist_count; -- pages on freelist (reclaimable)

-- A trace callback in Python surfaces slow statements at runtime
conn.set_trace_callback(lambda sql: log_slow(sql))

App-level logging of slow statements is a practical early-warning system. A trace callback that records execution time and query text reveals the few queries driving p99 latency. Watch for statements whose elapsed time sits near your busy_timeout, which indicates the query spent most of its time waiting for a lock, not executing. That is the signature of contention, not a slow query, and it points back to the transaction and pooling fixes above. The Micrologics guide frames the final decision threshold cleanly: if your system is read-heavy, fits within a few hundred gigabytes, and demands ultra-low latency, SQLite is a highly performant and operationally simple choice; if it needs complex distributed writes across regions or a dataset in terabytes, a client-server database is the correct tool.

None of these pitfalls are exotic, and all of them are avoidable. Start every write transaction with BEGIN IMMEDIATE, keep transactions short, serialize writes through one dedicated writer connection, back up with VACUUM INTO or the backup API rather than a bare file copy, use auto_vacuum = INCREMENTAL with scheduled reclamation, and monitor SQLITE_BUSY rates and WAL growth. A SQLite database that follows these practices handles substantial production traffic on a single server, which is exactly the niche this series has shown it fills best. For the full decision framework across all five parts, see the series index.

Key Takeaways

• SQLite allows only one writer at a time even in WAL mode; every write transaction holds a database-level lock for its full duration.
• The read-to-write transaction upgrade is the most common source of database is locked errors; start any write transaction with BEGIN IMMEDIATE.
• A single dedicated writer connection plus a separate reader pool can be roughly 20x faster than one shared pool, per the February 2026 benchmark.
• Backups need planning: copy the main file, -wal, and -shm together, or use VACUUM INTO and the backup API; never rely on a bare cp of the .db file.
• Frequent full VACUUM is costly and locks the database; prefer auto_vacuum = INCREMENTAL with scheduled incremental_vacuum.
• Monitoring SQLITE_BUSY rates, WAL file size, and slow-statement traces catches contention before it becomes an outage.

Related Reading

More in-depth coverage from this blog on closely related topics:

• SQLite vs PostgreSQL for Production
• Best Practices for SQLite to PostgreSQL

Sources and References

Sources cited while researching and writing this article:

• SQLite User Forum: Help avoiding 'database is locked' errors in …
• File Locking And Concurrency In SQLite Version 3
• SQLite busy timeout API
• SQLite concurrent writes and "database is locked" errors
• Gotchas with SQLite in Production | Anže’s Blog
• Oldmoe backup-strategies writeup
• SQLite Backup API
• SQLite in Production: The Disk Space Trap , Henrique Cardoso de Faria