Improve SQLite configuration: IMMEDIATE transaction mode and PRAGMAs

[mikerkelly]

Why are we doing this?

There are regular Sentry alerts for several Issues where requests fail with OperationalError: database is locked errors. The root cause is that our level of concurrency is currently too high for SQLite to reliably handle as currently configured. This Issue is one possible approach to improving the situation. Refer to the parent issue #2251 for more detail.

How will we know when it's done?

There are fewer instances of OperationalError: database is locked errors, as monitored through Sentry.

What are we doing?

This article, kindly pointed out in #2251 by Simon, argues for making several configuration changes from the default for SQLite in a server context, as opposed to the client context the defaults are optimized for (such as a data store for an app on a mobile phone). These include using transaction mode IMMEDIATE and adjusting various pragmas. These can improve performance or consistency in various ways detailed in the article. IMMEDIATE mode in particular may simply and directly resolve almost all of the database locked errors.

This was tried in Airlock and seems to work well. https://github.com/opensafely-core/airlock/blob/main/airlock/settings.py#L168

IMMEDIATE mode with SQLIte is a Django 5.1 feature. So #2115 blocks that part.

This might allow us to reduce the Django timeout (ref #2252).

Interestingly the core prod DB seems to be in WAL mode even though I can't see anything in the repo that would lead to that. That is probably the right thing to do for our context as reads may sometimes be much more common than writes and it helps address concurrency concerns. This issue would also incidentally tidy up that inconsistency.

[mikerkelly]

We're hoping to do #2115 this week in what's left of this Onboarding Opencodelists initiative. This ticket deferred until the resolution of that one.

[bloodearnest]

Interestingly the core prod DB seems to be in WAL mode even though I can't see anything in the repo that would lead to that. That is probably the right thing to do for our context as reads may sometimes be much more common than writes and it helps address concurrency concerns. This issue would also incidentally tidy up that inconsistency.

FYI, journal mode changes, like WAL, once enabled, are persistent. So I think this was probably just done manually on the server.

Ideally however, we'd ensure it locally in dev also, and thus using PRAGMAs in Django 5.1s connection init hook to set WAL makes sense to me.