SQLITE Best practice

Recently I’ve been doing quite a bit of work with the Android Sqlite database.  Mostly with the android piece of ormlite.

The Android examples cover some basic Sqlite usage, but they really don’t go into depth with regards to proper usage patters, and more importantly, improper usage patters.  Most examples and documentation is slated towards using very basic database queries, and beyond that, creating a ContentProvider.  What never really seems to be covered is stuff like:

• Where do you create and store your SQLiteOpenHelper instances?
• How many should you have?
• Are there any concerns when accessing the database from multiple threads?

If you look around for information you’ll find a lot of partial or incorrect info.  A great example was forwarded to me by Gray yesterday (he runs the ormlite project).  It was on stackexchange…

http://stackoverflow.com/questions/2493331/what-is-best-practice-with-sqlite-and-android/2493839

The first answer basically says you can connect to the sqlite database pretty much how you want, and the Android system will sort it out for you.  Sqlite has file level locking, which will serialize access and prevent trouble.

This is an example of a little truth and a little experience leave you open to a lot of pain.

Open source is great, by the way.  You can dig right into the code and see what’s going on.  From that and some testing, I’ve learned the following are true:

• Sqlite takes care of the file level locking.  Many threads can read, one can write.  The locks prevent more than one writing.
• Android implements some java locking in SQLiteDatabase to help keep things straight.
• If you go crazy and hammer the database from many threads, your database will (or should) not be corrupted.

Here’s what’s missing.  If you try to write to the database from actual distinct connections at the same time, one will fail.  It will not wait till the first is done and then write.  It will simply not write your change.  Worse, if you don’t call the right version of insert/update on the SQLiteDatabase, you won’t get an exception.  You’ll just get a message in your LogCat, and that will be it.

This issue may be tough to notice.  You have to have more than one distinct connection, and you have to write from more than one thread at the same time.  In most apps that are doing light database updates, that’s a lot of stars to align at the same time.

The first problem, real, distinct connections.  The great thing about open source code is you can dig right in and see what’s going on.  The SQLiteOpenHelper class does some funny things.  Although there is a method to get a read-only database connection as well as a read-write connection, under the hood, its always the same connection.  Assuming there are no file write errors, even the read-only connection is really the single, read-write connection.  Pretty funny.  So, if you use one helper instance in your app, even from multiple threads, you never *really* using multiple connections.

Also, the SQLiteDatabase class, of which each helper has only one instance, implements java level locking on itself.  So, when you’re actually executing database operations, all other db operations will be locked out.  So, even if you have multiple threads doing stuff, if you’re doing it to maximize database performance, I have some bad news for you.  No benefit.

I wrote a test application to show how this all works.

• Download Android APK File.
• Github Project Page.

UPDATE!!!! I switched hosting providers and lost the file links. I recreated this app, but it only has 2 buttons. One tests a single helper, and the other tests multiple helpers. Enjoy.

Buttons:

• One Helper – All threads get one helper object.  Writes are not in batch transactions.
• Many Helpers – Each thread gets its own helper.  Writes are not in batch transactions.  Since each thread has its own helper, they all have real independent db connections.

If you run the “One Helper” processes, you should see no failures.  If you run the Many Helpers, you should see failures.  Very important: You have to run this on an actual phone!!! On my desktop, its hard to get lock ups.  This is due, I imagine, to significantly better disk performance on my desktop.  I also imagine this contributes to the misconception that Android will just sort of “handle it” with regards to db locking.

Your phone may not see this issue, but if not I assume that’s because the phone performance is significantly better than mine (Samsung Vibrant, Android 2.1) UPDATE!!! Just tried on a Droid Bionic, and it had collisions. If this phone can’t deal, your phone can’t either.

Interesting Observations

If you turn off one writing thread, so only one thread is writing to the db, but another reading, and both have their own connections, the read performance shoots WAY up and I don’t see any lock issues.  That’s something to pursue.  I have not tried that with write batching yet.

If you are going to perform more than one update of any kind, wrap it in a transaction.  It seems like the 50 updates I do in the transaction (this was in the original app version, not the new one) take the same amount of time as the 1 update outside of the transaction.  My guess is that outside of the transaction calls, each update attempts to write the db changes to disk.  Inside the transaction, the writes are done in one block, and the overhead of writing dwarfs the update logic itself.

Performance of database calls is all over the place at different times.  Any serious db calls should probably be in an async process, just in case there’s something else going on behind the scenes.

Anyway, a screenshot (UPDATE!!! This screenshot is old, but I have other stuff to do right now).