[Question] about nest transaction?

[Guang1234567]

Hello dev:

I have a code that get into trouble about nest transaction like below. Could you give me some suggestion?

class ClassroomProvider {
    Future<Classroom> insert(Classroom room) async {
        return database.transaction((txn) async {
                 room.id = await db.insert(tableClassroom, room.toMap());
                 await _teacherProvider.insert(room.getTeacher());
                 await _studentProvider.bulkInsert(room.getStudents()); // nest transaction here
                  return room;
               }
            });
  }
}

class TeacherProvider {
    Future<Teacher> insert(Teacher teacher) async {
    teacher.id = await db.insert(tableTeacher, teacher.toMap());
    return teacher;
  }
}

class StudentProvider {
    Future<List<Student>> bulkInsert(List<Student> students) async {
           // use database object in a transaction here !!!
           return database.transaction((txn) async {
                  for(var s in students) {
                              s.id = await db.insert(tableStudent, student.toMap());
                  }
                  return students;
               }
            });
}

As we known in README:

Don't use the database but only use the Transaction object in a transaction to access the database

But the above code use the database object in a transaction to access the database 😭 !!!

In additional, has any unit test or example for nest transaction?

[alextekartik]

Nested transaction were indeed supported but it needed expensive zones in all situation. One solution is to pass the transaction object

class ClassroomProvider {
  Future<Classroom> insert(Classroom room) async {
    return database.transaction((txn) async {
      await _teacherProvider.txnInsert(txn, room.getTeacher());
      await _studentProvider.txnBulkInsert(
          txn, room.getStudents()); // nest transaction here
      // Insert room last to save the teacher and students ids
      room.id = await txn.insert(tableClassroom, room.toMap());
      return room;
    });
  }
}

class TeacherProvider {
  Future<Teacher> insert(Teacher teacher) =>
      database.transaction((txn) => txnInsert(txn, teacher));

  Future<Teacher> txnInsert(Transaction txn, Teacher teacher) async {
    teacher.id = await txn.insert(tableTeacher, teacher.toMap());
    return teacher;
  }
}

class StudentProvider {
  Future<List<Student>> bulkInsert(List<Student> students) =>
      database.transaction((txn) => txnBulkInsert(txn, students));

  Future<List<Student>> txnBulkInsert(
      Transaction txn, List<Student> students) async {
    for (var student in students) {
      student.id = await txn.insert(tableStudent, student.toMap());
    }
    return students;
  }
}

[Guang1234567]

@alextekartik
Thanks your reply.

In additional, has any unit test or example for nest transaction?

like this?

database.transaction((txn1) async {
                await database.transaction((txn2) async {
                               });
            });

[alextekartik]

Nested transaction are not supported. SQLite does not support it. Inside a transaction you should not used the database object.

[Guang1234567]

@alextekartik

Thanks your reply. You can close this issue now.

@Other_people_that_prefer_nested_transaction:

sqlfiltebrite is my flutter pkg that base on tekartik/sqflite and rxdart.

And it support a simulation of the nest transaction.