Nested transaction behaviour

[givip]

I have a table, like this

class TestTable extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get num => integer()();
}

Let's assume we initially have one row in db
{id: 1, num: 1}

transaction(() async {
      //transaction 1
     final t1 = transaction(() async {
        final elem = await (select(testTable)..where((tbl) => tbl.num.equals(1))).getSingle();

        await (delete(testTable)..where((tbl) => tbl.id.equals(elem.id))).go();

        await into(testTable).insert(
          TestTableCompanion.insert(num: elem.num + 1),
        );
      });

      //transaction 2
    final t2 =  transaction(() async {
        final elem =  await (select(testTable)..where((tbl) => tbl.num.equals(1))).getSingle();

        await (delete(testTable)..where((tbl) => tbl.id.equals(elem.id))).go();

        await into(testTable).insert(
          TestTableCompanion.insert(num: elem.num + 1),
        );
      });
     
     await Future.wait([t1, t2]);
});

Is there any chance, that result will be
{id: 2, num: 2} {id: 3, num: 2} ?

i.e. select retrieved the same state, and modifications (delete and insert) happened using this snapshot?

Here is repro example (check test)
moor_test copy.zip

[givip]

I believe this is because I use await Future.wait([t1, t2]) statement, but I must await each transaction separately?

[simolus3]

As the documentation of the transaction method points out, drift doesn't currently support nested transactions. If you call transaction in a transaction block, it'll just re-use the outer transaction.

This is mainly a restriction imposed to us by sqlite3. We could try to emulate nested transactions by using SAVEPOINT commands, but there's no builtin support for nested transactions into sqlite3.
Also, a single sqlite3 connection can only be in one transaction at a time. So even if we did support nested transactions, they couldn't run concurrently. Instead, we'd have to use a lock and run them sequentially, which pretty much makes them useless.