Core question

In SQLite WAL mode, readers and one writer should usually coexist. So why does writer contention appear to hurt the daemon’s ability to read?

What we know

The daemon makes many concurrent write attempts to a database that allows only one writer at a time.

Observed writer contention:

many callers enter BEGIN IMMEDIATE

only one can hold the SQLite writer lock

others wait inside the SQLite busy handler

after ~10s they return SQLITE_BUSY

Important distinction

Writer contention does not necessarily block reads directly at the SQLite lock level.

In WAL mode:

Many readers + one writer can coexist
Only one writer can write

But writer contention can still indirectly hurt reads.

Main mechanism: shared connection pool starvation

Reads and writes share the same sqlitex.Pool.

When many writer goroutines are stuck waiting in BEGIN IMMEDIATE, each one may hold a pooled SQLite connection.

Then read paths can block before SQLite even executes the read:

conn, release, err := db.Conn(ctx)

So the read is not blocked by a SQLite read lock. It is blocked because all/most DB connections are occupied by writers waiting for the write lock.

This is probably the main explanation.

Other mechanisms

CPU / I/O pressure

Heavy writers like indexing/sync can consume:

disk bandwidth

page cache

CPU

SQLite internal work

Reads are logically allowed, but they still compete for machine resources.

WAL growth / checkpoint pressure

In WAL mode, writes append to db.sqlite-wal.

If checkpointing falls behind, the WAL grows. A large WAL can make reads and checkpoints more expensive.

We observed an old daemon with a very large WAL file (~679 MB).

Mixed request pipelines

Many user-visible operations are not pure reads.

Example pipeline:

read peer list
connect to peers
update peers table
fetch blobs
PutMany/index blobs
read document/events

So a user may experience “reading is slow”, but the request may be waiting on best-effort writes somewhere earlier in the pipeline.

“Read-looking” code may still use savepoints

Many read paths use WithSave. Pure read savepoints are okay, but they still hold a DB connection. If any statement mutates temp/real tables, it can enter writer accounting.

Current likely offenders / signals

From debug snapshots:

hmnet.(*Node).connect has high begin_busy count and ~10s waits.

Its own hold time is tiny.

So it is mostly a victim/amplifier.

blob.(*Index).PutMany-range1 can hold writer lock for hundreds of ms.

sync/indexing paths can repeatedly take the writer lock.

domain cache writes are also small but can add pressure.

feed/list/event queries are mostly read-side performance concerns, not necessarily direct writer-lock holders.

Key conclusion

The issue is likely not:

writer lock directly blocks all reads

It is more likely:

writer storm consumes DB pool + I/O + scheduler capacity
so reads cannot get resources promptly

Proposed direction

We need both:

Reduce write work

optimize heavy SQL

shorten transactions

batch writes carefully

Control write admission

avoid many goroutines racing BEGIN IMMEDIATE

use Go-level queue/gate for writes

batch/coalesce best-effort writes

prioritize user-visible writes

Separate/guard read capacity

consider read vs write pool behavior

ensure writers waiting for lock do not consume all connections needed by reads

Practical next fix

Move hmnet.(*Node).connect peer-row update into peerWriter.

Reason:

it is best-effort bookkeeping

currently it can wait 10s on SQLite busy

it creates many busy events

it should not block successful network connect or read/sync flows

Then consider broader writer admission control for hot paths like PutMany, domain updates, and sync writes.