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