making multiple operations atomic in sqflite

Question

I am creating a mobile application in flutter where I need to do entries in the transaction table and also need to update the balance in the user table. Whenever a new transaction is to be added I first check the current balance of the user, add the entry in the transaction table and update the balance. My question is how can I make the entire operation atomic. Say if the update balance fails because for any reason how can I roll back from the transaction table also.

Answer 1

That is exactly what SQLite transactions are for! (Sorry for the unfortunate name of your table which is also called transaction)

More info on how to use transactions in sqflite here:

https://github.com/tekartik/sqflite/blob/master/sqflite/doc/sql.md#transaction

some copy/pasted information for convenience:

transaction

transaction handle the 'all or nothing' scenario. If one command fails (and throws an error), all other commands are reverted.

await db.transaction((txn) async {
  await txn.insert('my_table', {'name': 'my_name'});
  await txn.delete('my_table', where: 'name = ?', whereArgs: ['cat']);
});

• Make sure to sure the inner transaction object - txn in the code above - is used in a transaction (using the db object itself will cause a deadlock),
• You can throw an error during a transaction to cancel a transaction,
• When an error is thrown during a transaction, the action is cancelled right away and previous commands in the transaction are reverted,
• No other concurrent modification on the database (even from an outside process) can happen during a transaction,
• The inner part of the transaction is called only once, it is up to the developer to handle a try-again loop - assuming it can succeed at some point.