On Wed, 25 Sep 2019 at 05:14, Randall Smith <rsm...@qti.qualcomm.com> wrote:
> I have an application where remote users will be connecting to a SQLite DB > over a network connection that seems to be somewhat sketchy (I can't > characterize it well; I'm hearing this second-hand). > > My question is: Do the commit-or-rollback semantics of SQLite transactions > work if the connection to the file system is weird? For example, do I get > a rollback if the connection is lost mid-transaction or something? SQLite's transaction protocol provides durability in the face of a crash or severed connection - a partially committed transaction will be rolled back as you suspected. Whether that comes into play also depends on the behaviour on the filesystem though; eg. following a "lost connection" some file servers will still consider the file locked by the disconnected client until they hear back from said client or a timeout expires. I've seen some configurations where this seems to be retained indefinitely, locking all clients out of the DB until the server is restarted. Also SQLite relies on synchronous i/o operations to provide consistency and durability, and if the filesystem skimps on these (to try and provide better performance or whatever) you will likely end up with a corrupt DB. These synchronous ops tend to become the limiting factor in DB performance - a write transaction in this environment is quite expensive and you won't get anywhere close to the write throughput that you would from a traditional RDMS. Or, is the underlying assumption with transactions that the connection > between SQLite code and file system is 100% reliable? > As long as the filesystem provides a consistent view to each client of events surrounding locks and cache invalidation despite the sketchy network then you ought to get consistent data coming from sqlite. As others have said sqlite is very much at the mercy of the filesystem's locking semantics, and if that is not robust in the face of a sketchy network then it probably will not be a usable solution. Source: hundreds of production sqlite DBs on NFS over many years. We have had a few instances of DB corruption as well as some phantom lock scenarios so you do have to plan for those, but by and large things work well. That said we have a reliable network layer, and the write-concurrency caveat is a real concern. -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
