SQLiteOpenHelper single connection vs multiple connections

Question

I am very much confused regarding access to SQLiteDatabase. Either it should be one connection or multiple connections to have access from multiple threads. I have read many articles including following two.

https://stackoverflow.com/a/3689883/3027124 http://touchlabblog.tumblr.com/post/24474398246/android-sqlite-locking

Both of these recommends to have single connection. Even my own answer to same question was accepted by OP. I used the Singleton approach to access SQLiteopenHelper class.

https://stackoverflow.com/a/35358702/3027124

But I am still confused after reading documentation of enableWriteAheadLogging which states

This method enables parallel execution of queries from multiple threads on the same database. It does this by opening multiple connections to the database and using a different database connection for each query. The database journal mode is also changed to enable writes to proceed concurrently with reads.

Now this is the confusing part. If I want to access database from simultaneous multiple threads, I have Singleton access to SQLiteOpenHelper which in my understanding means the serial execution of insertions while Simultaneous reads can be done with no-errors. But above documentation says, in order to have Simultaneous access, enableWriteAheadLogging should be called which in returns create multiple connections. What is this going on?? What does it mean if I do insertions by calling getWritableDatabase() using Singleton SQLiteOpenHelper from multiple threads? Will the calls be serial? Should enableWriteAheadLogging be called?

Please clarify.

Answer 1

I would use the singleton instance whether I'm using enableWriteAheadLogging or not when dealing with threading which is the case in most apps unless it's a very trivial app like a sample.

Using a singleton instance is ensuring Thread Safety: The singleton instance ensures that synchronization works across that instance, that means when you have a read and write methods calling the database at the same time from different threads, one of them should wait for the other as the database gets locked while writing to it.

It's really clear that this is the case as written in the documentation and quoted below

it is not possible for reads and writes to occur on the database at the same time. Before modifying the database, the writer implicitly acquires an exclusive lock on the database which prevents readers from accessing the database until the write is completed.

enableWriteAheadLogging is actually altering the above behavior as the above statement is only true When write-ahead logging is not enabled (the default).

So what happens when you enable write-ahead logging through enableWriteAheadLogging?

It's actually changing the default behavior enabling actual parralism as it changes the underlying database journal file to enable doing a write and a read at the same time, but to do that it needs more memory than usual. Read the documentation quote below to know more!

In contrast, when write-ahead logging is enabled (by calling this method), write operations occur in a separate log file which allows reads to proceed concurrently. While a write is in progress, readers on other threads will perceive the state of the database as it was before the write began. When the write completes, readers on other threads will then perceive the new state of the database.

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.