http://www.perlmonks.org?node_id=863127


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

Replies are listed 'Best First'.
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;

    [download]
[reply]
[d/l]
      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>
    }

      [download]

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

          PerlModule Apache::DBI

      [download]

      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>

      [download]

      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
[reply]
[d/l]
[select]
        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 });

        [download]
        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.
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom