WAL Mode, But Open Writer Transaction Blocks Readers?

SQLCipher
1

Environment: net.zetetic:android-database-sqlcipher:3.5.9@aar

I am converting Room’s test suite over to test my CWAC-SafeRoom bridge between Room and SQLCipher for Android. On the whole, things are going well. However, I’m having trouble getting one test working and was wondering if there’s a known scenario that might explain what’s going on.

As part of Room’s WriteAheadLoggingTest, they have a readInBackground() test method that includes the following:

  • Begin a transaction
  • In that transaction, delete an existing row from a table
  • With that transaction still open, start a background thread
  • In that background thread, against the same SQLiteDatabase object, perform a query to retrieve the to-be-deleted row without an explicit transaction
  • Block the first thread waiting for the second thread to complete its query
  • Commit the transaction

The test works fine when tested against the framework SQLite implementation. With SafeRoom and SQLCipher for Android, the read thread blocks indefinitely. My guess is that the reader thread is blocked by SQLCipher waiting for the transaction to be committed.

However, WAL mode is enabled, insofar as PRAGMA journal_mode; returns wal or WAL (I’m using equalsIgnoreCase()). WAL mode would have been enabled before the bulleted list shown above begins, and I am testing if WAL mode is enabled during that transaction. If my understanding is correct, WAL mode should allow readers to not be blocked by writers, and so I’m not sure why I’m seeing the reader thread be blocked.

Is there some known issue here that I can address?

Thanks!

2

Hi @commonsguy

I would be happy to look into that further. The SQLiteDatabase utilizes some locking which is implemented via a ReentrantLock which might explain the behavior you are experiencing as the lock would be owned by the original calling thread, not the background thread. This can be disabled via setLockingEnabled on the SQLiteDatabase instance. I will investigate this and share my results.

3

OK, I temporarily hacked in a setLockingEnabled(false) call on this test. The good news is that it seems to unblock the background thread. The bad news is that the query fails – it should return a row and appears to not do so.

Let me know what more you need from me to help investigate this. Thanks!

4

Hello @commonsguy

We are glad to hear calling setLockingEnabled(false) allowed your testing to continue. With regard to the scenario above, are you able to point us to this specific example for further review? We have modeled the scenario above within the SQLCipher for Android test suite and it behaves as you describe, however, we believe the observed behavior to be correct in comparison to the described isolation behavior on the SQLite website here, specifically the section “No Isolation Between Operations On The Same Database Connection”.

In other words, if X begins a write transaction using BEGIN IMMEDIATE then issues one or more UPDATE, DELETE, and/or INSERT statements, then those changes are visible to subsequent SELECT statements that are evaluated in database connection X. SELECT statements on a different database connection Y will show no changes until the X transaction commits. But SELECT statements in X will show the changes prior to the commit.

So why would there be a difference in behavior between the two implementations? Some newer versions of SQLiteDatabase within AOSP support connection pooling, so it may be possible that you are not using the same connection from the pool. That is just speculation at this point, we would be happy to investigate this further if you could point us to the example you are referencing.

5

are you able to point us to this specific example for further review?

I can point you to the Room test method that I started with.

If you would like, I can try creating an isolated test project that contains the relevant bits, but that’ll be 1-2 weeks before I can get the time to create that.

6

Hi @commonsguy

It appears that the SQLite session used in the newer AOSP implementation is thread local, the owner of the SQLiteDatabase instance. So while the Room test appears to be operating on the same underlying connection, because the referenced test initializes a worker thread, a new database connection would be created during your background thread, which would explain why the test is able to see the row that was deleted in the pending transaction. For your test, you would need to create a new SQLiteDatabase connection to replicate the behavior.

7

I’m not looking to change the test, because this is a compatibility test. Either I figure out a way to get SafeRoom to be compatible, or I need to document the incompatibility.

8

Hi @commonsguy

To support this scenario, having SafeRoom utilize ThreadLocal around the underlying database connection it exposes should resolve the issue. This will typically require key derivation to occur again. Does Room often utilize multiple threads, or is this just a test scenario?

9

having SafeRoom utilize ThreadLocal around the underlying database connection it exposes should resolve the issue

SafeRoom does not expose what I would consider to be a database connection. It exposes a net.sqlcipher.database.SQLiteDatabase via a SupportSQLiteDatabase wrapper. I cannot have one of those per thread without holding onto the plaintext passphrase indefinitely. And since I’m not in control of the threads, the key derivation cost may be incurred quite a bit for all I know.

Does Room often utilize multiple threads, or is this just a test scenario?

Multithreading is key to Room. However, I don’t know how important this specific scenario is (having an open writer transaction while supporting simultaneous reads).