On Fri, 2004-09-24 at 14:51, Ara.T.Howard wrote: > On Fri, 24 Sep 2004, Eddy Macnaghten wrote: > > > However, reading the documentation it seems that SQLite is not hot on > > concurrent access through networks, or across platforms, due to the funnies > > of fcntl, or incompatibilities between Windows and Linux and so on. > > it works o.k. if your nfs impl is good (netapp, linux - not sun) but code > needs to be prepared to handle BUSY errors because sqlite uses byte ranges > locks and therefore you may have a lock only to find out you need some 'more' > lock. i do think this type of use is frowned upon though - i certainly find > it unnerving.
There is no way I can guarantee the "nfs" to be good. Also, a mixture of Linux and Windows clients need to be allowed for, I do not know how SAMBA supports the fcntl functionality, or how well it interfaces with the Windows Server or Client locking mechanisms. <snip content="lock file advocacy"/> > i've done the same thing. what's your locking algorithim? there are only a > few operations which are atomic on nfs and they are not open(O_EXCL) or mkdir > which people typically use. i've written a robust nfs safe lockfile api based > on the atomicity of link(2) and command line tool that is highly configurable > and that supports automatic recovery from stale locks left over from dead > processes/machine reboots, you can find it here > > http://raa.ruby-lang.org/project/lockfile/ > Hmm - interesting. However, I cannot really use your method as link(2) is not really available under Windows (as far as I can tell). The mechanism I use is not brilliant, that is that it does not guarantee 100% that locking is successful! - Though I have taken care to make sure it is as close to 99.999 (as many 9s as possible) % sure that it is. The more clients there is the more likely it is to fail, which is why this is only good for a small number of clients (approx 5) in non mission-critical applications. I do not know how easy it is to recover a corrupt sqlite3 database file, if there is not a utility to do that then I will probably work on one (it would open the file exclusively and create a dump that can be re-imported into a new database). My lock file has a 16 byte header, then a number of 4 byte records each representing a lock aquired by someone. Each client has a unique number. When a lock is to be aquired the cliet first checks to see if the file is free (the first byte is zero), if it is not it waits 1/100 of a second and tries again. If it is free it sets the first byte to -1, and the appropriate header field to the client number. It then reads the header (with the first 512 bytes) again and checks that it still "owns" the file (the appropriate header field set to the client number) - if not starts the process again - if it does it then performs searches and updates on the file accordingly, unowning the file (setting the first bu=yte to zero) when finished. I will post the source on my web site soon if you are interested. The mechanism, as I said, is not 100% guaranteed, but should work in practice for a small number of clients on a LAN - even with mixed (old) architectures - on a non mission critical scenario - so long as database corruption does not happen TOO often and that corrupt databases can be mostly recovered. Where more reliable scaleability and stability is required then PostgreSQL, or one of that ilk, is reccomended anyway! <snip> > > Ideally, I would like an indicator in the SQLite database file header record > > to determine if this file locking is required, and set accordingly when the > > database is created, and possibly be switched on and off with a PRAGMA > > command or similar, or maybe it's own utility. > > this could be o.k. - but better IMHO would simply be a switch that allowed the > entire database to be locked read/write in a blocking fashion rather than > using byte range locks. on the other hand this is an easy task for the > application domain... you are suggesting a pragma that would indicate some > sort of external lock was required to access the database? This is essential in my case IMHO. I suppose in theory this could be an atrribute of the connection API call, but it does not belong there. If some clients use one kind of locking mechanism, and others use another, database corruption and problems are guaranteed! -- Edward A. Macnaghten http://www.edlsystems.com
