In expo-sqlite, how does withExclusiveTransactionAsync differ from withTransactionAsync, and when is the exclusive one worth using?

middleshould knowfreq 38%

answer

shared connection versus a new one
awaited queries elsewhere join it
task gets txn; use txn only
other writers get database is locked
throws on web

basics

~20 s

withTransactionAsync runs BEGIN and COMMIT on the app's shared connection, so any query awaited meanwhile joins the transaction. withExclusiveTransactionAsync opens a separate connection and passes a txn object; only queries on txn are inside it.

solid answer

~50 s

withTransactionAsync is not isolated: because JavaScript interleaves at every await, a query another part of the app runs on the same db while the transaction is open becomes part of it, and rolls back with it if the task fails. withExclusiveTransactionAsync(async (txn) => ...) creates a new native connection, runs BEGIN, your task and COMMIT or ROLLBACK on it, and closes it in a finally. Queries inside must use txn; anything on db runs outside. Once txn has written, other writes on db fail with database is locked rather than queue. It is not supported on web and throws there. Use it for a long batch that must not absorb unrelated writes, such as importing a journal backup while the user keeps typing; the plain one is enough when nothing else can touch the database.

`withTransactionAsync` is not isolated: because JavaScript interleaves at every `await`, a query another part of the app runs on the same `db` while the transaction is open becomes part of it, and rolls back with it if the task fails. `withExclusiveTransactionAsync(async (txn) => ...)` creates a new native connection, runs `BEGIN`, your task and `COMMIT` or `ROLLBACK` on it, and closes it in a `finally`. Queries inside must use `txn`; anything on `db` runs outside. Once `txn` has written, other writes on `db` fail with `database is locked` rather than queue. It is not supported on web and throws there. Use it for a long batch that must not absorb unrelated writes, such as importing a journal backup while the user keeps typing; the plain one is enough when nothing else can touch the database.

code

typescript · 18 lines

typescript

import type { SQLiteDatabase } from 'expo-sqlite';

type BackupEntry = { id: number; body: string; createdAt: number; mood: string | null };

export async function importBackup(db: SQLiteDatabase, entries: BackupEntry[]): Promise<void> {
  await db.withExclusiveTransactionAsync(async (txn) => {
    for (const e of entries) {
      // Must be txn, not db: a db.runAsync here would run outside the transaction.
      await txn.runAsync(
        'INSERT OR REPLACE INTO entries (id, body, created_at, mood) VALUES (?, ?, ?, ?)',
        e.id,
        e.body,
        e.createdAt,
        e.mood
      );
    }
  });
}

go deeper

for a junior

Recall that the plain transaction shares the app's connection, while the exclusive one hands you a txn object whose queries are the only ones inside.

for a middle

Explain how await interleaving pulls outside queries into a plain transaction, and why concurrent writes fail with database is locked under the exclusive one.

for a senior

Choose per call site: plain in onInit and short saves, exclusive for long imports, with a plan for the locked writer and no network waits inside.

for a principal

Decide whether the app needs a single-writer data layer at all; routing every write through one queue can make exclusivity a property of the design rather than of each call.

Why the plain transaction is not isolated

db.withTransactionAsync(task) issues BEGIN on the connection that db wraps, awaits your task, then issues COMMIT (or ROLLBACK and a rethrow). A SQLite connection has at most one transaction open at a time, and every statement sent on that connection while it is open belongs to it.

JavaScript yields at every await. While your task is waiting on its second insert, a button press elsewhere in the app can call db.runAsync(...) on the same object. That statement goes down the same connection, so it lands inside your transaction. The expo-sqlite docs show exactly this with a Promise.all: an UPDATE written outside the callback runs during it, is included in it, and is rolled back when the callback's later assertion throws. The source comment says it plainly: the transaction is not exclusive and can be interrupted by other async queries.

What withExclusiveTransactionAsync does differently

In expo-sqlite 57 the method:

Throws immediately on web (withExclusiveTransactionAsync is not supported on web).
Creates a new native connection to the same database file (internally, the same open options with useNewConnection: true).
Runs BEGIN, awaits task(txn), then COMMIT, all on that connection; on error it runs ROLLBACK.
Closes the extra connection in a finally, then rethrows any error.

Two consequences follow.

Only txn is inside. The txn object has the same query methods as a database (runAsync, getAllAsync, execAsync and so on). A db.runAsync written inside the callback by mistake is not part of the transaction at all.
Other writers are rejected, not queued. Once txn has performed a write, SQLite holds the write lock for that connection. A write arriving on the main db connection fails with database is locked. The expo-sqlite test suite asserts exactly this error for a concurrent db.runAsync during an exclusive transaction.

The name is easy to misread. Both methods send a plain BEGIN. "Exclusive" means exclusive of the app's other JavaScript queries, achieved with a dedicated connection; it does not mean SQL's BEGIN EXCLUSIVE lock mode.

Side by side

	`withTransactionAsync`	`withExclusiveTransactionAsync`
Connection	the shared one behind `db`	a new one, closed afterwards
Task signature	`() => Promise<void>`	`(txn) => Promise<void>`
Concurrent query on `db`	joins the transaction	runs outside it; a write fails once `txn` has written
Web	supported	throws
Cost	none extra	open and close a connection per call

Reads during an exclusive transaction and WAL

Reads on db while txn is writing see the last committed data. How smoothly they proceed depends on the database's journal mode. The expo-sqlite docs recommend running PRAGMA journal_mode = WAL when you create a database; in WAL mode readers do not wait for the writer. In SQLite's default rollback-journal mode a reader can be locked out while the writer commits. How SQLite implements either mode belongs to SQLite itself; what matters here is that WAL makes the two-connection pattern friendlier.

When each one fits

Use the plain variant when nothing else can issue queries while it runs:

migrations in the SQLiteProvider onInit callback, which runs before any child renders;
a short save of one entry and its tags triggered by one user action.

Use the exclusive variant when a longer unit of work overlaps with ordinary app activity:

importing a journal backup of several thousand entries while the user can still open and edit entries;
a maintenance job that rewrites a table and must not accidentally commit or roll back an unrelated edit.

For a development-time assertion, db.isInTransactionAsync() (and isInTransactionSync()) reports whether the shared connection currently has a transaction open, which catches a long job accidentally started inside someone else's plain transaction.

Then plan for the other side: the user's edit during the import will fail with database is locked, so route writes through one module that retries, waits for the import, or disables editing until it finishes. Keep the exclusive task short and free of network waits, because every other writer is locked out for its whole duration.

A checklist for interviews

Name the interleaving problem first: await lets outside queries into a plain transaction.
Say what the exclusive variant costs: a connection per call, txn threading, no web support.
Say what the other writers see: an error, not a wait.

## Why the plain transaction is not isolated `db.withTransactionAsync(task)` issues `BEGIN` on the connection that `db` wraps, awaits your task, then issues `COMMIT` (or `ROLLBACK` and a rethrow). A SQLite connection has at most one transaction open at a time, and every statement sent on that connection while it is open belongs to it. JavaScript yields at every `await`. While your task is waiting on its second insert, a button press elsewhere in the app can call `db.runAsync(...)` on the same object. That statement goes down the same connection, so it lands **inside your transaction**. The expo-sqlite docs show exactly this with a `Promise.all`: an `UPDATE` written outside the callback runs during it, is included in it, and is rolled back when the callback's later assertion throws. The source comment says it plainly: the transaction is not exclusive and can be interrupted by other async queries. ## What withExclusiveTransactionAsync does differently In `expo-sqlite` 57 the method: 1. Throws immediately on web (`withExclusiveTransactionAsync is not supported on web`). 2. Creates a **new native connection** to the same database file (internally, the same open options with `useNewConnection: true`). 3. Runs `BEGIN`, awaits `task(txn)`, then `COMMIT`, all on that connection; on error it runs `ROLLBACK`. 4. Closes the extra connection in a `finally`, then rethrows any error. Two consequences follow. - **Only `txn` is inside.** The `txn` object has the same query methods as a database (`runAsync`, `getAllAsync`, `execAsync` and so on). A `db.runAsync` written inside the callback by mistake is not part of the transaction at all. - **Other writers are rejected, not queued.** Once `txn` has performed a write, SQLite holds the write lock for that connection. A write arriving on the main `db` connection fails with `database is locked`. The expo-sqlite test suite asserts exactly this error for a concurrent `db.runAsync` during an exclusive transaction. The name is easy to misread. Both methods send a plain `BEGIN`. "Exclusive" means exclusive of the app's other JavaScript queries, achieved with a dedicated connection; it does not mean SQL's `BEGIN EXCLUSIVE` lock mode. ## Side by side | | `withTransactionAsync` | `withExclusiveTransactionAsync` | |---|---|---| | Connection | the shared one behind `db` | a new one, closed afterwards | | Task signature | `() => Promise<void>` | `(txn) => Promise<void>` | | Concurrent query on `db` | joins the transaction | runs outside it; a write fails once `txn` has written | | Web | supported | throws | | Cost | none extra | open and close a connection per call | ## Reads during an exclusive transaction and WAL Reads on `db` while `txn` is writing see the last committed data. How smoothly they proceed depends on the database's journal mode. The expo-sqlite docs recommend running `PRAGMA journal_mode = WAL` when you create a database; in WAL mode readers do not wait for the writer. In SQLite's default rollback-journal mode a reader can be locked out while the writer commits. How SQLite implements either mode belongs to SQLite itself; what matters here is that WAL makes the two-connection pattern friendlier. ## When each one fits Use the **plain** variant when nothing else can issue queries while it runs: - migrations in the `SQLiteProvider` `onInit` callback, which runs before any child renders; - a short save of one entry and its tags triggered by one user action. Use the **exclusive** variant when a longer unit of work overlaps with ordinary app activity: - importing a journal backup of several thousand entries while the user can still open and edit entries; - a maintenance job that rewrites a table and must not accidentally commit or roll back an unrelated edit. For a development-time assertion, `db.isInTransactionAsync()` (and `isInTransactionSync()`) reports whether the shared connection currently has a transaction open, which catches a long job accidentally started inside someone else's plain transaction. Then plan for the other side: the user's edit during the import will fail with `database is locked`, so route writes through one module that retries, waits for the import, or disables editing until it finishes. Keep the exclusive task short and free of network waits, because every other writer is locked out for its whole duration. ## A checklist for interviews - Name the interleaving problem first: `await` lets outside queries into a plain transaction. - Say what the exclusive variant costs: a connection per call, `txn` threading, no web support. - Say what the other writers see: an error, not a wait.

How should the app handle the database is locked error another writer gets during an exclusive transaction?
Treat it as expected contention. Route writes through one data module that retries after the exclusive job finishes, or disable the editing UI while a long import runs. Keeping the exclusive task short and free of network waits shrinks the window.
Why not use withExclusiveTransactionAsync everywhere?
Each call opens and closes a native connection, it throws on web, every helper must be passed `txn` instead of `db`, and concurrent writers now fail instead of joining. Where nothing else can run, such as `onInit`, the plain variant is simpler.

saying these in an interview costs you the question

withExclusiveTransactionAsync issues BEGIN EXCLUSIVE on the app's existing connection
Queries on db inside withExclusiveTransactionAsync are part of that transaction
withTransactionAsync makes other queries wait until it commits
Writes on db simply queue until an exclusive transaction commits
withExclusiveTransactionAsync behaves the same on web

In expo-sqlite, how does withExclusiveTransactionAsync differ from withTransactionAsync, and when is the exclusive one worth using?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why the plain transaction is not isolated

What withExclusiveTransactionAsync does differently

Side by side

Reads during an exclusive transaction and WAL

When each one fits

A checklist for interviews

▾common follow-ups2 questions

▾red flags5 flags

Why the plain transaction is not isolated

What withExclusiveTransactionAsync does differently

Side by side

Reads during an exclusive transaction and WAL

When each one fits

A checklist for interviews

answer

go deeper