Best way to access database in multi threaded application Android?

Question

Note: Please don't mark this question as a duplicate. I have gone through several similar questions but couldn't find a satisfactory answer.

I have been working on an application which uses Sqlite Database. We are following singleton pattern which ensures that we can create only one instance of our helper class throughout our application.

public class CustomSqliteHelper extends SQLiteOpenHelper {

  public static CustomSqliteHelper getInstance(Context context) {
    if (instance == null) {
      synchronized (CustomSqliteHelper.class) {
        if (instance == null) {
          instance = new CustomSqliteHelper(context);
        }
      }
    }
    return instance;
  }
}

But sometimes the application crashes with SQLiteDatabaseLockedException. I understand this exception comes when more than one thread/process tries to write to the database at one time. Even if one thread/process tries to read the database when the write operation is still going on, this exception will be thrown.

So I have been reading a lot about this and the possible ways to prevent this from happening. A lot of posts suggests using ContentProvider instead of directly extending SqliteOpenHelper class and performing operations on the database object. While reading one of the posts, this post mentioned that while using Content Provider, you don’t need to manually take care of the multi threaded environment.

Although the ContentProvider lacks in thread-safety, often times you will find that no further action is required on your part with respect to preventing potential race conditions. The canonical example is when your ContentProvider is backed by a SQLiteDatabase; when two threads attempt to write to the database at the same time, the SQLiteDatabase will lock itself down, ensuring that one will wait until the other has completed. Each thread will be given mutually exclusive access to the data source, ensuring the thread safety is met.

The above quote seems sounds confusing because first it mentions that ContentProvider does not support thread safety. But he concludes that the application developer doesn't need to do anything on his part to achieve concurrency.

Also, if I choose to use SqliteOpenHelper, what will be the best way to prevent these crashes? I have been thinking of using locks for every db operation.

 public class CustomSqliteHelper extends SQLiteOpenHelper {

 private String lock = "lock";

 public void insert(){
    synchronized(lock){
       // Do the insert operation here.
    }
 }


 public void update(){
    synchronized(lock){
       // Do the update operation here.
    }
 }
 }

But one of my team members advised me not to do so because Java locks are expensive.

After going through one of the most popular projects on Github, I found that the developers have advised to wrap every database operation inside a transaction.

public void insert(ContentValues values) {
    // Create and/or open the database for writing
    SQLiteDatabase db = getWritableDatabase();

    // It's a good idea to wrap our insert in a transaction. This helps with performance and ensures
    // consistency of the database.
    db.beginTransaction();
    try {
        // The user might already exist in the database (i.e. the same user created multiple posts).

        db.insertOrThrow(TABLE_POSTS, null, values);
        db.setTransactionSuccessful();
    } catch (Exception e) {
        Log.d(TAG, "Error while trying to add post to database");
    } finally {
        db.endTransaction();
    }
}

I am really not sure if this can prevent the Lock exception? This seems more like a performance oriented step.

So finally after reading all those blogs and tutorials, I am still confused. My main questions are

1. Which is a better choice to use ContentProviders or extending SqliteOpenHelper given that my application doesn't share data with other applications.
2. Is putting Java locks on all the operations the best approach or is there any other approach which is better than this?

Update: Based on the answer by @Mustanar, it seems that the SQLiteDatabase takes care of the locking mechanism. It means that if you are doing a write opertaion, the database will be locked. But at the very same time, if some other thread tries to perform a write operation, then will the second operation be in waiting till the lock is released or will it throw an android.sqlite.database.SQLiteDatabaseLockedException exception ?

Update 2: Putting a bounty on this question, because the answer still seems to be not clear to me. I am using only one instance of the Helper class. But still getting this error.

P.S: Thanks for bearing for such a long question.

Answer 1

Use a Singleton pattern to instantiate the SQLiteOpenHelper, so throughout the application one instance of singleton should exist. This will ensure that no leaks occur, and will make your life a lot easier since it eliminates the possibility of forgetting to close your database as you code. It also will ensure safe access to the database throughout the application.

Moreover, You don't have to implement your own locking mecanism. SQLiteDatabase maintain the locking mecanism. So, There will only one transaction going at a particular time, all other transactions will be in Queue using the Sigleton.getInstance() approach. Ensure a single access point to the database.

Moreover, in this approach, You don't have to close your database connections, as SQLiteDatabase will be releasing all references once transaction has been done. So CustomSQLiteHelper.getInstance() should be used whenever you want to perform CRUD operations and remove your locking mecansim.

More information, Please go through the blog http://www.androiddesignpatterns.com/2012/05/correctly-managing-your-sqlite-database.html and see the comments also.

Hope this helps.

Answer 2

I would definitely recommend that you wrap your database in a ContentProvider.

It provides nice features out of the box and shifts a lot of potentially tricky tasks to the framework. You won't need to deal with singleton database helpers, because you are guaranteed that you have a single ContentResolver instance for your app that will forward commands to the intended provider.

You can make use of the whole Loader framework and nice helper classes like AsyncQueryHandler to deal with some of the most common caveats of DB access.

I would advice against synchronising the access to your CRUD methods. As mentioned in the documentation and the previous answers, this should be taken care of by the database itself. Not to mention the potential performance penalty in an app that requires intensive database usage.

Side note: As a first step, it may be useful if you try and pinpoint what causes these crashes - are they sporadic? A more concrete example may prove useful while troubleshooting.