Hmm, one for doing my own locking, one against it. As this seems to be an obvious issue in any network, I wonder why the network developers have not appropriately addressed this issue. Looks like I need to research this problem more before implementing. I dislike probability games of designs that will work most of the time, but have a potential collision scenario. Such is why so many applications have the occasional crash or corruption.
------------ Scott Doctor scott at scottdoctor.com On 5/3/2015 12:54 PM, James K. Lowden wrote: > On Sat, 02 May 2015 19:24:19 -0700 > Scott Doctor <scott at scottdoctor.com> wrote: > >> Each computer has its own copy of the program, but may >> share the database file which may be located on any of the computers. >> So I am wondering whether I should implement my own locking logic in >> my program. > Yes, you need to arrange within the application not to have two > processes writing to the remote database at the same time. The locking > SQLite uses depends on semantics not provided by the remote > filesystem. > > With a local filesystem, when two processes are updating a file, each > process's update is visible to the other in the unified buffer cache > supplied by the OS. In a networked filesystem, there is no unified > buffer cache: updates from process A, while manifest in the file, are > not necessarily reflected in the cache used by process B on another > machine. A subsequent update from B based on its outdated cache could > well create an incoherent file image. > > The only safe answer is arrange for each update to begin by locking the > file in the application. Then open the database, update it, close the > database, and unlock the file. By the same token, after any update > every reading process should close and re-open the database before > continuing to rely on the database > > One way to do that would be to keep an update.count file adjacent to > the database file. Lock it, read it, increment it, proceed with the > database update, and release it. Before each read, lock the file for > reading, and check the counter value. If it's changed, close and > re-open the database, execute the SELECT, and release the file. > > That's just an outline; I might have overlooked something. The > complexity and pitfalls explain why applications that need > inter-machine consistency connect to a DBMS daemon that manages its > database files locally. > > HTH. > > --jkl > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
