An Android Developer's Approach to Handling SQLite Hangups
by Jesse Hendrickson, Lead Android Engineer
So, here’s the situation. I was stress testing user authentication by repeatedly logging in and out of our Android application. Most of the time, this works great. Logging out would successfully clear all locally stored user data, drop the backing SQLite database, and return the user the Login screen. Every so often, however, logging out would cause the application to hang indefinitely. (This is, obviously, a bad thing).
Digging a bit deeper, I found that calling close() on my SQLiteOpenHelper singleton was causing the hangup. Even though Android’s SQLiteDatabase uses an internal locking mechanism to accommodate access by multiple Threads, deadlock occurred when I tried to close the database with many Threads writing to it. This explains why the hangups would not consistently occur: they only happened while simultaneously logging out in one Thread, and locally persisting server data in another.
As a result, it was impossible to close() the database, and subsequently impossible to move ahead in the logout process. Not good.
Knowing that I didn’t want to to close the SQLiteDatabase while other Threads were writing to it, I reached into the depths of computer science knowledge and decided to do the following:
Sleep.
Well, I myself didn’t sleep, regrettably. I sleep()’d the Thread that calls close() until the other Threads finished their writes and the database lock became available. I added this simple method to my SQLiteOpenHelper subclass:
public void closeSafely() {
try {
SQLiteDatabase db = getWritableDatabase();
while (db.isDbLockedByOtherThreads()) {
Thread.sleep(50);
}
close();
} catch (InterruptedException e) { }
}
SQLiteDatabase.isDbLockedByOtherThreads() does exactly what is sounds like: it checks to see if the database lock is held by another Thread.
Adding these few lines of code solved the deadlock issue. To avoid sleeping for too long, I added some logic that prevented other Threads from writing to the database when the user is logging out, so the closeSafely() method will only sleep at most for a couple hundred milliseconds.
Simple solutions like the one above often work surprisingly well. Avoid convincing yourself that a problem as complicated Thread and SQLite synchronization needs to have just as complicated a solution!