We're using SQLite in a component with significant performance and parallelism requirements, and we would like to rely on OS locking rather than retry loops to handle multiple readers/writers. Stated another way, we'd like to find a way to ensure that we never see SQLITE_BUSY returned to the application and instead wait on an OS handle whenever such a condition would occur.

From looking at the source, it appears that the win32 VFS uses OS primitives that would support blocking rather than failing with SQLITE_BUSY when there is contention, but SQLite currently only uses these primitives in non-blocking mode. For example:

/*
** Currently, SQLite never calls the LockFileEx function without wanting the
** call to fail immediately if the lock cannot be obtained.
*/
#ifndef SQLITE_LOCKFILEEX_FLAGS
# define SQLITE_LOCKFILEEX_FLAGS (LOCKFILE_FAIL_IMMEDIATELY)
#endif

I was able to implementing something similar with a wrapper VFS for journal_mode = DELETE, by following the source for the current win32 VFS and replacing the non-blocking calls with blocking calls (I can share the code if that context would clarify things on this thread). However, for WAL mode, some calls to ShmLock appear to be "probes" that require the call to fail immediately if the lock isn't available, including cases with just one thread where the lock is guaranteed not to be available. For non-WAL mode, it looks like a correct implementation can block/wait to acquire the lock, but for WAL mode, the contract appears to prohibit waiting.

Two main questions: 1. For non-WAL-mode, how feasible would it be to support a blocking mode directly in SQLite? (rather than using a custom VFS, as seems possible today) 2. For WAL mode, would it be feasible to extend the VFS layer to make it possible to implement such a blocking version via a custom VFS?

Thanks,

David

Did you see the blocking support for WAL mode databases?

https://www.sqlite.org/src/file?name=doc/wal-lock.md&ci=204dbc15a682125c

I had seen some related things in the source. It looks like that is POSIX-only - is that correct? I'm specifically looking for Windows support.

Post-holiday bump

We are kinda overwhelmed dealing with other more pressing issues at the moment. Sorry for the delay.