Android threading and database locking

Question

We are using AsyncTasks to access database tables and cursors.

Unfortunately we are seeing occasional exceptions regarding the database being locked.

E/SQLiteOpenHelper(15963): Couldn't open iviewnews.db for writing (will try read-only):
E/SQLiteOpenHelper(15963): android.database.sqlite.SQLiteException: database is locked
E/SQLiteOpenHelper(15963):  at     android.database.sqlite.SQLiteDatabase.native_setLocale(Native Method)
E/SQLiteOpenHelper(15963):  at     android.database.sqlite.SQLiteDatabase.setLocale(SQLiteDatabase.java:1637)
E/SQLiteOpenHelper(15963):  at     android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1587)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:638)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:659)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:652)
E/SQLiteOpenHelper(15963):  at android.app.ApplicationContext.openOrCreateDatabase(ApplicationContext.java:482)
E/SQLiteOpenHelper(15963):  at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:193)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:98)
E/SQLiteOpenHelper(15963):  at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:158)
E/SQLiteOpenHelper(15963):  at com.iview.android.widget.IViewNewsTopStoryWidget.initData(IViewNewsTopStoryWidget.java:73)
E/SQLiteOpenHelper(15963):  at com.iview.android.widget.IViewNewsTopStoryWidget.updateNewsWidgets(IViewNewsTopStoryWidget.java:121)
E/SQLiteOpenHelper(15963):  at com.iview.android.async.GetNewsTask.doInBackground(GetNewsTask.java:338)
E/SQLiteOpenHelper(15963):  at com.iview.android.async.GetNewsTask.doInBackground(GetNewsTask.java:1)
E/SQLiteOpenHelper(15963):  at android.os.AsyncTask$2.call(AsyncTask.java:185)
E/SQLiteOpenHelper(15963):  at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:256)
E/SQLiteOpenHelper(15963):  at java.util.concurrent.FutureTask.run(FutureTask.java:122)
E/SQLiteOpenHelper(15963):  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:648)
E/SQLiteOpenHelper(15963):  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:673)
E/SQLiteOpenHelper(15963):  at java.lang.Thread.run(Thread.java:1060)

Does anybody have a general example for code which writes to a database from a different thread than the one reading and how can we ensure thread safety.

One suggestion I've had is to use a ContentProvider, as this would handle the access of the database from multiple threads. I am going to look at this, but is this the recommended method of handling such a problem? It seems rather heavyweight considering we're talking about in front or behind.

Sheepshead

Answer 1

I solved this same exception just by making sure all my database opens have closes, and (more importantly) to assure this, making the scope of each database instance local ONLY to the method that needs it. ContentProvider is a good, safe class to use when accessing a db from multiple threads, but also make sure you're using good db practices:

• Keep db instances local (no SQLiteDatabase class members!)
• call close() on the db in the same method in which it's opened
• call close() on the cursors you get from the db
• listen to LogCat for any complaints that SQLiteDatabse might have

Bangle

After some reading, I think that it is not the recommended way that each request open its own db instances. AFAIK if 2 threads write to the database at the same time using 2 differences db instances, only one will write, other one is ignore without error (just log message). The database lock is optimist, you can open as much connection as you wished for Reading, but you should use only one database instance for Writing. Correct me if I'm wrong – Keeping

Answer 2

We used a ContentProvider in the end. This appeared to clear up the problems.

Sheepshead

The reason a ContentProvider solves the problem is because it usually uses one single SQLiteOpenHelper which means that there is only one connection to the database and the underlying SQLiteDatabase takes care of the locking. You don't need a ContentProvider - just make sure you don't write to the DB using 2 different db connections. This article explains how locking works on Android. kagii.squarespace.com/journal/2010/9/10/… – End

Updated link to the article: kagii.com/post/6828016869/android-sqlite-locking – Nigrosine

Having now been doing this for years my advice would be to always use a ContentProvider. – Sheepshead

@Nigrosine can you please provide another link for same – Zoophilia

@NikPatel perhaps this link then: kagii.tumblr.com/post/6828016869/android-sqlite-locking – Nigrosine

I keep moving my posts around. Sorry ;) touchlabblog.tumblr.com/post/24474398246/android-sqlite-locking and, for good measure: touchlabblog.tumblr.com/post/24474750219/… The 'kagii' blogs used to be mine. – Coseismal

Oh yeah, ContentProviders are evil (ish). If you're not explicitly sharing data between apps, its a whole lot of extra code and work. – Coseismal

Why are there so much upvotes for a workaround masked as a solution!? – Odaodab

developer.android.com/guide/topics/providers/… says "You don't need a provider to use an SQLite database if the use is entirely within your own application." – Neuro

Answer 3

Before some code, let's resume some of the approachs:

• Semaphores: by far the best solution presented. It goes in the heart of the problem: resource sharing! It will treat the locking of the database access, avoiding conflicts (database is locked).

• Java synchronization: A kind of semaphore implementation, but less sofisticated. Using synchronized you will not easily solve some cases involving transactions.

• ContentProvider: implement ContentProvider solve the problem only for some cases (or sweep the problem under the carpet). You'll yet face the same issues. The difference is that ContentProvider pattern will guide you to not make some commom mistakes when accessing Sqlite database. The ContentProvider docs says: "You don't need a provider to use an SQLite database if the use is entirely within your own application."

• Almost mandatory: keep db instances local, call close() on the db in the same method in which it's opened using finally statements, close() on the cursors using finally statements, etc are almost mandatory to avoid problems using Sqlite.

Let's show an example of the semaphore solution presented by Moss, which I took from CL. and improoved to cover transactions.

class DataAccess {
    private final ReentrantReadWriteLock rwl = new ReentrantReadWriteLock();
    private final Lock r = rwl.readLock();
    private final Lock w = rwl.writeLock();

    public Data readSomething(int id) {
        Cursor c = null;
        r.lock();
        try {
            c = getReadableDatabase().query(...);
            return c.getString(0);
        } finally {
            if (c != null) c.close();
            r.unlock();
        }
    }

    public void changeSomething(int id, int value) {
        w.lock();
        try {
            getWritableDatabase().update(...);
        } finally {
            w.unlock();
        }
    }

    private void beginTransactionWithSemaphores() {
        getWritableDatabase().beginTransactionWithListener(new SQLiteTransactionListener() {
            @Override
            public void onBegin() {
                w.lock();
            }

            @Override
            public void onRollback() {
                w.unlock();
            }

            @Override
            public void onCommit() {
                w.unlock();
            }
        });
    }
}

Neuro

Answer 4

Take into account that SQLite databases are file based and are not intended to be able to be accessed in a multi-process way. The best procedure on mixing SQLite with multi-processing is using semaphores (aquire(), release()) in each database related access.

If you create a Db wrapper that aquires/releases a global semaphore your DB access will be thread safe. Indeed this means that you could get a bootleneck because you are queueing the access to the DB. So in addition you could only wrap the access using semaphores if it's an operation that alters the database, so while you are alterin the db no one will be able to access it and wait until the write process has been completed.

Discover

Answer 5

We could not share Db connection with multiple thread to perform read and write operation in database simultaniously.We will have to make single object of DB using syncronization concept and we will perform one task at a time .We will use singleton pattern to make the DB object and it will be share within multiple threads.At a time will perform single task . then we will start other task or any operation on DB . Content provider is not the solution of DB locking issue .

import java.util.concurrent.atomic.AtomicInteger;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseManager {

private AtomicInteger mOpenCounter = new AtomicInteger();

private static DatabaseManager instance;
private static SQLiteOpenHelper mDatabaseHelper;
private SQLiteDatabase mDatabase;
//private static String DB_PATH = "";
//  private static String DB_NAME = "xyz.db";// Database name
private static String dbPathh;

public static synchronized void initializeInstance(SQLiteOpenHelper helper,
        String dbPath) {
    if (instance == null) {
        instance = new DatabaseManager();
        mDatabaseHelper = helper;
        dbPathh=dbPath;
    }
  }

public static synchronized DatabaseManager getInstance() {
    if (instance == null) {
        throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                " is not initialized, call initializeInstance(..) method first.");
    }

    return instance;
 }

  public synchronized SQLiteDatabase openDatabase(String thread) {

    if(mOpenCounter.get() == 0) {
        // Opening new database
        // mDatabase = mDatabaseHelper.getWritableDatabase();
        MyLog.e("Path Of DataBase", dbPathh);
        //  mDatabase=mDatabaseHelper.getWritableDatabase();
        mOpenCounter.incrementAndGet();
        mDatabase=SQLiteDatabase.openDatabase(dbPathh, null,   
 SQLiteDatabase.  CREATE_IF_NECESSARY|SQLiteDatabase.OPEN_READWRITE);   
        MyLog.e("Open Data Base", " New Connection created" +thread);
    }
    else{
        MyLog.e("Open Data Base", " Old Connection given " +thread);
    }
    //  Toast.makeText(NNacres.getConfig(), "open conn: present connection = 
   "   +mOpenCounter.get(), Toast.LENGTH_LONG).show();
    return mDatabase;
   }

    public synchronized void closeDatabase() {
    MyLog.e("Close db connection", ""+mOpenCounter.get());

    if(mOpenCounter.get() == 1) {
        // Closing database

        mDatabase.close();
        mOpenCounter.decrementAndGet();

        Log.e("DB CLOSED", "DONE");
    }
    //Toast.makeText(NNacres.getConfig(), "close conn: after close =   
 " +mOpenCounter.get(), Toast.LENGTH_LONG).show();
    }

    }

and write this method in your YourSQLiteDataABse helper class which extends SQLiteOpenHelper Class

     public SQLiteDatabase getWritableDatabase() {
DatabaseManager.initializeInstance(this,"data/data/your packgae name/databases/xyz");
    return DatabaseManager.getInstance().openDatabase(getClass().getSimpleName());

}



public static String getMyDbPath(String DB_NAME, Context context) {

    String myDbPath = context.getDatabasePath(DB_NAME).getPath();
    MyLog.e("DB Path: "+myDbPath);
    return myDbPath;
}

Sooth

Answer 6

You must be calling getWritableDatabase() from a function rather then the constructor of the db helper class. If the db helper class object is created with SQLiteDatabase.openOrCreateDatabase(DB_PATH, null); or similar and then getWritableDatabase() is called from a function, it will try to make a synchronous call to DB causing a DB lock exception.

Negation

The SQLiteOpenHelper onCreate() method is only called if the database file did not already exist. – Ridenour

agreed. the problem is not the call to oncreate() rather synchronous call to DB if a DB object already exists. – Negation

Answer 7

Are you talking of a single user action that, inside your program, causes multiple threads to be run, more than one of which may be accessing the database in update mode ?

That's bad design, period. There is no way for you to know in which order the threads will be scheduled by your OS (/VM), and therefore there is no way for you to know in which order the database accesses will happen, and that is very likely to imply that there is no way for you to know that database accesses will always happen in the order that you are expecting.

All database accesses generated by/coming from some user action should all be done in one single thread.

Galvanotropism