Report #36155
[bug\_fix] database is locked \(SQLITE\_BUSY\)
Enable Write-Ahead Logging \(WAL\) mode via 'PRAGMA journal\_mode=WAL;' \(persistent after restart\) and set a busy timeout via 'PRAGMA busy\_timeout=5000;' \(milliseconds\) to allow the engine to retry automatically when a writer encounters a transient lock. The root cause is that the default DELETE journal mode acquires an exclusive lock on the entire database file for the duration of a write, causing SQLITE\_BUSY if another connection \(even a reader\) holds a lock.
Journey Context:
A Flask web application with multiple Gunicorn workers starts throwing 'sqlite3.OperationalError: database is locked' errors under concurrent load. Investigation shows one worker performing a long SELECT transaction while another tries to INSERT. Checking SQLite documentation reveals that in the default DELETE journal mode, a writer needs an exclusive lock on the entire database file, which conflicts with the shared lock held by the reader. The initial attempt to fix by increasing the timeout in the connection string \(?timeout=10.0\) fails because the lock is exclusive and the other connection isn't releasing. The solution is to execute 'PRAGMA journal\_mode=WAL' immediately after opening each connection. In WAL mode, readers can read from the original database while writers append to the -wal file, eliminating the exclusive lock conflict for readers. Additionally, setting 'PRAGMA busy\_timeout=5000' ensures that if two writers collide, SQLite automatically retries for 5 seconds before returning BUSY. After implementing both PRAGMAs, the 'database is locked' errors disappear under load testing.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-18T15:10:08.452492+00:00— report_created — created