I’ve recently discovered that SQLite can raise a database is locked error without trying to retry to acquire a lock, making the timeout setting irrelevant.
I created a repo with steps to reproduce the problem, but the gist of it is this:
@transaction.atomic() # Start a deferred transaction; no db lock yet
def read_write_transaction(_):
read_from_db() # Read from the db here; no lock yet
write_to_db() # Try to acquire a lock in the middle of the transaction, but if the db is already locked because of another request, SQLite cannot retry because that might break the serializable isolation guarantees.
return HttpResponse("OK")
Trying to acquire a lock in the middle of an active transaction can make SQLite throw the db is locked error immediately instead of retrying until the timeout is up.
The solution (I think) is to force SQLite to open the transaction in immediate mode instead of deferred (BEGIN IMMEDIATE instead of BEGIN), but the proposal to do this was rejected 5 years ago.
I understand that there is a performance penalty that comes with BEGIN IMMEDIATE, especially for those with ATOMIC_REQUESTS = True, but maybe we can workaround this by only using IMMEDIATE only when starting a transaction manually (with transaction.atomic) or make it safe some other way?
Could we reopen #29280 (Fix SQLite "database is locked" problems using "BEGIN IMMEDIATE") – Django and restart the discussion around possibly adding BEGIN IMMEDIATE? I’d be happy to do some benchmarks to understand better how much this would impact users, and I’m also happy to discuss other possible solutions to this problem. At the very least, we should add more info about this to the docs.
PS: I’ve also written a blog post about this that goes into more detail and proposes some nonproduction-ready workarounds - I plan to update the blog post based on further conversations here. I hope we find a suitable solution!
Unfortunately, I don’t think we can change BEGIN IMMEDIATE without a way to opt-in into this behaviour as even if we put the performance aspect aside it has the potential of breaking read heavy applications out there as Aymeric pointed out when closing the ticket.
The ATOMIC_REQUEST setting is not special by any mean, it just manually wrap views in an atomic blocks so I don’t think it warrants special casing. It just happen to a common case that could be problematic.
Given Django doesn’t event support passing an isolation_level to atomic I’m also not convinced that allowing to specify immediate under a serializable isolation mode (e.g. it could result in NOT DEFERRABLE on Postgres and IMMEDIATE on SQLite`) is also going to fly.
At this point I think the only acceptable solution, assuming this gets some traction (this issue has seen no activity for over 5 years) would be to allow specifying whether or not all transaction start should be immediate though an OPTIONS which is what I assume Aymeric was alluding to
diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py
index 08de0bad5a..ce9eab8d9d 100644
--- a/django/db/backends/sqlite3/base.py
+++ b/django/db/backends/sqlite3/base.py
@@ -297,7 +297,12 @@ def _start_transaction_under_autocommit(self):
Staying in autocommit mode works around a bug of sqlite3 that breaks
savepoints when autocommit is disabled.
"""
- self.cursor().execute("BEGIN")
+ sql = (
+ "BEGIN IMMEDIATE"
+ if self.settings_dict["OPTIONS"].get("begin_immediate")
+ else "BEGIN"
+ )
+ self.cursor().execute(sql)
def is_in_memory_db(self):
return self.creation.is_in_memory_db(self.settings_dict["NAME"])
The ATOMIC_REQUEST setting is not special by any mean, it just manually wrap views in an atomicblocks so I don’t think it warrants special casing. It just happen to a common case that could be problematic.
I assume that the majority of cases when an atomic block wraps read-only queries is when ATOMIC_REQUEST = True. I also assume that if a developer uses transaction.atomic() explicitly, they use it because they are wrapping write queries. This could be a completely wrong assumption on my part, but that is why I thought using IMMEDIATE as a default for transactions.atomic() except for the case when transaction.atomic() is called in the ATOMIC_REQUEST if block that you linked, could be a potential solution.
I do like your solution. I could even wrap it in a PyPI package (as a custom ENGINE) so that people can play around with it and try it out before we make any changes to Django itself.
The only drawback of the OPTIONS solution is that developers would need to know about the option and turn it on, which would only happen after encountering the error. It’s not the best user experience, but it is better than potentially breaking existing code.
I think the reason for not much activity on the original ticket is that making the leap from the db is locked error to I need to use IMMEDIATE is not easy to make. I failed to figure it out a few months back, even though I spent much time debugging the error. My solution ended up being begrugingly switching to Postgres and I only found this solution by chance after Stephen pointed it out in response to my tweet).
I feel that SQLite will continue to build momentum, and it would be great if Django provided a good experience for it out of the box for production like workloads, so whatever I can do to help improve the rough edges I’ll happily do
Would like to see #29280 reopened, with something similar to the change you’ve suggested here.
(I’d be happy to contribute a patch, tests, documentation!).
It would be to see it as an option, along with a documented why someone would consider enabling it. Without it - it’s considerably harder to run Django on SQLite with more than one process/worker, as there’s a chance of hitting an immediate “database is locked” regardless of the timeout setting.
Yes, I have explored WAL. The core issue that requires BEGIN IMMEDIATE to resolve is present both with WAL and without WAL.
WAL itself does help with avoiding database locks because it unblocks reads while writes are in progress. Without WAL, writes block both reads and other writes. Because of this, I think for the majority of Django users WAL mode is a better default as it allows higher concurrency. At some point I’d like to also explore how Django could enable WAL mode by default (e.g. when creating a new SQLite database), but I think addressing the errors in transactions is more important so I haven’t brought it up until now. As I side note I did open a PR on Simon’s benchmark repository showing the db lock issue that we are discussing here.
One other note, I got notified today that GRDB.swift updated their documentation based on my blog post. They now recommend using IMMEDIATE mode as the default transaction mode in the busy lock error docs. Once Django has an option to do this as well we should probably add a similar note to Databases | Django documentation | Django
I also checked how GRDB implemented selecting immediate mode and it looks like they allow selecting any of the three transaction modes (link). This got me thinking that it would probably be better to add a transaction_mode OPTION that would allow setting any of the 3 transaction modes instead of only a begin_immediate OPTION.
Well that’s all very important data points that were missing from the conversation so far thank for sharing and leading these investigation to both of you!
I’m more convinced now of the value of having a setting to configure the transaction mode but I still think that for compatibility reason we cannot make BEGIN IMMEDIATE the default at least for the time being. There seems to be alignment in terms of the risks in making this the default for existing projects.
@anze3db if I understand your proposal correctly your ask here would be to introduce a transaction_mode mode OPTIONS member that allows either
deferred (default)
immediate (desired)
exclusive (any motivation to add given we don’t explicitly support journaling options?)
Yes, that’s it. I think adding exclusive makes sense for completenes sake, but I don’t think it will be a common option for Django users.
In DELETE journal_mode (default when Django creates a new db) exclusive locks both reads and writes, preventing access to the db until the transaction is closed. immediate locks only writes making the db read only until the transaction is closed.
In WAL journal_mode exclusive behaves the same as immediate. It locks writes but still allowing reads.
Sorry, I don’t fully understand what you are asking here, can you please rephrase?
thank for sharing and leading these investigation to both of you!