Concurrent Database Access

03 Dec 2013

Small article which describes how to make access to your Android database thread safe.

Assuming you have your own SQLiteOpenHelper.

public class DatabaseHelper extends SQLiteOpenHelper { ... }

Now you want to write data to database in separate threads.

 // Thread 1
 Context context = getApplicationContext();
 DatabaseHelper helper = new DatabaseHelper(context);
 SQLiteDatabase database = helper.getWritableDatabase();
 database.insert();
 database.close();

 // Thread 2
 Context context = getApplicationContext();
 DatabaseHelper helper = new DatabaseHelper(context);
 SQLiteDatabase database = helper.getWritableDatabase();
 database.insert();
 database.close();

You will get the following message in your logcat and one of your changes will not be written.

android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)

This is happening because every time you create new SQLiteOpenHelper object you are actually making new database connection. If you try to write to the database from actual distinct connections at the same time, one will fail.

To use database with multiple threads we need to make sure we are using one database connection.

Let’s make singleton class DatabaseManager which will hold and return single SQLiteOpenHelper object.

public class DatabaseManager {

    private static DatabaseManager instance;
    private static SQLiteOpenHelper mDatabaseHelper;

    public static synchronized void initialize(Context context, SQLiteOpenHelper helper) {
        if (instance == null) {
            instance = new DatabaseManager();
            mDatabaseHelper = helper;
        }
    }

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

        return instance;
    }

    public synchronized SQLiteDatabase getDatabase() {
        return new mDatabaseHelper.getWritableDatabase();
    }
}

Updated code which writes data to database in separate threads will look like this.

 // In your application class
 DatabaseManager.initializeInstance(getApplicationContext());

 // Thread 1
 DatabaseManager manager = DatabaseManager.getInstance();
 SQLiteDatabase database = manager.getDatabase()
 database.insert();
 database.close();

 // Thread 2
 DatabaseManager manager = DatabaseManager.getInstance();
 SQLiteDatabase database = manager.getDatabase()
 database.insert();
 database.close();

This will result in another crash:

java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase

Since we are using only one database connection, method getDatabase() returns same instance of SQLiteDatabase object forThread1 and Thread2. What is happening, Thread1 may close database, while Thread2 is still using it. That’s why we have legalStateException crash.

We need to make sure no-one is using database and only then close it. Some folks on Stack Oveflow recommend that you never close your SQLiteDatabase. However, it can "honor" you with the following logcat message:

Leak found
Caused by: java.lang.IllegalStateException: SQLiteDatabase created and never closed

Working sample

public class DatabaseManager {

    private AtomicInteger mOpenCounter = new AtomicInteger();

    private static DatabaseManager instance;
    private static SQLiteOpenHelper mDatabaseHelper;
    private SQLiteDatabase mDatabase;

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

    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() {
        if(mOpenCounter.incrementAndGet() == 1) {
            // Opening new database
            mDatabase = mDatabaseHelper.getWritableDatabase();
        }
        return mDatabase;
    }

    public synchronized void closeDatabase() {
        if(mOpenCounter.decrementAndGet() == 0) {
            // Closing database
            mDatabase.close();

        }
    }
}

And use it as follows:

SQLiteDatabase database = DatabaseManager.getInstance().openDatabase();
database.insert(...);
// database.close(); Don't close it directly!
DatabaseManager.getInstance().closeDatabase(); // correct way

Every time you need database you should call openDatabase() method of DatabaseManager class. Inside this method we have a counter, which indicates how many times database is opened. If it equals to one, it means we need to create new database. If not, database is already created.

The same happens in closeDatabase() method. Every time we call this method, counter is decreased; whenever it goes to zero, we are closing database.

Note: You should use AtomicInteger to deal with concurrency.

Now you should be able to use your database and be sure - it's thread safe.

Original article here.

SEE ALSO:

About Lemberg

Lemberg is a UK mobile and web development company with strong client base in the UK, Europe, and the USA.

Starting from 2007, Lemberg has been helping leading design and marketing agencies, start-ups, innovative businesses deliver brilliant digital solutions for a number of the world’s biggest brands.

We provide our clients with impeccable services, including mobile apps creation (iPhone, iPad, Android, Windows Phone), web development (Drupal, front-end, back-end and API development), and support services.

Our goal is to go beyond clients’ expectations: as a technology partner, we take the responsibility for implementing the most ambitious, creative and innovative ideas.