Re: [sqlite] Exclusive locking mode not working as expected?

[Jonathan Little]

> From: "Jay A. Kreibich" <j...@kreibi.ch>
> Date: July 29, 2011 6:23:24 AM PDT
> Subject: Re: [sqlite] Exclusive locking mode not working as expected?
> 
>  On many OSes and most filesystems, file locking is advisory, not
>  mandatory.  File locks are essentially OS maintained atomic flags
>  about the locking state of a file.  The application must explicitly
>  check and acquire locks to understand if it is able to perform read/write
>  operations on the file.
> 
>  In short, most file locking is about applications cooperatively
>  waiting their turn, rather than being forcibly prohibited from doing
>  things.  They're "locks" in the computer science sense of that
>  word, not the front-door sense of the word.
> 
>  It also means that if an application (such as a file manager) does not
>  understand the specifics of the locking scheme used with a file, or
>  simply ignores locks, the application is usually free to manipulate
>  the file however it wants.
> 
>http://en.wikipedia.org/wiki/File_locking

On Windows, file locks are not advisory wrt non memory-mapped IO.

> 
>> 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?
> 
>  You can set the file permissions.  That's a more appropriate means to
>  prevent this kind of operation.
> 

I'm not sure that would work for us -- there's no reason to prevent the user 
from copying the file when we don't have it open, and changing permissions only 
while our app is running isn't a good solution because they wouldn't get 
changed back in the case of a crash.

>   -j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
> 

> 
> From: Pavel Ivanov <paiva...@gmail.com>
> Date: July 29, 2011 7:54:18 AM PDT
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] Exclusive locking mode not working as expected?
> Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> 
> 
>> 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?
> 
> Yes, opening with exclusive access is the only option for you. But
> with latest SQLite version you don't have to modify SQLite sources for
> that. You can use xSetSystemCall in sqlite3_vfs to change open() call
> and add exclusiveness flag in provided function (see
> http://www.sqlite.org/c3ref/vfs.html).
> 
> 
> Pavel
> 
> 

Interesting, thanks. It may be that modifying the code is just as easy for us 
at this time since we already have a custom build for unrelated reasons, but 
I'm hoping to get rid of that custom build at some point and replacing 
individual system calls and this extension mechanism should help a lot with 
that!

- JonathanInformation (including any attachments) contained in this electronic 
transmission is a PRIVILEGED AND CONFIDENTIAL COMMUNICATION.  It is for the 
sole use of the sender and the entity named above.  If you received this 
electronic transmission in error, you are required to immediately delete it in 
its entirety without making a copy.  Further dissemination is absolutely 
prohibited. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exclusive locking mode not working as expected?

[Jonathan Little]

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

Re: [sqlite] Exclusive locking mode not working as expected?

[Pavel Ivanov]

> 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?

Yes, opening with exclusive access is the only option for you. But
with latest SQLite version you don't have to modify SQLite sources for
that. You can use xSetSystemCall in sqlite3_vfs to change open() call
and add exclusiveness flag in provided function (see
http://www.sqlite.org/c3ref/vfs.html).


Pavel


On Fri, Jul 29, 2011 at 2:49 AM, Jonathan Little  wrote:
> I've been tearing my hair out over this for a little while, but I feel like 
> there must be something simple I'm missing. Based on the behavior I'm seeing, 
> the EXCLUSIVE locking mode isn't working the way I'd expect it to work based 
> on the docs at http://www.sqlite.org/pragma.html#pragma_locking_mode.
>
> Specifically, that page says that locking_mode = EXCLUSIVE is useful if "The 
> application wants to prevent other processes from accessing the database 
> file." 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). I 
> cannot open it in another instance of SQLite (as expected) but from the 
> documentation I'd expect not to be able to read it from another process at 
> all until the lock had been released somehow or the database closed.
>
> I originally thought it was an OS issue but the behavior is the same on 
> Windows Vista w/ SQLite 3.7.7.1 and Mac OS X 10.6 w/ SQLite 3.6.12.
>
> This behavior seems undesirable to me -- we've got users of our application 
> copying the database file using Windows Explorer while it's being written to, 
> and ending up with inconsistent/corrupt databases. 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?
>
> Jonathan LittleInformation (including any attachments) contained in this 
> electronic transmission is a PRIVILEGED AND CONFIDENTIAL COMMUNICATION.  It 
> is for the sole use of the sender and the entity named above.  If you 
> received this electronic transmission in error, you are required to 
> immediately delete it in its entirety without making a copy.  Further 
> dissemination is absolutely prohibited. Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exclusive locking mode not working as expected?

[Jay A. Kreibich]

On Thu, Jul 28, 2011 at 11:49:34PM -0700, Jonathan Little scratched on the wall:
> I've been tearing my hair out over this for a little while, but I feel
> like there must be something simple I'm missing. Based on the behavior
> I'm seeing, the EXCLUSIVE locking mode isn't working the way I'd
> expect it to work based on the docs at
> http://www.sqlite.org/pragma.html#pragma_locking_mode.

> ...I can still read the file from other processes (e.g. I can copy it
> using a file manager UI). I cannot open it in another instance of 
> SQLite (as expected) but from the documentation I'd expect not to be
> able to read it from another process at all until the lock had been
> released somehow or the database closed.

  On many OSes and most filesystems, file locking is advisory, not
  mandatory.  File locks are essentially OS maintained atomic flags
  about the locking state of a file.  The application must explicitly
  check and acquire locks to understand if it is able to perform read/write
  operations on the file.

  In short, most file locking is about applications cooperatively
  waiting their turn, rather than being forcibly prohibited from doing
  things.  They're "locks" in the computer science sense of that
  word, not the front-door sense of the word.

  It also means that if an application (such as a file manager) does not
  understand the specifics of the locking scheme used with a file, or
  simply ignores locks, the application is usually free to manipulate
  the file however it wants.
  
http://en.wikipedia.org/wiki/File_locking

> This behavior seems undesirable to me -- we've got users of our
> application copying the database file using Windows Explorer while
> it's being written to, and ending up with inconsistent/corrupt databases.

  Yes, well... that's true of almost any type of complex file format.

> 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?

  You can set the file permissions.  That's a more appropriate means to
  prevent this kind of operation.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exclusive locking mode not working as expected?

[Jay A. Kreibich]

On Fri, Jul 29, 2011 at 11:08:19AM +0100, Simon Slavin scratched on the wall:
> On 29 Jul 2011, at 7:49am, Jonathan Little wrote:
> 
> > Specifically, that page says that locking_mode = EXCLUSIVE is useful

> An exclusive lock will be retained only long enough to write changes
> to the database files.

  "PRAGMA locking_mode = EXCLUSIVE" never releases the locks.

  http://www.sqlite.org/pragma.html#pragma_locking_mode


-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exclusive locking mode not working as expected?

[Max Vlasov]

On Fri, Jul 29, 2011 at 10:49 AM, Jonathan Little wrote:

>
> This behavior seems undesirable to me -- we've got users of our application
> copying the database file using Windows Explorer while it's being written
> to, and ending up with inconsistent/corrupt databases. 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?
>
>
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exclusive locking mode not working as expected?

[Simon Slavin]

On 29 Jul 2011, at 7:49am, Jonathan Little wrote:

> Specifically, that page says that locking_mode = EXCLUSIVE is useful if "The 
> application wants to prevent other processes from accessing the database 
> file." 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).

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




Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exclusive locking mode not working as expected?

[Kevin Martin]

On 29 Jul 2011, at 07:49, Jonathan Little wrote:

Hello,

I am no expert in sqlite, but I do use it one of our projects. My  
interpretation of the documentation is slightly different to yours -  
see below

> Specifically, that page says that locking_mode = EXCLUSIVE is useful  
> if "The application wants to prevent other processes from accessing  
> the database file."

The documentation on that page also says:

``This pragma sets or queries the database connection locking-mode.''

> 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?

> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users