Failed COMMIT leaves transaction open and breaks subsequent transactions

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.

Read packages/react-native-nitro-sqlite/src/operations/transaction.ts, especially the commit and rollback paths at the cited lines, and compare manual commit with automatic commit behavior. Add the regression coverage in a Harness test using the deferred foreign-key reproducer; done means the original commit error is preserved, the inserted row is absent, rollback errors remain visible if applicable, and a second transaction succeeds on the same connection.