file: — SQLITE_BUSY-failed statement is never finalized; next transaction cannot commit and its abandoned BEGIN holds the write lock forever

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/prisma#30028 — but the underlying behavior is reproducible with @libsql/client alone).

Two findings from the repro below:

1. 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 tursodatabase/libsql-js#228.
2. 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.

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).

Start by running the repro.mjs example against a local file: database, then trace client.transaction(), statement error handling, and tx.commit(). Check that failed statements and failed commits no longer leave SQL statements in progress or an abandoned write lock; rerun the recovery writes to confirm the database becomes usable without an explicit rollback.