SQLite WAL Mode in Go for Concurrent Readers and Writers

SQLite in its default mode has a simple concurrency rule that surprises Go programmers: while one connection is writing, nobody else can read, and while anyone reads, nobody can write. It is the rollback journal, and it suits a single user program. A web service with several goroutines hitting the same file hits "database is locked" errors and slow writes, and usually blames SQLite. The fix, nearly always, is one line: switch to write ahead logging. This article turns it on from Go using the pure Go modernc.org/sqlite driver v1.50.0, measures what it changes on Go 1.25.6, and covers the checkpoint behaviour and the settings that go with it.

It belongs to the database cornerstone. The choice of driver is covered in SQLite in Go with and without cgo, and the error this fixes is the subject of SQLite database is locked.

How the two modes differ

In rollback journal mode (called DELETE in the pragma), a write first copies the original pages to a journal file, then changes the database file in place, then deletes the journal. A reader needs a stable database file, so a writer must wait for all readers, and readers wait while it commits.

In WAL mode, a write appends the changed pages to a separate file, app.db-wal, and the database file itself is not touched. A reader reads the database file plus the part of the log that was committed when it started. So readers do not block the writer and the writer does not block readers. There is still one writer at a time, since the log is a single sequence, but a writer only waits for another writer. Periodically the log is copied back into the database file, a checkpoint, and the log is reused.

Commits are cheaper too. A rollback journal commit syncs two files, journal and database. A WAL commit appends to one file and, with synchronous = NORMAL, does not sync at all until a checkpoint, which gives up a little durability for a lot of speed: after a power failure (not just an application crash) the last few transactions may be lost, and the database is never corrupted.

The experiment

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"strings"
	"sync"
	"sync/atomic"
	"time"

	_ "modernc.org/sqlite"
)

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

type result struct {
	writes       int
	writeTime    time.Duration
	reads        int64
	busyReads    int64
	busyWrites   int
	walBytesPeak int64
}

// run does 2,000 single row transactions while four goroutines read in a loop.
func run(dir, mode, extra string) result {
	path := filepath.Join(dir, mode+".db")
	dsn := fmt.Sprintf("file:%s?_pragma=journal_mode(%s)&_pragma=synchronous(NORMAL)%s", path, mode, extra)
	db, err := sql.Open("sqlite", dsn)
	must(err)
	defer db.Close()
	db.SetMaxOpenConns(8)
	_, err = db.Exec("create table if not exists t (id integer primary key, v text)")
	must(err)
	_, err = db.Exec("insert into t (v) select hex(randomblob(50)) from (with recursive c(x) as (select 1 union all select x+1 from c where x < 5000) select x from c)")
	must(err)

	var res result
	var stop atomic.Bool
	var wg sync.WaitGroup
	for r := 0; r < 4; r++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for !stop.Load() {
				var n int
				if err := db.QueryRow("select count(*) from t").Scan(&n); err != nil {
					if strings.Contains(err.Error(), "SQLITE_BUSY") || strings.Contains(err.Error(), "locked") {
						atomic.AddInt64(&res.busyReads, 1)
					}
					continue
				}
				atomic.AddInt64(&res.reads, 1)
			}
		}()
	}
	start := time.Now()
	for i := 0; i < 2000; i++ {
		if _, err := db.Exec("insert into t (v) values (?)", fmt.Sprint(i)); err != nil {
			res.busyWrites++
			continue
		}
		res.writes++
		if st, err := os.Stat(path + "-wal"); err == nil && st.Size() > res.walBytesPeak {
			res.walBytesPeak = st.Size()
		}
	}
	res.writeTime = time.Since(start)
	stop.Store(true)
	wg.Wait()
	return res
}

func main() {
	dir, err := os.MkdirTemp("", "sqlite645")
	must(err)
	defer os.RemoveAll(dir)

	fmt.Println("2,000 single row inserts while 4 goroutines run select count(*) in a loop, busy_timeout 5s")
	fmt.Printf("%-14s %10s %12s %12s %10s %12s\n", "journal_mode", "writes", "write time", "reads done", "busy reads", "peak WAL")
	for _, mode := range []string{"DELETE", "WAL"} {
		r := run(dir, mode, "&_pragma=busy_timeout(5000)")
		fmt.Printf("%-14s %10d %12v %12d %10d %9.1f KB\n", mode, r.writes, r.writeTime.Round(time.Millisecond), r.reads, r.busyReads, float64(r.walBytesPeak)/1024)
	}

	// The WAL file and a checkpoint.
	path := filepath.Join(dir, "WAL.db")
	db, err := sql.Open("sqlite", "file:"+path+"?_pragma=journal_mode(WAL)&_pragma=wal_autocheckpoint(0)&_pragma=synchronous(NORMAL)")
	must(err)
	defer db.Close()
	db.SetMaxOpenConns(1)
	for i := 0; i < 3000; i++ {
		_, err := db.Exec("insert into t (v) values (?)", strings.Repeat("x", 200))
		must(err)
	}
	st, _ := os.Stat(path + "-wal")
	before := st.Size()
	var busy, logFrames, checkpointed int
	must(db.QueryRow("pragma wal_checkpoint(TRUNCATE)").Scan(&busy, &logFrames, &checkpointed))
	st, _ = os.Stat(path + "-wal")
	fmt.Printf("WAL with automatic checkpoints off: %.0f KB after 3,000 writes; wal_checkpoint(TRUNCATE) returned busy=%d, log=%d, checkpointed=%d and the WAL file is now %d bytes\n", float64(before)/1024, busy, logFrames, checkpointed, st.Size())

	var mode string
	must(db.QueryRow("pragma journal_mode").Scan(&mode))
	fmt.Println("journal_mode is stored in the file and survives reopening:", mode)
}

2,000 single row inserts while 4 goroutines run select count(*) in a loop, busy_timeout 5s
journal_mode       writes   write time   reads done busy reads     peak WAL
DELETE               2000        715ms          185          0       0.0 KB
WAL                  2000        145ms          567          0    8650.4 KB
WAL with automatic checkpoints off: 13342 KB after 3,000 writes; wal_checkpoint(TRUNCATE) returned busy=0, log=0, checkpointed=0 and the WAL file is now 0 bytes
journal_mode is stored in the file and survives reopening: wal

The DSN carries the settings: _pragma=journal_mode(WAL), _pragma=synchronous(NORMAL) and _pragma=busy_timeout(5000). This is the modernc.org/sqlite syntax for running a pragma on every new connection, which matters, because some pragmas are per connection and a pool opens connections as it likes. (The cgo driver mattn/go-sqlite3 uses _journal_mode=WAL and similar parameters.) Setting them in the DSN is the only reliable way to have them apply to every pooled connection.

run creates a table of 5,000 rows, starts four goroutines that run select count(*) from t in a loop, and meanwhile does 2,000 single row inserts, each its own transaction, which is what request handlers do. It runs once with journal_mode(DELETE) and once with WAL.

WAL was about five times faster for the writes, and the readers got three times as many queries done in the same run. Both modes finished without a single busy error, because the 5 second busy_timeout made waiting connections retry instead of failing. The difference is how long they waited. In rollback mode every write queued behind the readers and every read behind the writer; in WAL mode they overlapped.

Two things to remember about reading these numbers. The count(*) over 5,000 rows is cheap, so readers hold locks briefly; with longer reads the rollback mode would look worse. And synchronous(NORMAL) is part of the WAL result: with FULL, each commit syncs and writes are slower, though still not blocked by readers.

Checkpoints

The WAL file grows until a checkpoint copies its content back into the database file. By default SQLite checkpoints automatically when the log reaches 1,000 pages (about 4 MB with the default page size). The peak WAL of 8,650 KB in the run shows it growing past that, since a checkpoint can only copy pages no reader still needs, and four active readers delay it.

The second part of the program turns automatic checkpoints off (wal_autocheckpoint(0)) and writes 3,000 rows: the WAL reached 13,342 KB. Then pragma wal_checkpoint(TRUNCATE) ran. It returned busy=0, meaning it completed and was not blocked by a reader, and the WAL file was left at 0 bytes. The pragma has four modes: PASSIVE copies what it can without waiting, FULL waits for writers and readers, RESTART also resets the log for the next write, and TRUNCATE also shrinks the file to nothing.

Two operational facts follow. A long running read transaction prevents checkpoints from completing past its snapshot, so the WAL keeps growing while it lives; keep read transactions short, and never hold a *sql.Rows open across slow work. And the WAL file is part of the database: copying only app.db while the service runs can lose recent commits, and the -wal and -shm files must travel with it for a copy taken while it is open; use the backup approaches in Litestream and SQLite backups instead.

One writer: the pool settings that matter

WAL allows many readers and one writer. Go's pool does not know that, so a pool of eight connections doing writes will have connections waiting on each other, and a connection that starts a read transaction and then tries to write can fail immediately with SQLITE_BUSY_SNAPSHOT, which no busy_timeout fixes. The usual settings:

• Always set a busy_timeout, a few seconds, so a connection waits for the lock instead of failing.
• Use BEGIN IMMEDIATE for transactions that write (_txlock=immediate in the DSN), so the write lock is taken at the start and the timeout can work. The next article shows the failure it prevents.
• Consider two pools: one with SetMaxOpenConns(1) for writes, and one read only pool (mode=ro) for reads. A single writing connection serialises writes in Go, which is what SQLite does anyway, and removes lock contention.

Where WAL does not apply

• Network file systems. WAL uses shared memory (the -shm file) and needs all connections on the same host, with a file system that supports it. NFS and many container volume drivers do not work reliably. Keep the database on local disk.
• Read only media. WAL needs to create files next to the database unless the database is opened with specific options, so a database on a read only directory needs care.
• Very large transactions. A single huge write transaction makes a huge WAL, and the checkpoint afterwards is a large copy.
• Rolling back to the default. pragma journal_mode(DELETE) switches back when no other connection is open, and the mode setting persists in the file, as the program's last line shows by reading it back after reopening.

Checklist

1. DSN: journal_mode(WAL), synchronous(NORMAL), busy_timeout(5000), _txlock=immediate for writers.
2. One writing connection or a small pool with BEGIN IMMEDIATE, many readers.
3. Short read transactions, closed rows.
4. Leave automatic checkpoints on, and watch the WAL file size.
5. Back up with an online method, not a file copy.
6. Keep the file on local disk.

The behaviour is specified in the SQLite page on write ahead logging, which also documents the limits above, and the pragmas in the pragma reference.