SQLiteException: Cannot start a transaction in a transaction (code 1)

Question

I am having a problem completing a SQLite transaction and am completely puzzled by how to do this. This is completely similar to this error since 2007.

I create an employee table (which refers to another entity table) 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 start the transaction as follows (using the SQLiteDatabase object, I also report the status of the transaction in the log):

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

OK, everything is fine. 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. 

Note that all this did not happen if the FKs are immediate rather than delayed.

Answer 1

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

I was still reporting Android as issue nº74751 .

Answer 2

This seems to be a bug in Android. In the SQLiteSession class, endTransactionUnchecked clears its transaction state ( mTransactionStack ) before executing COMMIT and does not expect the database transaction to be active. (I do not think this can happen without a pending restriction.)

Send bug report .

Answer 3

To summarize the answers (thanks to: @CL and @ m0skit0), you should restore the transaction status in case of any exception inside endTransaction . This can be done in a central place by overriding the applyBatch 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; } } }