Parallel read and write in SQLite

22. 12. 2023

In many situations you need to read from SQLite database and write to it simultaneously. For example, you want to sync data in background with transaction opened and allow user to browse data in UI at the same time. This article show you how to have one writing thread and many reading threads at the same time. Unfortunately, simultaneous writing from more than one thread is not allowed.

The simplest way to read in parallel with writing transaction is to use our ORM joogar. Simultaneous read-write is in joogar enabled by default as well as many other cool features 🙂

For more details and example of implementation without joogar continue reading …

Why we cannot read when writing in transaction

The short answer is locking. Longer answer is rollback journal mode. These are mechanisms ensuring atomic commit and rollback in transactions. Before digging deeper, let me remind you that every writing to SQLite has to be done in transaction, even if implicit or explicit.

Let me explain briefly how rollback journal works. When a thread wants to write into a database, it needs to lock the database file with EXCLUSIVE a lock to avoid any concurrent reads or writes. Then it writes changes directly in main database file and original records are moved to rollback journal. On commit main database file is already modified, so SQLite only delete rollback journal file and transaction is persisted. In case of rollback, all records from rollback journal are restored back to main file. This is why we cannot read when other thread is writing – you would read uncommitted and potentially inconsistent data from main database file.

Switch to WAL mode to make it work

Now we know the show-stopper is rollback journal. Solution is to switch to Write-Ahead Log which works much better for our purposes. To be honest, it is much more better in almost all real-world use-cases. Don’t hesitate to enable it even if you don’t need a parallel read and write.

WAL is an inversion to rollback journal. It simply writes changes to WAL file and later moves them to main database file. This happens automatically and this operation is called checkpointing. In this approach anyone can read from main database file while someone else is writing to Write-Ahead log.

Because Write-Ahead log is merged back in bulk, many transactions can appear in it. Commit is simply written to WAL file as another record. When another thread wants to read from database, it simply remembers the last commit record in WAL file and ignore anything behind it for this one reading session.

And finally a piece of code …

There are two ways of how to enable Write-Ahead log. The first one works for API 16 and above, it uses SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING flag in SQLiteDatabase.openDatabase() method.

The second one is supported since API 11 and it is a little bit less efficient since it switches already created SQLiteDatabase object to WAL mode via SQLiteDatabase.enableWriteAheadLogging() method.

Let’s use these two options together:

File databasePath = getDatabasePath("my_database1");
if(!databasePath.getParentFile().exists()) {
   databasePath.getParentFile().mkdirs();
}

int flags = SQLiteDatabase.CREATE_IF_NECESSARY;
if(walModeEnabled) {
   if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) {
       flags = flags | SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING;
   }
}

SQLiteDatabase db = SQLiteDatabase.openDatabase(databasePath.getPath(), null, flags);

// backward compatibility hack to support WAL on pre-jelly-bean devices
if(walModeEnabled) {
   if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB &&
           Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN) {
       db.enableWriteAheadLogging();
   } else {
       Log.w(TAG, "WAL is not supported on API levels below 11.");
   }
}

This is maybe a too low-level solution, let’s place it in SQLiteOpenHelper:

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

   boolean walModeEnabled;

   public MySQLiteOpenHelper(Context context, boolean gWalMode) {
       super(context, DATABASE_NAME, null, DATABASE_VERSION);
       walModeEnabled = gWalMode;

       if(walModeEnabled) {
           if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN) {
               setWriteAheadLoggingEnabled(true);
           }
       }
   }

   @Override
   public void onConfigure(SQLiteDatabase db) {
       super.onConfigure(db);

       // backward compatibility hack to support WAL on pre-jelly-bean devices
       if(walModeEnabled) {
           if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB &&
                   Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN) {
               db.enableWriteAheadLogging();
           } else {
               Log.w(TAG, "WAL is not supported on API levels below 11.");
           }
       }
   }

}

Feel free to let me know if you found any issues. Happy parallel reading and writing!

Do you need more detailed help? Book a consultation:
Let’s chat about it

Do you want more?

We share the world of software development on our Instagram. Join us 🙂

We also tend to look for Android dev (iOS as well), but there are also chances for a project manager or a tester. Send us an e-mail: [email protected].