In sqflite, what is the difference between db.transaction and db.batch, and when would you choose each?
answer
- transaction: await each statement
- use txn, never db, inside
- throw to roll back
- batch: queued statements, one commit
- noResult and continueOnError
basics
~20 sA transaction runs awaited statements all-or-nothing and lets you read results mid-way; you must use its txn object. A batch queues statements and sends them in one commit, atomically by default, without intermediate reads, which makes bulk writes fast.
solid answer
~40 s`db.transaction((txn) async { ... })` wraps your code in `BEGIN`/`COMMIT`: you await each statement, can branch on a query's result, and if the callback throws, everything rolls back and the error is rethrown. Inside it you must call `txn`, not `db` — using `db` waits on the lock the transaction holds and deadlocks. `db.batch()` just records statements; `batch.commit()` sends them together and, outside a transaction, sqflite wraps the commit in one, so it is atomic too. A batch cannot read its own intermediate results, but it saves round trips: `commit(noResult: true)` skips the result list, `continueOnError: true` keeps going past failures. I use a batch to import a thousand expenses, and a transaction for read-then-write logic such as inserting an expense and updating a monthly total.
code
dart · 34 linesimport 'package:sqflite/sqflite.dart';
Future<void> importExpenses(
Database db,
List<Map<String, Object?>> rows,
) async {
final batch = db.batch();
for (final row in rows) {
batch.insert('expenses', row);
}
await batch.commit(noResult: true); // one round trip, atomic
}
Future<int> addWithTotal(
Database db,
Map<String, Object?> expense,
String month,
) {
return db.transaction((txn) async {
final id = await txn.insert('expenses', expense); // txn, not db
final rows = await txn.query(
'monthly_totals',
where: 'month = ?',
whereArgs: [month],
);
final total = rows.isEmpty ? 0 : rows.first['total_cents']! as int;
await txn.insert(
'monthly_totals', // month is the PRIMARY KEY
{'month': month, 'total_cents': total + (expense['amount_cents']! as int)},
conflictAlgorithm: ConflictAlgorithm.replace,
);
return id;
});
}go deeper
Remember that a transaction is all-or-nothing and that a batch groups many statements into one commit.
Explain txn versus db inside a transaction, rollback by throwing, commit options like noResult and continueOnError, and why batches are faster.
Choose batch, transaction or both per write path, keep transactions short, and diagnose lock warnings to a stray db call.
Define consistency rules for local data so multi-table writes always happen in one unit, and review code paths against them.
## Two tools that are often confused The sqflite documentation itself notes the confusion between the two. They solve different problems and combine well. | | `transaction` | `batch` | |---|---|---| | What it is | a SQLite transaction around your callback | a queue of statements sent together | | Atomic | yes | yes with `commit()`; no with `apply()` | | Read results mid-way | yes, await each call | no, results come back at the end | | Round trips | one per statement | one for the whole batch | | Error handling | throw to roll back; error rethrown | stops on first failure unless `continueOnError` | | Best for | read-then-write logic, upserts | bulk inserts or updates | ## Transactions `db.transaction` takes an async callback that receives a `Transaction` — call it `txn`. Rules worth knowing: - **Use `txn` for every statement inside.** Calling `db` while the transaction holds the lock waits forever; sqflite prints a warning after 10 seconds by default, reminding you to use the transaction object. - **Throwing rolls back.** If the callback throws, all statements are reverted and the same error is rethrown to the caller. Throwing is how you cancel on purpose. - **The callback runs once.** sqflite does not retry; a retry loop is your job. - **The value is returned.** `transaction` returns whatever the callback returns, so you can hand back an inserted id. - **Keep it short.** No network calls or user prompts inside: the database is locked for the duration. ## Batches `final batch = db.batch();` then `batch.insert(...)`, `batch.update(...)`, `batch.rawQuery(...)` and so on — these methods return `void`; nothing runs yet. Then: - `await batch.commit()` runs them all. On a plain `Database`, sqflite starts a transaction for the commit, so it is all-or-nothing, and returns a list with each statement's result in order. - `commit(noResult: true)` returns an empty list — faster when you do not need the ids. - `commit(continueOnError: true)` executes every statement; failing ones yield a `DatabaseException` in their result slot instead of stopping the batch. - A batch created **inside** a transaction, or inside `onCreate`/`onUpgrade`, is committed only when that transaction commits. - `batch.apply()` runs the statements without sqflite starting a transaction — rarely what you want. The speed gain comes from crossing from Dart to the native side once rather than once per statement. ## Choosing 1. Many independent writes, no decisions based on intermediate reads → **batch**. 2. Logic that reads, then decides what to write → **transaction**. 3. Both → a batch inside a transaction, created from `txn.batch()`. ## In the expense ledger Importing a year of bank-export rows is a batch of inserts committed with `noResult: true`. Adding a single expense while keeping a `monthly_totals` table in step is a transaction: insert the expense, read the month's total, write the new total, and return the new id. If the total write fails, the expense insert is rolled back too, so the two tables never disagree. ## Mistakes that show up in review - Using `db` inside the transaction callback — the classic self-deadlock. - Swallowing an error inside the callback with `try`/`catch`, which lets the transaction **commit** the statements that did succeed; rethrow if the unit must be all-or-nothing. - Awaiting a network call or a dialog inside a transaction, which holds the lock for seconds. - Expecting `batch.insert` to return an id — it returns `void`; ids arrive in the list from `commit()` unless `noResult: true`. - Committing a batch with `continueOnError: true` and never inspecting the results for `DatabaseException` entries.
- Why does calling db.insert inside a db.transaction callback hang?The transaction holds the database lock until the callback finishes, and a call on `db` queues behind that lock, while the callback is waiting on the call — a deadlock. sqflite prints a lock warning after 10 seconds by default. Every statement inside must use the `txn` argument.
- Is a batch committed inside onUpgrade applied immediately?No. `onCreate`, `onUpgrade` and `onDowngrade` already run inside the open transaction, so a batch committed there becomes permanent only when that transaction commits, together with the new version number.
saying these in an interview costs you the question
- A batch is not atomic, so a failure leaves half the rows written.
- Inside a transaction you can keep using db; it joins the transaction.
- You can read an inserted id from a batch before committing it.
- sqflite retries a transaction automatically if it fails.
- To roll back a transaction you must call a rollback method explicitly.