Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Re: SQLite with mod_perl

by Corion (Pope)
on Oct 02, 2010 at 23:21 UTC ( #863121=note: print w/replies, xml ) Need Help??

in reply to SQLite with mod_perl

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

Ideally, each HTTP request would query your database and if it writes, lock the database while updating and otherwise use no locks at all. I think you should create your database handle at server startup and then reuse that handle in each request. I'm not sure where you have problems, but as you haven't posted any code, it's hard to diagnose.

Replies are listed 'Best First'.
Re^2: SQLite with mod_perl
by james2vegas (Chaplain) on Oct 03, 2010 at 02:22 UTC
    Re-using an open database handle would be bad advice if it is a forking server (see the last sentence in this SQLite FAQ answer), perhaps using something like DBIx::Connecter (like Apache::DBI in the mod_perl world) might be helpful to manage the database connections properly.
Re^2: SQLite with mod_perl
by punkish (Priest) on Oct 03, 2010 at 02:19 UTC
    > 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
      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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://863121]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (8)
As of 2018-07-19 15:51 GMT
Find Nodes?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?

    Results (411 votes). Check out past polls.