On 11 November 2015 at 15:16, Greg Keogh <[email protected]> wrote: > Howdy again, I'm thinking aloud about a problem here in case there is > lateral thinking available. > > We have a mature app that uses a single-file database that is locked. Now > new apps want to use this file as well, but how can they share it? The > usual fix would be to (1) Migrate it into something like SQL Server (2) > Wrap the file in code in a different process and expose it as a service. > > Option 1 has too many dependencies that aren't available. Option 2 is easy > to code, but you have to manage the lifetime of the process and perhaps > make it a Windows Service, which makes a bigger install and runtime > footprint. > > At the moment I'm wondering if the "service" could be a hidden console or > WinForms app that is registered in HKLM Run, or similar. That way it's a > "fake lightweight service". >
Does each process need to lock the database file the entire time? I think the usual solution is for processes to release (or downgrade) the lock when it is not needed, as a kind of cooperative locking strategy. The simple way is to open and close the database file exclusively for each transaction and retry a transaction "soon" if the file is already open by another process. However, you may see latency issues because both opening a file and closing a file are costly system calls. The "best" way appears to be to the SQLite way, which I believe uses the fine-grained, per-region locking and blocking functionality of the operating system block I/O layer to upgrade/downgrade locks while keeping the file handle open the entire time in each process. -- Thomas Koster
