Database pitfalls

Creating and referencing an SQLite database is straightforward in an Android app. The documentation you’ll find at the Android Developer site and around the web is more than enough to get up and running rapidly.

But as you can imagine, there are a few nasty pitfalls awaiting behind such a simple API. As I was playing with Android’s database classes, I rapidly came across situations that required a deeper appreciation of their stucture and roles.

Database References

The docs caution that SQLiteOpenHelper’s getReadableDatabase() method “may take a long time to return”. I took this to heart and experimented with a database connection that stayed open across multiple queries.

I accessed a database of foods via getReadableDatabase() and queried it to populate a Cursor with all the records in the food table and display them on screen. However, I did not close() the database object after use, intending to call Cursor’s requery() method if I needed to refresh the listing.

To check this was a valid way to proceed, I then added a new food to the table and requeried. I didn’t get the output I was expecting, and the following error message turned up in the logs:

Invalid statement in fillWindow()

After a bit of head-scratching, I found my problem. In the code that added the new record, I was calling getWritableDatabase(), using the database that came back to save the new record, and then dutifully closing it. Since this was the only close() call, I wondered if it was somehow closing my ‘readable’ database too. This is the snippet that confirmed my suspicion:

Sure enough, “same” was logged. I never had readable and writable databases, just two differently named references. Calling close() shuts down both, because there was only ever one.

A dive into the SQLiteOpenHelper source confirms this. Each instance of this helper class creates and manage one instance of SQLiteDatabase at a time. There is a small implementation detail distinguishing getWritableDatabase() and getReadableDatabase(). The second method calls the first and returns the resulting database if it can. However, if this fails, it will then try to open the database in read-only mode.

Consequently, it is still good practice to call getReadableDatabase() if you will only be retrieving information, because there might just be an occasion when this will succeed where a request for a read-write database instance would fail. Whenever you’re doing a mix of reading and writing, however, you need to keep it in mind that you’re dealing with the same instance every time.

Concurrent access

While I was looking at the Android source I noticed that each SQLiteDatabase uses a ReentrantLock to allow only one operation at a time on the underlying database. This means that if you only ever create one SQLiteOpenHelper, and you’re the only client of your database on the system, your queries will never encounter conflicts. Even if two threads try to query simultaneously SQLiteOpenHelper, they will not conflict. Instead, one will block until the other has finished.

Should you want to, you can get round this by disabling this conservative locking strategy, or by creating multiple instances of SQLiteDatabase which reference the same database. But in any normal application, I don’t think you’ll hit performance bottlenecks that require you to do this. Instead, play it safe, keeping one database reference which you can share between threads pretty freely.

So how should you manage your SQLiteOpenHelper to make sure there’s only ever one of it? As is often the case with Android, there are conflicting recommendations out there, which I will try to summarise below. The decision over which one to use is going to come down to your own unique requirements.

The decision is also bound up with a choice of context. In case you’re not familiar, an Android Context represents aspects of the system on which an application runs. It’s used to access files and resources, so Android classes will often take one as a constructor argument. SQLiteOpenHelper is no exception. It needs a context so it can get hold of the physical file that represents your SQLite database. There are two key contexts, Application and Activity.

Leave a Reply

Your email address will not be published. Required fields are marked *