Go in production

Go SQLite reads returning 404 for rows that exist

2026-11-09 · 9 min read

While writing the reference service for a Go handbook we ran a load generator against it. Writes were fine. Reads were not: between 37 and 70 percent of them came back 404 for a row that had been committed seconds earlier. The data was never missing. The row was in the file, and a second request a moment later sometimes found it.

A bug of the form "it is there and it is not there" is almost always about snapshots, and this one was. It took a load generator to see it and a specific kind of test to pin it down, and both are techniques you can reuse on your own service whatever driver you use.

Who needs this

An API that reads its own writes. Create an order, redirect to the order page, load it. The create goes through the writer, the read goes through a pooled reader, and a stale reader turns a successful create into a 404 on the next page. Users see it as "I clicked save and it vanished".

A job queue polled over HTTP. A worker enqueues a job and a client long-polls its status. A reader holding an old view keeps saying "queued" or "not found" after the job finished, and the client waits until it times out. The same queue also has to survive restarts, which the graceful shutdown post covers.

A webhook receiver that deduplicates. It checks a table for the delivery id before processing. A stale read says "never seen it" for an id that was committed a moment ago, and you process the event twice. The idempotency patterns post explains why the dedupe check needs a correct read, not just a fast one.

Any service with a read pool and cancellable requests. The trigger in our case was cancellation, and clients cancel all the time: a closed tab, a mobile network switch, a proxy timeout. If your handlers pass r.Context() to the database, which they should, you are exposed to this class of problem.

The setup that exposed it

The service uses SQLite in WAL mode through a pure Go driver (modernc.org/sqlite), with one connection for writing and a small pool for reading. It is the standard shape for a small service:

type Store struct {
	w *sql.DB // exactly one connection
	r *sql.DB // read-only pool
}

func dsn(path string, readonly bool) string {
	q := url.Values{}
	q.Add("_pragma", "busy_timeout(5000)")
	if readonly {
		q.Add("_pragma", "query_only(1)")
	} else {
		q.Add("_pragma", "journal_mode(WAL)")
		q.Add("_pragma", "synchronous(NORMAL)")
	}
	return "file:" + path + "?" + q.Encode()
}

func Open(path string, readConns int) (*Store, error) {
	w, err := sql.Open("sqlite", dsn(path, false))
	if err != nil {
		return nil, err
	}
	w.SetMaxOpenConns(1) // writers queue inside database/sql
	// ... create tables on w ...
	r, err := sql.Open("sqlite", dsn(path, true))
	if err != nil {
		w.Close()
		return nil, err
	}
	r.SetMaxOpenConns(readConns)
	r.SetMaxIdleConns(readConns)
	r.SetConnMaxIdleTime(5 * time.Minute)
	return &Store{w: w, r: r}, nil
}

The reasoning behind the shape: SQLite allows many readers and exactly one writer. With WAL, readers do not block the writer and the writer does not block readers. One writer connection turns concurrent writes into an orderly queue inside database/sql instead of database is locked errors, and query_only(1) on the read pool means a bug cannot write through it.

In WAL mode a reader sees the database as it was when its transaction began. Each plain SELECT outside a transaction starts and ends its own implicit read transaction, so the next query on that connection should see the latest commit. That is the contract the pool depends on. The load generator broke it.

What it looked like

The load generator has modes. In write mode it posts jobs. In read mode, every client asks for the same job that was already committed. Write mode passed. Read mode, with many clients, returned HTTP 404 for a large share of requests, and the share grew from run to run.

Our first mistake was that the generator reported only "errors". Changing it to print why requests failed turned "errors" into failures=map[HTTP 404:...] and told us we were looking at not-found, not timeouts or 500s. That is a general rule for load tools: print the breakdown of failures by cause, or you will theorise about the wrong thing.

Then we checked the obvious thing. The row was in the file:

sqlite3 app.db 'SELECT id, status FROM jobs WHERE id = 42;'

It was. And PRAGMA journal_mode; said wal. So the data was fine and the reads were wrong.

Narrowing it down

The pattern we kept seeing involved a query being cancelled in the middle of its work, after which the connection went back to the pool still holding its old view. We reproduced it without HTTP, which is what makes a bug fixable: 16 goroutines run thousands of reads whose contexts are cancelled after microseconds, which is what client disconnects do to in-flight queries. Then a new row is committed and read 40 times through the pooled connections.

func TestReadsStayFreshAfterCancelledQueries(t *testing.T) {
	ctx := context.Background()
	s, err := Open(filepath.Join(t.TempDir(), "t.db"), 4)
	if err != nil {
		t.Fatal(err)
	}
	defer s.Close()
	id0, err := s.Create(ctx, "{}")
	if err != nil {
		t.Fatal(err)
	}

	// Hammer the read pool with queries that are cancelled mid-flight.
	var wg sync.WaitGroup
	for g := 0; g < 16; g++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for i := 0; i < 3000; i++ {
				c, cancel := context.WithTimeout(ctx, time.Duration(i%7)*20*time.Microsecond)
				s.Exists(c, id0) // may be cancelled; the result does not matter
				cancel()
			}
		}()
	}
	wg.Wait()

	// Now every pooled connection must see a fresh commit.
	for round := 0; round < 20; round++ {
		id, err := s.Create(ctx, "{}")
		if err != nil {
			t.Fatal(err)
		}
		stale := 0
		for i := 0; i < 40; i++ { // 40 reads over 4 pooled connections
			if ok, err := s.Exists(ctx, id); err == nil && !ok {
				stale++
			}
		}
		if stale > 0 {
			t.Fatalf("round %d: %d of 40 reads missed a committed row", round, stale)
		}
	}
}

Exists is SELECT COUNT(*) FROM jobs WHERE id = ? on the read pool, and Create inserts through the writer. The file needs context, path/filepath, sync, testing and time. Zero-microsecond timeouts (when i%7 == 0) are cancelled before the query even starts, and the others are cancelled while it is running, so you get both cases.

On the driver version we were using then, v1.34.5, all 40 reads returned "not found" in the first round, and the test failed in 6 of 8 runs. The failure is a connection that kept an old snapshot of the database after an interrupted query.

Which versions we tried

This is the full table from the handbook's investigation, one entry per version:

The reference module pins v1.44.0, which needs Go 1.24, so go.mod carries go 1.24.0. We have not traced the change to a specific commit inside the driver. What we have is a test that separates the versions that misbehave from the ones that do not, and that is the thing to rely on. A changelog entry can be wrong or incomplete. A test that fails on your own pool cannot.

What you can check today

If you use a SQLite driver with a read pool and cancellable requests:

1. Look at the version in your go.mod.
2. Add the test above, adapted to your store. Run it many times with the race detector:

``bash go test -race -count=20 -run TestReadsStayFreshAfterCancelledQueries ./... ``

1. If it fails, move to a newer driver release, run it again, and pin the version that passes.
2. Keep the test in the suite. The next driver upgrade is the next time this can regress, and a one-goroutine unit test will not tell you.

The test takes a few seconds. The failure is the kind that never shows up with one goroutine and always shows up in production with real clients.

Other causes of stale reads

Before you blame the driver, rule out the ordinary causes. They produce the same symptom:

• A long-lived read transaction. If any code path begins a sql.Tx on the read pool and forgets to commit or roll back, that connection holds its snapshot indefinitely. It also stops WAL checkpoints from completing, so the -wal file grows. Always defer tx.Rollback() right after BeginTx.
• Unclosed *sql.Rows. A query whose rows you never close or exhaust holds the connection and its read transaction open. Always defer rows.Close() and check rows.Err().
• Reading from a different file. A relative database path plus a changed working directory, or two services each with their own copy, gives you a "stale" database that is simply another database.
• The wrong pool. A read that must see a just-written row but goes through a replica or a cache.
• Caches in front. An in-memory cache that was not invalidated on write looks exactly like a stale snapshot.

The distinguishing feature of the cancellation bug is that it appears under concurrent load with cancelled requests, and is easy to confirm by running the same read through the writer connection.

Rule of thumb: if the writer connection sees the row and a pooled reader does not, the pool is the problem.

Design choices that make you less exposed

For most small services, pinning a passing version and keeping the test is the right answer. Routing the immediate read-after-create through the writer is a cheap extra for the one or two places where a 404 would confuse a user. The retry is a last resort. We would not use it as the only defence.

Mistakes we see

Unit tests with one goroutine. The bug needed concurrency, cancellation and a pool with more than one connection. A sequential test passes every time.

Load tests that report only an error count. Print status codes and error messages by cause. "Errors: 52%" is not a diagnosis.

Fixing the symptom in the handler. Adding a sleep or a retry before the read makes the 404s rarer and the bug permanent.

Trusting a driver upgrade to fix or not break things without a test. Our own results show the failure rate changing from release to release, from 6 of 8 runs failing to none. Pin the version and run the test when you move it.

Passing context.Background() to database calls to avoid the problem. That removes cancellation from the picture and also removes your ability to stop a slow query when the client leaves. Cancellation is correct behaviour. The pool should survive it.

Opening the reader pool before the writer has created the WAL files. Create the database and run migrations on the writer first, then open readers on the same file. Our Open does it in that order.

FAQ

Why use separate reader and writer pools with SQLite at all?

SQLite has one writer at a time. A single writer connection serialises writes inside database/sql, so you get a queue instead of database is locked errors. A separate read pool opened with query_only(1) lets reads run in parallel with the writer in WAL mode and guarantees a bug cannot write through it.

Does this affect mattn/go-sqlite3 or other drivers?

We only observed it with modernc.org/sqlite and have no results for other drivers. The test is generic, though: it needs only a store with a write method and a read method. If you use another driver with a read pool, run it.

Can WAL mode alone cause stale reads?

By design a reader sees a consistent snapshot as of its transaction start, and a fresh implicit transaction should see the latest commit. Stale reads from WAL come from transactions that stay open, usually an unclosed Tx or Rows. Checking those first is quick.

How do I know the pool is the problem and not my data?

Query the file directly with the sqlite3 shell while the service is returning 404s. If the row is there, the data is fine. Then run the same read through the writer connection. If that succeeds and a pooled reader fails, you are looking at a stale connection.

Which driver version should I pin?

The newest one that passes your own regression test. In our runs v1.41.0 and later passed, and the handbook pins v1.44.0. A version number from a blog post is a starting point. The test result on your code is the answer.

The Go on One VPS handbook has this as a section of its database chapter, with the full store, the regression test and the load generator, and a separate chapter listing production bugs next to the check that catches each one: Go on One VPS: Production Handbook.

gosqlite