On Tue, 28 Jun 2016 at 19:17 Rowan Worth <row...@dugeo.com> wrote: > On 28 June 2016 at 16:07, dandl <da...@andl.org> wrote: > > > > Do not use SQLite for concurrent access over a network connection. > > Locking > > > semantics are broken for most network filesystems, so you will have > > > corruption issues that are no fault of SQLite. > > > > I have seen this comment made more than once on this list. Is there any > > reliable evidence to support this for a Windows-based network? > > > > Disclosure: we wrote and maintain an ISAM-based multi-user database > > product which relies on network locking. We have conducted exhaustive > tests > > over many years and in our opinion, locking and multi-user semantics on > > Windows XP and later networks are reliable and free of errors, if > performed > > correctly by the client software. > > > > I can't comment on windows sorry, but in-house we use sqlite databases > shared between many unix clients via network file systems. Over the years > we've used nfs3, nfs4, and lustre. > > These databases are subject to highly concurrent usage every working day. > In the past two years we've had maybe one corruption issue which implicated > the file system (client side logging suggested that four RESERVED locks > were obtained concurrently). > > > More commonly corruption has been the result of user/application > misbehaviour: > > 1. Users copying databases while they're being updated (leaving them with a > corrupt copy) > 2. Users inadvertently symlinking/hardlinking database files > 3. Our application inadvertently discarding sqlite's locks after backing up > the database (thanks POSIX locking semantics) > > > (3) was the main offender for us. Since figuring that out we've been left > with a very robust environment - but not bullet proof as indicated above. I > can imagine this kind of thing being sensitive to network/file system > configuration, which is not easy to diagnose as a sysadmin let alone via > email so in that sense I understand why network file systems are > discouraged on the list (aside from the fact that sqlite and its > database-level single user lock was not designed for networked concurrent > usage). >
We are Windows-based and the only corruptions we've seen is naive copying of an in-use database (#1 in your list) plus bugs such as crashes when transferring the database between servers resulting in an incomplete copy. Neither have been the responsibility of SQLite and we've generally mitigated both by tool and usage improvement as best we can. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
