Thread synchronization issues in shared-cache mode (when opening a database)

[resilar]

In shared-cache mode (disabled by default), sqlite3_key_v2() sometimes returns SQLITE_NOTADB (or SQLITE_LOCKED?) due to synchronization issues with respect to reading the database page 1 for codec verification in codec_set_to(). The core problem seems to be the synchronization of shared page cache usage and, in particular, the clearing of the page cache in order to read the page 1 from disk (cached page is decrypted and therefore useless for verifying the codec).

[resilar]

Ideas for fixing the issue:

1. Add missing shared-cache locking (setSharedCacheTableLock() and Btree locks?)
2. Read the database page 1 with a novel method
   • Consider low-level file locks & OS functions to bypass (shared) page cache etc.
3. Verify codec without the page 1 (compare to verified codecs associated with the shared cache?)
   • Obviously already verified codecs do not always exist
4. ... something else

I think the second option has the most potential.

[resilar]

Accidental close. Reopening.

---

Also quick note about the suggested fix (1) which may feel like the obvious solution. I'd argue that is not necessarily the case because the current approach of accessing the database through SQLite3's abstractions (page cache etc.) may be flawed to begin with. SQLite3's internals have not been designed this use case in mind, which complicates the implementation. For example, dirty hacks may be needed to workaround limitations of programming interfaces (e.g., API offers table-based locking of shared cache, but page-level locks are required).

Thus, alternative approaches such as the fix (2) may produce more elegant and "correct" final result.