Re: [sqlite] suggestions for avoiding "database locked" on ajax

[P Kishor]

Conclusion: onblur() is an error prone strategy. I am going to look
into changing my approach that doesn't involve onblur().

I will research into busy_timeout() settings that might contribute to
more reliability from the point of view of sqlite.

Thanks you all who helped out.


On Mon, Oct 19, 2009 at 12:45 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:
>>But, if I enter something in the field, and, instead of tabbing out of
>>it, if I click on the submit button, two events fire simultaneously.
>>There is the onblur() from the field (this is a SELECT query), and the
>>submit from the form (this is an UPDATE/INSERT query). The events
>>reach sqlite simultaneously, and it croaks.
>
> I seem to remember Javascript offers no guarantee about which event fires 
> first.  It's browser-dependent.  So I think, as you originally indicated, 
> this is to be avoided.
>
> Perhaps onBlur isn't the best event to listen for.  Maybe an onFocus handler 
> on the second field, would be more appropriate.
>
>
>>Mind it, I am not talking about Amazon.com here. But, even with a few
>>hundred users, someone is likely to hit the db at the same time
>>someone else is hitting it. How do you all manage this situation?
>
> I see the more knowledgeable among us have weighed-in.  For my $0.02, I'd say 
> make sure the busy_timeout is set appropriately.  Also, I have the impression 
> (on Windows, anyway), localhost seems more prone to this than a remote Apache 
> box.  ISTR you're a Mac user, so none of this may apply :-))
>
>
>
>
> - Original Message 
> From: P Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Mon, October 19, 2009 9:44:39 AM
> Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax
>
> Let me shed a bit more light on this (as I have understood). Top
> posting follows --
>
> What is happening is that I have an onblur() action on a field which
> sends off a request to check the validity of the entered text. Then,
> of course, I have the submit button which also sends off the entire
> form to the server for more work. If a user (me, when testing the
> darned thing) tabs away from the field with onblur(), all is well. The
> onblur() fires, the request is sent to the server, sqlite responds,
> all is well in the 20 acre woods.
>
> But, if I enter something in the field, and, instead of tabbing out of
> it, if I click on the submit button, two events fire simultaneously.
> There is the onblur() from the field (this is a SELECT query), and the
> submit from the form (this is an UPDATE/INSERT query). The events
> reach sqlite simultaneously, and it croaks.
>
> Now, here is the question -- in real life, the web server would
> receive many concurrent requests, not just the one described above.
> What happens in that case? If there is an UPDATE/INSERT request while
> a SELECT is happens to be happening, there is going to be a block, no?
> Putting logic in my code to keep retrying till a query (SELECT or
> UPDATE/INSERT) succeeds is going to be very messy. Does this make
> sqlite unsuitable for a web application?
>
> Mind it, I am not talking about Amazon.com here. But, even with a few
> hundred users, someone is likely to hit the db at the same time
> someone else is hitting it. How do you all manage this situation?
>
>
>
> On Sun, Oct 18, 2009 at 2:28 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:
>>>Ajax is always asynchronous. That is what the first "A" in Ajax is.
>>
>> Well, not quite always.  The open() method of the XMLHttpRequest object lets 
>> you specify syncronous, or async.  But, since you're using jQuery's 
>> wrappers, it's async.  Good choice.  Love jQuery.
>>
>>>I am not doing $dbh->disconnect. DBI is supposed to that automatically
>>>when the script finishes. Don't have any issues other than during
>>>these Ajax calls.
>>
>> I agree.  It should.  But I wonder if it takes more time for the forked Perl 
>> process to clean-up and close than you're expecting.  I never did figure out 
>> the exact cause and effect.  I just $dbh->disconnect (before printing the 
>> response where appropriate) and the issue usually seems to resolve itself.
>>
>>
>>
>>>I don't have any timeout set at all. Roger Binns suggestion a
>>>busy_timeout, but I am not sure how to even use that. I am using DBI.
>>>Where do I set sqlite busy_timeout? Besides, how would that help me?
>>
>> If you're currently set to a short value, lengthening it might help 
>> narrow-down the real culprit (or prove a timing issue).
>>
>> DBD-S

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[Clark Christensen]

>But, if I enter something in the field, and, instead of tabbing out of
>it, if I click on the submit button, two events fire simultaneously.
>There is the onblur() from the field (this is a SELECT query), and the
>submit from the form (this is an UPDATE/INSERT query). The events
>reach sqlite simultaneously, and it croaks.

I seem to remember Javascript offers no guarantee about which event fires 
first.  It's browser-dependent.  So I think, as you originally indicated, this 
is to be avoided.

Perhaps onBlur isn't the best event to listen for.  Maybe an onFocus handler on 
the second field, would be more appropriate.


>Mind it, I am not talking about Amazon.com here. But, even with a few
>hundred users, someone is likely to hit the db at the same time
>someone else is hitting it. How do you all manage this situation?

I see the more knowledgeable among us have weighed-in.  For my $0.02, I'd say 
make sure the busy_timeout is set appropriately.  Also, I have the impression 
(on Windows, anyway), localhost seems more prone to this than a remote Apache 
box.  ISTR you're a Mac user, so none of this may apply :-))




- Original Message 
From: P Kishor <punk.k...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, October 19, 2009 9:44:39 AM
Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax

Let me shed a bit more light on this (as I have understood). Top
posting follows --

What is happening is that I have an onblur() action on a field which
sends off a request to check the validity of the entered text. Then,
of course, I have the submit button which also sends off the entire
form to the server for more work. If a user (me, when testing the
darned thing) tabs away from the field with onblur(), all is well. The
onblur() fires, the request is sent to the server, sqlite responds,
all is well in the 20 acre woods.

But, if I enter something in the field, and, instead of tabbing out of
it, if I click on the submit button, two events fire simultaneously.
There is the onblur() from the field (this is a SELECT query), and the
submit from the form (this is an UPDATE/INSERT query). The events
reach sqlite simultaneously, and it croaks.

Now, here is the question -- in real life, the web server would
receive many concurrent requests, not just the one described above.
What happens in that case? If there is an UPDATE/INSERT request while
a SELECT is happens to be happening, there is going to be a block, no?
Putting logic in my code to keep retrying till a query (SELECT or
UPDATE/INSERT) succeeds is going to be very messy. Does this make
sqlite unsuitable for a web application?

Mind it, I am not talking about Amazon.com here. But, even with a few
hundred users, someone is likely to hit the db at the same time
someone else is hitting it. How do you all manage this situation?



On Sun, Oct 18, 2009 at 2:28 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:
>>Ajax is always asynchronous. That is what the first "A" in Ajax is.
>
> Well, not quite always.  The open() method of the XMLHttpRequest object lets 
> you specify syncronous, or async.  But, since you're using jQuery's wrappers, 
> it's async.  Good choice.  Love jQuery.
>
>>I am not doing $dbh->disconnect. DBI is supposed to that automatically
>>when the script finishes. Don't have any issues other than during
>>these Ajax calls.
>
> I agree.  It should.  But I wonder if it takes more time for the forked Perl 
> process to clean-up and close than you're expecting.  I never did figure out 
> the exact cause and effect.  I just $dbh->disconnect (before printing the 
> response where appropriate) and the issue usually seems to resolve itself.
>
>
>
>>I don't have any timeout set at all. Roger Binns suggestion a
>>busy_timeout, but I am not sure how to even use that. I am using DBI.
>>Where do I set sqlite busy_timeout? Besides, how would that help me?
>
> If you're currently set to a short value, lengthening it might help 
> narrow-down the real culprit (or prove a timing issue).
>
> DBD-SQLite adds this driver private method
>
>   $dbh->func(  'busy_timeout' );  # getter
>   $dbh->func( $ms, 'busy_timeout' );  # setter
>
> I seem to think it defaults to 30 seconds, but I don't see it documented now.
>
> If you move the second AJAX request from the onBlur event, to an
> explicit user click (button, link, etc), do you still get the locked
> DB?  If not, I think it would help prove it's a timing issue (or not).
>
>
>
> - Original Message 
> From: P Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Sat, October 17, 2009 8:07:09 PM
> Subject: Re: [sqlite] suggestions for avoiding "databas

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[P Kishor]

On Mon, Oct 19, 2009 at 12:13 PM, Keith Roberts <ke...@karsites.net> wrote:
> On Mon, 19 Oct 2009, P Kishor wrote:
>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> From: P Kishor <punk.k...@gmail.com>
>> Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax
>>
>> Let me shed a bit more light on this (as I have understood). Top
>> posting follows --
>>
>> What is happening is that I have an onblur() action on a field which
>> sends off a request to check the validity of the entered text. Then,
>> of course, I have the submit button which also sends off the entire
>> form to the server for more work. If a user (me, when testing the
>> darned thing) tabs away from the field with onblur(), all is well. The
>> onblur() fires, the request is sent to the server, sqlite responds,
>> all is well in the 20 acre woods.
>>
>> But, if I enter something in the field, and, instead of tabbing out of
>> it, if I click on the submit button, two events fire simultaneously.
>> There is the onblur() from the field (this is a SELECT query), and the
>> submit from the form (this is an UPDATE/INSERT query). The events
>> reach sqlite simultaneously, and it croaks.
>
> What about dumping the onblur event and using a seperate graphical check
> button for the user to send the SELECT query.
>
> So the user has to click the graphical button to send the SELECT query,
> using the onclick event?


Yes, that is the "Patient: 'Doctor, it hurts when I press here;'
Doctor: 'So, don't press there'" strategy. May have to resort to that.




>
> Kind Regards,
>
> Keith Roberts
>


>
>
>
>
>
>
>
>> Now, here is the question -- in real life, the web server would
>> receive many concurrent requests, not just the one described above.
>> What happens in that case? If there is an UPDATE/INSERT request while
>> a SELECT is happens to be happening, there is going to be a block, no?
>> Putting logic in my code to keep retrying till a query (SELECT or
>> UPDATE/INSERT) succeeds is going to be very messy. Does this make
>> sqlite unsuitable for a web application?
>>
>> Mind it, I am not talking about Amazon.com here. But, even with a few
>> hundred users, someone is likely to hit the db at the same time
>> someone else is hitting it. How do you all manage this situation?
>>
>>
>>
>> On Sun, Oct 18, 2009 at 2:28 PM, Clark Christensen <cdcmi...@yahoo.com>
>> wrote:
>>>>
>>>> Ajax is always asynchronous. That is what the first "A" in Ajax is.
>>>
>>> Well, not quite always.  The open() method of the XMLHttpRequest object
>>> lets you specify syncronous, or async.  But, since you're using jQuery's
>>> wrappers, it's async.  Good choice.  Love jQuery.
>>>
>>>> I am not doing $dbh->disconnect. DBI is supposed to that automatically
>>>> when the script finishes. Don't have any issues other than during
>>>> these Ajax calls.
>>>
>>> I agree.  It should.  But I wonder if it takes more time for the forked
>>> Perl process to clean-up and close than you're expecting.  I never did
>>> figure out the exact cause and effect.  I just $dbh->disconnect (before
>>> printing the response where appropriate) and the issue usually seems to
>>> resolve itself.
>>>
>>>
>>>
>>>> I don't have any timeout set at all. Roger Binns suggestion a
>>>> busy_timeout, but I am not sure how to even use that. I am using DBI.
>>>> Where do I set sqlite busy_timeout? Besides, how would that help me?
>>>
>>> If you're currently set to a short value, lengthening it might help
>>> narrow-down the real culprit (or prove a timing issue).
>>>
>>> DBD-SQLite adds this driver private method
>>>
>>>           $dbh->func(  'busy_timeout' );  # getter
>>>           $dbh->func( $ms, 'busy_timeout' );  # setter
>>>
>>> I seem to think it defaults to 30 seconds, but I don't see it documented
>>> now.
>>>
>>> If you move the second AJAX request from the onBlur event, to an
>>> explicit user click (button, link, etc), do you still get the locked
>>> DB?  If not, I think it would help prove it's a timing issue (or not).
>>>
>>>
>>>
>>> - Original Message 
>>> From: P Kishor <punk.k...@gmail.com>
>>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>>> Sent: Sat, Oc

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[Keith Roberts]

On Mon, 19 Oct 2009, P Kishor wrote:


To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
From: P Kishor <punk.k...@gmail.com>
Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax

Let me shed a bit more light on this (as I have understood). Top
posting follows --

What is happening is that I have an onblur() action on a field which
sends off a request to check the validity of the entered text. Then,
of course, I have the submit button which also sends off the entire
form to the server for more work. If a user (me, when testing the
darned thing) tabs away from the field with onblur(), all is well. The
onblur() fires, the request is sent to the server, sqlite responds,
all is well in the 20 acre woods.

But, if I enter something in the field, and, instead of tabbing out of
it, if I click on the submit button, two events fire simultaneously.
There is the onblur() from the field (this is a SELECT query), and the
submit from the form (this is an UPDATE/INSERT query). The events
reach sqlite simultaneously, and it croaks.


What about dumping the onblur event and using a seperate 
graphical check button for the user to send the SELECT 
query.


So the user has to click the graphical button to 
send the SELECT query, using the onclick event?


Kind Regards,

Keith Roberts

-
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-











Now, here is the question -- in real life, the web server would
receive many concurrent requests, not just the one described above.
What happens in that case? If there is an UPDATE/INSERT request while
a SELECT is happens to be happening, there is going to be a block, no?
Putting logic in my code to keep retrying till a query (SELECT or
UPDATE/INSERT) succeeds is going to be very messy. Does this make
sqlite unsuitable for a web application?

Mind it, I am not talking about Amazon.com here. But, even with a few
hundred users, someone is likely to hit the db at the same time
someone else is hitting it. How do you all manage this situation?



On Sun, Oct 18, 2009 at 2:28 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:

Ajax is always asynchronous. That is what the first "A" in Ajax is.


Well, not quite always.  The open() method of the XMLHttpRequest object lets 
you specify syncronous, or async.  But, since you're using jQuery's wrappers, 
it's async.  Good choice.  Love jQuery.


I am not doing $dbh->disconnect. DBI is supposed to that automatically
when the script finishes. Don't have any issues other than during
these Ajax calls.


I agree.  It should.  But I wonder if it takes more time for the forked Perl 
process to clean-up and close than you're expecting.  I never did figure out the 
exact cause and effect.  I just $dbh->disconnect (before printing the response 
where appropriate) and the issue usually seems to resolve itself.




I don't have any timeout set at all. Roger Binns suggestion a
busy_timeout, but I am not sure how to even use that. I am using DBI.
Where do I set sqlite busy_timeout? Besides, how would that help me?


If you're currently set to a short value, lengthening it might help narrow-down 
the real culprit (or prove a timing issue).

DBD-SQLite adds this driver private method

          $dbh->func(  'busy_timeout' );  # getter
          $dbh->func( $ms, 'busy_timeout' );  # setter

I seem to think it defaults to 30 seconds, but I don't see it documented now.

If you move the second AJAX request from the onBlur event, to an
explicit user click (button, link, etc), do you still get the locked
DB?  If not, I think it would help prove it's a timing issue (or not).



- Original Message 
From: P Kishor <punk.k...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Sat, October 17, 2009 8:07:09 PM
Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax

On Sat, Oct 17, 2009 at 7:16 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:

Sorry for top-posting...

What's running on the the server?  A Perl CGI script?  Apache HTTPD?  mod-perl?


Although I have Apache mod_perl installed, I am running a plain
vanilla Perl cgi script for now.



Is the AJAX exchange asyncronous?


Ajax is always asynchronous. That is what the first "A" in Ajax is.


Are you sure the first AJAX exchange is finished when the second one fires?


Am I sure? Not really. I guess it is not finished when the second one
fires, which is what causes the database lock, no?


Does the AJAX request wait for a 200 response?


I am using jQuery. It does what it does. I don't do anything special.



Assuming Perl, are you explicitly closing the DB with  $dbh-

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[P Kishor]

Let me shed a bit more light on this (as I have understood). Top
posting follows --

What is happening is that I have an onblur() action on a field which
sends off a request to check the validity of the entered text. Then,
of course, I have the submit button which also sends off the entire
form to the server for more work. If a user (me, when testing the
darned thing) tabs away from the field with onblur(), all is well. The
onblur() fires, the request is sent to the server, sqlite responds,
all is well in the 20 acre woods.

But, if I enter something in the field, and, instead of tabbing out of
it, if I click on the submit button, two events fire simultaneously.
There is the onblur() from the field (this is a SELECT query), and the
submit from the form (this is an UPDATE/INSERT query). The events
reach sqlite simultaneously, and it croaks.

Now, here is the question -- in real life, the web server would
receive many concurrent requests, not just the one described above.
What happens in that case? If there is an UPDATE/INSERT request while
a SELECT is happens to be happening, there is going to be a block, no?
Putting logic in my code to keep retrying till a query (SELECT or
UPDATE/INSERT) succeeds is going to be very messy. Does this make
sqlite unsuitable for a web application?

Mind it, I am not talking about Amazon.com here. But, even with a few
hundred users, someone is likely to hit the db at the same time
someone else is hitting it. How do you all manage this situation?



On Sun, Oct 18, 2009 at 2:28 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:
>>Ajax is always asynchronous. That is what the first "A" in Ajax is.
>
> Well, not quite always.  The open() method of the XMLHttpRequest object lets 
> you specify syncronous, or async.  But, since you're using jQuery's wrappers, 
> it's async.  Good choice.  Love jQuery.
>
>>I am not doing $dbh->disconnect. DBI is supposed to that automatically
>>when the script finishes. Don't have any issues other than during
>>these Ajax calls.
>
> I agree.  It should.  But I wonder if it takes more time for the forked Perl 
> process to clean-up and close than you're expecting.  I never did figure out 
> the exact cause and effect.  I just $dbh->disconnect (before printing the 
> response where appropriate) and the issue usually seems to resolve itself.
>
>
>
>>I don't have any timeout set at all. Roger Binns suggestion a
>>busy_timeout, but I am not sure how to even use that. I am using DBI.
>>Where do I set sqlite busy_timeout? Besides, how would that help me?
>
> If you're currently set to a short value, lengthening it might help 
> narrow-down the real culprit (or prove a timing issue).
>
> DBD-SQLite adds this driver private method
>
>           $dbh->func(  'busy_timeout' );  # getter
>           $dbh->func( $ms, 'busy_timeout' );  # setter
>
> I seem to think it defaults to 30 seconds, but I don't see it documented now.
>
> If you move the second AJAX request from the onBlur event, to an
> explicit user click (button, link, etc), do you still get the locked
> DB?  If not, I think it would help prove it's a timing issue (or not).
>
>
>
> - Original Message 
> From: P Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Sat, October 17, 2009 8:07:09 PM
> Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax
>
> On Sat, Oct 17, 2009 at 7:16 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:
>> Sorry for top-posting...
>>
>> What's running on the the server?  A Perl CGI script?  Apache HTTPD?  
>> mod-perl?
>
> Although I have Apache mod_perl installed, I am running a plain
> vanilla Perl cgi script for now.
>
>>
>> Is the AJAX exchange asyncronous?
>
> Ajax is always asynchronous. That is what the first "A" in Ajax is.
>
>> Are you sure the first AJAX exchange is finished when the second one fires?
>
> Am I sure? Not really. I guess it is not finished when the second one
> fires, which is what causes the database lock, no?
>
>> Does the AJAX request wait for a 200 response?
>
> I am using jQuery. It does what it does. I don't do anything special.
>
>>
>> Assuming Perl, are you explicitly closing the DB with  $dbh->disconnect (as 
>> opposed to $dbh = undef)?  Does the script end with an exit instruction?
>
> I am not doing $dbh->disconnect. DBI is supposed to that automatically
> when the script finishes. Don't have any issues other than during
> these Ajax calls.
>
>>  I'm guessing you're sure there's no writer or writers that jumped-in.
>
> I am sure no other process is interfering other than what I have
> specified. I am

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[Douglas E. Fajardo]

While the idea of uniquely identifying a user is interesting, the assumption 
that there is only one user for each IP address is questionable at best - 
Consider assigning a 'session id' of some kind instead. In *theory* (and yes, 
I've seen it happen in reality) the source address can change between two 
successive TCP connections with a multi-homed host.
*** Doug F.


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Keith Roberts
Sent: Saturday, October 17, 2009 7:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax

On Fri, 16 Oct 2009, Roger Binns wrote:

> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> From: Roger Binns <rog...@rogerbinns.com>
> Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> P Kishor wrote:
>> So, what suggestion might you all have for getting around this?
>
> Why not set a busy timeout?
>
> Roger

As each user's IP address is unique, you could use a LOCK 
column for the whole database, or a particular table.

You could then store the IP address of the user initiating 
the transaction.

When a transaction starts, you can use a trigger to test 
whether the LOCK column is NULL for empty.

If so, set the LOCK column to value of the users IP address.

If another user from a different IP address attempts a 
similar transaction, then block that transaction from 
continuing, until the LOCK on the other IP address has been 
removed.

Once the transaction has finished, release the LOCK by 
clearing the user's IP address from the LOCK column by 
setting it back to NULL.

I'm not sure how you would go about dealing with muliple 
transaction being stalled, and waiting to get the LOCK 
freed, so each one can continue.

Maybe muliple retries, until the user's can get access to 
the db?

Kind Regards,

Keith Roberts

-
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[Clark Christensen]

>Ajax is always asynchronous. That is what the first "A" in Ajax is.

Well, not quite always.  The open() method of the XMLHttpRequest object lets 
you specify syncronous, or async.  But, since you're using jQuery's wrappers, 
it's async.  Good choice.  Love jQuery.

>I am not doing $dbh->disconnect. DBI is supposed to that automatically
>when the script finishes. Don't have any issues other than during
>these Ajax calls.

I agree.  It should.  But I wonder if it takes more time for the forked Perl 
process to clean-up and close than you're expecting.  I never did figure out 
the exact cause and effect.  I just $dbh->disconnect (before printing the 
response where appropriate) and the issue usually seems to resolve itself.



>I don't have any timeout set at all. Roger Binns suggestion a
>busy_timeout, but I am not sure how to even use that. I am using DBI.
>Where do I set sqlite busy_timeout? Besides, how would that help me?

If you're currently set to a short value, lengthening it might help narrow-down 
the real culprit (or prove a timing issue).

DBD-SQLite adds this driver private method

   $dbh->func(  'busy_timeout' );  # getter
   $dbh->func( $ms, 'busy_timeout' );  # setter

I seem to think it defaults to 30 seconds, but I don't see it documented now.

If you move the second AJAX request from the onBlur event, to an
explicit user click (button, link, etc), do you still get the locked
DB?  If not, I think it would help prove it's a timing issue (or not).



- Original Message 
From: P Kishor <punk.k...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Sat, October 17, 2009 8:07:09 PM
Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax

On Sat, Oct 17, 2009 at 7:16 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:
> Sorry for top-posting...
>
> What's running on the the server?  A Perl CGI script?  Apache HTTPD?  
> mod-perl?

Although I have Apache mod_perl installed, I am running a plain
vanilla Perl cgi script for now.

>
> Is the AJAX exchange asyncronous?

Ajax is always asynchronous. That is what the first "A" in Ajax is.

> Are you sure the first AJAX exchange is finished when the second one fires?

Am I sure? Not really. I guess it is not finished when the second one
fires, which is what causes the database lock, no?

> Does the AJAX request wait for a 200 response?

I am using jQuery. It does what it does. I don't do anything special.

>
> Assuming Perl, are you explicitly closing the DB with  $dbh->disconnect (as 
> opposed to $dbh = undef)?  Does the script end with an exit instruction?

I am not doing $dbh->disconnect. DBI is supposed to that automatically
when the script finishes. Don't have any issues other than during
these Ajax calls.

>  I'm guessing you're sure there's no writer or writers that jumped-in.

I am sure no other process is interfering other than what I have
specified. I am sure because I am the only one using my laptop on
which I am doing the development. :-)


> Are you doing anything like modifying the default 30 second busy_timeout?  
> Perhaps to too short a timeout?

I don't have any timeout set at all. Roger Binns suggestion a
busy_timeout, but I am not sure how to even use that. I am using DBI.
Where do I set sqlite busy_timeout? Besides, how would that help me?

>
> Sometimes it's useful to $dbh->disconnect before you print the response.
>
> I've seen this happen from time to time in my own environment.  It's annoying 
> as hell.   It seems to always come down to when and how I disconnect from the 
> DB file.
>
> I seldom see it in regular CGI scripts.  More often, I see it with 
> mod_perl::PerlRun (I don't use mod_perl::Registry) when I open the $dbh in a 
> module where $dbh is exported into the main package.

No, as I described, what I think is happening is that two Ajax events
are hitting the db at the nearly the same time. The first one is a
select (checking the db if the user exists), and the second is an
insert (creating a user because the user didn't exist). Even though
Javascript has gotten the response from the first event, and started
on the second process. sqlite is still busy from the first process, so
throws a lockout.

Yes, it is very annoying. I have to figure out some other shenanigans
to prevent this from happening.

>
>  -Clark
>
>
>
> - Original Message 
> From: P Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Fri, October 16, 2009 12:53:28 PM
> Subject: [sqlite] suggestions for avoiding "database locked" on ajax
>
> Yes, I know, if it "hurts when I press here," then I shouldn't "press
> here," but, so it goes...
>
> I have an account creation fo

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[P Kishor]

On Sat, Oct 17, 2009 at 7:16 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:
> Sorry for top-posting...
>
> What's running on the the server?  A Perl CGI script?  Apache HTTPD?  
> mod-perl?

Although I have Apache mod_perl installed, I am running a plain
vanilla Perl cgi script for now.

>
> Is the AJAX exchange asyncronous?

Ajax is always asynchronous. That is what the first "A" in Ajax is.

> Are you sure the first AJAX exchange is finished when the second one fires?

Am I sure? Not really. I guess it is not finished when the second one
fires, which is what causes the database lock, no?

> Does the AJAX request wait for a 200 response?

I am using jQuery. It does what it does. I don't do anything special.

>
> Assuming Perl, are you explicitly closing the DB with  $dbh->disconnect (as 
> opposed to $dbh = undef)?  Does the script end with an exit instruction?

I am not doing $dbh->disconnect. DBI is supposed to that automatically
when the script finishes. Don't have any issues other than during
these Ajax calls.

>  I'm guessing you're sure there's no writer or writers that jumped-in.

I am sure no other process is interfering other than what I have
specified. I am sure because I am the only one using my laptop on
which I am doing the development. :-)


> Are you doing anything like modifying the default 30 second busy_timeout?  
>Perhaps to too short a timeout?

I don't have any timeout set at all. Roger Binns suggestion a
busy_timeout, but I am not sure how to even use that. I am using DBI.
Where do I set sqlite busy_timeout? Besides, how would that help me?

>
> Sometimes it's useful to $dbh->disconnect before you print the response.
>
> I've seen this happen from time to time in my own environment.  It's annoying 
> as hell.   It seems to always come down to when and how I disconnect from the 
> DB file.
>
> I seldom see it in regular CGI scripts.  More often, I see it with 
> mod_perl::PerlRun (I don't use mod_perl::Registry) when I open the $dbh in a 
> module where $dbh is exported into the main package.

No, as I described, what I think is happening is that two Ajax events
are hitting the db at the nearly the same time. The first one is a
select (checking the db if the user exists), and the second is an
insert (creating a user because the user didn't exist). Even though
Javascript has gotten the response from the first event, and started
on the second process. sqlite is still busy from the first process, so
throws a lockout.

Yes, it is very annoying. I have to figure out some other shenanigans
to prevent this from happening.

>
>  -Clark
>
>
>
> - Original Message 
> From: P Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Fri, October 16, 2009 12:53:28 PM
> Subject: [sqlite] suggestions for avoiding "database locked" on ajax
>
> Yes, I know, if it "hurts when I press here," then I shouldn't "press
> here," but, so it goes...
>
> I have an account creation form -- users enter their email and their
> desired username, and the form --
>
> onblur from the username field, sends off an ajax request to see if
> the desired username already exists in the db, and if yes, it sends
> back a suggestion;
>
> onblur from the email field, sends off an ajax request to see if the
> email already exists in the db, and if yes...
>
> If neither the username nor the email exist in the db, then the
> application creates a record and informs the user of success. Except,
> the previous ajax request (I am assuming it is the previous ajax
> request from onblur event from the email field) has locked the
> database, and the app returns an error that "the database is locked."
>
> So, what suggestion might you all have for getting around this?
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[Clark Christensen]

Sorry for top-posting...

What's running on the the server?  A Perl CGI script?  Apache HTTPD?  mod-perl?

Is the AJAX exchange asyncronous?  Are you sure the first AJAX exchange is 
finished when the second one fires?  Does the AJAX request wait for a 200 
response?

Assuming Perl, are you explicitly closing the DB with  $dbh->disconnect (as 
opposed to $dbh = undef)?  Does the script end with an exit instruction?  I'm 
guessing you're sure there's no writer or writers that jumped-in.  Are you 
doing anything like modifying the default 30 second busy_timeout?  Perhaps to 
too short a timeout?

Sometimes it's useful to $dbh->disconnect before you print the response.

I've seen this happen from time to time in my own environment.  It's annoying 
as hell.   It seems to always come down to when and how I disconnect from the 
DB file.  

I seldom see it in regular CGI scripts.  More often, I see it with 
mod_perl::PerlRun (I don't use mod_perl::Registry) when I open the $dbh in a 
module where $dbh is exported into the main package.

 -Clark



- Original Message 
From: P Kishor <punk.k...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Fri, October 16, 2009 12:53:28 PM
Subject: [sqlite] suggestions for avoiding "database locked" on ajax

Yes, I know, if it "hurts when I press here," then I shouldn't "press
here," but, so it goes...

I have an account creation form -- users enter their email and their
desired username, and the form --

onblur from the username field, sends off an ajax request to see if
the desired username already exists in the db, and if yes, it sends
back a suggestion;

onblur from the email field, sends off an ajax request to see if the
email already exists in the db, and if yes...

If neither the username nor the email exist in the db, then the
application creates a record and informs the user of success. Except,
the previous ajax request (I am assuming it is the previous ajax
request from onblur event from the email field) has locked the
database, and the app returns an error that "the database is locked."

So, what suggestion might you all have for getting around this?



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
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] suggestions for avoiding "database locked" on ajax

[Keith Roberts]

On Fri, 16 Oct 2009, Roger Binns wrote:

> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> From: Roger Binns <rog...@rogerbinns.com>
> Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> P Kishor wrote:
>> So, what suggestion might you all have for getting around this?
>
> Why not set a busy timeout?
>
> Roger

As each user's IP address is unique, you could use a LOCK 
column for the whole database, or a particular table.

You could then store the IP address of the user initiating 
the transaction.

When a transaction starts, you can use a trigger to test 
whether the LOCK column is NULL for empty.

If so, set the LOCK column to value of the users IP address.

If another user from a different IP address attempts a 
similar transaction, then block that transaction from 
continuing, until the LOCK on the other IP address has been 
removed.

Once the transaction has finished, release the LOCK by 
clearing the user's IP address from the LOCK column by 
setting it back to NULL.

I'm not sure how you would go about dealing with muliple 
transaction being stalled, and waiting to get the LOCK 
freed, so each one can continue.

Maybe muliple retries, until the user's can get access to 
the db?

Kind Regards,

Keith Roberts

-
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

P Kishor wrote:
> So, what suggestion might you all have for getting around this?

Why not set a busy timeout?

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

iEYEARECAAYFAkrY1T8ACgkQmOOfHg372QTfogCdGrcPbCZKxKZ39adEwGjE4H9V
3H0AoNnAXRmgmF9Lv//9cpaFQnkM/fTE
=EcPJ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] suggestions for avoiding "database locked" on ajax

[P Kishor]

On Fri, Oct 16, 2009 at 3:09 PM, Stephan Wehner  wrote:
> On Fri, Oct 16, 2009 at 12:58 PM, Simon Slavin
>  wrote:
>>
>> On 16 Oct 2009, at 8:53pm, P Kishor wrote:
>>
>>> If neither the username nor the email exist in the db, then the
>>> application creates a record and informs the user of success. Except,
>>> the previous ajax request (I am assuming it is the previous ajax
>>> request from onblur event from the email field) has locked the
>>> database, and the app returns an error that "the database is locked."
>>>
>>> So, what suggestion might you all have for getting around this?
>>
>> You're using AJAX ?  That means JavaScript.  Put a 3 second pause into
>> the routine that returns the query about the email field.  3 seconds
>> should be enough to get the database unlocked.
>
> What if two users sign up at the same time? Javascript surely wouldn't help.
>


Right. Which is why I am hoping for some sure-shot way of avoiding
such a race condition.





-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users