What this usually means
SQLite uses file-level locking for serialized writes. The error means a writer or a reader (in some modes) holds a lock that another writer is trying to acquire. The default timeout is zero, so any contention immediately fails. Common causes: unclosed transactions, long-running queries, multiple connections from different processes, or the journal mode (DELETE vs. WAL) causing contention. SQLite is not designed for high-concurrency writes from multiple threads/processes — but it can handle low-to-moderate concurrency with proper configuration.
The first ten minutes — establish facts before touching code.
- 1Check the exact error message and SQLite error code (should be 5 or SQLITE_BUSY).
- 2Run `lsof /path/to/database.sqlite` to see which processes have the file open.
- 3Use `sqlite3 /path/to/database.sqlite 'PRAGMA busy_timeout;'` to see current timeout (0 means immediate failure).
- 4Execute `PRAGMA database_list;` within sqlite3 to confirm the database path and journal mode.
- 5Look at the journal file (e.g., -journal or -wal) — if it exists, a transaction is active or crashed.
The specific files, logs, configs, and dashboards that usually own this bug.
- searchApplication logs for the first occurrence of SQLITE_BUSY
- searchDatabase file directory for stale journal files: `ls -la *.sqlite*`
- searchSQLite PRAGMA settings: `PRAGMA journal_mode; PRAGMA busy_timeout; PRAGMA synchronous;`
- searchProcess list: `ps aux | grep sqlite` or `lsof | grep sqlite`
- searchApplication code for connection pooling / transaction management (look for missing .commit() or .close())
- searchSystem monitoring: disk I/O (iostat), inotify watches if applicable
- searchBackup or sync tool logs that might hold the file open
Practical causes, not theory. These are the things you will actually find.
- warningDefault busy_timeout is 0 — immediate error on lock contention
- warningDELETE journal mode causes exclusive locks during checkpoints
- warningUnclosed transactions (missing commit or rollback) hold locks indefinitely
- warningMultiple processes/threads writing simultaneously without retry logic
- warningStale .journal file from a crash leaves database in recovery state
- warningLong-running read transactions in WAL mode block checkpointing and writers
Concrete fix directions. Pick the one that matches your root cause.
- buildSet a reasonable busy timeout: `PRAGMA busy_timeout = 5000;` (5 seconds)
- buildSwitch to WAL (Write-Ahead Log) mode: `PRAGMA journal_mode=WAL;` — allows concurrent reads and one writer
- buildAdd retry logic in application code: catch SQLITE_BUSY and retry up to N times with exponential backoff
- buildEnsure every transaction is properly closed: use try/finally or context managers
- buildUse a single connection for writes with a queue to serialize write operations
- buildFor high-concurrency scenarios, consider a connection pool with max 1 writer or use a database like PostgreSQL
A fix you cannot prove is a guess. Close the loop.
- verifiedAfter setting busy_timeout, reproduce the load and confirm errors are gone or reduced
- verifiedSwitch to WAL mode and verify journal_mode is 'wal' with `PRAGMA journal_mode;`
- verifiedMonitor lsof output during load to ensure no stale connections linger
- verifiedRun a stress test: `for i in {1..100}; do sqlite3 test.db 'INSERT INTO t VALUES('$i');' & done` and count errors
- verifiedCheck that checkpoint settings (PRAGMA wal_autocheckpoint) are appropriate (default 1000 pages)
- verifiedVerify application retry logic logs retries and eventual success
Things that make this bug worse or harder to find.
- warningSetting busy_timeout to a huge value (e.g., 60000) without understanding it delays failure, doesn't fix contention
- warningSwitching to WAL mode but not handling checkpoint locks (checkpoint still needs exclusive lock briefly)
- warningUsing multiple connections from different processes without any synchronization mechanism
- warningIgnoring stale journal files after a crash — manually delete them only after ensuring no active transaction
- warningAssuming the error is transient and not investigating the root cause
- warningUsing `PRAGMA synchronous=OFF` to gain speed — risks database corruption on crash
Payment Service Hangs Under Load — SQLITE_BUSY Every 3rd Request
Timeline
- 09:15Deploy new payment processing endpoint
- 09:45PagerDuty alert: Payment endpoint latency > 10s
- 09:50Check logs: 'sqlite3.OperationalError: database is locked' on ~30% of POST /payment
- 10:00Run lsof: 4 gunicorn workers all have the database file open with different file descriptors
- 10:05Check PRAGMA: busy_timeout = 0, journal_mode = delete
- 10:10Temporarily set busy_timeout=5000 via code — errors drop but latency spikes to 5s
- 10:20Switch to WAL mode and set busy_timeout=3000 — errors disappear, latency normal
- 10:30Add retry logic with exponential backoff as a safety net
- 10:45Monitor for 30 minutes — zero errors, p95 latency 200ms
We deployed a new payment endpoint that inserts a row into SQLite for each payment. Within 30 minutes, we got alerts that the endpoint was timing out. I SSHed into the instance and saw 4 gunicorn workers all hitting the same SQLite file. The logs showed 'database is locked' on about 1 in 3 requests. The default busy_timeout is 0, so any write conflict immediately throws an error.
I first tried setting busy_timeout=5000 in the connection setup. That stopped the errors, but now requests were waiting up to 5 seconds for the lock, causing high latency. I realized the real problem was lock contention from multiple worker processes. I switched the journal mode to WAL, which allows concurrent reads and only serializes writes. After that, the errors vanished and latency dropped to normal.
I also added application-level retry logic with exponential backoff as a safety net, and we set wal_autocheckpoint to 1000 to keep checkpoints frequent enough. The fix held. Later, we moved the payment data to PostgreSQL for better concurrency, but for the immediate incident, WAL mode and a reasonable timeout solved it.
Root cause
SQLite's default journal mode (DELETE) and busy_timeout=0 caused immediate write failures under concurrent gunicorn workers.
The fix
Set busy_timeout=3000 and switched journal mode to WAL. Added retry logic in the application code.
The lesson
Always configure SQLite for the expected concurrency level. WAL mode is essential for multiple readers/writers. Never rely on the defaults for production.
SQLite uses a tiered locking system: UNLOCKED, SHARED, RESERVED, PENDING, EXCLUSIVE. A read transaction acquires a SHARED lock; a write transaction first acquires RESERVED, then upgrades to PENDING and finally EXCLUSIVE. Multiple SHARED locks can coexist. Only one RESERVED lock can exist at a time, but writers can still be working on their buffer while readers read. The transition from RESERVED to EXCLUSIVE blocks all new readers.
The 'database is locked' error occurs when a transaction tries to acquire a lock but cannot because another transaction holds a conflicting lock. For example, a writer trying to upgrade from RESERVED to PENDING will fail if there are active SHARED locks (readers). This is why long-running read transactions can block writes.
WAL (Write-Ahead Log) mode changes the locking model. Readers do not block writers and vice versa. A writer appends changes to a .wal file, and readers read from both the original database and the .wal file. This allows one writer and many readers concurrently. However, checkpoints (which merge the .wal back into the main database) need an EXCLUSIVE lock briefly. If a checkpoint cannot acquire the lock, it is skipped until the next checkpoint attempt.
Common WAL pitfalls: wal_autocheckpoint default is 1000 pages. If writes are heavy, the .wal file can grow large, slowing reads. Also, if a reader holds a SHARED lock for a long time, checkpointing can stall, causing writers to wait. Setting wal_autocheckpoint to a lower value (e.g., 100) can help, but at the cost of more frequent checkpoint locks.
PRAGMA busy_timeout tells SQLite to wait up to N milliseconds before returning SQLITE_BUSY. This is a simple backoff inside the SQLite library. It works well for short contention but can mask underlying issues. Setting it too high (e.g., 30 seconds) makes the application appear hung. A moderate timeout (2-5 seconds) combined with application-level retry is best.
Application retry logic should catch SQLITE_BUSY, wait with exponential backoff + jitter, and retry up to a limit (e.g., 3 retries). Use try/except in Python or retry libraries like tenacity. This handles cases where the timeout expires but the lock is released shortly after.
If SQLite crashes while a transaction is active, a journal file (e.g., .sqlite-journal) may remain. On next open, SQLite will try to roll back the incomplete transaction. This can delay opening and may cause 'database is locked' if another process tries to access the database concurrently. To check: look for .sqlite-journal or .sqlite-wal files. If present and the database is not in use, you can manually delete them after ensuring no active connections.
A safe way to recover: run `sqlite3 database.sqlite 'PRAGMA integrity_check;'`. If it passes, delete the journal file. Never delete a journal file while the database is in use — you will corrupt it. Use `fuser` or `lsof` to confirm no process has it open.
SQLite is not designed for connection pooling across threads/processes because each connection holds its own transaction state. A common mistake: using a thread-safe connection pool that shares a single connection across threads — this leads to serialized access and potential deadlocks. Better: use a connection per thread with WAL mode, or use a single writer connection with a queue.
For Flask with gunicorn, each worker process should open its own SQLite connection. Use `check_same_thread=False` in Python's sqlite3 module if you must share within a process, but avoid sharing across threads. Consider using a dedicated SQLite connection factory with retry logic.
Frequently asked questions
Why does 'database is locked' happen even with only one process?
If you have multiple threads within that process accessing the same connection without proper locking, or if you have a long-running transaction in one thread, another thread will get SQLITE_BUSY. Also, check that you aren't accidentally opening multiple connections (e.g., in different parts of the code). Use `PRAGMA busy_timeout` to add a wait and review your transaction boundaries.
Should I always use WAL mode to avoid locking issues?
WAL mode is great for read-heavy workloads with occasional writes. It reduces lock contention significantly. However, if you have many concurrent writers (e.g., 10+ processes writing simultaneously), WAL still serializes writes. For high write concurrency, consider a client-server database. Also, WAL mode may have slightly higher read overhead due to the WAL index. Test under your workload.
How do I check if a journal file is stale?
First, ensure no process is using the database: `lsof /path/to/db.sqlite`. Then run `sqlite3 /path/to/db.sqlite 'PRAGMA integrity_check;'`. If it passes, you can safely delete the journal file (e.g., `rm /path/to/db.sqlite-journal`). If integrity_check fails, restore from backup. Never delete a journal file while the database is open — it will cause corruption.
What's the difference between SQLITE_BUSY and SQLITE_LOCKED?
SQLITE_BUSY (error code 5) means the database file is locked by another connection. SQLITE_LOCKED (error code 6) means a table in the same connection is locked (e.g., you have two cursors on the same connection and one is writing). Both are related to locking, but SQLITE_BUSY is about inter-process/connection contention, while SQLITE_LOCKED is intra-connection. Fix SQLITE_LOCKED by using separate connections or completing one operation before starting another.
Can I use SQLite in a serverless environment like AWS Lambda?
Yes, but with caution. Lambda functions can have concurrent executions, each with its own SQLite file (e.g., in /tmp). If you share a file across invocations via EFS, you'll face locking issues. For Lambda, use a per-execution temporary file or switch to a managed database. If you must share, use WAL mode and busy_timeout, but expect contention under high concurrency.