Monthly Archives: August 2014

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);
        public void onCreate(SQLiteDatabase db) {
        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() {
    public void run() {

new Thread(new Runnable() {
    public void run() {

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“.

Java: Unescaping HTML entities, or Don’t use libraries

Some time ago I needed to write some Android code which had to convert HTML entities to their string representations. In case you don’t know, HTML entities is the way of encoding charaters where you can write “&” as either “&” (by name) or “& #38;” (by ascii code). This is a well-documented and widely used conversion, and I figured it should already be available somewhere. So there’s Apache Commons Lang jar. Normally I don’t trust 3rd-party libraries, but it’s Apache. It just has to be super optimal and rock solid all around.

So I added this jar and started writing other code. Luckily, I was running performance tests on that. On some point I noticed that performance dropped very significantly. I started looking and found that, even though I barely had any entities in my input, Apache code really took a while to run.

The library is open source, so I looked into what it does. It was quite nice code, but also it did a lot of unnecessary stuff. Not the kind of stuff that handles rare cases – rather, the kind that makes it modular, easy to follow, and generally smart-looking. But not extremely effective. So I spent some time and tried to just rewrite the unescape function in a stupid way. Then I ran this test:

String s = "This is a test string & it has an entity";

Log.i("test", "start test 1");
long time1 = System.currentTimeMillis();

for (int i = 0; i < 10000; i++) {
    String s1 = org.apache.commons.lang3.StringEscapeUtils.unescapeHtml3(s);

Log.i("test", "start test 2");
long time2 = System.currentTimeMillis();

for (int i = 0; i < 10000; i++) {
    String s1 = StringUtils.unescapeHtml3(s);

Log.i("test", "end test 2");
long time3 = System.currentTimeMillis();
Log.i("test", "time 1: " + (time2 - time1) + " time 2: " + (time3 - time2));

Here are the results, they are rather interesting:

"This is a test string &amp; it has an entity"
time 1: 3421 time 2: 80
"This is a test string - it has no entity"
time 1: 3767 time 2: 5

Now, I’m not claiming my code is as good as Apache’s. My version may not handle some extremely rare corner cases. But so far it has been tested with very rapid calls in a large project, with multiple languages, and there has been no issues.

This is a great example of why I distrust 3rd-party libraries in general. It’s fine if they really do something serious. But free utility libraries doing small tasks are usually not worth it. They just make simple things look complicated and important. Take this entity conversion. It’s really nothing but a 2-screen function plus a data map. But what if you didn’t know that? You’d be stuck with a black box, which takes, literally, thousands of times more resources than it should, and you’d be thinking “that’s just the amount this task takes, nothing to be done about it”. Which is, I suspect, exactly the case with lots of people using this very common library.