On Wed, 8 May 2019 21:36:43 +0200 Clemens Ladisch <clem...@ladisch.de> wrote:
> Andrew Moss wrote: > > ... an SQLite database hosted on a windows network share (using > > server 2012 R2 or later). We are well aware this is not advisable > > There are three possible sources of network filesystem data > corruption: > > 1) Bad locking implementations. Some Unix-y network filesystems > products allow to disable locking (in a misguided attempt to > increase performance), or do not implement locking at all. Although it doesn't solve anything, it's useful to understand that "bad" locking is not the whole story. File operations under NFS -- by design -- do not agree with the semantics on a local filesystem. The basic problem is that file I/O operations act on the kernel's filebuffer, not what's on the disk. Since all processes on one machine share the same kernel, they share the same filebuffer, and that filebuffer is the very definition an accurate representation of the state of the filesystem. With NFS, that's not true. There are as many filebuffers as there are nodes using the fileserver, plus the fileserver's own. If machine X updates the file, nothing informs machine Y. If the second machine acts on stale information in its filebuffer -- boom! -- welcome to a corrupted file. As a matter of fact, when machine X updates the file, nothing guarantees *any* data reach the server. Data are not necessarily written until the file is closed. Under SQLite, that could be a long time indeed! Even if locks were honored with perfect fidelity, inconsistent filebuffers in different clients sharing the same file provide lots of opportunity for inconsistency. A low-traffic system with a single writer and not much contention might not bump into it very often (or notice when it does!) but on NFS none of the ACID promises SQLite makes are actually in force. You may read that NFSv4 solves locking problems and others. It does not alter the basic consistency guarantee, though, that nothing is assuredly on the disk until the file is closed. It does not inform other clients' kernels of changes to files they're sharing. NFSv4 provides new functions to commit data to the disk and ascertain whether the current in-kernel image is up to date. SQLite does not use those functions and, even if it did, the mishmash of implementations would make any guaratees tenuous at best. If this sounds like an indictment of NFS, it's really not. Ironically, back when NFS was being invented, the cognoscenti already knew that what the filesystem couldn't provide, database servers could, and would, and soon did. Any problems with consistency, concurrency, locking, etc., were evidence that the process required a DBMS, not that the filesystem was insufficient to the purpose. They were right: the inconsistent-information problem is better solved not by disseminating the information across N filebuffers, but by routing the information through a single one, on a shared DBMS server. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
