Android SQLite: Should I use beginTransactionNonExclusive() instead of beginTransaction() for ALL transactions if my app is only for API >= 11?

Question

When doing a transaction in Android, there are beginTransaction() and beginTransactionNonExclusive() methods starting from API 11.

For me it's not very clear from the documentation whether if my application is targetting only API 11 devices and above, I should use beginTransactionNonExclusive() for all transactions I have in my application. Is there any downsides?

It is also mentioned that, if I understand it correctly, for beginTransactionNonExclusive() to work correctly, the database must have Write-Ahead Logging enabled.

I tried to look for some articles about this, but I can't find anything from Google regarding this, and I found this article that looks great but it is in Japanese.

Any help in guiding when and how beginTransactionNonExclusive() should be used would be very much appreciated.

Answer 1

I think you should use beginTransactionNonExclusive together with enableWriteAheadLogging.

And according to the documentation:

It is a good idea to enable write-ahead logging whenever a database will be concurrently accessed and modified by multiple threads at the same time. However, write-ahead logging uses significantly more memory than ordinary journaling because there are multiple connections to the same database. So if a database will only be used by a single thread, or if optimizing concurrency is not very important, then write-ahead logging should be disabled.]

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#enableWriteAheadLogging()

Answer 2

beginTransactionNonExclusive set TRANSACTION_MODE_IMMEDIATE transactionMode flag but beginTransaction set TRANSACTION_MODE_EXCLUSIVE transactionMode flag. Due to SQLiteSession.java logic:

  // Set up the transaction such that we can back out safely
        // in case we fail partway.
        if (mTransactionStack == null) {
            // Execute SQL might throw a runtime exception.
            switch (transactionMode) {
                case TRANSACTION_MODE_IMMEDIATE:
                    mConnection.execute("BEGIN IMMEDIATE;", null,
                            cancellationSignal); // might throw
                    break;
                case TRANSACTION_MODE_EXCLUSIVE:
                    mConnection.execute("BEGIN EXCLUSIVE;", null,
                            cancellationSignal); // might throw
                    break;
                default:
                    mConnection.execute("BEGIN;", null, cancellationSignal); // might throw
                    break;
            }
        }

It finally came to differ in IMMEDIATE and EXCLUSIVE that SQLite official documentation says:

EXCLUSIVE is similar to IMMEDIATE in that a write transaction is started immediately. EXCLUSIVE and IMMEDIATE are the same in WAL mode, but in other journaling modes, EXCLUSIVE prevents other database connections from reading the database while the transaction is underway.

p.s: WAL is short for write-ahead logging

on the other hand, Android Document says:

Writers should use beginTransactionNonExclusive() or beginTransactionWithListenerNonExclusive(android.database.sqlite.SQLiteTransactionListener) to start a transaction. Non-exclusive mode allows database file to be in readable by other threads executing queries.

I know you may confuse. It's more a trade-off to who you trust more! I prefer Android Documents, which means to use them together.

in database creation use :

db.enableWriteAheadLogging();

and for transaction use :

db.beginTransactionNonExclusive();

p.s: don't forgot that enableWriteAheadLogging returns a boolean that specifies it enabled so use beginTransactionNonExclusive when you sure is enabled.