SQLite DB locking on Windows like a mutex and without polling

Question

I have the following situation: One process is reading from a SQLite database. Another processes is updating the database. Updates do not happen very often and all transactions are short. (less than 0.1ms on average) The process that is reading should have low latencies for the queries. (around 0.1ms)

If the locking of SQLite would work like a mutex or readers-writer lock, everything would be ok. From reading http://www.sqlite.org/lockingv3.html this should be possible. SQLite is using LockFileEx(), sometimes without the LOCKFILE_FAIL_IMMEDIATELY, which would block the calling process as desired.

However I could not figure out how to use/configure SQLite to achieve this behavior. Using a busy-handler would involve polling, what is not acceptable because the minimal sleep time is usually 15ms on Windows. I would like that the query is executed as soon as the update transaction ends.

Is this possible without changing the source code of SQLite. If not, is there such a patch available somewhere?

Answer 1

SQLite does not use a synchronization mechanism that would wait just until a lock is released. SQLite never uses a blocking locking call; when it finds that the database is locked, is waits for some time and tries again. (You could install your own busy handler to wait for a shorter time.)

The easiest way to prevent readers and a writer 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 common named mutex in all processes, and lock it around all your transactions. (This would reduce concurrency if you had multiple readers.)