3

I have a Room database with entities A and B. B references A through a foreign key. The foreign key is set to be deferred until the transaction has ended:

@ForeignKey(entity = A.class, deferred = true, parentColumns = "_id", childColumns = "A_id")

When I delete a row in A that is referenced by a row in B while in a transaction (e.g. using RoomDatabase.runInTransaction()), the transaction fails and an SQLiteConstraintException is thrown, as expected. However, when I try to start another transaction on the same database object, I get the following exception:

android.database.sqlite.SQLiteException: cannot start a transaction within a transaction (code 1 SQLITE_ERROR)

In addition, the following is printed to Logcat:

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

So it looks like the initial transaction was not ended in the underlying SQLite layer when SQLiteConstraintException was thrown. Is this intended behaviour?

From this state there does not seem to be a way to end the underlying transaction. Calling RoomDatabase.inTransaction() yields false, and RoomDatabase.endTransaction() (marked obsolete) does not end the transaction.

I am using Room version 2.1.0.

user4157124
  • 2,809
  • 13
  • 27
  • 42
robert
  • 133
  • 2
  • 5
  • I'm seeing the same bug with deferred FKs. More of a work-around than a solution: if an exception is thrown inside the transaction, then operations are rolled back correctly and db is left in a working state. So the last thing I do in my transaction is to manually check if my FKs are valid, and if not, throw my own Exception. Otherwise exit transaction normally and the FK enforcement is restored. – Splash Mar 22 '21 at 23:57

1 Answers1

1

That happened because of bug in SQLiteDatabase class: If you set a FK as DEFERRABLE INITIALLY DEFERRED and it fails during a transaction, after commit(), database session controller is left in an unstable state where it doesn't allow a new transaction nor closing the previous one.

There is a workaround: close the DB and open it again. That will update the transaction status correctly.

I prefer to check FK constraint manually before commit transaction and throw SQLiteConstraintException exception in case of FK violation. Check can be done with PRAGMA foreign_key_check

geisy
  • 26
  • 2