↓ Skip to main content

How to Fix SQLite "Database is Locked" Errors in Go Concurrent Web APIs

How to Fix SQLite Database is Locked Errors in Go Concurrent Web APIs

I had SQLite humming along fine in local dev, then kicked off a load test against the Go API and watched my log stream turn into a wall of the same four words over and over: database is locked. Transactions rolling back, requests dropping, and that creeping feeling that maybe SQLite was never built for this and I’d just wasted a weekend.

It wasn’t SQLite. It was the config — or more accurately, the config I never touched, because the defaults are quietly built for a single-user desktop app circa 2005, not a Go API fielding concurrent goroutines.

The Default Trap
#

Out of the box, SQLite runs on a Rollback Journal, and underneath that journal is a locking state machine most people never look at: UNLOCKED, SHARED, RESERVED, PENDING, EXCLUSIVE. Multiple connections can hold SHARED locks at once — that’s your readers, all fine, all coexisting. The moment one connection wants to write, it grabs a RESERVED lock, which other readers can still tolerate for a bit. But to actually commit, that connection has to escalate through PENDING to EXCLUSIVE, and EXCLUSIVE means exactly what it sounds like — nobody else touches the file, not even to read, until the commit finishes.

That escalation window is short, usually milliseconds, but Go doesn’t care about milliseconds when it’s firing concurrent goroutines at a connection pool. database/sql opens that pool by default, so the instant your API handles real concurrent traffic, you’ve got multiple goroutines racing to hit EXCLUSIVE at the same time. SQLite doesn’t queue that gracefully. It just throws SQLITE_BUSY the second two writers collide, and your load test turns into a wall of identical errors.

Turning On WAL
#

The first real fix is switching the journaling mode from rollback to Write-Ahead Logging. In WAL mode, readers and writers stop blocking each other entirely — readers pull from the main database file while writers append changes to a separate -wal file sitting next to it. Multiple readers can query freely while a write is mid-flight, because they’re not even touching the same file the writer is appending to.

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
)

func OpenDB(dataSourceName string) (*sql.DB, error) {
    db, err := sql.Open("sqlite3", dataSourceName+"?_journal_mode=WAL&_busy_timeout=5000&_synchronous=NORMAL")
    if err != nil {
        return nil, err
    }

    // Force serialized writes to prevent SQLITE_BUSY under Go's connection pool
    db.SetMaxOpenConns(1)
    return db, nil
}

_journal_mode=WAL is the mode switch itself. _busy_timeout=5000 tells SQLite to sit and wait up to five seconds for a lock to clear instead of panicking into SQLITE_BUSY the instant it hits contention — worth noting the timeout only helps if the lock actually clears within that window, it won’t save you from a genuinely stuck writer. I threw in _synchronous=NORMAL too, which is worth explaining rather than just dropping in: under the default rollback journal, NORMAL synchronous can leave you with a corrupted database if the OS crashes mid-write. Under WAL specifically, that risk goes away — NORMAL under WAL only risks losing the most recent transactions on a power loss or OS crash, not corruption, and you get a real write-throughput gain for it. If your use case can’t tolerate even that, stay on FULL, but most web APIs can eat it.

The Checkpoint You Forgot About
#

Here’s the part that bites people a few weeks after they think they’ve fixed it. WAL doesn’t write changes into the main database file as they happen — it appends them to the -wal file, and a separate process called a checkpoint periodically folds those changes back into the main file. If you’ve got a long-running read transaction open somewhere — an analytics query, a slow report, a connection somebody forgot to close — that checkpoint can’t fully complete, because it can’t safely overwrite WAL frames a reader still needs. The -wal file just keeps growing. I’ve seen it balloon into gigabytes on a database that’s logically a few megabytes of real data, and the fix isn’t glamorous: keep read transactions short, and if you’re running anything long-lived, consider PRAGMA wal_checkpoint(TRUNCATE) on a schedule rather than hoping SQLite’s automatic checkpoint keeps up under load.

BEGIN IMMEDIATE, or You’ll Still Get Burned
#

WAL mode alone doesn’t save you from every collision. SQLite’s default transactions are deferred — the write lock isn’t actually grabbed until the first UPDATE, INSERT, or DELETE runs. So if two goroutines both open a transaction, both read the same row, and both then try to promote to a write, the second one loses and crashes right there, and no busy_timeout saves it — that’s a snapshot conflict, not a lock wait, and the two aren’t the same failure.

The fix is forcing BEGIN IMMEDIATE at the start of the transaction instead of letting it stay deferred — that grabs the reserved write lock up front, so a competing writer either fails fast or waits out the busy timeout before it even gets to the expensive part. In go-sqlite3, the clean way to do this is a DSN flag, _txlock=immediate, which makes every db.Begin() call issue BEGIN IMMEDIATE automatically instead of the default deferred BEGIN:

db, err := sql.Open("sqlite3", dataSourceName+"?_journal_mode=WAL&_busy_timeout=5000&_synchronous=NORMAL&_txlock=immediate")

With that set, your transaction code doesn’t change at all — it just behaves correctly:

func PerformUpdate(db *sql.DB, userID int, status string) error {
    // _txlock=immediate on the DSN means this Begin() is already BEGIN IMMEDIATE
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    _, err = tx.Exec("UPDATE users SET status = ? WHERE id = ?", status, userID)
    if err != nil {
        return err
    }

    return tx.Commit()
}

Stop Setting MaxOpenConns(100) on SQLite
#

I get why people do it — SetMaxOpenConns(100) is muscle memory from Postgres or MySQL, where it’s basically free performance. On SQLite it’s closer to self-sabotage. Even with WAL running, there’s still only one writer slot. Open twenty connections and let them all try to write, and nineteen of them are just going to sit there hammering the lock and throwing errors, or worse, silently stacking up busy_timeout waits until requests start timing out upstream.

Set db.SetMaxOpenConns(1) on your write handle — that’s the serialized pipeline writes need to go through. If you need real read throughput, open a second connection pool pointed at the same file with mode=ro, and let Go spread reads across that pool freely while every write funnels through the single handle. Two sql.DB instances, same file, different jobs — one tuned for a single serialized writer, one tuned for however many concurrent readers your traffic actually needs.

When You Still Hit SQLITE_BUSY
#

Even with all four of these in place, under genuinely heavy write bursts you can still see an occasional SQLITE_BUSY once the busy_timeout window itself gets exhausted. That’s not a sign something’s broken — it’s SQLite telling you the write serialization queue is backed up longer than your timeout allows. The honest fix at that point isn’t a longer timeout, it’s a retry wrapper around your write calls with a short backoff, so a request that hits contention gets one or two more attempts instead of failing outright. It’s a last line of defense, not a replacement for the config above — get the four settings right first, and you’ll rarely need the retry to actually fire.

Melvin
Author
Melvin
I am a software developer building high-performance local AI tools and web architectures. I created Sablegrid, a platform that automates project scoping end to end. Stellar Tech Labs is where I write up what I learn along the way.

Related

Comments