Completed EXPLAIN QUERY PLAN causes SQLITE_BUSY at ANALYZE after an index transaction

[yazalulloa]

This was generated by AI during triage.

Summary

With a local file client, a completed EXPLAIN QUERY PLAN followed by a committed write transaction that creates an index causes a subsequent ANALYZE to fail with SQLITE_BUSY. Replacing EXPLAIN with the SELECT itself passes. Closing and reopening the client after EXPLAIN also passes.

This reproduces without Effect, application migrations, hosted services, or credentials. The failing client remains open throughout the sequence.

Environment

• Node v24.20.0
• Linux x64, glibc 2.43
• @libsql/client 0.17.4
• libsql 0.5.29, native @libsql/linux-x64-gnu 0.5.29

Reproduction

In an empty directory, install the pinned packages:

npm init -y
npm install --save-exact @libsql/client@0.17.4 libsql@0.5.29
npm ls @libsql/client libsql @libsql/linux-x64-gnu
node repro.mjs

Save this as repro.mjs. Each case uses a new disposable local database with 400 synthetic rows. Clients are closed and temporary directories removed in finally blocks.

import { createClient } from "@libsql/client";
import { mkdtemp, rm } from "node:fs/promises";
import { tmpdir } from "node:os";
import { join } from "node:path";

let failed = false;
for (const mode of ["select", "explain", "explain-reopen"]) {
  const dir = await mkdtemp(join(tmpdir(), "libsql-explain-"));
  const url = `file:${join(dir, "test.db")}`;
  let client = createClient({ url });
  let phase = "setup";
  try {
    await client.execute("CREATE TABLE bank_audit(id TEXT PRIMARY KEY, at TEXT)");
    await client.execute("WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<400) INSERT INTO bank_audit SELECT CAST(n AS TEXT),'2026' FROM s");
    phase = "probe";
    const query = "SELECT id FROM bank_audit ORDER BY at DESC,id DESC LIMIT 500";
    await client.execute(mode === "select" ? query : `EXPLAIN QUERY PLAN ${query}`);
    if (mode === "explain-reopen") {
      client.close();
      client = createClient({ url });
    }
    phase = "index transaction";
    const tx = await client.transaction("write");
    try {
      await tx.execute("CREATE INDEX idx ON bank_audit(at DESC,id DESC)");
      await tx.commit();
    } finally {
      tx.close();
    }
    phase = "ANALYZE";
    await client.execute("ANALYZE bank_audit");
    console.log(`${mode} PASS`);
  } catch (error) {
    failed = true;
    console.log(`${mode} FAIL at ${phase}: ${error.code} ${error.message}`);
  } finally {
    client.close();
    await rm(dir, { recursive: true, force: true });
  }
}
process.exitCode = failed ? 1 : 0;

Observed on 2026-09-10 using the installed versions above:

select PASS
explain FAIL at ANALYZE: SQLITE_BUSY SQLITE_BUSY: database is locked
explain-reopen PASS

The script exits 1 when any case fails; a fixed implementation should produce PASS for all three and exit 0. The index transaction commits successfully before the failure. The tested invocation used the repository's installed pinned packages; the npm commands above describe a standalone setup.

Expected behavior

Once execute("EXPLAIN QUERY PLAN ...") completes and the subsequent index transaction commits, ANALYZE should succeed, as it does with the SELECT control, without requiring client replacement or garbage collection.

Related reports and uncertainty

#228 describes prepared statements retaining locks after Database.close(), and #214 proposes statement lifecycle management. They may be related, but this case fails while the client is still open and closing/reopening avoids it. I have not tested #214 or established the exact native defect.

Upstream searches for EXPLAIN, SQLITE_BUSY, database-is-locked, and ANALYZE found no report of this exact sequence. Filing here because the native libsql package points to this tracker; please redirect if the fix belongs in libsql-client-ts.

Downstream tracking: https://github.com/yaz-org/bank-crawler/issues/1305. Our local migration proof currently performs plan inspection after its final write. We have not added production retries or changed journal/migration behavior.

[yazalulloa]

Tested the statement-lifecycle candidate from #214 at e4a845e29bec9541f13a5bda811b95a37ee68514 locally on 2026-09-10. It does not resolve the unchanged reproduction in this issue.

Installation	SELECT	EXPLAIN	EXPLAIN/reopen	Diagnostic exit
Released baseline	PASS	FAIL at ANALYZE, SQLITE_BUSY	PASS	1
Built #214 candidate	PASS	FAIL at ANALYZE, SQLITE_BUSY	PASS	1

Both EXPLAIN failures report SQLITE_BUSY: database is locked. The index transaction completes before ANALYZE fails.

Runtime for both runs: Node v24.20.0, Linux x64, glibc 2.43. Both use @libsql/client 0.17.4. Baseline native packages are libsql and @libsql/linux-x64-gnu 0.5.29. The candidate uses the checkout's libsql package version 0.6.0-pre.30, compiled from the commit above with Rust 1.88.0, resolved libsql/libsql-sys/libsql-ffi crates 0.9.30, and napi 2.16.17. This is a source build, not a published native package or a JavaScript-only replacement over the old binary.

Commands, run inside separate local scratch installations:

# Baseline and the initial candidate installation, each in its own directory:
npm install --save-exact @libsql/client@0.17.4 libsql@0.5.29
# Baseline:
node repro.mjs
# Candidate source checkout:
git clone https://github.com/tursodatabase/libsql-js.git
git -C libsql-js checkout e4a845e29bec9541f13a5bda811b95a37ee68514
# Inside libsql-js:
npm install --ignore-scripts
./node_modules/.bin/tsc --typeRoots ./node_modules/@types
CARGO_BUILD_JOBS=2 ./node_modules/.bin/napi build --platform --release
# In the candidate installation, replace only node_modules/libsql with a symlink
# to that built checkout; leave @libsql/client at 0.17.4. Then:
node repro.mjs

The successful native build exited 0. Rust and CMake 4.2.3 were installed in the local scratch area, with their directories supplied through PATH, CARGO_HOME, RUSTUP_HOME, and LD_LIBRARY_PATH. The explicit TypeScript type root prevents enclosing workspace types from contaminating this upstream build. No candidate implementation source changed.

Verified that the candidate client's libsql import resolves to the checkout's compat.js and that its loaded native module is the newly built libsql-js/libsql.linux-x64-gnu.node. Native binary SHA-256: 728a28b2353aa1a580c510bbd8dc2ea434805e8a5ea3cc97a4993aac839d7552. Resolved Cargo lockfile SHA-256: 46cc595ba40f93b1857a2164009ea9677376c1897b06e7dbcdbf1881706e523a.

Both runs used the exact reproduction from the issue body, SHA-256 e81f9ed2186f4b79095364a8ff2bc3a054feccd535ab02e984a47afd34e0ba76. Each case had a separate disposable local database and 400 synthetic rows. The reproduction closes clients and removes those directories in finally; the candidate's dedicated temporary root was empty after the run. No retries, forced GC, or additional client replacement were introduced. No hosted endpoint or credentials were used.

This result only says that #214 at this commit does not fix this sequence through @libsql/client 0.17.4. It does not establish the exact native resource-lifetime defect or rule out the intended benefits of explicit statement close. Downstream #1305 remains open pending another usable candidate that passes all three cases. No downstream production dependencies or mitigation changed.