What is best practice with SQLite and Android ?

Question

What is considered "best practice" when executing queries on a SQLite db within an Android app?

Is it safe to run inserts, deletes and select queries from an AsyncTask's doInBackground ? Or should I use the UI Thread ? I suppose that db queries can be "heavy" and should not use the UI thread as it can lock up the app - resulting in an ANR.

If I have several AsyncTasks, should they share a connection or should they open a connection each ?

Any best practices in this area on android?

Answer 1

The Database is very flexible with multi-threading. My apps hit their DBs from many different threads simultaneously and it does just fine. In some cases I have multiple processes hitting the DB simultaneously and that works fine too.

Your async tasks - use the same connection when you can, but if you have to, its OK to access the DB from different tasks.

Answer 2

Inserts, updates, deletes and reads are generally OK from multiple threads, but answer #1 is not correct. You have to be careful with how you create your connections and use them. There are situations where your update calls will fail, even if your database doesn't get corrupted.

The basic answer.

The SqliteOpenHelper object holds on to one database connection. It appears to offer you a read and write connection, but it really doesn't. Call the read-only, and you'll get the write database connection regardless.

So, one helper instance, one db connection. Even if you use it from multiple threads, one connection at a time. The SqliteDatabase object uses java locks to keep access serialized. So, if 100 threads have one db instance, calls to the actual on-disk database are serialized.

So, one helper, one db connection, which is serialized in java code. One thread, 1000 threads, if you use one helper instance shared between them, all of your db access code is serial. And life is good (ish).

However. If you create multiple helper instances, say one per thread, you are in a bad way. Why? Well, its true the sqlite uses file level locking to prevent database corruption, but the java code simply recognizes the low level error, and deals rather harshly with it. Say you call 'insert' on the SqliteDatabase object. You won't even get an exception. You'll get a little logcat error, but otherwise your app will continue on its merry way, but with no actual insert having been done.

So, multiple threads? Use one helper. Period. If you KNOW only one thread will be writing, you MAY be able to use multiple connections, and your reads will be faster, but buyer beware. I haven't tested that much.

Here's a blog post with far more detail and an example app.

http://kagii.squarespace.com/journal/2010/9/10/android-sqlite-locking.html

Gray and I are actually wrapping up an ORM tool, based off of his Ormlite, that works natively with Android database implementations, and follows the safe creation/calling structure I describe in the blog post. That should be out very soon. Take a look.