Android SQlite multithread access

Question

I am using SQLiteOpenHelper to write and read from SQlite database on Android. When user clicks on UI I read from SQLite database using AsyncTask but at the exact samo moment I am updating and writing to the database in the background using other AsyncTask.

Every x times I get database locked exception. How can I fix this? Can SQlite be accessed somehow from multiple thread at the same time?

I am using it like that: I have a Database class which extends from SQLiteOpenHelper. I implemented onCreate and onUpgrade methods and everytime I am reading from database or writing to database I use SQLiteDatabase like that:

SQLiteDatabase database = null;
try {

    database = new Database(context).getWritableDatabase();

    ....
    writing and reading from database...
    ....

} catch (Exception e) {
    e.printStackTrace();
} finally {
    if (database != null) {
        database.close();
    }
}

At the end I also close SQLiteStatements and Cursors if I use them. Should I use @Justin answer and have a singleton of database class in Application?

This is the error I get:

E/SqliteDatabaseCpp(17377): sqlite3_open_v2("/data/data/com.skulptur/databases/LGM.s3db", &handle, 6, NULL) failed
E/SQLiteDatabase(17377): Failed to open the database. closing it.
E/SQLiteDatabase(17377):     android.database.sqlite.SQLiteDatabaseLockedException: database is locked
E/SQLiteDatabase(17377):    at android.database.sqlite.SQLiteDatabase.dbopen(Native Method)
E/SQLiteDatabase(17377):    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:983)
E/SQLiteDatabase(17377):    at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:956)
E/SQLiteDatabase(17377):    at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:1021)
E/SQLiteDatabase(17377):    at   android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:750)
E/SQLiteDatabase(17377):    at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:221)
E/SQLiteDatabase(17377):    at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:149)
E/SQLiteDatabase(17377):    at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:223)

Answer 1

Implement a ContentProvider on top of the Database with Loaders it will handle the threading. You can consult this tutorial: http://www.vogella.com/articles/AndroidSQLite/article.html

Answer 2

This is what I do to avoid locking issues.. I let my application handle a single instance of my db helper and always refer to it to get a handle on my DB.

public class MyApp extends Application {
    // My instance of SQLiteOpenHelper
    public static DbHelper openHelper;

    @Override
    public void onCreate() {
        super.onCreate();
        if (openHelper == null) {
            openHelper = new DbHelper(this);
        }
    }

    public synchronized static SQLiteDatabase getDB() {
        return openHelper.getWritableDatabase();
    }
}

Once you do this, anytime you want to do a db query, get the db handle like MyApp.getDB() and you will never have a locking issue. Be sure to NEVER close the DB though. This pattern maintains a single connection at all times in your app. SQLite is meant to be synchronous on Android so if you have long running DB processes, like a 1000 inserts in a single transaction you'll want to code it like this:

db.beginTransaction();
try {
    for (DBRow row : insertList) {
        // your insert code
        insertRow(row);
        db.yieldIfContendedSafely();
    }
    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

This will allow other queries to interject themselves so that you won't see your app grind to a halt during background updates.