SQLite Database Is Locked in Go and How to Fix It Properly

The error looks like this:

database is locked (5) (SQLITE_BUSY)

or, with a different code in brackets, database is locked (517), or the older database table is locked. It appears in a Go service that works perfectly in development, where one request runs at a time, and fails under load, where several do. It is the most common SQLite problem in Go and it has a small number of causes, each with a specific fix. This article reproduces each with the pure Go modernc.org/sqlite driver v1.50.0 on Go 1.25.6, shows what it prints, and gives the setting that removes it. The WAL background is in SQLite WAL mode, and the database is introduced in SQLite in Go. It sits within the database cornerstone.

What the error means

SQLite lets many connections read and one connection write. If a connection needs a lock that another holds, SQLite either waits or returns SQLITE_BUSY, and database is locked is how drivers print that code. It does not mean the file is damaged or that a process crashed holding it. It means two connections wanted the write lock at once, or one wanted to write after reading data that has since changed. Go makes this likely because database/sql is a pool: several goroutines each get their own connection, and so they compete.

package main

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

	_ "modernc.org/sqlite"
)

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

func open(path, query string) *sql.DB {
	db, err := sql.Open("sqlite", "file:"+path+"?"+query)
	must(err)
	return db
}

func short(err error) string {
	if err == nil {
		return "ok"
	}
	s := err.Error()
	if i := strings.Index(s, "\n"); i > 0 {
		s = s[:i]
	}
	return s
}

func main() {
	ctx := context.Background()
	dir, err := os.MkdirTemp("", "sqlite646")
	must(err)
	defer os.RemoveAll(dir)
	path := filepath.Join(dir, "app.db")

	setup := open(path, "_pragma=journal_mode(WAL)")
	_, err = setup.Exec("create table counters (id integer primary key, n integer not null)")
	must(err)
	_, err = setup.Exec("insert into counters values (1, 0)")
	must(err)
	setup.Close()

	// 1. A second writer while the first holds a write lock, with no busy timeout.
	a := open(path, "_pragma=busy_timeout(0)")
	b := open(path, "_pragma=busy_timeout(0)")
	ta, _ := a.BeginTx(ctx, nil)
	_, err = ta.Exec("update counters set n = n + 1")
	must(err)
	_, err = b.Exec("update counters set n = n + 1")
	fmt.Println("1. second writer, busy_timeout 0:", short(err))

	// 2. The same, with a busy timeout: it waits for the first to finish.
	c := open(path, "_pragma=busy_timeout(2000)")
	go func() { time.Sleep(300 * time.Millisecond); ta.Commit() }()
	start := time.Now()
	_, err = c.Exec("update counters set n = n + 1")
	fmt.Printf("2. second writer, busy_timeout 2s: %s after waiting %v\n", short(err), time.Since(start).Round(50*time.Millisecond))

	// 3. A deferred transaction that reads and then writes can fail at once, whatever the timeout.
	d := open(path, "_pragma=busy_timeout(2000)")
	d.SetMaxOpenConns(2)
	tx, _ := d.BeginTx(ctx, nil) // BEGIN, deferred
	var n int
	must(tx.QueryRow("select n from counters where id = 1").Scan(&n))
	other := open(path, "_pragma=busy_timeout(2000)")
	_, err = other.Exec("update counters set n = n + 10") // commits while tx holds its read snapshot
	must(err)
	start = time.Now()
	_, err = tx.Exec("update counters set n = ? where id = 1", n+1)
	fmt.Printf("3. read then write in a deferred transaction after another writer committed: %s (after %v)\n", short(err), time.Since(start).Round(50*time.Millisecond))
	tx.Rollback()

	// 4. BEGIN IMMEDIATE takes the write lock first, so the busy timeout can do its job.
	imm := open(path, "_pragma=busy_timeout(5000)&_txlock=immediate")
	imm.SetMaxOpenConns(8)
	var wg sync.WaitGroup
	var mu sync.Mutex
	failed := 0
	var startN int
	must(imm.QueryRow("select n from counters where id = 1").Scan(&startN))
	start = time.Now()
	for i := 0; i < 20; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			tx, err := imm.BeginTx(ctx, nil)
			if err == nil {
				var n int
				if err = tx.QueryRow("select n from counters where id = 1").Scan(&n); err == nil {
					_, err = tx.Exec("update counters set n = ? where id = 1", n+1)
				}
				if err == nil {
					err = tx.Commit()
				} else {
					tx.Rollback()
				}
			}
			if err != nil {
				mu.Lock()
				failed++
				mu.Unlock()
			}
		}()
	}
	wg.Wait()
	var final int
	must(imm.QueryRow("select n from counters where id = 1").Scan(&final))
	fmt.Printf("4. 20 concurrent read-then-write transactions with _txlock=immediate: %d failed, counter moved by %d, in %v\n", failed, final-startN, time.Since(start).Round(time.Millisecond))

	// 5. One writer connection, many readers: the pattern that makes the error disappear in Go.
	writer := open(path, "_pragma=busy_timeout(5000)&_txlock=immediate")
	writer.SetMaxOpenConns(1)
	readers := open(path, "_pragma=busy_timeout(5000)&mode=ro")
	readers.SetMaxOpenConns(4)
	failed = 0
	for i := 0; i < 200; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			if i%4 == 0 {
				if _, err := writer.Exec("update counters set n = n + 1"); err != nil {
					mu.Lock()
					failed++
					mu.Unlock()
				}
				return
			}
			var n int
			if err := readers.QueryRow("select n from counters").Scan(&n); err != nil {
				mu.Lock()
				failed++
				mu.Unlock()
			}
		}()
	}
	wg.Wait()
	fmt.Println("5. a single connection pool for writes and a read only pool for reads, 50 writes and 150 reads at once: failures", failed)
	_ = errors.New
}

1. second writer, busy_timeout 0: database is locked (5) (SQLITE_BUSY)
2. second writer, busy_timeout 2s: ok after waiting 350ms
3. read then write in a deferred transaction after another writer committed: database is locked (517) (after 0s)
4. 20 concurrent read-then-write transactions with _txlock=immediate: 0 failed, counter moved by 20, in 115ms
5. a single connection pool for writes and a read only pool for reads, 50 writes and 150 reads at once: failures 0

Cause 1: no busy timeout

The first case opens two connections to the same file, with busy_timeout(0). The first begins a transaction and updates a row, holding the write lock. The second tries to update and gets database is locked (5) (SQLITE_BUSY) immediately. With a timeout of zero, a connection that cannot get the lock gives up at once, which is SQLite's default.

Fix: set busy_timeout on every connection, a few seconds. In the second case the same attempt, with busy_timeout(2000), waited 350ms while the first transaction committed, and then succeeded. The timeout is a retry loop inside SQLite: the connection keeps trying for up to that long.

Set it in the DSN so that it applies to every pooled connection, not with a single db.Exec("pragma busy_timeout = 5000"), which sets it on whichever connection happened to run that statement:

db, err := sql.Open("sqlite", "file:app.db?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)")

(The syntax _pragma=name(value) is for modernc.org/sqlite; the cgo driver mattn/go-sqlite3 uses _busy_timeout=5000.) This one setting fixes most reports of the error, and it is also the one people skip because their tests pass.

Cause 2: a transaction that reads, then writes

The third case is the one that survives a busy timeout, and it confuses everyone who meets it. A transaction begins with BEGIN, which is deferred: no lock is taken yet. It reads a row, which takes a read snapshot. Meanwhile another connection writes and commits. Now the first transaction tries to write, and SQLite cannot grant it the write lock, because its snapshot is out of date: committing would overwrite changes it never saw. The lab output:

database is locked (517)

after 0s. Code 517 is SQLITE_BUSY_SNAPSHOT, and it returned at once although busy_timeout(2000) was set. Waiting cannot help, because the snapshot will never become current. The only way out is to roll back and retry the whole transaction.

Fix: take the write lock at the start. BEGIN IMMEDIATE acquires the write lock when the transaction begins, before any read, so the busy timeout can do its work: a connection that needs to write waits at the start, then runs on current data. In modernc.org/sqlite, put _txlock=immediate in the DSN so that db.BeginTx issues BEGIN IMMEDIATE. (The cgo driver's equivalent is _txlock=immediate as well.)

In the fourth case, 20 goroutines each ran a read then write transaction against the same counter with _txlock=immediate and busy_timeout(5000): 0 failed, and the counter moved by exactly 20, in 115ms. The transactions queued on the write lock and ran one after another, each reading the previous one's result. The rule: any transaction that might write should start as immediate. Read only transactions can stay deferred, and in WAL mode they never block.

Cause 3: too many connections doing writes

Even with both fixes, a pool of many connections used for writes spends time waiting on the lock, and every wait is a goroutine and a connection held. SQLite writes one at a time, so give it exactly one writer.

The last case does that with two pools. The write pool is limited with SetMaxOpenConns(1), so all writes queue in Go, inside the database/sql pool, with no lock contention in SQLite at all. The read pool is opened read only (mode=ro) with a few connections. 200 goroutines then ran, a quarter of them writing: 50 writes and 150 reads at the same time, with 0 failures.

This layout is also the best performance setup for a Go service on SQLite in WAL mode: reads scale with the read pool and are never blocked by the writer, and writes are serialised without errors. The cost is that you must route statements correctly, so wrap the two pools in a small type that exposes Read() and Write() handles, and make it hard to run a write on the read pool, which fails with attempt to write a readonly database.

Other causes to rule out

• A forgotten rows.Close(), tx.Rollback() or Conn.Close(). An open Rows keeps a read lock; an abandoned write transaction keeps the write lock until the connection is closed, and every other writer then waits until the timeout and fails. This is the connection leak of connection leaks, and here it shows up as lock errors. Use defer rows.Close() and defer tx.Rollback().
• A long running transaction holding the write lock while it does slow work, such as a network call. Keep write transactions to the database work only.
• Another process with the file open: a CLI, a backup tool, a database browser. Check with lsof app.db. In rollback mode a reader blocks writers; in WAL mode only a writer does.
• A network file system or a container volume that does not honour locks, which causes errors and corruption. Keep the database on a local disk.
• Opening the file with different journal modes from different processes. Choose WAL and use it everywhere.

A checklist that stops the error

1. busy_timeout of several seconds in the DSN of every pool.
2. journal_mode(WAL) and synchronous(NORMAL), as in the WAL article.
3. _txlock=immediate for any pool used for writes.
4. One writer connection, a read only pool for reads.
5. Short transactions, closed rows, deferred rollbacks.
6. Retry the whole transaction on SQLITE_BUSY or SQLITE_BUSY_SNAPSHOT as a last resort, as you would for a serialization failure in PostgreSQL.
7. A test that runs a few dozen concurrent writers against a temporary file, like the lab, and asserts zero errors and the right final count.

The codes and the locking states are documented in the SQLite pages on result codes, locking and concurrency and transactions, which describe the deferred and immediate behaviour shown above.