I have quite a large python3 application (20.000 lines) that uses the SQLite database intensively. It has a web-ui and API, which leads to it having 10 threads for the web server and 2-3 other threads. All these threads can write to the database, so there could be a fair bit of concurrency. There is one database connection per thread.
The problem my application has been having since the start is that the database will lock sometimes (the 'Database is locked' error). As far as I know, this happens when two connections want to write to the database, but one has not committed their changes before the timeout of the other connection (waiting to write) has passed.
The peculiar thing that I have noticed is that sometimes I get the error within seconds of the application starting up. I have my timeout set to 20 seconds (sqlite3.connect('database.db', timeout=20.0)), but I will get the error after like 2 seconds of the application starting up. How can this happen? The documentation states that the timeout parameter decides how long the connection should wait on a lock before it gives up. So how can it give up after 2 seconds when I set my value to 20 seconds?
I still can't figure out why your attempts to set a 20-second busy timeout (either with the timeout parameter or the SQL PRAGMA busy_timeout statement) aren't working. But our discussion in the comments found a workaround:
sqlite3.connect has an optional isolation_level parameter, which controls the database locking behavior during transactions.
DEFERRED (the default) mode, a transaction acquires a read lock on the database when it begins, and a write lock only when a write operation is requested.IMMEDIATE mode, a transaction acquires a write lock immediately, even if it's only performing read operations. Other transactions attempting to write to the database will be blocked, but read-only transactions can still proceed.EXCLUSIVE mode, a transaction acquires an exclusive lock on the database, blocking all other transactions, reading or writing, until the current transaction is committed or rolled back.Using EXCLUSIVE mode seems to have solved your immediate problem.