Database.close() does not finalize prepared statements — WAL file lock held until GC

[abhiaiyer91]

Environment

• libsql 0.5.29 (also reproduces through @libsql/client 0.17.4, which wraps this package for local files)
• Node v22.22.2, macOS arm64 (also observed on Linux)

Summary

Database.close() does not finalize outstanding prepared statements. If a connection ever prepared a statement, its underlying connection — and therefore its WAL shared-memory lock on the database file — stays alive after close() until GC happens to run finalizers. Lock release after close() is effectively nondeterministic.

A connection that only ever used exec() releases its lock on close() as expected, which isolates the pin to prepared statements.

Repro

const Database = require('libsql');

// 1. Open, prepare any statement, close.
{
  const db = new Database('test.db');
  db.exec('PRAGMA journal_mode = WAL');
  db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY)');
  db.prepare('SELECT 1').get(); // <-- remove this line and everything works
  db.close();
}

// 2. A second connection should now be the only one and able to leave WAL mode.
const db2 = new Database('test.db');
db2.exec('PRAGMA busy_timeout = 500');
db2.exec('PRAGMA journal_mode = DELETE'); // throws: database is locked

Observed behavior (node --expose-gc, probing repeatedly):

FAIL (right after close()): database is locked
FAIL (gc attempt 1 + 200ms): database is locked
OK (gc attempt 2 + 200ms)

So even one explicit GC pass isn't reliably enough — the statement handles are only collected on a later cycle. Without --expose-gc (i.e. any production process) there is no way to force release.

If the db.prepare('SELECT 1').get() line is removed (exec-only connection), the second connection acquires exclusive access immediately after close().

Impact

Any operation that needs exclusive access to the file after closing a connection is broken in-process:

• switching journal_mode WAL → DELETE (clean shutdown)
• compacting/swapping the database file (VACUUM INTO + rename) safely
• deleting the file right after close() (the Windows EBUSY from Drop libsql::Database on close() to release file locks #213)

@libsql/client inherits this: it prepares a statement for every execute() and never finalizes, so every closed local client leaves a GC-pinned lock behind.

We currently work around it by probing exclusivity with a separate exec-only connection and verifying the outcome from the SQLite file header bytes, which works but shouldn't be necessary.

Related work

• Drop libsql::Database on close() to release file locks #213 (merged) fixed the analogous problem for the libsql::Database handle itself.
• Add statement lifecycle management and explicit close support #214 (open) — statement lifecycle tracking via WeakRefs so close() closes all associated statements — looks like it fixes exactly this. 🎉

Two questions:

1. Is there anything blocking Add statement lifecycle management and explicit close support #214 from landing?
2. Since 0.6.0 is still in pre-release, would you consider backporting the statement-finalization fix to a 0.5.x patch release?

Happy to test a build against our reproduction.

[dcostenco]

Confirming this on win32-x64, where the consequence is harder than a lock-visibility problem: the database file cannot be unlinked or renamed after close().

Environment: libsql 0.5.29 via @libsql/client 0.17.2, Node v22.23.1, windows-latest (GitHub Actions).

Six experiments, each isolating one hypothesis. Every row is unlink() attempted immediately after close() resolves:

experiment	-shm	-wal	db
E1 close() only	EBUSY	EBUSY	EBUSY
E2 wal_checkpoint(TRUNCATE) + close	EBUSY	EBUSY	EBUSY
E3 close + 3s wait	EBUSY	EBUSY	EBUSY
E4 raw createClient, WAL	EBUSY	EBUSY	EBUSY
E5 raw createClient, journal_mode=DELETE	absent	absent	EBUSY

Three things this adds to the report:

1. It is not the WAL sidecars. E5 disables WAL entirely — -wal/-shm never exist — and the primary database file still reports EBUSY. The pin is on the db handle itself, not the shared-memory file.
2. It is not a timing race. E3 waits 3 seconds. A retry loop (10 × 100ms) also failed. Consistent with your GC finding: it is released nondeterministically, so waiting a fixed interval cannot fix it.
3. The sharing mode still permits read and write. Copying from the open file works, and so does copying onto it. Only unlink/rename are blocked — so on Windows the practical failure is anything that deletes or replaces the file by path (temp-dir cleanup, file-level restore-by-rename).

Your macOS repro symptom — PRAGMA journal_mode = DELETE throwing "database is locked" right after close() — reproduces for me on macOS too, via a connection that used prepare(). Same root cause; win32 just turns it into a filesystem error instead of a lock error.

Happy to share the standalone repro script if useful.