`ConcurrentDatabaseConnection` and its NSRecursiveLock

[andersio]

ConcurrentDatabaseConnection at the moment uses a NSRecursiveLock, which is kinda emulating the SQLite Serialized mode, i.e., open with flag SQLITE_OPEN_FULLMUTEX. Meanwhile, the SQLite3 distribution on Darwin platforms has long been defaulted to use the Multi-thread mode.

If a user is doing connection pooling, the connection should have already been exclusively bound to a thread, until the connection can truly be returned to the pool for reuse (e.g., all active statements are closed; no escaped reference). In this use case, the additional locking on every call would be:

• quite defensive for a Multi-thread mode connection; and
• unnecessary for a Serialized mode connection.

So it seems there are two opportunities of minor improvements:

• Use SQLITE_OPEN_FULLMUTEX so we don't need to roll a lock manually in the library.

  (bonus: two objc_msgSend calls are skipped)

• potentially expose a lock-less version of ConcurrentDatabaseConnection.

  Though for SQLDelight specifically, whether or not the lock-free option can be used depends on how [native + sqlite WAL] Read-your-writes consistency violation under stress, due to SqlCursor escaping mutual exclusion of its parent connection sqldelight/sqldelight#2123 is resolved.

[kpgalligan]

I've been thinking about the concurrent connection for a while. I do think this will largely be on the back burner until the remainder of the discussions are driver updates and sqldelight are resolved. I have work happening on updating the current sqldelight driver's internals that include you connection pooling and some other optimizations that have been pending for a while, and we've started the discussion around the read lambda change, which along with some other potentially breaking changes, might go into a bigger release, but that needs some more chatting.