Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Re^2: SQLite with mod_perl

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

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

> How / why does your first query create a lock on
> the database that is never released?

The application is loaded when server starts via Plack::Handler::Apache2. As soon as I query, a lock a placed on the entire db (SQLite doesn't have a row-level lock), and never let go. Any subsequent query, as long as it is a SELECT, works, but any other query that UPDATEs or DELETEs fails.

>but as you haven't posted any code, it's hard to diagnose

I don't know what code to post, in the sense that it is my entire application. Of course, there is no problem in using SQLite if use cgi, but I really do want to move to a persistent environment.

Other than the above problem, SQLite is really brilliant. I can't quite imagine anything (CouchDB, MongoDB, or any other new fangled data storage technologies) quite matching what it can do.


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

Comment on Re^2: SQLite with mod_perl
Re^3: SQLite with mod_perl
by james2vegas (Chaplain) on Oct 03, 2010 at 02:42 UTC
    What error are you getting? When I run code like this twice, I don't get errors, I just get waiting until the other transaction is committed:
    use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=test.sqlite","","", { RaiseError => 1, AutoCommit => 1 }); $dbh->begin_work; $dbh->do("UPDATE abc SET foo = 'heyo'"); my @x = <>; $dbh->commit;
      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/ </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
        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.

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (8)
As of 2014-11-27 18:55 GMT
Find Nodes?
    Voting Booth?

    My preferred Perl binaries come from:

    Results (187 votes), past polls