Obtaining sql connection blocks 4+ threads when many queries are executed in parallel

[Snow4DV]

Hello! I am currently working with sqlcipher 4.7.2 community edition

Currently room in android obtains sql connection in blocking way as seen here:

  override fun query(query: String): Cursor =
        session.useReaderBlocking { connection ->
            connection.usePrepared(query) { stmt -> stmt.toCursor() }
        }

source: https://cs.android.com/androidx/platform/frameworks/support/+/androidx-main:room/room-sqlite-wrapper/src/main/kotlin/androidx/room/support/RoomSupportSQLiteDatabase.kt;l=41?q=roomsupportsqlitedatabase&sq=

in library only one connection can be created at the same time due to global mLock:

   private SQLiteConnection waitForConnection(String sql, int connectionFlags,
            CancellationSignal cancellationSignal) {
        final boolean wantPrimaryConnection =
                (connectionFlags & CONNECTION_FLAG_PRIMARY_CONNECTION_AFFINITY) != 0;

        final ConnectionWaiter waiter;
        final int nonce;
        synchronized (mLock) {

That leads to whole default ArchTaskExecutor thread pool (that consists of 4 threads - used in room by default) being fully locked for 1+ second when first operations are executed

That is not good because in android it is usually a good practice to use coroutines that are not blocking. It also blocks default arch thread pool that is used by work manager workers, non-security room databases and etc

So the question is - is it possible to get rid of these locks or maybe speed up the connection creation somehow?

The other problem with this approach is that i can't use existing connections while another connection is created because releaseConnection locks the same lock as acquireConnection. Maybe it could be possible to execute queries/transactions on existing connection while creating one connection?

[Snow4DV]

After researching the problem i came to conclusion:

While key is derived, not a single thread can release/obtain connections that have already derived the key due to synchronized(mLock). That means that perfectly usable connections are not used until all connection's key is derived. Limiting room database thread pool to one thread resolves the issue, but no gains from new sqlcipher version :(

[sjlombardo]

Hello @Snow4DV - thanks for writing in about this. We did look into the report and agree that the locking serializes connection initialization to ensure everything is tracked by the pool properly.

Unfortunately, it would not be trivial to restructure the way the SQLiteConnectionPool works. That logic in SQLiteConnectionPool is inherited from the upstream API (SQLite Android Bindings). We try hard to avoid introducing major changes, conflicts, or large diffs to wrapper APIs. That is especially true where such changes would introduce behavior changes in the API or break existing expectations. Therefore we don't anticipate making changes to the initialization at this time.

As an aside, there are some completely separate optimizations in SQLCipher Commercial Edition, outside of the Android wrapper entirely, which speed up connection initialization considerably. If you are using SQLCipher for business, it might be worth taking a look.

If that is not an option, you could also consider adjusting the key derivation settings to reduce the time required for key derivation which would also minimize the impact of the synchronization.