Add option for a process exclusive mode to open files on Windows

(1) By anonymous on 2020-10-15 07:52:06 [source]

Originally posted to https://system.data.sqlite.org/index.html/tktview/0b91277bb4e13c7c68bd

When making calls to the Win32 CreateFile() function, SQLite appears to always set the file sharing parameter dwShareMode = FILE_SHARE_READ | FILE_SHARE_WRITE which allows the file to be opened for shared access across processes.  CreateFile() must be called with a dwShareMode = 0 in order to open the file in a true process exclusive access mode. See https://docs.microsoft.com/en-us/windows/win32/fileio/creating-and-opening-files for full details.

A side effect of this behavior is even when SQLite is configured for EXCLUSIVE WAL mode, users are still able to copy open SQLite database files on Windows using Explorer or other means which can result in corrupt copies that befuddle end users.

To avoid this for our use cases, we're currently having to rebuild our own custom version of SQLite which allows us to force dwShareMode = 0.  We're reaching out and see if there would be any appetite to add an option to allow for a process exclusive file access mode in the upstream SQLite?

(2) By anonymous on 2020-10-15 09:37:36 in reply to 1 [link] [source]

That option would necessarily be Windows-only, no?

There's no process-exclusive open() on *nix, is there?

(3.1) By Warren Young (wyoung) on 2020-10-15 11:02:43 edited from 3.0 in reply to 2 [link] [source]

Yes, the OP is referring to a line down in SQLite's Windows-specific code.

The reason this question was bounced from the ticket tracker for the System.Data.SQLite wrapper by Joe Mistachkin to this forum is that it potentially affects all Windows builds of SQLite, not just that one variant.

(4) By anonymous on 2020-10-15 11:28:11 in reply to 3.1 [link] [source]

I get that. That wasn't my point.

If surfaced as a public API option, it would be a Windows-only option,
since there's no equivalent on Linux/Unix (at least I think). I'm not against it,
I just wonder if there's precedent, and if there is, which one(s), that's all.

(5) By David Jones (vman59) on 2020-10-15 11:53:39 in reply to 4 [link] [source]

Couldn't the Windows VFS use the hook into the PRAGMA interface to set windows-specific options?

(6) By anonymous on 2020-10-15 18:52:55 in reply to 5 [link] [source]

Yes, PRAGMAs and/or URI parameters are often used for setting options specific to a VFS.

(7) By Larry Brasfield (LarryBrasfield) on 2020-10-15 19:31:59 in reply to 1 [link] [source]

This checkin made today appears to be evidence of a mild appetite along the lines you suggest.

(8) By anonymous on 2020-10-20 01:39:44 in reply to 7 [link] [source]

Glad to hear that. We appreciate the upstreaming of our exclusive file locking behavior on Windows.

Thank you!

(9.1) By Max (Maxulite) on 2020-12-04 11:29:31 edited from 9.0 in reply to 7 [link] [source]

Just checked sqlite 3.34.0. The exclusive parameter for URI filename mode seems to work on Windows, quite a nice addition. But both release history and the page about sqlite3_open_v2 probably lack this information. I hope this can be fixed for others to know. Thanks

(10) By anonymous on 2024-06-08 09:12:04 in reply to 7 [link] [source]

This function argument option should not be used because it is not documented. It could be phased out without programmers who read the official documentation finding out.

(11) By anonymous on 2024-06-09 03:35:18 in reply to 2 [link] [source]

Oops, sorry I wrote in asking if possible to use flock to get exclusive access to a data file. Further looking up indicates flock is non-binding, like a robots.txt file. I have found this article describing that exclusive access of data files on that type of OS is difficult:

https://kernel.org/doc/Documentation/filesystems/mandatory-locking.txt

Probably not worth it. I do hope you could plan to consider supporting such a feature in the future in case of new OS versions or new OSes supporting it.