Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 11:19pm, Roger Binns wrote:

> Simon Slavin wrote:
>> On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote:
>>
>>> sqlite> .timeout 1
>>
>> What is it that that command does ?  I looked for a PRAGMA but didn't
>> find one.  Or does it correspond to a check-and-retry loop which the
>> programmer has to do yourself in her or his own code ?
>
> It calls sqlite3_busy_timeout with the value supplied.
>
>  http://sqlite.org/c3ref/busy_timeout.html

I believe that page, and the one it points to

http://sqlite.org/c3ref/busy_handler.html

fill in the missing details.  Thank you.

Now all we need to know is whether the OP's reported error 'database  
is locked' from his API is equivalent to one of the SQLite errors  
SQLITE_BUSY or SQLITE_IOERR_BLOCKED.  And the answer to that is not  
part of SQLite.

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

Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote:

> sqlite> .timeout 1

What is it that that command does ?  I looked for a PRAGMA but didn't  
find one.  Or does it correspond to a check-and-retry loop which the  
programmer has to do yourself in her or his own code ?

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

Re: [sqlite] readers and writer

[Pavel Ivanov]

> If there is a timeout and it is set to 0 by default then that it is not
> very useful.

It's indeed so and it's useful in some cases. And you know, everything
can be tested pretty easily. Just make some test database and execute
in one terminal:

sqlite> create table t (a);
sqlite> begin;
sqlite> select * from t;

Then in other terminal:

sqlite> .timeout 1
sqlite> insert into t values (1);
SQL error: database is locked

And you'll see that before 'database is locked' appears shell will
wait for 10 seconds. If while it waits you make commit in the first
terminal then insert will succeed.

And about your earlier discussion about locks: when connection has
RESERVED or PENDING lock and it tries to propagate it to EXCLUSIVE it
waits for busy_timeout and if still unsuccessful then it returns
SQLITE_BUSY to the caller (or message 'database is locked' in the
shell). If somebody locked database with PENDING or EXCLUSIVE lock and
you're trying to get SHARED lock then SQLite will again wait for
busy_timeout and if SHARED lock cannot be acquired yet then you will
get SQLITE_BUSY or 'database is locked' message.

Pavel

On Wed, Oct 14, 2009 at 2:23 PM, priimak  wrote:
> Simon Slavin wrote:
>> On 14 Oct 2009, at 5:39am, Dmitri Priimak wrote:
>>
>>
>>> Yes, I understood that, but the impression I got is that SELECT will
>>> place shared lock on the database. While INSERT or UPDATE will first
>>> place PENDING lock indicating that it wants to write.
>>>
>>
>> Okay, I see what you mean.  I don't know how long the write process
>> will wait for the shared lock to be released before returning an error
>> message.  If it ever gives up, that is.  If it never gives up, a note
>> from the developers on what
>>
>>
 database is locked

>>
>> means would be useful, since if it never gives up there's never any
>> reason to generate that error message.  I assume there's a timeout
>> setting somewhere you can change but I see no PRAGMAs about timeout.
>>
> If there is a timeout and it is set to 0 by default then that it is not
> very useful.
> Moreover this http://www.sqlite.org/faq.html#q5 says
>
>    "Multiple processes can have the same database open at the same
> time. Multiple processes can be doing a SELECT at the same time. But
> only one process can be making changes to the database at any moment in
> time, however."
>
> Which does not seem to be true.
>
>> Googling on 'sqlite database is locked' suggests that other people
>> have discussed this problem.
> Well. One common "solution" is to copy database file apply changes
> and then copy it back or to apply changes only to 1.db then copy it
> to 2.db which would only be used for reading. This however is not
> much of a solution and I have been actually doing just that, but the
> file is getting bigger and bigger and copying it over is not an option
> anymore since it takes too much time and IO, which is not good for
> other processes running on that machine. And it also have effect on
> latency between application of changes to the database and making
> those changes available for querying.
>
> --
> Dmitri Priimak
> ___
> 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] readers and writer

[priimak]

Simon Slavin wrote:
> On 14 Oct 2009, at 5:39am, Dmitri Priimak wrote:
>
>   
>> Yes, I understood that, but the impression I got is that SELECT will
>> place shared lock on the database. While INSERT or UPDATE will first
>> place PENDING lock indicating that it wants to write.
>> 
>
> Okay, I see what you mean.  I don't know how long the write process  
> will wait for the shared lock to be released before returning an error  
> message.  If it ever gives up, that is.  If it never gives up, a note  
> from the developers on what
>
>   
>>> database is locked
>>>   
>
> means would be useful, since if it never gives up there's never any  
> reason to generate that error message.  I assume there's a timeout  
> setting somewhere you can change but I see no PRAGMAs about timeout.
>   
If there is a timeout and it is set to 0 by default then that it is not 
very useful.
Moreover this http://www.sqlite.org/faq.html#q5 says

"Multiple processes can have the same database open at the same 
time. Multiple processes can be doing a SELECT at the same time. But 
only one process can be making changes to the database at any moment in 
time, however."

Which does not seem to be true.

> Googling on 'sqlite database is locked' suggests that other people  
> have discussed this problem.
Well. One common "solution" is to copy database file apply changes
and then copy it back or to apply changes only to 1.db then copy it
to 2.db which would only be used for reading. This however is not
much of a solution and I have been actually doing just that, but the
file is getting bigger and bigger and copying it over is not an option
anymore since it takes too much time and IO, which is not good for
other processes running on that machine. And it also have effect on
latency between application of changes to the database and making
those changes available for querying.

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

Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 5:39am, Dmitri Priimak wrote:

> Yes, I understood that, but the impression I got is that SELECT will
> place shared lock on the database. While INSERT or UPDATE will first
> place PENDING lock indicating that it wants to write.

Okay, I see what you mean.  I don't know how long the write process  
will wait for the shared lock to be released before returning an error  
message.  If it ever gives up, that is.  If it never gives up, a note  
from the developers on what

>> database is locked

means would be useful, since if it never gives up there's never any  
reason to generate that error message.  I assume there's a timeout  
setting somewhere you can change but I see no PRAGMAs about timeout.

Googling on 'sqlite database is locked' suggests that other people  
have discussed this problem.

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

Re: [sqlite] readers and writer

[Edzard Pasma]

--- prii...@stanford.edu wrote:
> 
> Simon Slavin wrote:
>> On 14 Oct 2009, at 1:21am, priimak wrote:
>>
>>   
>>> I am heaving small problem with sqlite. I have a webapp which connects
>>> to the database using sqlite-jdbc and performs SELECTs to response to
>>> different GET requests, while this happens if I try to write to a
>>> database ( UPDATE or INSERT ) from command line, that (i.e. update
>>> process) would occasionally fail with error message "SQL error near  
>>> line
>>> 1: database is locked". Notice that I have only one writer, but many
>>> readers. Reading documentation (http://www.sqlite.org/ 
>>> lockingv3.html) I
>>> was under impression that process which intends to update database  
>>> will
>>> place it in the pending state allowing all currently running reads (
>>> SELECTs ) to proceed, while blocking new SELECTs, the lock database
>>> apply changes and then unlock it allowing all pending and new  
>>> SELECTs to
>>> proceed. Am I right about it and if so why do I "SQL error near line  
>>> 1:
>>> database is locked" when trying to write to a database?
>>> 
>>
>> I hope this will do until an expert comes along.  I think you got it  
>> right, you just don't know something.
>>
>> The SELECT activity requires a lock to the database.  For instance,  
>> consider a TABLE contact with columns name, address, phone .  An index  
>> is declared on just the name column.  You execute
>>
>> SELECT phone FROM contacts WHERE name = 'Jackie'
>>
>> This requires a two-stage process: first use the index to find the ids  
>> of the rows which have the right name.  Then look up those rows in the  
>> table and find out which phone numbers they have.  Obviously, this  
>> requires locking: you wouldn't want someone to make changes to the  
>> table between those two steps.  However, it requires locking only  
>> against writing: other reads going on at the same time are harmless,  
>> but a change between the two steps can invalidate the data.
>>
>> So if a SELECT is in progress, other SELECT commands can be allowed to  
>> proceed without problems. But no INSERT or UPDATE can be allowed until  
>> the SELECT is finished.  Hence you will sometimes get a lock on the  
>> write.
>>
>> How you deal with this, I don't know.  Random wait-and-try-again ?
> Yes, I understood that, but the impression I got is that SELECT will
> place shared lock on the database. While INSERT or UPDATE will first
> place PENDING lock indicating that it wants to write. While it is in a
> PENDING lock state all operations that placed SHARED lock ( such as
> SELECTs ) will allow to complete and new SHARED locks either denied
> or blocked ( this part of documentation is not clear as to which one of
> these two actions are taken  ). Then when all SHARED locks are
> removed due to completion of SELECTs, database moves from PENDING
> into EXCLUSIVE lock, which is cleared when update/write completed and then
> new/pending SHARED locks are allowed to proceed. This should mean
> that with many processes reading and only one writing there is no need to
> use sqlite3_busy_timeout() function, which is to be used when we have
> many processes trying to write to the database and/or reader if new
> SHARED locks are denied while database is in a PENDING and/or
> EXCLUSIVE lock state ( again, this point it not clear in documentation ).
> Do I understand it correctly?
> 
> --
> Dmitri Priimak

Hello Dmitri, I understaod it the same way. However recently I observed that a 
PENDING lock does not perform its useful function (prevent writer starvation) 
in case readers and writers are threads of a single process! May that be the 
case? Best regards, Edzard Pasma. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] readers and writer

[Dmitri Priimak]

Simon Slavin wrote:
> On 14 Oct 2009, at 1:21am, priimak wrote:
>
>   
>> I am heaving small problem with sqlite. I have a webapp which connects
>> to the database using sqlite-jdbc and performs SELECTs to response to
>> different GET requests, while this happens if I try to write to a
>> database ( UPDATE or INSERT ) from command line, that (i.e. update
>> process) would occasionally fail with error message "SQL error near  
>> line
>> 1: database is locked". Notice that I have only one writer, but many
>> readers. Reading documentation (http://www.sqlite.org/ 
>> lockingv3.html) I
>> was under impression that process which intends to update database  
>> will
>> place it in the pending state allowing all currently running reads (
>> SELECTs ) to proceed, while blocking new SELECTs, the lock database
>> apply changes and then unlock it allowing all pending and new  
>> SELECTs to
>> proceed. Am I right about it and if so why do I "SQL error near line  
>> 1:
>> database is locked" when trying to write to a database?
>> 
>
> I hope this will do until an expert comes along.  I think you got it  
> right, you just don't know something.
>
> The SELECT activity requires a lock to the database.  For instance,  
> consider a TABLE contact with columns name, address, phone .  An index  
> is declared on just the name column.  You execute
>
> SELECT phone FROM contacts WHERE name = 'Jackie'
>
> This requires a two-stage process: first use the index to find the ids  
> of the rows which have the right name.  Then look up those rows in the  
> table and find out which phone numbers they have.  Obviously, this  
> requires locking: you wouldn't want someone to make changes to the  
> table between those two steps.  However, it requires locking only  
> against writing: other reads going on at the same time are harmless,  
> but a change between the two steps can invalidate the data.
>
> So if a SELECT is in progress, other SELECT commands can be allowed to  
> proceed without problems. But no INSERT or UPDATE can be allowed until  
> the SELECT is finished.  Hence you will sometimes get a lock on the  
> write.
>
> How you deal with this, I don't know.  Random wait-and-try-again ?
Yes, I understood that, but the impression I got is that SELECT will
place shared lock on the database. While INSERT or UPDATE will first
place PENDING lock indicating that it wants to write. While it is in a
PENDING lock state all operations that placed SHARED lock ( such as
SELECTs ) will allow to complete and new SHARED locks either denied
or blocked ( this part of documentation is not clear as to which one of
these two actions are taken  ). Then when all SHARED locks are
removed due to completion of SELECTs, database moves from PENDING
into EXCLUSIVE lock, which is cleared when update/write completed and then
new/pending SHARED locks are allowed to proceed. This should mean
that with many processes reading and only one writing there is no need to
use sqlite3_busy_timeout() function, which is to be used when we have
many processes trying to write to the database and/or reader if new
SHARED locks are denied while database is in a PENDING and/or
EXCLUSIVE lock state ( again, this point it not clear in documentation ).
Do I understand it correctly?

--
Dmitri Priimak

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

Re: [sqlite] readers and writer

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> Perhaps this passage could be rephrased to warn explicitly about NFS  
> rather than about the more general "files on a network filesystem".

As a general rule network filesystems are buggy.  Local filesystems get to
make all the decisions themselves - there is no other party.  With remote
filesystems everything is passed to the remote server which makes all the
decisions.  This of course is eye wateringly slow adding latency to every
filesystem operation.  So the network clients occasionally make a decision
locally instead of sending it to the server.  (This is also a *lot* easier
to code.)

Earlier NFS releases were remarkably lax on the client side - the Unix
Hater's Guide even has an entire entertaining chapter on it.  SQLite
exercises codepaths that aren't particularly normal compared to most
applications and locking is even rarer.  Unless you can guarantee *all*
client side code, the server side and interactions with multiple clients is
correct then there is the possibility of corrupting SQLite files.  Based on
past experience there is also the probability they will be corrupted.

Are you willing to stake your reputation and whatever else on there being
bug free implementations of AFP and SMB.  (BTW in a past life I coded an SMB
server - the other clients and servers out there are definitely not bug free :-)

Users of SQLite won't appreciate their databases being just a little bit
corrupted infrequently.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrVOV4ACgkQmOOfHg372QTxkgCfVrY2bpmoDtfw2rI2pnsG0o8G
uRkAoIRFY8A1sKZRFTyV1/2iqcxH4a6G
=jv8p
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] readers and writer

[Jean-Christophe Deschamps]

´¯¯¯
>So if a SELECT is in progress, other SELECT commands can be allowed to
>proceed without problems. But no INSERT or UPDATE can be allowed until
>the SELECT is finished.  Hence you will sometimes get a lock on the
>write.
>
>How you deal with this, I don't know.  Random wait-and-try-again ?
`---

Isn't that precisely what sqlite3_busy_timeout() is for?



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

Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 1:21am, priimak wrote:

> http://www.sqlite.org/lockingv3.html

By the way, I just read some of that page and a bit of it, while  
possibly technically correct, may be putting some people off from  
using SQLite.

"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."

This is correct in that implementation of NFS file locking under OS X  
is buggy, as it is in many OSen.  However, almost no users of OS X  
mount shared volumes using NFS.  Both AFP and SMB offer so many other  
benefits (including decent security) that NFS is hardly used at all.   
And both AFP and SMB do locking properly (or at least any bugs do get  
fixed quickly because everyone complains about them).

Perhaps this passage could be rephrased to warn explicitly about NFS  
rather than about the more general "files on a network filesystem".

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

Re: [sqlite] readers and writer

[Simon Slavin]

On 14 Oct 2009, at 1:21am, priimak wrote:

> I am heaving small problem with sqlite. I have a webapp which connects
> to the database using sqlite-jdbc and performs SELECTs to response to
> different GET requests, while this happens if I try to write to a
> database ( UPDATE or INSERT ) from command line, that (i.e. update
> process) would occasionally fail with error message "SQL error near  
> line
> 1: database is locked". Notice that I have only one writer, but many
> readers. Reading documentation (http://www.sqlite.org/ 
> lockingv3.html) I
> was under impression that process which intends to update database  
> will
> place it in the pending state allowing all currently running reads (
> SELECTs ) to proceed, while blocking new SELECTs, the lock database
> apply changes and then unlock it allowing all pending and new  
> SELECTs to
> proceed. Am I right about it and if so why do I "SQL error near line  
> 1:
> database is locked" when trying to write to a database?

I hope this will do until an expert comes along.  I think you got it  
right, you just don't know something.

The SELECT activity requires a lock to the database.  For instance,  
consider a TABLE contact with columns name, address, phone .  An index  
is declared on just the name column.  You execute

SELECT phone FROM contacts WHERE name = 'Jackie'

This requires a two-stage process: first use the index to find the ids  
of the rows which have the right name.  Then look up those rows in the  
table and find out which phone numbers they have.  Obviously, this  
requires locking: you wouldn't want someone to make changes to the  
table between those two steps.  However, it requires locking only  
against writing: other reads going on at the same time are harmless,  
but a change between the two steps can invalidate the data.

So if a SELECT is in progress, other SELECT commands can be allowed to  
proceed without problems. But no INSERT or UPDATE can be allowed until  
the SELECT is finished.  Hence you will sometimes get a lock on the  
write.

How you deal with this, I don't know.  Random wait-and-try-again ?

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