Locking SQLite DB on Windows as a mutex and no polling
I have the following situation: One process is being read from a SQLite database. The other processes are database updates. Updates are not very frequent and all transactions are short. (less than 0.1ms on average) The process that is being read should have low latency for requests. (about 0.1ms)
If the SQLite lock works like a mutex or read / write lock, you should be fine. From reading http://www.sqlite.org/lockingv3.html it should be possible. SQLite uses LockFileEx (), sometimes without LOCKFILE_FAIL_IMMEDIATELY, which blocks the call at will.
However, I couldn't figure out how to use / configure SQLite to achieve this behavior. Using a busy handler will enable polling, which is unacceptable since the minimum sleep time is typically 15ms on Windows. I want the request to be executed as soon as the update transaction completes.
Is it possible without changing the SQLite source code. If not, is there such a patch available somewhere?
source to share
SQLite does not use a synchronization mechanism that will wait until the lock is released. SQLite never uses a blocking call to a block; when it finds that the database is locked, it waits for a while and tries again. (You can set your own busy handler to wait for a shorter time.)
The easiest way to prevent readers and writers from blocking each other is to use WAL mode .
If you cannot use WAL mode, you can synchronize your transactions by implementing your own synchronization mechanism: use a shared named mutex in all processes, and block it in all transactions. (This would reduce concurrency if you had multiple readers.)
source to share