Transaction Isolation in SQLite

So you have a long running service (a separate thread) that is synchronising your database with a server or doing some periodic work on it. This task can potentially take a few minutes. To prevent the rest of the application seeing incomplete data you will execute everything in one transaction and then commit it after the work is done. You also have one singleton SQLiteOpenHelper class which opens and maintains the DB connection. Everything sounds good… Except, this is wrong and can lead to various hidden problems.

By default — changes that are being done in a transaction on a single SQLite database connection can be visible to other transactions on that connection immediately — even before calling SQLiteDatabase.endTransaction() .

The changes your “SyncService” is doing in a long running transaction are immediately visible to the rest of the application. This is a fatal issue.

Let me quote https://www.sqlite.org/isolation.html:

1) Transactions in SQLite are SERIALIZABLE. 2) Changes made in one database connection are invisible to all other database connections prior to commit. 3) A query sees all changes that are completed on the same database connection prior to the start of the query, regardless of whether or not those changes have been committed. 4) If changes occur on the same database connection after a query starts running but before the query completes, then it is undefined whether or not the query will see those changes. 5) If changes occur on the same database connection after a query starts running but before the query completes, then the query might return a changed row more than once, or it might return a row that was previously deleted.

In SQLite there can be only one writer and this is guarded by the SQLiteOpenHelper. But a transaction can have several writes to the database spanned over a longer time (e.g. waiting for all the API calls to finish) — in that case simple synchronisation will not work. You would block other queries in the rest of the application.

Calling getWritableDatabase() in each call in your SQLiteOpenHelper will not solve this (the database connection is cached within the SQLiteOpenHelper instance). You will need to have a complete separate DB connection for your “SyncService” — which means having multiple SQLiteOpenHelpers, one for the background service and one for the rest and properly synchronise access. Or you need to start using WAL mode (mentioned in this article).

Even if you don’t have long running transactions — it’s good to limit only one thread to be doing changes on your DB connection. This will prevent cases where several threads write on a same connection — potentially leading to inconsistent data. Simple thread synchronisation can solve this. Execute whole database statements (whole transactions) synchronised on one common object.

Solving transaction isolation issues with WAL — Write Ahead Logging

SQLite version 3.7.0 (2010–07–21) introduced WAL mode. This version is present on Android 3.0 and newer. In WAL mode, changes are not written to the original database file. Instead, changes go into a separate “write-ahead log” or “WAL” file. Later, after the transaction commits, those changes will be moved from the WAL file back into the original database in an operation called “checkpoint”.

WAL mode can be enabled on Android by calling SQLiteDatabase.enableWriteAheadLogging(), and is disabled by default. This has to be called right after opening a database connection.

It is a good idea to enable write-ahead logging whenever a database will be concurrently accessed and modified by multiple threads at the same time. However, write-ahead logging uses significantly more memory than ordinary journaling because there are multiple connections to the same database. So if a database will only be used by a single thread, or if optimizing concurrency is not very important, then write-ahead logging should be disabled.

Using WAL mode allows transaction separation without the need to manually open new connections. This is a good way to solve problems where you have several threads accessing the database and you want to ensure that the changes are not visible until committed.

In WAL — While a write is in progress, other readers can access the database and perceive the state of the database as it was before the write began. You can only have one (!) running transactions, the other transactions will be blocked and wait until it’s finished. You are only able to execute read queries while the transaction is in progress.

File lock exceptions

Generally most people open a new SQLite connection by calling SQLiteOpenHelper.getWritableDatabase() and then either never close it during application life-time (which is generally not a bad approach — Android will close the connection once the application process is killed) or they close it explicitly.

Calling SQLiteOpenHelper.close() and SQLiteOpenHelper.getWritableDatabase() from multiple threads at the same time can result in the following exception:

java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.

The close() method itself is synchronised, but this will not prevent a case where one thread closes the DB and a second thread has a reference to that database you just closed in the mean time.

In my opinion — it’s totally OK to not close the DB connection explicitly in your code and you will not need to handle this case. Otherwise you will need to make sure that only one DB “operation” is running at a time (no other operation can happen between getWritableDatabase() and close() ).

Difference between getReadableDatabase() and getWritableDatabase()

I think not many people are aware that there usually is no difference between these two methods.

The SQLiteOpenHelper.getReadableDatabase() javadoc explains it:

Create and/or open a database. This will be the same object returned by getWritableDatabase() unless some problem, such as a full disk, requires the database to be opened read-only. In that case, a read-only database object will be returned. If the problem is fixed, a future call to getWritableDatabase() may succeed, in which case the read-only database object will be closed and the read/write object will be returned in the future.

The database connection is cached so repeated calls to getWritableDatabase() or getReadableDatabase() will return the same DB connection instance. Unless you close the connection explicitly between the calls.

Problems with multiple DB connections on the same database

There are some cases where multiple connections to the same database are unavoidable. This can be because you need to isolate the transactions (as mentioned) and don’t want use WAL or for some reason you need access your DB from multiple threads.

Opening a new DB connection from a different thread or process will likely (depending on timing) result in the following exception:

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

Again — the solution here is to synchronise any operations on the database. Only one SqliteOpenHelper instance should access/modify the DB.

Avoiding most DB multi-thread related issues