tom_hartley0 views
I have a script that loads data every few minutes and a second one that updates a status table. When they overlap, one of them fails with sqlite3.OperationalError: database is locked.
They both open the same file. Do I have to switch to PostgreSQL, or is there a setting that helps?
Be kind and specific. Read the community guidelines.
Accepted answer
You probably do not need to switch yet. SQLite allows many readers but only one writer at a time, and Python's sqlite3 waits only five seconds by default for the other writer before giving up. Two changes cover most cases:
import sqlite3
con = sqlite3.connect("app.db", timeout=30) # wait up to 30 seconds for the other writer
con.execute("PRAGMA journal_mode=WAL") # readers no longer block the writer
The longer timeout makes the second script wait its turn instead of failing. WAL mode (write-ahead logging) lets readers and one writer work together; it is stored in the database file, so you set it once.
Also keep each write transaction short: open it, write, commit(), and do not hold it open while the script does slow work such as calling an API. If two scripts regularly need to write for a long time at once, that is the point to consider PostgreSQL.
The long transaction was the problem. The load opened one at the start and called an API in the middle. With a timeout and a commit after each batch the errors stopped.
For anyone arriving here later, the article SQLite: 'database is locked' and how to fix it walks through the same causes with a script you can run.