On 7/29/2011 05:00, sqlite-users-requ...@sqlite.org wrote: > ------------------------------ > > Date: Fri, 29 Jul 2011 10:41:59 +0100 > From: Kevin Martin<ke...@khn.org.uk> > Subject: Re: [sqlite] Exclusive locking mode not working as expected? > > However, if I start up a SQLite shell, set the locking_mode to > > exclusive, and perform an insert or other write operation (such that > > the exclusive lock should be acquired and not released), I can still > > read the file from other processes (e.g. I can copy it using a file > > manager UI). > Are these processes making a connection to the database? If no, why > would you expect them to obey locking_mode? I expected it because the documentation mentions preventing access to the "database file" rather than simply "database." >> > Am I missing something here or if I want to prevent this, is my only >> > option to modify our build of SQLite to open the file for exclusive >> > access? > That is certainly not your only option. Personally, I would create a > program that makes a database connection (which will take account of > the locks), and copies everything relevant into another database. > After the user has run this to create their copy, they can then do > what they want with it. > > Kevin Martin In fact we do have a feature that uses the SQLite Backup API to copy the database to a new file if the user wants to, but training is an issue for us and some users are still trying to copy the file using Explorer which we'd like to prevent. > ------------------------------ > > Date: Fri, 29 Jul 2011 11:08:19 +0100 > From: Simon Slavin<slav...@bigfraud.org> > Subject: Re: [sqlite] Exclusive locking mode not working as expected? > > An exclusive lock will be retained only long enough to write changes to the > database files. And since SQLite is very fast at this, the files will be > locked only for a short time. If you want to keep the database file locked > for a longer time, declare your transactions explicitly using > > BEGIN EXCLUSIVE TRANSACTION > do all reading and writing here for the set of changes > END TRANSACTION > > for the time you want the database to be locked. > > More than you need to know about locking can be found at > > <http://www.sqlite.org/lang_transaction.html> > <http://www.sqlite.org/lockingv3.html> > > Simon. The documentation at http://www.sqlite.org/pragma.html#pragma_locking_mode indicates that when locking_mode is set to EXCLUSIVE, the exclusive lock obtained for writing will not be released. That implies that you should not need to always be in an exclusive transaction in order to have exclusive access to the file. Further, this *is* the behavior seen in practice if you interpret the exclusive lock to be a lock against additional DB connections rather than concurrent file access (which is looking like the case). > ------------------------------ > > Date: Fri, 29 Jul 2011 14:47:51 +0400 > From: Max Vlasov<max.vla...@gmail.com> > Subject: Re: [sqlite] Exclusive locking mode not working as expected? > > When you say "inconsistent/corrupt databases" are you talking about the same > users trying to use this copied bases later or about the base uses while > this copy operation was in process and being corrupt after this? > > If the former, as I see from winOpen (windows case), the sharing set as > FILE_SHARE_READ | FILE_SHARE_WRITE so there's no denying on the os level > during access to this file and the only option here is to implement your own > virtual file system for your files. But just curious, do you really want to > give your users an "official" way to manipulate sqlite files manually? I > know that many advanced users know what sqlite really is and try to do some > back-up by hands, but they also should know that such knowledge voids > "warranty" in a sense > > Max
I'm referring to the former. Based on my testing, if the file stays locked (via LockFile()) access from other processes (including Explorer) is denied even with FILE_SHARE_READ enabled. It seems like SQLite is just not keeping the file locked, even though it's keeping its own internal notion that the DB exclusive lock is held. Changing winOpen is a better option for us than implementing a virtual file system, I think, given our deployment scenario. To answer your other question: no, we do not want users to be manipulating the sqlite files manually. We're using the db as an application file format, though, and users are trying to hot copy the file to email it out for support. I mentioned in a previous method that we do use the SQLite backup API for this purpose in an official feature, so the workflow isn't an issue -- just the specific way the users are trying to copy the file is. We will train them all to use the backup feature, but in the mean time we're getting corrupted files in support requests. - Jonathan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
