[Bug]: transaction() resolves after failed automatic COMMIT (18.2.1, Android)

[JaredSystems]

What happened?

db.transaction() can resolve successfully when its automatic COMMIT fails. It does not automatically roll back and leaves the native SQLite transaction open. Reproduced on a physical Android device using the published 18.2.1 native module, RN 0.79.6 and Hermes. The same failure is reproducible with the unchanged JS wrapper and Node SQLite.

This is the implicit commit path, not a caller forgetting to await an explicit tx.commit() (as in #164). The callback's INSERT succeeds; the deferred constraint rejects COMMIT itself.

Versions and environment

• OP-SQLite: 18.2.1, vanilla SQLite, performance mode disabled.
• Published source gitHead: 6a6a4e592f4162dd50715310203ee15f97543984; current main matched when checked September 12, 2026.
• React Native: 0.79.6, Hermes, legacy architecture.
• Device: OnePlus 7T HD1907, Android 12, arm64-v8a.
• Native SELECT sqlite_version(): 3.51.3.
• Separate debug-signed test APK with developer support disabled. No claim of testing JSC, New Architecture, iOS or every engine backend.

Reproducible example

Run this in a React Native app with @op-engineering/op-sqlite@18.2.1 installed and linked. It uses a unique disposable database and deliberately cleans it up after recording the result. The unhandled COMMIT rejection may also appear in the runtime log.

import { open } from '@op-engineering/op-sqlite';

export async function reproduceCommitFailure() {
  const db = open({ name: `commit-failure-${Date.now()}.db` });
  await db.execute('PRAGMA foreign_keys = ON');
  await db.execute('CREATE TABLE parent(id INTEGER PRIMARY KEY)');
  await db.execute(`CREATE TABLE child(
    id INTEGER PRIMARY KEY,
    parent_id INTEGER REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED
  )`);

  let outcome = 'resolved';
  let error: string | undefined;
  try {
    await db.transaction(async tx => {
      // This succeeds. The FK is checked when COMMIT runs.
      await tx.execute('INSERT INTO child VALUES (1, 999)');
    });
  } catch (e) {
    outcome = 'rejected';
    error = String(e);
  }

  const rowsBeforeCleanup = (await db.execute(
    'SELECT COUNT(*) AS n FROM child'
  )).rows[0].n;

  let transactionStillOpen = false;
  try {
    await db.execute('ROLLBACK');
    transactionStillOpen = true;
  } catch {
    // Expected if the transaction helper already rolled back.
  }

  const result = { outcome, error, rowsBeforeCleanup, transactionStillOpen };
  console.log(result);
  db.close();
  db.delete();
  return result;
}

Actual, unmodified 18.2.1 on Android:

{"outcome":"resolved","rowsBeforeCleanup":1,"transactionStillOpen":true}

The remaining row is visible inside the still-open transaction; this does not mean it was committed. A delayed unhandled rejection reports FOREIGN KEY constraint failed.

Expected: reject with the COMMIT error, roll back, leave zero child rows and no open transaction, and allow the next queued transaction to proceed normally.

A second native test holds a read transaction on another connection in DELETE journal mode, sets the writer's busy_timeout=0, and attempts to commit an INSERT. The real database is locked COMMIT failure produces the same false-success behavior.

Cause

commit is async, but the implicit commit call is not awaited. A thrown executeSync('COMMIT;') becomes a rejected promise outside the runner's catch.

Simply adding await exposes two nearby lifecycle problems:

• isFinalized is set after awaiting reactive flushing. If COMMIT succeeds but flushing rejects, the catch attempts ROLLBACK on an already committed transaction and masks the notification error.
• The finally block resets isFinalized=false, allowing a retained explicitly committed/rolled-back transaction object to write afterward.

Candidate fix

The following source patch awaits implicit COMMIT, marks successful COMMIT finalized before notification work, and keeps completed handles finalized. No native C++ changes were needed for these cases.

--- a/src/functions.ts
+++ b/src/functions.ts
@@ -208,9 +208,9 @@
         }
         const result = enhancedDb.executeSync("COMMIT;");
 
+        isFinalized = true;
+
         await db.flushPendingReactiveQueries();
-
-        isFinalized = true;
         return result;
       };
 
@@ -238,7 +238,7 @@
           });
 
           if (!isFinalized) {
-            commit();
+            await commit();
           }
         } catch (executionError) {
           if (!isFinalized) {
@@ -248,7 +248,6 @@
           throw executionError;
         } finally {
           lock.inProgress = false;
-          isFinalized = false;
           startNextTransaction();
         }
       }

Validation and scope

The same 15 behavioral regression cases were run on Node SQLite and on the physical Android device with the real OP-SQLite native handle:

Variant	Node	Android
Unmodified 18.2.1	7/15	7/15
Only adding await commit()	12/15	12/15
Candidate patch above	15/15	15/15

Cases cover successful commit, statement/callback failures, actual deferred-FK and SQLITE_BUSY COMMIT failures, injected COMMIT failure, queued-transaction recovery, delayed/rejected reactive flushing, explicit commit/rollback, and completed-handle reuse. The public-API reproduction above does not substitute the native handle. The larger suite wraps the actual native handle to log control SQL and inject the explicitly identified commit/flush faults. Hermes rejection tracking was allowed to settle between cases.

A notification error after confirmed COMMIT still rejects with that notification error; consumers must not treat every rejection as proof of rollback or automatically retry. This patch does not claim to solve failed-BEGIN/rollback-error handling, root queries bypassing the transaction queue, or the broader native-lock design in #416.