On Thu, 26 Aug 2004 16:57:55 -0400 Kevin McCann kmccann@bellanet.org wrote:

"SQLite uses POSIX advisory locks to implement locking on Unix. On windows it uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite assumes that these system calls all work as advertised. If that is not the case, then database corruption can result. One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under windows. Your best defense is to not use SQLite for files on a network filesystem."

I've spent some time mucking about in this area and it is a minefield. Very simply, throwing out all of the messy details, there is only one operation which is atomic under the network filesystems: creat (2). The exceptions where creat(2) is not guaranteed atomic are for some of the more obscure cacheing network filesystes which attempt to be resilient across intermittent/bad connectivity and node failures. If you want to account for those filesystems as well then sorry, there's just no guaranteed correct locking methodology.

Yes, it is that bad.