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

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.

Start by running the provided repro.mjs with the pinned @libsql/client and libsql versions, confirming that only the EXPLAIN path fails at ANALYZE. Trace the client.execute and write-transaction lifecycle around EXPLAIN, commit, and ANALYZE, using related issues #228 and #214 for context. Done means all three cases print PASS and the script exits 0 without reopening the client.