4 ms·
SQLite can't be reliably used in networked file systems because it heavily relies on locking to be correctly implemented. I recently had to add a check for such
by lifthrasiir 1y ago
SQLite can't be reliably used in networked file systems because it heavily relies on locking to be correctly implemented. I recently had to add a check for such file systems in my application [1] because I noticed a related corruption firsthand. Simpler file formats do not demand such requirements. SQLite is certainly good, but not for this use.
[1] https://github.com/lifthrasiir/angel/commit/50a15e703ef2c1af15484b8a38c125a6061e84c2 https://github.com/lifthrasiir/angel/commit/50a15e703ef2c1af...
- greenavocado 1y agoEasy fix is an empty lock file adjacent to the real one.
- lifthrasiir 1y agoYeah, but only if SQLite did support that mode in some built-in VFS implementation...
- hedora 1y agoWhich network filesystems are still corrupting sqlite files? Sqlite on NFSv3 has been rock solid for some NFS servers for a decade. Maybe name and shame?
- lifthrasiir 1y agoSpecifically I had an issue over 9p used by WSL2. (I never thought it was networked before this incident.)
- hedora 1y agoIt seems odd to break a wide range of valid configs for something so obscure.
- lifthrasiir 1y agoYes, that was surprising to me too! I was able to fairly reliably reproduce data corruption in this case.
- afiori 1y agoIn that case the application would keep a temporary file and copy over when saving
- chungy 1y agoThat's pretty broad and over-generalized. Networking file systems without good lock support is almost always a bad setup by an administrator. Both NFS and CIFS can work with network-wide locks just fine. SQLite advises against using a networking file system to avoid potential issues, but you can successfully do it.
- jdboyd 1y agoAre the typical Synology, Qnap, or TrueNAS devices with default Linux, macOS and Windows clients going to be set up correctly by default? If any of the typical things someone is likely to setup following wizards in a home or small office is likely to result in lock not working correctly for SQLite, then it is fair for them to warn against using it on a network file system. As an application format, you don't generally expect people to be editing an ODF file at the same time though, so network locking doesn't really disqualify it for use as a document format.
- mschuster91 1y ago> As an application format, you don't generally expect people to be editing an ODF file at the same time though Oh hell yes you do. Excel spreadsheets are notorious for people wanting to collaborate on them, and PowerPoint sheets come in close second. It used to be an absolute PITA but at least Office 365 makes the pains bearable.
- lifthrasiir 1y agoAs noted in my other comment, those "potential" issues are real and do happen from time to time. Unless SQLite gives some set of configurations to avoid such issues, I can't agree that it's over-generalized.
- kvdveer 1y agoIn the context of this article, that's largely irrelevant: ZIP cannot be used in a multi-user scenario at all, so even if sqlite isn't perfect, it's still miles better than the ZIP format it replaces in this thought experiment.