SQLite: read-only views that serve a picture, a script or a poll take the write lock for the whole request #1129

New issue

Closed

opened 2026-10-31 03:04:13 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

2026-10-31 03:04:13 +00:00

Owner

What exists today

config/settings/base.py:178 sets ATOMIC_REQUESTS and config/sqlite.py:51-55 gives every file-backed SQLite connection transaction_mode: IMMEDIATE. Django's BaseHandler.make_view_atomic (django/core/handlers/base.py:347) wraps every view not marked non_atomic_requests in transaction.atomic, and Atomic.__enter__ calls set_autocommit(False, force_begin_transaction_with_broken_autocommit=True), which on SQLite runs _start_transaction_under_autocommit and so issues BEGIN IMMEDIATE (django/db/backends/sqlite3/base.py:321-331). The write lock is therefore taken on view entry, before any query and even for a view that makes none, and held until the view returns.

The config/sqlite.py docstring (lines 15-33) accepts this as the price of never failing a write, and #357 moved the slow views out view by view (29 non_atomic_requests uses today). The views that only read, are fetched many at a time by the browser or on a timer, and gain nothing from a transaction are still inside one:

jobs/views.py:1473 CompanyLogoView.get: one request per company logo on the board and lists (application_card.html:40, application_row.html:31, company_row.html:18), so a board with 50 cards is 50 serialised write-lock acquisitions on a cold cache.
accounts/views.py:368 AvatarView.get: the profile picture on every page that shows it.
notifications/views.py:27 worker (/sw.js): reads a cached file, no query at all, yet takes the lock.
core/views.py:140 manifest: reads instance settings.
core/views_errands.py:60 errand_state: polled every 2 s (POLL_SECONDS) while slow work runs, and the slow work is exactly what holds the lock in db_worker; it uses render(), so the template is drawn inside the transaction too.

Measurement (work/r1_lock.py in the audit scratchpad, plain sqlite3 with WAL, the same options): with one connection holding BEGIN IMMEDIATE for 1 s, a second connection's BEGIN IMMEDIATE; SELECT; COMMIT took 990 ms; the same SELECT in autocommit, or in a deferred transaction, took 0.1 ms, because WAL readers do not wait for the writer.

The notices poll (notifications/views.py:40 waiting) was also named by the scout. It writes (it deletes expired notices and marks the handed ones shown, notifications/inbox.py:75-93), runs once a minute per open tab, and is correct either way; it is not part of this fix (see Decisions).

What would change

Mark the five read-only views above transaction.non_atomic_requests (method_decorator(..., name="dispatch") on the two class views, the plain decorator on the three functions), with a one-line reason each in the style of #357, and add them to the list in the config/sqlite.py docstring. On PostgreSQL nothing changes in practice: each view runs one or two reads in autocommit instead of inside a transaction.

A test beside tests/security/test_captured_pages.py:601-605 asserts _non_atomic_requests == {"default"} on each resolved view, so a refactor does not silently put them back.

No library is involved; this is Django's own switch.

What it saves or protects

medium: a logo, avatar, service worker, manifest or errand poll no longer queues behind every writer; measured 990 ms wait against 0.1 ms for the same read when another connection holds the lock for 1 s

Accessibility

none

Decisions

All GET views at once, or view by view? Recommended: view by view, as config/sqlite.py:29-31 already decided; a blanket rule would also release GET views that write (for example a get_or_create on first visit).
Include the notices poll? Recommended: no. It writes, it runs once a minute per tab, and its delete plus update belong together.
Merge A9. A9 is the same root cause (errand and notices polls); it is folded in here.

Found by the efficiency and reliability audit of 2026-10-30 (finding R1; index postulo/postulo#1108). Checked against main at daee30b15 (2026-10-30).

## What exists today `config/settings/base.py:178` sets `ATOMIC_REQUESTS` and `config/sqlite.py:51-55` gives every file-backed SQLite connection `transaction_mode: IMMEDIATE`. Django's `BaseHandler.make_view_atomic` (`django/core/handlers/base.py:347`) wraps every view not marked `non_atomic_requests` in `transaction.atomic`, and `Atomic.__enter__` calls `set_autocommit(False, force_begin_transaction_with_broken_autocommit=True)`, which on SQLite runs `_start_transaction_under_autocommit` and so issues `BEGIN IMMEDIATE` (`django/db/backends/sqlite3/base.py:321-331`). The write lock is therefore taken on view entry, before any query and even for a view that makes none, and held until the view returns. The `config/sqlite.py` docstring (lines 15-33) accepts this as the price of never failing a write, and #357 moved the slow views out view by view (29 `non_atomic_requests` uses today). The views that only read, are fetched many at a time by the browser or on a timer, and gain nothing from a transaction are still inside one: - `jobs/views.py:1473` `CompanyLogoView.get`: one request per company logo on the board and lists (`application_card.html:40`, `application_row.html:31`, `company_row.html:18`), so a board with 50 cards is 50 serialised write-lock acquisitions on a cold cache. - `accounts/views.py:368` `AvatarView.get`: the profile picture on every page that shows it. - `notifications/views.py:27` `worker` (`/sw.js`): reads a cached file, no query at all, yet takes the lock. - `core/views.py:140` `manifest`: reads instance settings. - `core/views_errands.py:60` `errand_state`: polled every 2 s (`POLL_SECONDS`) while slow work runs, and the slow work is exactly what holds the lock in `db_worker`; it uses `render()`, so the template is drawn inside the transaction too. Measurement (`work/r1_lock.py` in the audit scratchpad, plain `sqlite3` with WAL, the same options): with one connection holding `BEGIN IMMEDIATE` for 1 s, a second connection's `BEGIN IMMEDIATE; SELECT; COMMIT` took 990 ms; the same `SELECT` in autocommit, or in a deferred transaction, took 0.1 ms, because WAL readers do not wait for the writer. The notices poll (`notifications/views.py:40` `waiting`) was also named by the scout. It writes (it deletes expired notices and marks the handed ones shown, `notifications/inbox.py:75-93`), runs once a minute per open tab, and is correct either way; it is not part of this fix (see Decisions). ## What would change Mark the five read-only views above `transaction.non_atomic_requests` (`method_decorator(..., name="dispatch")` on the two class views, the plain decorator on the three functions), with a one-line reason each in the style of #357, and add them to the list in the `config/sqlite.py` docstring. On PostgreSQL nothing changes in practice: each view runs one or two reads in autocommit instead of inside a transaction. A test beside `tests/security/test_captured_pages.py:601-605` asserts `_non_atomic_requests == {"default"}` on each resolved view, so a refactor does not silently put them back. No library is involved; this is Django's own switch. ## What it saves or protects medium: a logo, avatar, service worker, manifest or errand poll no longer queues behind every writer; measured 990 ms wait against 0.1 ms for the same read when another connection holds the lock for 1 s ## Accessibility none ## Decisions - **All GET views at once, or view by view?** Recommended: view by view, as `config/sqlite.py:29-31` already decided; a blanket rule would also release GET views that write (for example a `get_or_create` on first visit). - **Include the notices poll?** Recommended: no. It writes, it runs once a minute per tab, and its delete plus update belong together. - **Merge A9.** A9 is the same root cause (errand and notices polls); it is folded in here. --- Found by the efficiency and reliability audit of 2026-10-30 (finding R1; index postulo/postulo#1108). Checked against `main` at `daee30b15` (2026-10-30).

tiagoagueda added this to the 0.6.0 milestone

2026-10-31 03:04:13 +00:00

tiagoagueda added the

performance

bug

labels

2026-10-31 03:04:13 +00:00

tiagoagueda referenced this issue

2026-10-31 03:04:40 +00:00

Efficiency and reliability audit of 2026-10-30: index #1108

tiagoagueda referenced this issue from a commit

2026-11-10 13:37:55 +00:00