Concurrent Database Access

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:

Lemberg is a technology consulting, software & hardware engineering company.

Startups and established businesses rely on our industry expertise to build new products and deliver digital transformation.

Join our Newsletter?