Sqlite transaction is blocking Android ui

Question

In my android app there is a relatively long running AsyncTask I'm running to update the database when the app starts up. I'm needing to wrap that in a transaction to roll things back if the user exits the app before the task is finished. However since wrapping the code in a transaction it is blocking the ui untill the task is complete. Why would this be since the code is already running in a separate thread?

I'm using ORMLite and this is the wrapper for the transaction basically,the code to update the db goes inside call().., before adding the code to update the db inside a transaction there was no locking of the ui...

public ConnectionSource source; 
@Override
protected Boolean doInBackground(Context... params) {
    try {
        TransactionManager.callInTransaction(source, new Callable<Void>() {
            public Void call() throws Exception {
                return null;
            }
        });

score 7 · Accepted Answer · edited May 23 '17 at 10:29

7

Unfortunately, SQLite transactions are exclusive and they block all other database activity. I suspect that even though you are in another thread, the UI thread is doing some sort of database activity which has to wait for the transaction to finish.

Here's a related question.
Here's the docs about SQLite transaction levels.

edited May 23 '17 at 10:29

Community

1
1

answered Oct 25 '11 at 17:00

Gray

115,027
24
293
354

ive found code in the ui that would be trying to access the db at the same time – TNM Oct 26 '11 at 06:12
Android with API levels >= 11 (Android 3.0), support a method called beginTransactionNonExclusive(). However, it that ORMLite supports calling this method on any version of Android. https://groups.google.com/forum/#!msg/ormlite-android/Wq9BUyK-O2Q/Ki1coRNF6PAJ - will writing the transaction in SQL, BEGIN IMMEDIATE TRANSACTION... work on older versions of Android, or is this a limitation of the underlying sqlite library? – Mark Jan 22 '14 at 23:19
Huh. Any idea which Sqlite versions support that @Mark? – Gray Jan 23 '14 at 12:06
Unfortunately the sqlite documentation isn't quite as nice as the Android docs - doesn't tell you when a feature appeared: http://www.sqlite.org/lang_transaction.html - I'll pop open some emulators and do some tests to see how far back that syntax will work. – Mark Jan 23 '14 at 14:32
@Gray Is there any fix for that or we have to manage it by ignoring other database operations? – Kalpesh Jan 20 '16 at 11:07

score 0 · Answer 2 · answered Oct 03 '16 at 10:34

Since API 11 Android allows one writing thread and many reading threads in WAL mode. You have to switch to WAL mode and use beginTransactionNonExclusive() to avoid blocking reading threads.

int flags = SQLiteDatabase.CREATE_IF_NECESSARY;
if(walModeEnabled) {
   if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) {
       flags = flags | SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING;
   }
}

SQLiteDatabase db = SQLiteDatabase.openDatabase(databasePath.getPath(), null, flags);

// backward compatibility hack to support WAL on pre-jelly-bean devices
if(walModeEnabled) {
   if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB &&
           Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN) {
       db.enableWriteAheadLogging();
   } else {
       Log.w(TAG, "WAL is not supported on API levels below 11.");
   }
}

Check my article for more details about WAL mode and concurency in SQLite:

http://www.skoumal.net/en/parallel-read-and-write-in-sqlite/

Sqlite transaction is blocking Android ui

2 Answers2