SQLite in WAL Mode: Why Concurrent Writers Still Block, and How Go's database/sql Pool Makes It Worse

Someone turns on PRAGMA journal_mode=WAL, reads that it "allows concurrent readers and writers", wires up a Go service with the default database/sql pool, and puts it under load. Within a few seconds the logs fill up with database is locked. This is one of the most common SQLite-in-production surprises, and it comes from believing a half-true sentence about WAL mode combined with not knowing what *sql.DB actually is.

What WAL mode actually changes

In the default rollback journal mode, a writer needs an exclusive lock on the whole database file for the duration of a transaction. Readers and writers block each other in both directions: a writer can't start while a reader holds a shared lock, and a reader can't proceed while a writer is committing. This is the behaviour most people mean when they say "SQLite doesn't do concurrency".

WAL mode changes the read side. Instead of writing changes directly into the database file, a writer appends them to a separate write-ahead log file. Readers construct their view from the main database file plus whatever WAL frames existed at the moment their transaction started, so a reader never has to wait for a writer's commit, and a writer never has to wait for a reader to finish. That's genuinely useful and is the correct reason to enable WAL.

What WAL mode does not change is the number of writers SQLite will allow at once, which is exactly one. There's still a single writer lock on the WAL file itself, acquired for the duration of a write transaction. A second writer that tries to start while another is mid-transaction gets SQLITE_BUSY, precisely as it would under the rollback journal. WAL buys you reader/writer concurrency, not writer/writer concurrency. If your workload has many concurrent writers rather than many concurrent readers, WAL mode doesn't solve your actual problem; it just changes which lock you're queuing on.

Where the Go connection pool comes in

sql.Open doesn't give you a connection. It gives you a pool, and the pool opens real underlying connections lazily, up to SetMaxOpenConns, which defaults to unlimited. For most databases this is exactly the abstraction you want: a client-server database happily services many concurrent connections, and the pool exists to avoid the cost of reconnecting.

SQLite is not a client-server database. Every connection the driver opens is a full, independent handle onto the same file on disk, each with its own view of locking state. When two goroutines each grab a connection from the pool and issue a write, the pool has no idea that both connections point at the same SQLite file with a single writer slot. It cheerfully hands out two (or twenty) separate connections and lets them fight over the one write lock SQLite actually has. The pool's entire purpose, on every other database, is to enable exactly the kind of concurrency that SQLite's write path cannot support.

Here's the failure reproduced directly:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"sync"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, err := sql.Open("sqlite3", "file:events.db?_journal_mode=WAL")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, payload TEXT)`); err != nil {
		log.Fatal(err)
	}

	var wg sync.WaitGroup
	errs := make(chan error, 20)
	for i := 0; i < 20; i++ {
		wg.Add(1)
		go func(n int) {
			defer wg.Done()
			_, err := db.Exec(`INSERT INTO events (payload) VALUES (?)`, fmt.Sprintf("event-%d", n))
			if err != nil {
				errs <- err
			}
		}(i)
	}
	wg.Wait()
	close(errs)

	for err := range errs {
		fmt.Println(err)
	}
}

Run that against a fresh database and a handful of the twenty inserts will fail with database is locked, because the pool opened several concurrent connections and let them race for the same writer lock. WAL mode being enabled changes nothing here, since the contention is writer against writer, not writer against reader.

The first fix people reach for, and why it's incomplete

PRAGMA busy_timeout tells SQLite that if it can't immediately get a lock, it should retry quietly for up to N milliseconds before returning SQLITE_BUSY to the caller. Set it via the DSN and most of the errors above disappear:

dsn := "file:events.db?_journal_mode=WAL&_busy_timeout=5000"

This helps, but it's papering over the shape of the problem rather than fixing it. You still have an unbounded number of pooled connections each trying to become the writer, now each blocking for up to five seconds inside a call to Exec instead of failing fast. Under sustained write load you get long queuing latency instead of errors, and if a caller's context has a shorter deadline than the busy timeout, you get a context-cancelled error that's just as confusing as the original one.

There's a second, sharper issue that busy_timeout doesn't touch at all. A transaction opened with the default BEGIN DEFERRED doesn't take any lock until its first statement runs, and if that first statement is a read, the transaction starts as a reader. If it then tries to write and another connection has committed a change in the meantime, SQLite can't safely upgrade the existing read snapshot to a write lock, because the data it read might now be one commit out of date, so it returns SQLITE_BUSY_SNAPSHOT. This is a genuine serialisation conflict, not ordinary lock contention, and busy_timeout retries won't fix it because waiting longer doesn't make the snapshot valid again. The usual fix is to start write transactions with BEGIN IMMEDIATE, which grabs the write lock up front instead of deferring it, converting that failure mode back into an ordinary, retryable "someone else is writing" case:

dsn := "file:events.db?_journal_mode=WAL&_busy_timeout=5000&_txlock=immediate"

The _txlock=immediate parameter (mattn/go-sqlite3's way of asking for BEGIN IMMEDIATE on every transaction) is worth setting by default in any Go service doing writes through sql.Tx. If you're on modernc.org/sqlite instead, the equivalent is setting the transaction lock mode via its own DSN options; the underlying SQLite behaviour is identical, only the driver's spelling differs.

The actual fix: stop pooling writers

Neither of the above changes the fact that the pool is still allowed to open multiple connections that all try to write. The straightforward, correct fix is to make the pool's shape match SQLite's own concurrency model: many readers, exactly one writer. That means two separate *sql.DB handles onto the same file, one capped at a single connection for writes, one left more open for reads:

func openDB(path string) (writeDB, readDB *sql.DB, err error) {
	dsn := fmt.Sprintf(
		"file:%s?_journal_mode=WAL&_busy_timeout=5000&_txlock=immediate",
		path,
	)

	writeDB, err = sql.Open("sqlite3", dsn)
	if err != nil {
		return nil, nil, fmt.Errorf("open write db: %w", err)
	}
	writeDB.SetMaxOpenConns(1)

	readDB, err = sql.Open("sqlite3", dsn)
	if err != nil {
		writeDB.Close()
		return nil, nil, fmt.Errorf("open read db: %w", err)
	}
	readDB.SetMaxOpenConns(4)

	return writeDB, readDB, nil
}

Every write in the application goes through writeDB, which the pool can never expand past one live connection, so writes are serialised in Go before they ever reach SQLite's lock. That serialisation was always going to happen somewhere; doing it in the pool means it happens without a single SQLITE_BUSY retry, and without a five-second stall hiding inside a call that looks like it should be instant. Reads go through readDB, which can happily hand out several concurrent connections, because WAL mode means none of them block the writer or each other.

This isn't a workaround bolted onto database/sql; it's the pool configured to reflect the database it's actually talking to. An application-level mutex around write calls achieves the same serialisation and is a reasonable alternative if you'd rather keep a single *sql.DB, but a capped pool is less code and gets you the same guarantee for free from the connection acquisition path.

One more thing that catches people out: checkpoint starvation

WAL frames don't stay in the WAL file forever; SQLite periodically checkpoints them back into the main database file so the WAL doesn't grow without bound. A checkpoint can only reclaim the portion of the WAL that no active reader still needs, because a reader's snapshot might depend on frames a checkpoint would otherwise remove. If you follow the reader/writer split above and then let read transactions run for a long time (an HTTP handler that holds a transaction open across a slow downstream call, say), you can end up with a WAL file that never shrinks, because there's always some old reader still pinning the oldest frames. The database keeps working, but every read gets slower as the WAL grows, and the failure looks nothing like a locking problem, which makes it a nasty one to diagnose after the fact. Keep read transactions short and this doesn't come up; it's only long-lived readers that turn WAL's core feature into a slow leak.