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

Homiletics

Are you sure endTransaction() runs in your code under all circumstances, even when exceptions are thrown ? i.e. is transaction end inside a finally block ? – Noiseless

@S.D. Yes, always executed, it's in a finally block. – Homiletics

@S.D. In case it was not executed, then the rollback would not fail anyway. – Homiletics

Answer 1

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.

Homiletics

I wonder why they marked the issue as "Won't fix (Obsolete)". – Bedridden

@Bedridden They do that many times. I understand they have more important issues to worry about. – Homiletics

Answer 2

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.

Vulgus

Answer 3

To summarise the answers (thanks to to: @CL & @m0skit0 ) it worth recovering the transaction status in case of any exception inside endTransaction. This can be done in a centric place if to override applyBatch of your ContentProvider implementation:

    @NonNull
    @Override
    public ContentProviderResult[] applyBatch(@NonNull ArrayList<ContentProviderOperation> operations) throws OperationApplicationException {

        SQLiteDatabase db = mDbHelper.getWritableDatabase();    // automatically opens db, if closed.
        try {
            db.beginTransaction();
            ContentProviderResult[] results = super.applyBatch(operations);
            db.setTransactionSuccessful();
            return results;
        } finally {
            try{
                db.endTransaction();
            }catch (Throwable e2){
                //Log.e(LOG_TAG,"Failed to close a db transaction. The only way to recover is to close the db.", e2);
                db.close();
                throw e2;
            }
        }
    }

Plenary

You should catch the specific exceptions that are causing the problem, and you should never catch Throwable since Error is not meant to be caught. – Homiletics