Manually Cancel Transaction or Batch

[yeanpei]

Is there a way to manually cancel a transaction or batch insert? I am doing a download data function from server and batch insert the data. In the event of user choose to cancel download, I would like to cancel the insert (which is rollback everything).

Below are the code snippet:

await this._dbHelper.transaction(() async {
    await _dbHelper.aDao.deleteAll();
    await _dbHelper.aDao.upsertMany(data);
});

//aDao
Future<void> upsertMany(List<A> entries) {
    return batch((b) {
      aTable.upsertMany(b, entries);
    });
}
 
//extension on custom table statement
void upsertMany(Batch batch, Iterable<Insertable<Row>> rows) {
    batch.customStatement('pragma defer_foreign_keys = on;');
    for (final row in rows) {
      batch.insert(this, row,
          mode: InsertMode.insertOrRollback, onConflict: DoUpdate((_) => row));
    }
}

[simolus3]

You can throw an exception inside of the transaction and catch it on the outside. This will roll back the transaction.

[yeanpei]

Do u mean to put the throw exception in 1 or 2? If put in 1/2 it still will run the statement finish before throwing an exception. User can click cancel anytime in between inserting and I do not want to wait the upsert finish (as it can be quite a large number of records around 10k) before throwing the exception.

await this._dbHelper.transaction(() async {
    await _dbHelper.aDao.deleteAll();
    //here... 1
    await _dbHelper.aDao.upsertMany(data);
    //here... 2
});

My ideal solution is to call transaction.rollBack() so that at the moment user click cancel, regardless which DB action is running currently all will be cancelled and rollback. Any alternatives to do so if this is not possible?

Transaction t;

 t = await this._dbHelper.transaction(() async {
    await _dbHelper.aDao.deleteAll();
    await _dbHelper.aDao.upsertMany(data);
});

cancelDownload() {
    t.rollBack();
}

[simolus3]

Does it work if you do something like

import 'package:drift/src/runtime/cancellation_zone.dart';

CancellationToken token;
await this._dbHelper.transaction(() async {
  token = runCancellable(() async {
    // transaction work here
  });
  await token.result;
});

Calling token.cancel() should interrupt pending statements from executing and roll back the transaction.

However, I don't think something like this should be part of drift's API. You can implement something similar by having a bool cancelled flag and checking that before each statement - perhaps with a query interceptor throwing if the flag is true in its runXXX methods.