SQLiteException: cannot start a transaction within a transaction (code 1)

Question

I'm having a problem finishing an SQLite transaction and I'm totally baffled on how to do it. It totally looks like this bug from 2007.

I'm creating my employee table (which references another table entity) as follows (edited for brevity):

CREATE TABLE employee (_id INTEGER NOT NULL, PRIMARY KEY ( _id ) , 
FOREIGN KEY (_id) REFERENCES entity(_id)  ON DELETE cascade ON UPDATE cascade DEFERRABLE INITIALLY DEFERRED )

Then I run a transaction as follows (using a SQLiteDatabase object, I also report status for transaction in log):

>> In transaction: false
beginTransaction();
>> In transaction: true
doSomeWrongINSERT();
setTransactionSuccessful();
endTransaction();
>> In transaction: false
SQLiteConstraintException: foreign key constraint failed (code 19)

Ok, all normal. Now if I try to start a new transaction or rollback, both fail:

>> In transaction: false
beginTransaction();
android.database.sqlite.SQLiteException: cannot start a transaction within a transaction (code 1)

---

>> In transaction: false
endTransaction();
java.lang.IllegalStateException: Cannot perform this operation because there is no current transaction.

Please note all this did not happen if FKs are immediate instead of deferred.

---

Bug report: https://issuetracker.google.com/issues/37001653

Answer 1

This appears to be a bug in Android. In the SQLiteSession class, endTransactionUnchecked clears its transaction state (mTransactionStack) before the COMMIT is executed, and does not expect that the database's transaction might still be active. (I don't think this can ever happen without a deferred constraint.)

Submit a bug report.

Answer 2

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

I still reported to Android as issue nº74751.