How careful should I be with thread-safety when creating methods/activities which interact with SQLite database?

Question

I am creating an app which allows for many different Activities to be started from a TabActivity(up to ~25). Most of the activities require data from the sqlite database, so when onCreate is run, an AsyncTask creates an SQLiteOpenHelper object(which will open a readable/writable database), runs a query, data is retrieved, and everything is then closed.

i was just testing messing around to see if i could break something, so i added every Activityto the TabActivity's TabHost. I then started mashing each tab as quickly as possible.

i noticed that very quickly i began to see in the LogCat: Caused by: android.database.sqlite.SQLiteException: database is locked: BEGIN EXCLUSIVE; and the app proceeded to die.

Typically there will only be about 4-6 tabs(i can just limit the user anyway) for the TabHost. I haven't been able to break anything with a small amount of tabs to mash, but i am still worried that maybe i am accessing the database in a poor way.

How can i prevent my SQLiteDatabase objects to cause a lock?

If i create a ContentProvider will that eliminate the possibility of database locking?

Do you have any suggestions for changes I could make for accessing data from an SQLiteDatabase?

I ended up taking the approach of using the Application class and storing 1 SQLiteOpenHelper and trying my best to keep it synchronized. This seems to be working great - i put all my 25 activities in the TabHost and mashed away on them with no errors.

I am calling ((SQLiteDbApplication)getApplication()).setDbHelper(new DBHelper(this, Constants.DB_NAME, null, Constants.DB_VERSION_CODE)); method(shown below) in every onCreate() in my activities

Any further suggestions to this approach or to the changes i made using this Application class?

import android.app.Application;
import android.database.sqlite.SQLiteDatabase;

public class SQLiteDbApplication extends Application {
    private DBHelper dbHelper;
    private SQLiteDatabase db;
    public synchronized DBHelper getDbHelper() {
        db = dbHelper.getDatabase();//returns the already opened database object
        while(db.isDbLockedByCurrentThread() || db.isDbLockedByOtherThreads());
        return dbHelper;
    }
    public synchronized void closeDb() {
        if(null != dbHelper)
            dbHelper.close();
        if(null != db)
            db.close();
    }

    @Override
    protected void finalize() throws Throwable {
        if(null != dbHelper)
            dbHelper.close();
        if(null != db)
            db.close();
        super.finalize();
    }
    public synchronized void setDbHelper(DBHelper dbHelper) {
        if(null == this.dbHelper) {
            this.dbHelper = dbHelper;
            this.dbHelper.setDb(this.dbHelper.getWritableDatabase());//creates and sets the database object via getWritableDatabase()
        }
    }
}

Answer 1

Maybe this could be usefull. locking in sqlite

Answer 2

All activity callbacks happen on the main thread, so in the scenario you describe there is no multi-threading going on, no matter how many activities or tabs you have.

ContentProvider doesn't provide any locking. In fact, it can introduce multithreading where you wouldn't already have it because it allows other processes to make calls in to your own process, and when that happens the call is dispatched from a separate thread in your process (not on the main UI thread).

Of course if you create your own threads, then you will also have multi-threading going on.

Answer 3

If you are to worried about all the database connections try to limit yourself to one SqliteOpenHelper and be sure to wrap a synchronization layer around it.

You can extend the application class and then call getApplication and cast the object you get into your application. Now you can store a SqliteOpenHelper in this application class and build your own thread safe access method to the database connection.

If you are using AsyncTask in all of your onCreate methods and you are experiencing problems with a lot of tabs these problems can also occur with a slower device, a faster user or a database that is grown big over the time of usage.

Depending on the use case of you app you can go the save way and go through all the effort and pain of threading and locking, or you can just publish the app with a number of tabs that never produced the error and be sure to catch the database exception and send yourself a notification (for example through google analytics) to test if the threading problem does occur in real life usage of the app.