Android: SQLiteDatabase locking and multi-threading

One of the confusing topics on Android is working with SQLite database. It is simple to use, but there’s almost no explanations on how it works in more difficult scenarios, especially when multiple threads are involved.

I’ll review the usage patterns around SQLiteDatabase first, because most problems start with getting the DB object incorrectly.

Single instance of SQLiteOpenHelper

First thing you need to know is that there should be only one instance of SQLiteOpenHelper in your app. See “Android Sqlite Locking” and “Single SQLite connection” by Kevin Galligan (note: Android source code has changed a lot since then, details may not be entirely accurate). Reason for that is, OpenHelper caches an internal instance of SQLiteDatabase, and if you have many OpenHelpers, you’ll also have many poorly synchronized instances of the same database. I like a pattern which is slightly different from Kevin’s, but does the same thing.

public class Database {
    private static final int DATABASE_VERSION = 1;
    private static OpenHelper openHelper = new OpenHelper();

    private static class OpenHelper extends SQLiteOpenHelper {
        private OpenHelper() {
            super(App.getAppContext(), "test.db", null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    } 

    public static SQLiteDatabase getDatabase() { // pattern 1
        return openHelper.getWritableDatabase();
    }

    public static int fetchData() { // pattern 2
        SQLiteDatabase db = openHelper.getWritableDatabase(); 
        // run some queries on the db and return result
    }
}

See my other post about App.getAppContext().

I’m showing you two different patterns in this example. If you only want this class as a DB opener, and will handle the queries somewhere else, then you’re good with just getDatabase(). However, you can also make this class the data access object for the database, and run all queries right here. Then, you don’t need getDatabase(), you’ll want to add a bunch of fetchData()s instead.

Writable vs Readable

Now, you might have a question: why does getDatabase() translate to getWritableDatabase()? Wouldn’t it be safer to return getReadableDatabase() when appropriate?

Turns out, inside OpenHelper, that’s the same object. That’s even mentioned in the documentation. Barring some extremely rare cases, you will always receive the same reference for both. So there’s usually not much reason to bother.

Saved instances of SQLiteDatabase

Another question: wouldn’t it be more effective to save SQLiteDatabase to a member variable, instead of opening it from helper every time?

public class Database {
    private static OpenHelper openHelper = new OpenHelper();
    private static SQLiteDatabase db = openHelper.getWritableDatabase();

    public static int fetchData() { 
        // just use this.db, it's already there!
    }
}

As it turns out, there’s no reason to do it. SQLiteOpenHelper already does the same thing – it has an internal instance of the DB, which it can manage better than you could. Plus, saved instance can cause you problems. See this discussion as an example. So, using OpenHelper is just a safer pattern.

Closing the database

Another non-documented point is, whether you should SQLiteDatabase.close(), and when exactly. Popular answers are Activity.onStop(), and Application.onTerminate(). They are of course both wrong, because onTerminate is not a real event, and you are likely to need your DB in more than one activity. Possible problem is mentioned here, and “Concurrent Database Access” offers a solution based on reference counting. Also, there’s a google groups thread, where a trustworthy source says you don’t need that at all.

To answer this question, I looked into Android code (v4.4.4). Turns out, Android already implements the reference counting internally. Basically, whenever you do anything with SQLiteDatabase, it increments a counter, and when you’re done, it decrements it. When all operations are done, DB just closes itself. I saw absolutely no reason to second-guess this system. Just don’t close the DB. You have to close cursors though.

Multi-thread access

Now that we have a properly initialized DB, next question is – is it at all thread-safe? And if it is, how exactly does it work? Is a transaction thread-safe? What about iterating a cursor? Again, popular answer is “you could use synchronized sections and semaphores”, which really translates to “nobody can possibly know”. Documentation is really vague on this subject. Android source makes an impression of being very thread-safe, but also doesn’t seem to have any apparent thread locks, so from a first glance it looks like that’s entirely up to SQLite’s internal file locking.

To understand this topic, I ran some simple tests with setup like this:

new Thread(new Runnable() {
    @Override
    public void run() {
        Database.runTransaction("A");
    }
}).start();

new Thread(new Runnable() {
    @Override
    public void run() {
        Database.runTransaction("B");
    }
}).start();

Results are actually very simple:

– SQLiteDatabase is thread-safe.

– All operations, including insert, update, and read, are mutually exclusive. Meaning, two operations from different threads can not run in parallel. Transaction counts as one operation.

– Read operations are caching data on query(), before cursor.moveToNext(). Therefore, if query() was completed prior to a transaction, you can iterate the cursor parallel to the executing transaction, and that transaction will not affect the received data. It is even possible to entirely drop a table while a cursor is open on it.

Parallel operations

Rules above, however, only apply to the default Android setup. There are situations when you might want a DB to behave differently. Like, if you have some huge writes, and you don’t want to block reads for them. I haven’t explored this subject much, but here’s what you’ll be looking for.

First thing you’ll try is setLockingEnabled(false). It says right there that it doesn’t work anymore, but nobody believes that. Well it doesn’t. Here’s the precise implementation of this method from the Android source: { } . So don’t bother.

Next you might try to beginTransactionNonExclusive(). Thing is, by default, it works exactly like beginTransaction(). I spent some time trying to figure that out, because it kinda looks like it should allow parallel reads, and it doesn’t. My understanding is, this method only works with enableWriteAheadLogging(). And that latter one is actually the way to go, if you want to allow parallel DB operations. Also check out “Handling some SQLite issues“.