Android – How to avoid concurrency problems when using SQLite on Android

androiddatabasesqlite

What would be considered the best practices when executing queries on an SQLite database 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 database queries can be "heavy" and should not use the UI thread as it can lock up the app – resulting in an Application Not Responding (ANR).

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

Are there any best practices for these scenarios?

Best Answer

Inserts, updates, deletes and reads are generally OK from multiple threads, but Brad's answer 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).

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.

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.

Android Sqlite Locking (Updated link 6/18/2012)
Android-Database-Locking-Collisions-Example by touchlab on GitHub

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.

In the meantime, there is a follow up blog post:

Single SQLite connection

Also checkout the fork by 2point0 of the previously mentioned locking example:

Android-Database-Locking-Collisions-Example by 2point0 on GitHub

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Android – How to save an activity state using the save instance state

You need to override onSaveInstanceState(Bundle savedInstanceState) and write the application state values you want to change to the Bundle parameter like this:

@Override
public void onSaveInstanceState(Bundle savedInstanceState) {
  super.onSaveInstanceState(savedInstanceState);
  // Save UI state changes to the savedInstanceState.
  // This bundle will be passed to onCreate if the process is
  // killed and restarted.
  savedInstanceState.putBoolean("MyBoolean", true);
  savedInstanceState.putDouble("myDouble", 1.9);
  savedInstanceState.putInt("MyInt", 1);
  savedInstanceState.putString("MyString", "Welcome back to Android");
  // etc.
}

The Bundle is essentially a way of storing a NVP ("Name-Value Pair") map, and it will get passed in to onCreate() and also onRestoreInstanceState() where you would then extract the values from activity like this:

@Override
public void onRestoreInstanceState(Bundle savedInstanceState) {
  super.onRestoreInstanceState(savedInstanceState);
  // Restore UI state from the savedInstanceState.
  // This bundle has also been passed to onCreate.
  boolean myBoolean = savedInstanceState.getBoolean("MyBoolean");
  double myDouble = savedInstanceState.getDouble("myDouble");
  int myInt = savedInstanceState.getInt("MyInt");
  String myString = savedInstanceState.getString("MyString");
}

Or from a fragment.

@Override
public void onViewStateRestored(@Nullable Bundle savedInstanceState) {
    super.onViewStateRestored(savedInstanceState);
    // Restore UI state from the savedInstanceState.
    // This bundle has also been passed to onCreate.
    boolean myBoolean = savedInstanceState.getBoolean("MyBoolean");
    double myDouble = savedInstanceState.getDouble("myDouble");
    int myInt = savedInstanceState.getInt("MyInt");
    String myString = savedInstanceState.getString("MyString");
}

You would usually use this technique to store instance values for your application (selections, unsaved text, etc.).

Best Answer

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

Android – How to save an activity state using the save instance state

Related Topic