SQLite 'database is locked' (SQLITE_BUSY / OperationalError) in a multi-threaded or multi-process app: how do I fix it?

Cause: SQLite allows many readers but only one writer at a time. In the default rollback-journal mode a writer needs exclusive access to the file to commit, and a reader or another writer holding a lock makes the call return SQLITE_BUSY immediately unless a busy timeout is set. Common triggers: no busy timeout, long transactions, a reader that upgrades to a writer in the middle of a transaction, an unfinished cursor or uncommitted transaction left open, and many writers in several processes. Fix (best first): 1. Set a busy timeout on every connection so SQLite waits instead of failing at once: PRAGMA busy_timeout = 5000; (milliseconds). In Python, sqlite3.connect(path, timeout=5) does the same. 2. Turn on write-ahead logging: PRAGMA journal_mode=WAL; It is persistent in the database file. In WAL mode readers do not block the writer and the writer does not block readers, but there is still only one writer at a time. Optionally add PRAGMA synchronous=NORMAL; which the SQLite docs call a good balance in WAL mode, at the cost that the last commits may be lost on power failure. 3. Keep write transactions short: do slow work (network calls, computation) before BEGIN, commit promptly, close cursors, and never hold a transaction open across user input. 4. If a transaction will write, start it as a write transaction (BEGIN IMMEDIATE) so it takes the write lock up front instead of failing later when upgrading from a read lock. 5. Serialize writes inside the app (a single writer thread or a queue) when write volume is high. 6. Do not put a SQLite file on a network filesystem (NFS/SMB); locking is unreliable there, and WAL does not work across hosts. If you need many concurrent writers or several machines, move to a client/server database such as PostgreSQL. Verify: concurrent test load finishes without OperationalError; PRAGMA journal_mode; returns wal and PRAGMA busy_timeout; returns your value on each connection. Sources: https://www.sqlite.org/lockingv3.html https://www.sqlite.org/wal.html https://www.sqlite.org/pragma.html