Re: [sqlite] Continuous exclusive lock

[Cecil Westerhof]

2011/7/1 Simon Slavin 

> >> If you _need_ exclusiveaccess all along, then start app, "begin
> >> exclusive", do your stuf, "commit" and exit.
> >
> > The 'problem' is that the application can run for the whole day.
>
> There's no problem with this.  You can maintain an EXCLUSIVE lock on the
> database for the whole day.  You can even maintain it while you're waiting
> an unknown amount of time for user input.  SQLite will happily keep the
> database locked the whole time.
>

That is exactly what I mend to say. Thanks for the clarification.



> Some may argue that this would be bad use of resources but that's a
> different matter.
>

Depends on the situation, but in this case I think permissible. There is
only one user. Saves me a lot of headache and the user also. Started editing
something.  Is interrupted. Forgot that he was working on it and starts the
program again. In this case the program stops with the message that the
table is locked and he can continue where he left of. ;-}

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

Re: [sqlite] Continuous exclusive lock

[Simon Slavin]

On 1 Jul 2011, at 3:51am, Cecil Westerhof wrote:

> 2011/6/30 Jean-Christophe Deschamps 
> 
>> If you _need_ exclusiveaccess all along, then start app, "begin
>> exclusive", do your stuf, "commit" and exit.
> 
> The 'problem' is that the application can run for the whole day.

There's no problem with this.  You can maintain an EXCLUSIVE lock on the 
database for the whole day.  You can even maintain it while you're waiting an 
unknown amount of time for user input.  SQLite will happily keep the database 
locked the whole time.

Some may argue that this would be bad use of resources but that's a different 
matter.

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

Re: [sqlite] Continuous exclusive lock

[Cecil Westerhof]

2011/7/1 Jean-Christophe Deschamps 

>
> > > If you _need_ exclusiveaccess all along, then start app, "begin
> > > exclusive", do your stuf, "commit" and exit.
> > >
> >
> >The 'problem' is that the application can run for the whole day.
>
> Granted. And the 'problem' is ???
>

That there is not a clear R-M-W cycle. The data is read when starting up on
eight o'clock, but maybe only at three in the afternoon something is
changed. But the current solution is good enough. When starting the program
for a second time (because you forgot it was already open), the second one
is terminated with a message that the database is locked.

When I make sure I do a COMMIT after a change and immediately a BEGIN
EXCLUSIVE, I do not have to worry about anything.

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

Re: [sqlite] Continuous exclusive lock

[Jean-Christophe Deschamps]

> > If you _need_ exclusiveaccess all along, then start app, "begin
> > exclusive", do your stuf, "commit" and exit.
> >
>
>The 'problem' is that the application can run for the whole day.

Granted. And the 'problem' is ???

> > What I don't get is you later say it's a single-user, single-app
> > use.  You don't have to take that much care in this case.
>
>In principal could the user use another program to change the 
>database. But
>maybe I am paranoid. ;-}

One sometimes has to. If you admin the system, then place the DB in a 
folder owned by a special account and deny user access. Run your app 
under this account and the system will prevent unexpected writes from 
outside.



--
j...@antichoc.net  

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

Re: [sqlite] Continuous exclusive lock

[Cecil Westerhof]

2011/6/30 Jean-Christophe Deschamps 

> If you _need_ exclusiveaccess all along, then start app, "begin
> exclusive", do your stuf, "commit" and exit.
>

The 'problem' is that the application can run for the whole day.



> What I don't get is you later say it's a single-user, single-app
> use.  You don't have to take that much care in this case.


In principal could the user use another program to change the database. But
maybe I am paranoid. ;-}

At the moment I am not even writing to the database. But I like to be
prepared. To often things are not important, but when they become, it is to
late. (When it is raining, you can not repair your roof. When it is not
raining, it is not necessary.)

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

Re: [sqlite] Continuous exclusive lock

[Jean-Christophe Deschamps]

Cecil,


>Do I understand it correctly that after a commit the database is writeable
>again for others?

Yes.

>  In that case it is maybe better to do a:
> *PRAGMA locking_mode = EXCLUSIVE;
>*followed by an update of the database. As long as the application is
>running, I want to be sure that nobody writes to the database.*
>*

If you _need_ exclusiveaccess all along, then start app, "begin 
exclusive", do your stuf, "commit" and exit.

>I am now using:
> stat.execute("" +
> "PRAGMA locking_mode = EXCLUSIVE;\n" +
> "UPDATE authors\n" +
> "SETname = name\n" +
> "WHERE  id = (\n" +
> "SELECT MIN(id)\n"+
> "FROM   authors\n" +
> ");" +
> "COMMIT;");

A commit without begin * is useless and should result in an error being 
returned.

But in the example above, there is asolutely no need for an explicit 
transaction, nor any exclusive mode.  Your UPDATE is an atomic 
auto-commit operation in itself.

>The COMMIT is to be sure that this will not unlock the database.

Erroneous.

What I don't get is you later say it's a single-user, single-app 
use.  You don't have to take that much care in this case.  Anyway, 
enclosing R-M-W operations in an immediate (or exclusive) transaction 
is always a good idea, should you have to introduce companion apps 
later, or use a third-party DB manager in parallel.


--
j...@antichoc.net  

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

Re: [sqlite] Continuous exclusive lock

[Cecil Westerhof]

2011/6/30 Simon Slavin 

>
> On 30 Jun 2011, at 6:34pm, Cecil Westerhof wrote:
>
> > It is a single user application and database.
>
> Sorry about that, Cecil.  I was remembering some of the bonehead manoeuvres
> some of my former clients have pulled, then complained about.
>

No problem. Better a warning to much as to less. :-D

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

Re: [sqlite] Continuous exclusive lock

[Simon Slavin]

On 30 Jun 2011, at 6:34pm, Cecil Westerhof wrote:

> It is a single user application and database.

Sorry about that, Cecil.  I was remembering some of the bonehead manoeuvres 
some of my former clients have pulled, then complained about.

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

Re: [sqlite] Continuous exclusive lock

[Cecil Westerhof]

2011/6/30 Simon Slavin 

> > As long as the application is
> > running, I want to be sure that nobody writes to the database.
>
> This may be sensible if the application never waits for any input.  Some
> sort of bulk-update application, for example, or an overnight batch run.
>  But if the application pauses for input, aren't you giving a user power
> over the database ?  They could just start up the application then leave it
> in the background while they browse the web.
>

It is a single user application and database. So I do not think it is a
problem. With a multi-user application I would not do this, and check that
the data has not changed before doing an update.

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

Re: [sqlite] Continuous exclusive lock

[Simon Slavin]

On 30 Jun 2011, at 6:16pm, Cecil Westerhof wrote:

> As long as the application is
> running, I want to be sure that nobody writes to the database.

This may be sensible if the application never waits for any input.  Some sort 
of bulk-update application, for example, or an overnight batch run.  But if the 
application pauses for input, aren't you giving a user power over the database 
?  They could just start up the application then leave it in the background 
while they browse the web.

Anyway, you do it the way we've already discussed.  Use BEGIN EXCLUSIVE.

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

Re: [sqlite] Continuous exclusive lock

[Cecil Westerhof]

2011/6/30 Jean-Christophe Deschamps 

> >Is good enough for me. My only problem is that between reading the
> >data and
> >writing the changes, I want to be sure that no one  has changed the data.
> >For me that is enough.
>
> For this, a simple "Begin immediate;"  ...  "Commit;" embrassing you
> read-modify-write block will do.  Doesn't block readers and works in
> std and WAL journaling modes.
>

Do I understand it correctly that after a commit the database is writeable
again for others? In that case it is maybe better to do a:
*PRAGMA locking_mode = EXCLUSIVE;
*followed by an update of the database. As long as the application is
running, I want to be sure that nobody writes to the database.*
*

I am now using:
stat.execute("" +
"PRAGMA locking_mode = EXCLUSIVE;\n" +
"UPDATE authors\n" +
"SETname = name\n" +
"WHERE  id = (\n" +
"SELECT MIN(id)\n"+
"FROM   authors\n" +
");" +
"COMMIT;");

The COMMIT is to be sure that this will not unlock the database. It does
not. But I can read the database. Not a problem for me, but I understood it
should not be possible.

I just tried it with "BEGIN IMMEDIATE". Gives exactly the same results. So
properly I should stick to the "BEGIN IMMEDIATE"?

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

Re: [sqlite] Continuous exclusive lock

[Jean-Christophe Deschamps]

Cecil,

>Is good enough for me. My only problem is that between reading the 
>data and
>writing the changes, I want to be sure that no one  has changed the data.
>For me that is enough.

For this, a simple "Begin immediate;"  ...  "Commit;" embrassing you 
read-modify-write block will do.  Doesn't block readers and works in 
std and WAL journaling modes.


--
j...@antichoc.net  

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

Re: [sqlite] Continuous exclusive lock

[Cecil Westerhof]

2011/6/30 Richard Hipp 

> > > I am writing a desktop application in which I want to have exclusive
> > rights.
> > > In this way I do not need to check if the data has changed when the
> user
> > of
> > > my program wants to change records. Is this possible?
> >
> > You can open a transaction as
> >
> > BEGIN EXCLUSIVE
> >
> > 
> >
> > "After a BEGIN EXCLUSIVE, no other database connection except for
> > read_uncommitted connections will be able to read the database and no
> other
> > connection without exception will be able to write the database until the
> > transaction is complete."
> >
> > This is how you do that sort of thing.  So what you do is …
> >
> > 1) BEGIN EXCLUSIVE
> > 2) Check the result code from step 1 and make sure you got the lock.
> > 3) Do SELECTs to check the conditions for your data change and decide
> what
> > to do.
> > 4) If the results of step suggest changes, make them.
> > 5) END
> >
> > It is extremely common to see programs where step 3 of the above is done
> > before step 1.  Obviously, from your question, you understand this.
> >
> > Note to SQLite experts: it's not clear to me at what level the exclusive
> > lock is maintained on the file.  If an app crashes in while an EXCLUSIVE
> > lock is held, is the lock released ?  Does this vary by platform ?
> >
>
> The BEGIN EXCLUSIVE...COMMIT technique shown above only gives you exclusive
> access in rollback mode.  If you select WAL mode, then BEGIN EXCLUSIVE
> makes
> you the exclusive writer, but other processes can continue to read.  To get
> exclusive access in WAL mode, use PRAGMA locking_mode=EXCLUSIVE.
>

Is good enough for me. My only problem is that between reading the data and
writing the changes, I want to be sure that no one  has changed the data.
For me that is enough. But it is good to know for when I want really
exclusive access.

Thanks. I am going to try it.

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

Re: [sqlite] Continuous exclusive lock

[Richard Hipp]

On Thu, Jun 30, 2011 at 8:59 AM, Simon Slavin  wrote:

>
> On 30 Jun 2011, at 1:48pm, Cecil Westerhof wrote:
>
> > I am writing a desktop application in which I want to have exclusive
> rights.
> > In this way I do not need to check if the data has changed when the user
> of
> > my program wants to change records. Is this possible?
>
> You can open a transaction as
>
> BEGIN EXCLUSIVE
>
> 
>
> "After a BEGIN EXCLUSIVE, no other database connection except for
> read_uncommitted connections will be able to read the database and no other
> connection without exception will be able to write the database until the
> transaction is complete."
>
> This is how you do that sort of thing.  So what you do is …
>
> 1) BEGIN EXCLUSIVE
> 2) Check the result code from step 1 and make sure you got the lock.
> 3) Do SELECTs to check the conditions for your data change and decide what
> to do.
> 4) If the results of step suggest changes, make them.
> 5) END
>
> It is extremely common to see programs where step 3 of the above is done
> before step 1.  Obviously, from your question, you understand this.
>
> Note to SQLite experts: it's not clear to me at what level the exclusive
> lock is maintained on the file.  If an app crashes in while an EXCLUSIVE
> lock is held, is the lock released ?  Does this vary by platform ?
>

The BEGIN EXCLUSIVE...COMMIT technique shown above only gives you exclusive
access in rollback mode.  If you select WAL mode, then BEGIN EXCLUSIVE makes
you the exclusive writer, but other processes can continue to read.  To get
exclusive access in WAL mode, use PRAGMA locking_mode=EXCLUSIVE.

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

SQLite usually employs robust locks - meaning that if the application
crashes, the locks are automatically released.  Exceptions to this rule
occur when you select an alternative VFS such as "unix-dotfile".  The
"unix-dotfile" VFS uses classic dot-file locks, which can go stale on you
and require manual intervention to unlock.  But as long as you avoid exotic
VFSes, the locks will be released automatically following a application
crash or other abnormal termination.



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



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Continuous exclusive lock

[Simon Slavin]

On 30 Jun 2011, at 1:48pm, Cecil Westerhof wrote:

> I am writing a desktop application in which I want to have exclusive rights.
> In this way I do not need to check if the data has changed when the user of
> my program wants to change records. Is this possible?

You can open a transaction as

BEGIN EXCLUSIVE



"After a BEGIN EXCLUSIVE, no other database connection except for 
read_uncommitted connections will be able to read the database and no other 
connection without exception will be able to write the database until the 
transaction is complete."

This is how you do that sort of thing.  So what you do is …

1) BEGIN EXCLUSIVE
2) Check the result code from step 1 and make sure you got the lock.
3) Do SELECTs to check the conditions for your data change and decide what to 
do.
4) If the results of step suggest changes, make them.
5) END

It is extremely common to see programs where step 3 of the above is done before 
step 1.  Obviously, from your question, you understand this.

Note to SQLite experts: it's not clear to me at what level the exclusive lock 
is maintained on the file.  If an app crashes in while an EXCLUSIVE lock is 
held, is the lock released ?  Does this vary by platform ?

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

[sqlite] Continuous exclusive lock

[Cecil Westerhof]

I am writing a desktop application in which I want to have exclusive rights.
In this way I do not need to check if the data has changed when the user of
my program wants to change records. Is this possible?

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