SQLite throws 'database locked' exception way before timeout has passed

Question

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?

Answer 1

There are indeed conditions when SQLite will skip the busy timeout and return 'database is locked' earlier. From https://www.sqlite.org/c3ref/busy_handler.html:

The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY to the application instead of invoking the busy handler. Consider a scenario where one process is holding a read lock that it is trying to promote to a reserved lock and a second process is holding a reserved lock that it is trying to promote to an exclusive lock. The first process cannot proceed because it is blocked by the second and the second process cannot proceed because it is blocked by the first. If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed.

Basically, if your multithread/multiprocess app ends up in a deadlock situation, it will send SQLITE_BUSY (resulting in the Python OperationalError 'database is locked') to at least one of the processes right away, because it knows it won't be able to be resolved after waiting any time.

You can read more about the different types of SQLite locks here: https://www.sqlite.org/lockingv3.html

In my case, I found that if I opened two DEFERRED transactions nearly simultaneously, it would likely result in this sort of deadlock situation. This is because a DEFERRED transaction establishes a reserved lock early, and only upgrades to an exclusive lock right before a write statement. So if you have a DEFERRED transaction that does a read followed by a write, two threads running the same transaction simultaneously will deadlock (thread A will be trying to establish a reserved lock at the start of its transaction, while thread B will have the reserved lock and be trying to get an exclusive lock to do its write). Switching to IMMEDIATE transactions helped because IMMEDIATE transactions go straight to the exclusive lock state at their beginning.