Concurrency issues in Android SQLite

Question

I am having issues when I am running an AsyncTask that performs a long delete action on the local SQLite database. If the user rotates the screen, then the Activity is recreated and a new connection to the database is created, while the AsyncTask is running.

I know that SQLite supports multiple reading and one writing at a time, and this will be the case, so I do not understand why it crashes when opening the database as readable while deleting is done...

The failure is the next:

04-26 12:54:21.839: I/Database(3925): sqlite returned: error code = 5, msg = database is locked
04-26 12:54:21.839: E/Database(3925): SELECT locale FROM android_metadata failed
04-26 12:54:21.839: E/Database(3925): Failed to setLocale() when constructing, closing the database
04-26 12:54:21.839: E/Database(3925): android.database.sqlite.SQLiteException: database is locked
04-26 12:54:21.839: E/Database(3925):   at android.database.sqlite.SQLiteDatabase.native_setLocale(Native Method)
04-26 12:54:21.839: E/Database(3925):   at android.database.sqlite.SQLiteDatabase.setLocale(SQLiteDatabase.java:1967)
04-26 12:54:21.839: E/Database(3925):   at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1835)
04-26 12:54:21.839: E/Database(3925):   at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:820)
04-26 12:54:21.839: E/Database(3925):   at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:197)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:172)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:180)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:180)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:180)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:180)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:180)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:180)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:180)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:180)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.openReadableDatabaseWithForeignKeySupport(DatabaseManager.java:180)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.open(DatabaseManager.java:157)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.persistence.DatabaseManager.<init>(DatabaseManager.java:147)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.openerp.models.ConnectionProfile$ConnectionProfileManager.<init>(ConnectionProfile.java:138)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.activities.SelectConnectionProfile.fillData(SelectConnectionProfile.java:71)
04-26 12:54:21.839: E/Database(3925):   at com.caumons.trainingdininghall.activities.SelectConnectionProfile.onResume(SelectConnectionProfile.java:66)
04-26 12:54:21.839: E/Database(3925):   at android.app.Instrumentation.callActivityOnResume(Instrumentation.java:1149)
04-26 12:54:21.839: E/Database(3925):   at android.app.Activity.performResume(Activity.java:3833)
04-26 12:54:21.839: E/Database(3925):   at android.app.ActivityThread.performResumeActivity(ActivityThread.java:2085)
04-26 12:54:21.839: E/Database(3925):   at android.app.ActivityThread.handleResumeActivity(ActivityThread.java:2110)
04-26 12:54:21.839: E/Database(3925):   at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1643)
04-26 12:54:21.839: E/Database(3925):   at android.app.ActivityThread.handleRelaunchActivity(ActivityThread.java:2796)
04-26 12:54:21.839: E/Database(3925):   at android.app.ActivityThread.access$1600(ActivityThread.java:117)
04-26 12:54:21.839: E/Database(3925):   at android.app.ActivityThread$H.handleMessage(ActivityThread.java:932)
04-26 12:54:21.839: E/Database(3925):   at android.os.Handler.dispatchMessage(Handler.java:99)
04-26 12:54:21.839: E/Database(3925):   at android.os.Looper.loop(Looper.java:123)
04-26 12:54:21.839: E/Database(3925):   at android.app.ActivityThread.main(ActivityThread.java:3647)
04-26 12:54:21.839: E/Database(3925):   at java.lang.reflect.Method.invokeNative(Native Method)
04-26 12:54:21.839: E/Database(3925):   at java.lang.reflect.Method.invoke(Method.java:507)
04-26 12:54:21.839: E/Database(3925):   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
04-26 12:54:21.839: E/Database(3925):   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
04-26 12:54:21.839: E/Database(3925):   at dalvik.system.NativeStart.main(Native Method)

I coded the following workaround, but I think this is not very polite:

private void openReadableDatabaseWithForeignKeySupport() {
    try {
        mDatabase = mDatabaseHelper.getReadableDatabase();
        mDatabase.execSQL("PRAGMA foreign_keys = ON");
    } catch (SQLiteException e) {
        /*
         * If there is an exception because the database is locked,
         * we will retry until it is free and can be opened.
         * */
        Log.d(getClass().getName(), e.toString());
        openReadableDatabaseWithForeignKeySupport(); // Recursive call
    }
}

The most curious is that what seems to fail is the setLocale() method called automatically by Android, but the docs says that:

setLocale() Sets the locale for this database. Does nothing if this database has the NO_LOCALIZED_COLLATORS flag set or was opened read only.

Any help will be much appreciated! :)

Answer 1

It's not a great idea to be opening and closing the database regularly like this. Open it once on application startup, and reuse this connection throughout your app's lifetime. You don't even need to bother closing it. SQLite manages the concurrency by itself, so you don't need to worry about multiple threads either.

This will make all your "database is locked" errors go away, as well as make your code tidier and simpler.