Search code examples
androidsqlitesqlexception

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

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

Solution

  • 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.