iOS: executeAsync can concurrently access a thread-unsafe connection and bypass transaction isolation

[chrispader]

Summary

On iOS/visionOS, NitroSQLite v9.7.0 compiles the bundled SQLite with SQLITE_THREADSAFE=0, but multiple public APIs can dispatch work against the same sqlite3* connection concurrently.

The JavaScript DatabaseQueue serializes transactions and async batches, but ordinary executeAsync() calls bypass it. They are each dispatched through an independent Promise::async. This creates two correctness problems:

1. concurrent native access to a connection whose SQLite mutexes are compiled out; and
2. ordinary queries can interleave with a queued transaction and become part of that transaction.

This was found by auditing main at ad8b835ba0f44a207649ecc2953820d39e4e8639 (package version 9.7.0).

Evidence

• The iOS/visionOS podspec hard-codes performance_mode = 1 and adds -DSQLITE_THREADSAFE=0: RNNitroSQLite.podspec#L9-L43
• Connections request SQLITE_OPEN_FULLMUTEX: operations.cpp#L34-L52. Per SQLite's threading documentation, serialized mode cannot be selected at open time if single-thread mode was selected at compile time: https://www.sqlite.org/threadsafe.html
• Every native executeAsync() call launches an independent Promise::async and reaches the shared dbMap connection: HybridNitroSQLite.cpp#L101-L109
• The public JS executeAsync() wrapper calls native directly, without queueOperationAsync: execute.ts#L19-L34
• Transactions are queued, but their statements use those unqueued execute wrappers: transaction.ts#L20-L92
• By comparison, async batches do enter the per-database queue: executeBatch.ts#L25-L38

Consequences

• Concurrent use of a SQLite connection compiled with mutexes disabled is outside SQLite's supported threading contract and can lead to data races, corruption, crashes, or nondeterministic errors.
• An unrelated db.executeAsync() can execute after BEGIN and before COMMIT/ROLLBACK on the same connection.
• A rollback can therefore undo writes made outside the transaction callback.
• Async batch and transaction ordering does not extend to normal queries, so the queue currently gives a false sense of per-connection isolation.

Minimal transaction-isolation reproduction

This does not require winning a native race. It deliberately pauses a transaction while an ordinary query bypasses its queue:

const db = open({ name: 'isolation.sqlite' })

db.execute('CREATE TABLE events (name TEXT NOT NULL)')

let transactionStarted!: () => void
const started = new Promise<void>((resolve) => {
  transactionStarted = resolve
})

let releaseTransaction!: () => void
const release = new Promise<void>((resolve) => {
  releaseTransaction = resolve
})

const transaction = db.transaction(async (tx) => {
  tx.execute("INSERT INTO events(name) VALUES ('inside')")
  transactionStarted()

  await release
  throw new Error('force rollback')
})

await started

// This is not called through tx and should not become owned by its transaction.
await db.executeAsync("INSERT INTO events(name) VALUES ('outside')")

releaseTransaction()
await transaction.catch(() => undefined)

const rows = db.execute<{ name: string }>(
  'SELECT name FROM events ORDER BY rowid',
).rows._array

// Current risk: [] because "outside" ran on the same connection between BEGIN/ROLLBACK.
// Expected isolation: [{ name: 'outside' }] or explicit rejection/queuing until the transaction ends.

Native stress-test outline

On an iOS simulator and physical iPhone, Release and Debug, New Architecture/Hermes:

1. Open one connection.
2. Create a WAL database with one write table and one read table.
3. Launch hundreds/thousands of executeAsync() reads and writes concurrently with Promise.all.
4. Repeat while an async transaction and executeBatchAsync() are active.
5. Validate row counts, PRAGMA integrity_check, transaction ownership, and absence of native crashes under Thread Sanitizer where practical.
6. Run the same Harness test repeatedly to catch nondeterministic failures.

Proposed direction

The connection needs one consistent concurrency contract. Possible directions include:

• route every public operation for a connection through one serialization mechanism, while providing transaction-internal operations that do not recursively enqueue and deadlock; or
• use a native per-connection executor/lock with explicit transaction ownership.

The choice of SQLITE_THREADSAFE should match and enforce that contract. Merely requesting SQLITE_OPEN_FULLMUTEX cannot restore mutex code omitted at compile time.

This issue intentionally does not prescribe whether serialization belongs in JS or native code. The key requirement is that all connection operations participate, including ordinary execute calls, transactions, batches, attach/detach, file loading, and lifecycle operations as appropriate.

Acceptance criteria

• Two operations cannot concurrently touch the same thread-unsafe sqlite3*.
• A non-transaction query cannot interleave between another transaction's BEGIN and COMMIT/ROLLBACK.
• Sync calls have defined behavior while async work is active (queued or rejected consistently).
• Transaction-internal calls complete without recursive-queue deadlocks.
• iOS Harness tests cover concurrent ordinary queries, transaction isolation, and async batch ordering.
• Stress tests validate expected row counts and PRAGMA integrity_check.
• The documented performance/threading mode matches the actual build flags and runtime behavior.

Related

• General development suggestions #62 discusses the broader goal of one background thread per connection and the JS/native bookkeeping split. This issue is narrower: it tracks the current correctness gap caused by APIs bypassing serialization.