4

I've been struggling with "sqlite3.OperationalError database is locked" all day....

Searching around for answers to what seems to be a well known problem I've found that it is explained most of the time by the fact that sqlite does not work very nice in multithreading where a thread could potentially timeout waiting for more than 5 (default timeout) seconds to write into the db because another thread has the db lock .

So having more threads that play with the db , one of them using transactions and frequently writing I've began measuring the time it takes for transactionns to complete. I've found that no transaction takes more than 300 ms , thus rendering as not plausible the above explication. Unless the thread that uses transactions makes ~21 (5000 ms / 300 ms) consecutive transactions while any other thread desiring to write gets ignored all this time

So what other hypothesis could potentially explain this behavior ?

Improve this question
4
  • a thread is keeping the db connection alive while a different thread is trying to write to it ... all your writes should be done in one thread Commented Jul 21, 2015 at 18:51
  • unfortunately , there are some background tasks that play with the db. When a transaction is complete isn't its connection closed ?, so that other threads could get a chance to write ? Commented Jul 21, 2015 at 18:56
  • you should handle all your db transactions in one thread ... there is alot of "in theory" it should work, but in reality sqlite was not designed with concurrent connection in mind and as such has very poor support ... alternatively use a database that knows about concurrent connections (mysql/postgres/mongo/etc) Commented Jul 21, 2015 at 19:05
  • I have a 500s timeout set. But some transactions fail emediately with sqlite3.OperationalError: database is locked and don't even try to wait. I can produce this message is 20s. Way under 500. I would like to understand deeply why this happens. Commented Feb 24, 2023 at 13:41

2 Answers 2

Reset to default
10

I have had a lot of these problems with Sqlite before. Basically, don't have multiple threads that could, potentially, write to the db. If you this is not acceptable, you should switch to Postgres or something else that is better at concurrency.

Sqlite has a very simple implementation that relies on the file system for locking. Most file systems are not built for low-latency operations like this. This is especially true for network-mounted filesystems and the virtual filesystems used by some VPS solutions (that last one got me BTW).

Additionally, you also have the Django layer on top of all this, adding complexity. You don't know when Django releases connections (although I am pretty sure someone here can give that answer in detail :) ). But again, if you have multiple concurrent writers, you need a database layer than can do concurrency. Period.

I solved this issue by switching to postgres. Django makes this very simple for you, even migrating the data is a no-brainer with very little downtime.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

this is a perfect answer to this question imho
0

In case anyone else might find this question via Google, here's my take on this.

SQLite is a database engine that implements the "serializable" isolation level (see here). By default, it implements this isolation level with a locking strategy (although it seems to be possible to change this to a more MVCC-like strategy by enabling the WAL mode described in that link).

But even with its fairly coarse-grained locking, the fact that SQLite has separate read and write locks, and uses deferred transactions (meaning it doesn't take the locks until necessary), means that deadlocks might still occur. It seems SQLite can detect such deadlocks and fail the transaction almost immediately.

Since SQLite does not support "select for update", the best way to grab the write lock early, and therefore avoid deadlocks, would be to start transactions with "BEGIN IMMEDIATE" or "BEGIN EXCLUSIVE" instead of just "BEGIN", but Django currently only uses "BEGIN" (when told to use transactions) and does not currently have a mechanism for telling it to use anything else. Therefore, locking failures become almost unavoidable with the combination of Django, SQLite, transactions, and concurrency (unless you issue the "BEGIN IMMEDIATE" manually, but that's pretty ugly and SQLite-specific).

But anyone familiar with databases knows that when you're using the "serializable" isolation level with many common database systems, then transactions can typically fail with a serialization error anyway. That happens in exactly the kind of situation this deadlock represents, and when a serialization error occurs, then the failing transaction must simply be retried. And, in fact, that works fine for me.

(Of course, in the end, you should probably use a less "lite" kind of database engine anyway if you need a lot of concurrency.)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.