SQLiteDatabase Nested Transaction and Workaround

Question

Edited by:
Yes, SQLite does not support a nested transaction , but docs declare what SQLiteDatabase does.
Situation
I have a method containing a transaction, and I need to call this method from another transaction.
Additionally - both transactions work with the same set of records, but different columns are updated.
Problem
It seems that the results of my external transactions are canceled by the internal one, but both of them are marked clean with setTransactionSuccessful () and endTransaction () ends - I checked this.
Questions
- Any idea why this could happen?
- Is there a recommended way to make such transactions?

Answer 1

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

Transactions can be nested. When an external transaction is completed, all work performed in this transaction and all nested transactions will be committed or canceled. Changes will be undone if any transaction is completed without marking as clean (by calling setTransactionSuccessful). Otherwise they will be committed.

Another approach that I saw in sqlite in the general case is to pass the boolean parameter isInTransaction , which tells the called method whether it should process the transactions on its own or allow the handler to process the transactions.

Answer 2

Android Nested Transactions do not use SQLite nested transaction / save support.

Rather, an Android nested transaction suppresses the manifestation of a SQLite transaction. A nested transaction cannot be undone on its own because it does not exist separately from an external transaction. This can be seen here with mTransactionStack == null guard.

The only way to actually support nested transactions that SQLite supports rather than BEGIN / COMMIT is to manually use the SAVEPOINT / RELEASE commands. Of course, designing the code so as not to rely on this will eliminate the additional manual control that it requires.

(I would probably move all transactional work from the actual individual operations, leaving control to the high-level callers, this works pretty well for the UoW pattern, but may not always be applicable.)

Answer 3

Currently, I am finished with a symmetrical method design (similar to laalto's suggestion), which ensures proper operation. This is a workaround that explicitly "converts" nested transactions into one. Now I can name them separately or one from the other (without side effects, which I still do not understand).
There he is:

  public void method1() { SQLiteDatabase db = dbhelper.getWritableDatabase(); boolean doAsTransaction = !db.inTransaction(); if (doAsTransaction) db.beginTransaction(); try { // ... if (doAsTransaction) db.setTransactionSuccessful(); } catch (Exception e) { // ... } finally { // ... if (doAsTransaction) db.endTransaction(); } } public void method2() { SQLiteDatabase db = dbhelper.getWritableDatabase(); boolean doAsTransaction = !db.inTransaction(); if (doAsTransaction) db.beginTransaction(); try { // ... method1(); if (doAsTransaction) db.setTransactionSuccessful(); } catch (Exception e) { // ... } finally { // ... if (doAsTransaction) db.endTransaction(); } } 

Answer 4

You can execute a nested transaction using the source SQL for the savepoint in execSQL() as follows:

 db.execSql("SAVEPOINT test"); // declare savepoint // ... do some operations db.execSql(";ROLLBACK TO test"); // rollback db.execSql("RELEASE test"); // save changes 

A semicolon before ROLLBACK is required because without it, the Android database will try to call endTransaction() . For more details, see the code for the methods android.database.sqlite.SQLiteSession#executeSpecial and android.database.sqlite.SQLiteSession#execute .