SQLiteDatabase nested transaction and workaround

Question

Edited:
Yes, SQLite doesn't support nested transaction, but docs declare that SQLiteDatabase does.
Situation
I've got a method containing transaction and I need to call this method from another transaction.
Additionally - both transactions work on the same set of records, but update different columns.
Problem
It looks like results of my outer transactions are cancelled by inner one, still both are marked clean by setTransactionSuccessful() and finished by endTransaction() - I've checked this.
Questions
- Any idea why this may happen?
- Is there a recommended way to do such transactions?

Answer 1

"Nested Android Transactions" do not use SQLites nested transaction/savepoint support.

Rather a nested Android transaction suppresses manifesting a SQLite transaction. The nested transaction cannot be rolled back itself, because it does not exist apart from the outside transaction. This can be seen here with the mTransactionStack == null guard.

The only way to actually support nested transactions - which SQLite does support, just not with BEGIN/COMMIT - is to to manually use the SAVEPOINT/RELEASE commands. Of course, designing the code to not rely on this will eliminate the extra manual management this requires.

(I would probably move all the transaction work out of the actual individual operations, leaving the management to the high-level caller; this works fairly well for a UoW pattern, but might not always be applicable.)

Answer 2

You can nest transactions with the Android sqlite API, with caveats:

Transactions can be nested. When the outer transaction is ended all of the work done in that transaction and all of the nested transactions will be committed or rolled back. The changes will be rolled back if any transaction is ended without being marked as clean (by calling setTransactionSuccessful). Otherwise they will be committed.

Another approach I've seen used with sqlite in general is to pass in a boolean parameter isInTransaction that tells the called method whether it should handle transactions on its own, or to let the caller handle transactions.