Flutter with SQLite - constraint violation in transaction leads to error in following transaction

Question

I use Flutter with the sqflite package.

I execute a query that violates a FOREIGN KEY constraint, inside a transaction.

I catch the exception, and then try to start another transaction. This fails with this DatabaseException: cannot start a transaction within a transaction.

Here is a minimally-reproducible example:

/////////////////////////////
/////////////////////////////
//
// Create/open the database
//
/////////////////////////////
/////////////////////////////
String databasePath = await getDatabasesPath();
String finalPath = join(databasePath, "testsqlite.db");
Database db = await openDatabase(finalPath,
  onCreate: (Database _db, int version) async {
    await _db.transaction((Transaction trx) async {
      await trx.execute("CREATE TABLE User(id INTEGER PRIMARY KEY, age INTEGER);");
      await trx.execute("""
        CREATE TABLE Purchase(
          id INTEGER PRIMARY KEY,
          userId INTEGER REFERENCES User(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
        );
      """);
    });
  },
  onOpen: (Database db) async {
    await db.execute("PRAGMA foreign_keys = ON");
  },
  version: 1,
);


/////////////////////////////
/////////////////////////////
//
// ACTION!
//
/////////////////////////////
/////////////////////////////
try {
  await db.transaction((Transaction trx) async {
    // Run query to cause SQLITE_CONSTRAINT_FOREIGNKEY
    await trx.execute("INSERT INTO Purchase(id, userId) VALUES(1, 1);");
  });
}
catch (ex) {
  print("Correctly crashed on SQLITE_CONSTRAINT_FOREIGNKEY");

  try {
    // Try to start a new transaction
    await db.transaction((Transaction trx) async {

    });
  }
  catch (ex2) {
    print("Incorrectly crashed on 'cannot start a transaction within a transaction'");
  }
}

The exception on await trx.execute("INSERT INTO Purchase(id, userId) VALUES(1, 1);"); is expected.

The exception on the following await db.transaction((Transaction trx) async is not expected.

I tried to explicitly add await db.execute("ROLLBACK"); after the SQLITE_CONSTRAINT_FOREIGNKEY exception, but I get another exception saying that there is no active transaction.

I also tried to start the second transaction later (e.g. do it 5 seconds later, using Timer) - and got the same exception.

Why does the attempt to start the second transaction throw an exception?

How can I start a new transaction after an SQLITE_CONSTRAINT_FOREIGNKEY exception?

This reproduces for me consistently on Android 11 (I haven't tried other versions).

Answer 1

From this answer, a solution is to close the database and open it again.

You can extract the code to open the database into a method so you can call it when you encounter the error in order to open the database again.

/////////////////////////////
/////////////////////////////
//
// Create/open the database
//
/////////////////////////////
/////////////////////////////
    String databasePath = await getDatabasesPath();
    String finalPath = join(databasePath, "testsqlite.db");
    Database db = await getDatabase(finalPath);

/////////////////////////////
/////////////////////////////
//
// ACTION!
//
/////////////////////////////
/////////////////////////////
    try {
      await db.transaction((Transaction trx) async {
        // Run query to cause SQLITE_CONSTRAINT_FOREIGNKEY
        await trx.execute("INSERT INTO Purchase(id, userId) VALUES(1, 1);");
      });
    } catch (ex) {
      print("Correctly crashed on SQLITE_CONSTRAINT_FOREIGNKEY");

      try {
        db.close();
        db = await getDatabase(finalPath);
        // Try to start a new transaction
        await db.transaction((Transaction trx) async {});
      } catch (ex2) {
        print(
            "Incorrectly crashed on 'cannot start a transaction within a transaction'");
      }
    }
  }

  Future<Database> getDatabase(String finalPath) async {
    return await openDatabase(
      finalPath,
      onCreate: (Database _db, int version) async {
        await _db.transaction((Transaction trx) async {
          await trx.execute(
              "CREATE TABLE User(id INTEGER PRIMARY KEY, age INTEGER);");
          await trx.execute("""
      CREATE TABLE Purchase(
        id INTEGER PRIMARY KEY,
        userId INTEGER REFERENCES User(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
      );
    """);
        });
      },
      onOpen: (Database db) async {
        await db.execute("PRAGMA foreign_keys = ON");
      },
      version: 1,
    );
  }