Repository navigation
Description
On a local file: database, a statement that fails with SQLITE_BUSY is never finalized on the client's shared native connection. The next transaction() on that client then fails at commit() with:
SQLITE_BUSY: cannot commit transaction - SQL statements in progress
and — because transaction() hands the current native connection to the transaction and nothing ever closes it (#350) — if the caller does not explicitly roll back, the open BEGIN holds the database write lock until the process exits. Every subsequent write from any connection fails SQLITE_BUSY immediately.
This is the mechanism behind two production outages for us (the caller was @prisma/adapter-libsql, which never issues a successful rollback after a failed commit — filed as prisma/orm#30028 — but the underlying behavior is reproducible with @libsql/client alone).
Two findings from the repro below:
- The priming step is just one transient
SQLITE_BUSY(in production: a Litestream checkpoint briefly holding the write lock). The client looks healthy afterwards; the corruption only surfaces at the next transaction's commit. Likely the same statement-finalization gap as Database.close() does not finalize prepared statements — WAL file lock held until GC libsql-js#228. - An explicit
rollback()after the failed commit does succeed and releases the lock — so the state is recoverable, but only if the caller knows to do this. Callers that treat a failed commit as "transaction closed" (a reasonable assumption, and what Prisma's engine does) leak the lock permanently.
repro.mjs — @libsql/client 0.17.4, Node 24, needs `sqlite3` CLI on PATH
import { execFile } from 'node:child_process'
import { createClient } from '@libsql/client'
const client = createClient({ url: 'file:./repro2.db' })
await client.execute('CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY, v TEXT)')
await client.execute('PRAGMA journal_mode = WAL')
await client.execute("INSERT OR REPLACE INTO t (id, v) VALUES (1, 'base')")
const write = async (label) => {
try {
await client.execute({ sql: 'UPDATE t SET v = ? WHERE id = 1', args: [label] })
console.log(`${label}: OK`)
} catch (e) {
console.log(`${label}: FAIL ${String(e.message).slice(0, 80)}`)
}
}
// 1. Prime: a write fails SQLITE_BUSY while another process holds the lock.
const holder = new Promise((res) =>
execFile('bash', ['-c', `(echo "BEGIN IMMEDIATE; UPDATE t SET v='held' WHERE id=1;"; sleep 2; echo "COMMIT;") | sqlite3 repro2.db`], () => res()),
)
await new Promise((r) => setTimeout(r, 400))
await write('during-lock') // FAIL SQLITE_BUSY — the priming statement
await holder
await write('after-release') // OK — client looks healthy
// 2. Transaction with concurrent writes on the same client.
const tx = await client.transaction('deferred')
try {
await tx.execute("UPDATE t SET v = 'itx' WHERE id = 1")
await Promise.all([write('concurrent-1'), write('concurrent-2')])
await tx.commit()
console.log('tx: committed')
} catch (e) {
console.log(`tx commit: FAIL ${String(e.message).slice(0, 90)}`)
// NOTE: uncommenting this recovers — but a caller that assumes a failed
// commit closed the transaction (e.g. Prisma) leaks the write lock forever:
// await tx.rollback()
}
// 3. Recovery probes — all fail until the process exits.
for (let i = 1; i <= 4; i++) {
await new Promise((r) => setTimeout(r, 700))
await write(`recovery-${i}`)
}
process.exit(0)Output (abandoned-tx variant):
during-lock: FAIL SQLITE_BUSY: database is locked
after-release: OK
concurrent-1: FAIL SQLITE_BUSY: database is locked
concurrent-2: FAIL SQLITE_BUSY: database is locked
tx commit: FAIL SQLITE_BUSY: cannot commit transaction - SQL statements in progress
recovery-1: FAIL SQLITE_BUSY: database is locked
recovery-2: FAIL SQLITE_BUSY: database is locked
recovery-3: FAIL SQLITE_BUSY: database is locked
recovery-4: FAIL SQLITE_BUSY: database is locked
Suggested fixes, any of which would break the chain: finalize failed statements before returning the error; make commit() roll back + close the detached connection when it fails with SQL statements in progress; or close the detached connection when the Transaction object is GC'd (#350 / libsql-js#230 territory).
Activity
zsilbi commentedon Sep 8, 2026
Confirming this on
@libsql/client0.18.0, nativelibsql0.5.29, Node 24.15.0, Linux x86_64.This reproduces with two clients in one process. No external SQLite CLI or sleeps are needed.
An additional detail for 0.18.0: calling
rollback()andclose()after the failed commit does not restore recovery in this reproduction.Reproduction
Observed output:
Expected: after the first client commits, the second client’s next transaction can insert and commit successfully.
Additional findings
transaction()returns its connection to the pool, allowing later attempts to reuse it.commit()releases its transaction handle infinally, including on failure. A subsequentrollback()sees a null handle and returns. Pool release already attempts SQL rollback whendb.inTransaction, but the reproduction still fails despite that cleanup.libsql0.5.29 alone, a faileddb.prepare("BEGIN IMMEDIATE").run()causes a later commit on that connection to fail. Replacing only that failed operation withdb.exec("BEGIN IMMEDIATE")allows the later transaction to commit. This points to cleanup of failed prepared statements.db.execfor internal BEGIN statements fixed the failed-BEGIN case experimentally. However, failed ordinary writes can cause the same later commit failure, so this is only a partial workaround.Automatically reconnecting the client is unsuitable as a general recovery strategy: it closes other active transactions and loses private in-memory state.
ejc3 commentedon Sep 21, 2026
This adds to the report above: a reproduction against the
libsqlbinding alone, the lines that cause it, a control that shows a reset is what is missing, and the smallest fix. Related: tursodatabase/libsql-js#228.Versions
libsql0.5.29 (tag v0.5.29, commit 55bee86), built on the Rust crateslibsql,libsql-sysand
libsql-ffi0.9.30 (bundled SQLite 3.45.1).@libsql/client0.15.15. Node 22, Linux x64.On
@libsql/client0.18.0 (published 2026-09-02)repro-client.mjsfails the same way,although that client's local connections are now a pool. Read, not
run: the binding's main branch (0.6.0-pre.42) still resets a statement before it runs and not
after its step fails.
What happens
BEGIN IMMEDIATE. After B's busy timeout the step fails withSQLITE_BUSY: database is locked. That is correct.COMMITwithSQLITE_BUSY: cannot commit transaction - SQL statements in progress, a readtransaction and a write transaction alike, although B holds nothing and the lock is free.
has finalized the failed statement object, which needs a collection and then a turn of the
event loop. A process cannot force that.
Through
@libsql/clientthe same thing happens toclient.batch(): one write batch thatfails busy makes the following batches on that client fail, read and write. A lone
execute()is not refused, because it sends noCOMMITof its own.Reproductions
Each is one file with no other dependency.
package.jsonpins the two packages. The files are below the list.repro-binding.mjs: the binding alone. Withnode --expose-gcit also shows that acollection followed by a turn of the event loop ends the condition, and that a collection
with no turn of the event loop does not.
repro-client.mjs: the same through@libsql/client.repro-reset-control.mjs: the control. The statement object that failed, run a secondtime, works and leaves the connection whole with no garbage collection, because the binding
resets a statement before it runs it.
package.json
{ "name": "libsql-busy-statement-repro", "private": true, "type": "module", "dependencies": { "@libsql/client": "0.15.15", "libsql": "0.5.29" } }repro-binding.mjs
repro-client.mjs
repro-reset-control.mjs
Output of
node --expose-gc repro-binding.mjs:Cause, by file and line
libsql-ffi-0.9.30/bundled/src/sqlite3.c. A statement that starts running iscounted:
db->nVdbeActive++; if( p->readOnly==0 ) db->nVdbeWrite++;(lines 92253-92254),and
BEGIN IMMEDIATEis not read-only, because itsOP_Transactioncarries a write flag(line 86826). When
OP_Transactioncannot get its lock it does not halt the statement. Itsaves the program counter and returns, so that the caller can step again:
if( (rc&0xff)==SQLITE_BUSY ){ p->pc = ...; p->rc = rc; goto vdbe_return; }(lines98280-98284). The counts are given back only when the statement halts, which after this
return means
sqlite3_resetorsqlite3_finalize(lines 89404-89405).COMMITrefuseswhile any writing statement is counted:
else if( desiredAutoCommit && db->nVdbeWrite>0 )with the message above and
SQLITE_BUSY(lines 98177-98184).libsql-0.9.30/src/local/statement.rslines 53-64:runbinds, steps andon any code but DONE or ROW returns the error. It does not reset.
libsql-jssrc/statement.rslines 78-91 (js_run):raw_stmt.reset()comesBEFORE
raw_stmt.run(params), and when the run fails the function throws and returns. Thesame holds for
js_getand the rows paths. A statement is finalized when its JavaScriptobject is collected (
libsql-sys-0.9.30/src/statement.rslines 22-35,Drop), and thebinding offers JavaScript neither a reset nor a finalize.
@libsql/clientlib-esm/sqlite3.jslines 245-299 (executeStmt): every callprepares a new statement, runs it once and drops the reference, so the statement that
failed is never run again and nothing but the collector ever resets it.
Smallest fix
Reset the statement when its step fails, in the binding (
js_run,js_get, the rows pathsand
rowsNext) or one layer down in the crate'srun,executeandquery. better-sqlite3,whose API the binding follows, resets after every execution, failed or not. A reset of a
statement that failed busy gives the counts back and changes nothing else.
What a caller can do until then
Replace the connection after a failed call (
client.reconnect()on a file database), withcare: a reconnect opens the client's path again, so a relative path opened before the process
changed directory, a file removed or replaced while it was open, and an empty path (a private
temporary database) each land on another database. Resolve the path first, check that the file
is still the one that was open, and never reconnect a database that lives in its one
connection:
:memory:, an empty path. On 0.15 close the client before reconnecting, because afailed open otherwise leaves it holding a closed database, and reading
inTransactionon aclosed database aborts the process.
antialias commentedon Oct 30, 2026
Also hitting this on
@libsql/client0.18.0 withlibsql0.5.29 (file:, WAL), behind a retry loop that waits out busy errors. One effect worth adding: a plain write can be lost silently, not only a later COMMIT failing.After a statement fails with
SQLITE_BUSY,inTransactionstill readsfalse, soConnectionPool.release()puts the connection back and the pool hands it out next. A retriedclient.execute("INSERT …")then resolves, and the same client reads the row back. But it ran inside the failed statement's implicit transaction, so it never commits: other connections don't see it and can't write, and when GC finalizes the failed statement the write is rolled back. tursodatabase/libsql-js#245 shows the same at the binding level.Repro (client level)
Output:
With the connection closed on a busy error (below), lines 3, 4 and 6 read
[ 2 ],okandok.The root fix looks like tursodatabase/libsql-js#238 (reset a statement after a failed step). Until that ships, we close the connection in the client on any busy error, so
release()drops it. If a fix here takes that route, three places need it besidesexecuteStmt()'scatch:executeMultiple()'scatchalso just maps and rethrows.migrate()'sfinallyreadsdb.inTransactionwithout checkingdb.open. Once a busy error has closed the connection, that read aborts the process, as the comment inSqlite3Transactionnotes.release()also belongs in an innerfinally, so the connection is released even when the ROLLBACK or the PRAGMA throws:(e.rawCode & 0xff) === 5, so extended codes such asSQLITE_BUSY_SNAPSHOTcount too.Ken-Michi commentedon Nov 10, 2026
Adding how this shows up on @libsql/client 0.18.0, where the sqlite3 client now pools connections (#342).
Because a statement that failed with
SQLITE_BUSYstays "in progress" on its native connection (tursodatabase/libsql-js#240), andConnectionPool.release()only checksdb.inTransaction, the poisoned connection goes back to the idle list. With 0.18.0 the nexttransaction()usually gets the same connection back, so an application-level retry loop fails on every attempt:On 0.14.0 the first retry fails the same way, but the transaction takes the connection with it, so the second retry gets a fresh connection and succeeds. In our test (two clients on one WAL file, concurrent write transactions with retries), 0.14.0 failed 0/30 runs and 0.18.0 failed 30/30 (macOS, Node 24.22.1). Reads on the poisoned connection also keep the old snapshot until GC.
The workaround we applied as a local patch of
lib-esm/sqlite3.jsandlib-cjs/sqlite3.js:executeStmt/executeMultiple, when the mapped error code isSQLITE_BUSYorSQLITE_LOCKED, remember the connection in aWeakSet.ConnectionPool.release(db), run the existingROLLBACKfirst ifdb.inTransaction. Then, if the connection is in that set, or theROLLBACKthrew, close it instead of returning it to the pool. If a caller is waiting, give it a newly opened connection.The order matters: closing without the
ROLLBACKwhile a stranded statement exists defers the real close, so the connection keeps its write lock until GC. With this patch, the 30-run test passes 30/30. ABEGIN IMMEDIATEthat fails before any transaction starts is not fully covered when databases areATTACHed: the main database lock stays held until GC.