Failed COMMIT leaves transaction open and breaks subsequent transactions

[chrispader]

Summary

When COMMIT fails, transaction() marks the transaction as finished before executing the SQL. The catch block then skips rollback, leaving SQLite inside the failed transaction. A later transaction on the same connection fails with cannot start a transaction within a transaction.

Verified on main at ad8b835 (v9.7.0).

Code evidence

transaction.ts#L44-L52 sets isFinished = true before execute(dbName, 'COMMIT').

If that execute throws, control reaches transaction.ts#L81-L90. The rollback guard is if (!isFinished), so rollback is skipped.

SQLite can legitimately fail COMMIT, including for deferred foreign-key constraints, I/O errors, and busy/locking conditions. A failed commit can leave the transaction active.

Smallest reliable reproducer

const db = open({ name: 'commit-failure.sqlite' })
db.execute('PRAGMA foreign_keys = ON')
db.execute('CREATE TABLE parent (id INTEGER PRIMARY KEY)')
db.execute(`
  CREATE TABLE child (
    parent_id INTEGER
      REFERENCES parent(id)
      DEFERRABLE INITIALLY DEFERRED
  )
`)

await expect(
  db.transaction(async (tx) => {
    tx.execute('INSERT INTO child(parent_id) VALUES (?)', [404])
  }),
).rejects.toThrow(/FOREIGN KEY constraint failed/)

// Expected: starts cleanly after the failed transaction was rolled back.
// Observed: "cannot start a transaction within a transaction".
await db.transaction(async (tx) => {
  tx.execute('SELECT 1')
})

The underlying SQLite sequence is deterministic:

PRAGMA foreign_keys = ON;
CREATE TABLE parent(id INTEGER PRIMARY KEY);
CREATE TABLE child(parent_id INTEGER REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED);
BEGIN;
INSERT INTO child(parent_id) VALUES (404);
COMMIT; -- FOREIGN KEY constraint failed
BEGIN;  -- cannot start a transaction within a transaction

Impact

One commit-time failure poisons the connection for later writes. Callers receive the original commit error, but the library's transaction abstraction no longer restores its documented all-or-nothing/usable-connection invariant.

Acceptance criteria

• Do not transition to a committed/finalized state until COMMIT succeeds.
• On commit failure, attempt rollback while the transaction remains active.
• Preserve the original commit failure; if rollback also fails, expose both errors without replacing the primary cause.
• Model transaction state explicitly enough to distinguish active, committed, rolled back, and failed-finalization states.
• Keep manual commit() and automatic commit behavior consistent.

Regression-test target

A Harness test using a deferred foreign-key violation that asserts:

1. The first transaction rejects with the commit-time constraint error.
2. Its inserted row is absent.
3. A second transaction on the same connection begins and commits successfully.