How to update using `where` in a transaction?

[cirediew]

I am trying to to run an update using where inside a transaction but it gives met the following error message:

E/SQLiteLog(12156): (1) statement aborts at 3: [BEGIN EXCLUSIVE;] cannot start a transaction within a transaction

My code:

// update from a list using a transaction
await database.transaction(() async { 
  //forEach does not seem to be working in a transaction
  for (int listIndex = 0; listIndex < list.length; listIndex++) {
    if (list[listIndex] != null) {
      await dao.updateProduct(list[listIndex]);
    }
  }
}).catchError((error, stack) => //The SQLiteLog error is also never caught here
      LOG.exception(
        message: '_toDatabase: E: $error, S: $stack',
        exception: error,
        stacktrace: stack,
  ),
);

In my DAO:

  Future<bool> updateProduct(Product product) => (update(products)
        ..where((products) => products.product.equals(product.product)))
      .write(
        ProductsCompanion(
          appearance: Value(product.appearance),
          productType: Value(product.productType),
          name: Value(product.name),
          updatedAt: Value(DateTime.now()),
          productIndex: product.productIndex == null
              ? const Value.absent()
              : Value(product.productIndex),
        ),
      )
      .then((value) => true);

dependencies:
#  (...)
  moor_flutter: ^3.1.0

dev_dependencies:
#  (...)
  moor_generator: ^3.2.0

How can I update my table using where in a transaction?

Thank you!

Not as important but still noteworthy, as mentioned in my code comments:

• forEach does not seem to be working in a transaction.
• The cannot start a transaction within a transaction error is never caught by my dart code in catchError.

[simolus3]

Thanks for the report! Does the exception print a stack trace? It should not be possible to hit that error in sqlite since moor is supposed to have guards against using multiple transactions.

forEach does not seem to be working in a transaction

Were you using someList.forEach((it) async {})? That's really dangerous since forEach does not await a the future started by the lambda, so you can't catch errors that might happen in the async callback. You'd have to use a for loop as you're doing now, or use Future.wait(someList.map((it) async {})).

[cirediew]

• No the exception is never caught, so my log is not printed, only the one fromSQLiteLog.

• I don't really understand what you mean by this It should not be possible to hit that error in sqlite since moor is supposed to have guards against using multiple transactions. What do I need to change to make my code work using where?

• Ok, I'll keep using my for loop, thank you.

[cirediew]

In addition to my first report i've found another query which does not seem to work in a transaction. I have a mixin on my DAOs to upsert my data. In there I check if a row exists.

It works from time to time but sometimes it throws the error mentioned above: E/SQLiteLog(12156): (1) statement aborts at 3: [BEGIN EXCLUSIVE;] cannot start a transaction within a transaction

This error message is only shown on my android emulator (api 29) though, iOS simulator (iOS 13.7) doesn't show any error and just hangs.

  Future $upsertAll(List<D> rows) async {
    return await transaction(() async {
      for (final row in rows) {
        await $upsert(row).catchError((e, s) {
          LOG.shout(e);
          LOG.shout(s);
        });
      }
    });
  }

  Future $upsert(D data) async {
    if (await $exists(data)) {
      return await $update(data);
    } else {
      return await $insert(data);
    }
  }

  Future<bool> $exists(D data) {
    var query = getQuery(data);
    return customSelect('SELECT $query', readsFrom: {table})
            ?.map((row) => row.readBool(query))
            ?.getSingle() ??
        Future.value(false);
  }

  String getQuery(D data) {
    return 'EXISTS(SELECT 1 FROM ${table.$tableName} '
        'WHERE $primaryKey = ${primaryKeyValue(data)} '
        'LIMIT 1)';
  }