Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

SQLite with mod_perl

by punkish (Priest)
on Oct 02, 2010 at 20:11 UTC ( #863104=perlquestion: print w/ replies, xml ) Need Help??
punkish has asked for the wisdom of the Perl Monks concerning the following question:

I have been successfully using SQLite as a data store for my web applications, but now I am implementing a web site with mod_perl, and am running into database locking issues.

As expected, my entire web application is loaded by the Plack Apache handler (Plack::Handler::Apache2) when the web server is started. Well, the first db query creates a lock on the entire database, and any subsequent query that has to modify the db fails. Disconnecting the dbh is not an answer, as subsequent web queries will fail.

What is my way out? Can I use SQLite in a persistent web environment or not? Should I be looking for some other db store?

I am not a fan of MySQL, and don't want to use it. I could potentially use PostGres, but I'd rather use something lightweight, and preferably sql-based as using key/value databases such as Tokyo Cabinet would require learning a whole new way. I'd rather really use SQLite.

--

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

Comment on SQLite with mod_perl
Re: SQLite with mod_perl
by Corion (Pope) on Oct 02, 2010 at 23:21 UTC

    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.

      > 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;
      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: SQLite with mod_perl
by morgon (Deacon) on Oct 03, 2010 at 00:36 UTC
    I don't have real experience with SQLite but I think(I may be wrong here) that it does not support concurrent access by 2 or more processes.

    This is why I think it would be a poor choice in a web-application as it severly limits scalability.

    As for the locks, you should ensure that your application either does a commit or a rollback on the database for every http-request it handles (if it touches the database at all).

    This ensures that your applications leaves no locks in the database between requests.

      According to this question in the SQLite faq, "Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however." If you aren't using the AutoCommit mode and you have an uncommitted transaction (which would lock the database), that may be your problem, so either commit soon after making an update or set AutoCommit on.

      Alternatively, you may have kept the same database handle open across a fork call, which this mentions as the source of problems, so don't do that either.
        Alternatively, you may have kept the same database handle open across a fork call, which this mentions as the source of problems, so don't do that either.
        So, this may partly be just my ignorance. I am assuming that Apache2 is the forking server here. After all, it spawns little children to manage the web queries.

        I am not sure how things work under a mod_perl (or even a fastcgi) kind of persistent perl environment under Apache2. I am assuming that my entire application is compiled and loaded when the server starts. At this point, a db handle is not created. Then, a user queries, which causes a db handle to be created. Since I don't want a new db handle created for subsequent queries by the same user, I reuse the db handle. And, I don't disconnect the db handle, because, if I do, then subsequent queries will fail. How do I ensure that a persistent, unique db handle is created for each user to my web site?

        --

        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: perlquestion [id://863104]
Approved by planetscape
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2014-08-30 01:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (291 votes), past polls