Best way to access database in multi threaded application Android?

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.

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.

Query 1 (Singleton) :

Can you change below double locking code to create a true Singleton object?

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

Have a look at this article to understand the issues with double locking.

Have a look at related SE question:

What is an efficient way to implement a singleton pattern in Java?

I prefer to create Singleton

 private static final CustomSqliteHelper instance = new CustomSqliteHelper ();

Other approach:

enum CustomSqliteHelper {    INSTANCE;}

One more approach if you still need lazy Singleton.

Have a look at @ Nathan Hughes answer in this SE question:

Synchronized and locks in singleton factory

Query 2: ( Locking)

Using java lock is not a bad idea to improve the consistency of the application. Consistency wins over expense.

From SQLiteDatabaseLockedException page,

Thrown if the database engine was unable to acquire the database locks it needs to do its job. If the statement is a [COMMIT] or occurs outside of an explicit transaction, then you can retry the statement. If the statement is not a [COMMIT] and occurs within a explicit transaction then you should rollback the transaction before continuing.

I hope you should follow above recommendation along with code snippet you have posted in your question (Github) project. I like that idea and above recommendation is in-line with Github example code.