What is an sqflite transaction? Provide a practical example.

What's really being asked

This question probes your understanding of ACID atomicity in local SQLite databases and whether you know how sqflite exposes transactional guarantees in Dart. The interviewer wants to see that you treat a transaction as an all-or-nothing boundary around related writes, not merely a convenient wrapper for multiple queries.

The full answer

First, define a transaction as an atomic unit of work that groups multiple SQL operations so they commit together or roll back together. Second, explain that sqflite sits on top of SQLite and therefore inherits BEGIN, COMMIT, and ROLLBACK semantics, surfaced through the db.transaction API. Third, give a concrete fund-transfer example where deducting from account A and adding to account B must both succeed or both fail to prevent inconsistent balances. Fourth, mention the platform-specific constraint documented by the sqflite team that the transaction mechanism is not cross-isolate safe and should therefore be executed on the main isolate.

The mistakes people make

Candidates often describe a transaction as simply a series of queries without mentioning atomicity or rollback behavior. Another red flag is proposing to run transaction logic inside a background isolate for a push notification or scheduled task without noting the singleInstance false requirement and the inherent risk of lock issues. Some candidates also suggest manually locking tables or implementing application-level compensating logic instead of relying on the database's built-in ACID guarantees.

What usually comes next

The interviewer may ask how you handle partial failures inside a transaction, how sqflite handles concurrency when multiple writes arrive from the UI, or how you would structure a repository pattern so that business logic does not leak raw SQL into the widget layer. They might also probe the difference between using db.transaction and a Batch object for bulk operations.

A concrete example

Imagine a wallet app with an accounts table. To transfer one hundred units from user one to user two, you open a transaction block and pass an async callback that receives a transaction object. Inside the callback, you first run an UPDATE to subtract one hundred from account one and check that exactly one row was affected. If not, you throw an error. Then you run a second UPDATE to add one hundred to account two. If this second step throws, the entire block is rolled back automatically so account one keeps its original balance. Because the transaction mechanism is not cross-isolate safe, this code must run on the main isolate while the native plugin handles the actual disk IO on a background thread.