CODEWITHSUNDEEP
androiddatabasemultithreadingandroid-sqlite
Loadeed
Loadeed

Reputation: 567

Android SQlite multithread access

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)

Upvotes: 5

Views: 9755

Answers (4)

Justin Morris
Justin Morris

Reputation: 7329

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.

Upvotes: 7

C.d.
C.d.

Reputation: 9995

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

Upvotes: 3

rus1f1kat0R
rus1f1kat0R

Reputation: 1665

I think you are using database connections incorrectly. It can be caused by unclosed cursors or smth else. Sqlite database implementation is thread safe and can be accessed simultaneousely from different threads. Please provide more details about your problem (exception stack trace, code sample that causes exception etc) in order we can help you.

Upvotes: 0

Agata
Agata

Reputation: 399

There are few things to try. First try passing the same instance of SQLiteOpenHelper to every of your tasks. Second is using beginTransaction and endTransaction on your SQLiteDatabase inside of your Helper. Third is adding synchronized to every of your helper methonds being used in tasks.

Upvotes: 1

Related Questions