5 ms·
> evidently a poor platform for files that need transactional updates made to them by multiple users at once What makes this the case?
by kayson 3y ago
> evidently a poor platform for files that need transactional updates made to them by multiple users at once
What makes this the case?
- simonw 3y agoIf it was a solid platform for this I imagine SQLite would work already!
- kayson 3y agoIt does work... Sort of. From what I've read, the NFSv4 server properly implements locking (and I think most of those bits are in the Linux kernel now anyway), but sqlite won't support it anyway. And I am able to run sqlite on NFSv4 with minimal problems. Every once in a while I do get a hiccup, but it's not clear why.
- btilly 3y agohttps://access.redhat.com/solutions/120733 https://access.redhat.com/solutions/120733 explains it. The critical bit is the root cause at the end. Which is that NFS sees access to any part of the file as access to all of the file. So any access locks it for anyone. Therefore shared access to the database will cause random hangs due to client behavior. If you turn off locking, then there is no way to avoid data corruption. And this is with NFS working correctly. Which is not a safe assumption given that widely used platforms like OS X implement it wrong. In short, there is a reason that we've joked since the last millennium that NFS stands for "No File System". And the joke is still relevant today.
- kayson 3y agoThanks for posting that! I've always wondered what the deal is. How do other file systems do it differently that allows for concurrent access to the file?
- btilly 3y agohttps://www.sqlite.org/lockingv3.html https://www.sqlite.org/lockingv3.html explains what it wants to happen. Note in particular that multiple processes can read at a time, and only slowly escalate into a write lock which is held as short a time as you can before going back to the normal state. While NFS assumes that if you read, you may write, and may not take care to make sure you have the most recent version WHEN you write. (These are all important assumptions to make for random programs written by random programmers. Few programmers can be assumed to take the care that databases do around getting locking logic correct.)
- kayson 3y agoThanks. Now I finally get it, but am still no less annoyed that this doesn't "just work."
- chasil 3y agoAlso, you cannot use WAL mode as all processes cannot see the shared memory. Below are well-known limitations of WAL mode. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf “To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.” “It is not possible to change the page size after entering WAL mode.” “In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.” https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html “SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”