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
