getWritableDatabase() instance throughout the application lifecycle

Question

I have made Singelton object to make queries to SQLiteOpenHelper in which I have saved instance of getWriteableDatabase(). Throughout the application lifecycle, for all select and insert/update queries I am using that instance from multiple IntentServices. I have read Write-ahead Logging (WAL) which supports concurrent execution of queries. I am using the above instance with WAL disabled. Actually at a point the database does not return data, I was wondering if SQLite file can get corrupted because I am using getWritableabledatabse for reading/writing from multiple intent services.

Can a deadlock occur with this approach?

As per my findings, WAL should be enabled if you are accessing database from multiple threads.

EDIT

DatabaseAdapter.java

public class DatabaseAdapter {
    private Context mContext;
    private SQLiteDatabase mSqLiteDatabase;
    private DatabaseHelper mDbHelper;
    private static DatabaseAdapter adapter;

    public static DatabaseAdapter getInstance() {
        if(adapter == null) {
            synchronized (DatabaseAdapter.class) {
                if(adapter == null)
                    adapter = new DatabaseAdapter(MyApp.getInstance());
            }
        }

        return adapter;
    }

    public DatabaseHelper getDatabaseHelper() {
        return mDbHelper;
    }

    private DatabaseAdapter(Context c) {
        mContext = c;
        mDbHelper = new DatabaseHelper(mContext);
        mSqLiteDatabase = mDbHelper.getWritableDatabase();
    }

    private long insert(String tableName, ContentValues contentValues) throws Exception {
        this.open();
        long id = mSqLiteDatabase.insert(tableName, null, contentValues);
        this.close();
        return id;
    }

    private int update(String tableName, ContentValues contentValues, int pk_id) throws Exception {
        this.open();
        String whereClause = mDbHelper.pk_id + " = " + pk_id;
        int n = mSqLiteDatabase.update(tableName, contentValues, whereClause, null);
        this.close();
        return n;
    }

    private ArrayList<MyObject> selectChallans(String whereClause, String orderby) throws Exception {
        try {
            ArrayList<MyObject> arrayListObjects = new ArrayList<MyObject>();
            Cursor queryCursor = mSqLiteDatabase.query(tableName, null, whereClause, null, null, null, orderby, null);
            if (queryCursor == null) {
                return null;
            }
            while (queryCursor.moveToNext()) {
                MyObject myobject = getMyObject(queryCursor);
                if(myobject != null)
                    arrayListObjects.add(myobject);
            }
            queryCursor.close();
            return arrayListObjects;
        } catch (Exception e) {
            e.printStackTrace();
            this.forceClose();
            throw e;
        }
    }
}

I am using this Adapter singleton instance through the application for insert/update and select queries. I was concerned about mSqLiteDatabase instance. These functions are being called from multiple IntentServices.

Answer 1

AFAIK, the best practice is calling getWritableabledatabse only once with one SQLiteOpenHelper. After that, you can use the returned database for all thread without any issue. You have to make sure that you are using one database connection. You can check this Good Answer for more detail.

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).

For me, I usually create and open the SQLiteOpenHelper in Application class, then I can use it everywhere in any thread in my app.