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