Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re^4: SQLite with mod_perl

by punkish (Priest)
on Oct 09, 2010 at 02:12 UTC ( #864309=note: print w/ replies, xml ) Need Help??


in reply to Re^3: SQLite with mod_perl
in thread SQLite with mod_perl

Hi james2vegas, sorry for the delay in reply.

Well, I thought I had surmounted this problem, but no, it is back with a vengeance. Here is what is going on in a single web action --

SELECT col1 FROM table WHERE condition; if (col1 exists) { UPDATE table SET col2 = <new value> }

Well, I get the "db is locked" error at the UPDATE statement. Now, in my httpd.conf I started using

PerlModule Apache::DBI

and I thought my troubles were over. Except, this lock problem has started rearing up again. I have tried a few other things, such as starting every db action with $dbh->begin_work and ending with $dbh->commit, but I am already using AutoCommit => 1 so I am not sure if that even has any effect. I have even tried sqlite_use_immediate_transaction => 1, but that doesn't help either.

My application is being loaded in Apache conf file with

<Location /> SetHandler perl-script PerlHandler Plack::Handler::Apache2 PerlSetVar psgi_app /path/to/application.pl </Location>

So, I want all the perl modules to be loaded when Apache2 starts, and then a $dbh created for each user when the user comes to the web site, but not recreated for the same user on every reload. I am assuming that would be the correct way to work speedily.

--

when small people start casting long shadows, it is time to go to bed


Comment on Re^4: SQLite with mod_perl
Select or Download Code
Re^5: SQLite with mod_perl
by james2vegas (Chaplain) on Oct 09, 2010 at 09:27 UTC
    Do not cache your database handles, rather let Apache::DBI take care of it, use Apache::DBI in your mod_perl startup script (or use PerlModule Apache::DBI in your httpd.conf) then whenever you need to open a database connection, do it as would normally my $dbh = DBI->connect(...). If you need connections per user add private_username => $username to your connection attributes, like so:
    my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", "", "", { private_ +username => $username });
    This would ensure that each user would get a per-process database handle. Note that you can't share database handles across processes unless you use something single process like FastCGI.
      Hmmm... as I mentioned in my post above, I am indeed using  PerlModule Apache::DBI. Maybe I tinkered above (too smart for my own good) trying to cache the db handle. Now I am trying with just plain my $dbh = ... , and things seem to be working.

      One other thing I have done now -- I have turned off transactions. My db handle is now created with AutoCommit => 1.

      This is really unreliable. I have no idea what caused my app to start puking and croaking yesterday, and what caused it to start working today. I have become too dependent on SQLite (and for good reason -- it is a great db store), and I really don't know of any alternatives. Any suggestions? I could try some of the key-value stores such as MongoDB/CouchDB, etc., but it would require learning a whole new paradigm, and writing new code for retrieving values (is there a DBI interface to a key-value store? **) While I do use PostGres for the bigger datasets, SQLite seems perfect for serving as the Session store, and user accounts and such.

      Update: Hot damn! DBD::DBM

      --

      when small people start casting long shadows, it is time to go to bed

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://864309]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (8)
As of 2015-07-07 00:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (85 votes), past polls