Thank you so much!

That apparently was the problem after a few quick tests. I updated my test script to the below (each fork() creates its own db handle), and letting it run for ~5 minutes, there are no issues. I'll apply similar logic to my real app and see how it goes, then I'll mark this thread solved if the problem vanishes.

I was looking in all the wrong places for information. I should have done more than just skimmed the docs. sigh.

use warnings;
use strict;

use Parallel::ForkManager;
use DBI;

my $db_file = 'test.db';

my $dbh = DBI->connect(
    "dbi:SQLite:dbname=$db_file",
    "",
    "",
    {RaiseError => 1}
) or die $DBI::errstr;

my $pm = Parallel::ForkManager->new(20);

for (1..2){
    $pm->start and next;
    my $interval = $_ / 5;

    my $dbh = DBI->connect(
        "dbi:SQLite:dbname=$db_file",
        "",
        "",
        {RaiseError => 1}
    ) or die $DBI::errstr;

    while (1){
        dbwrite($dbh, $interval);
        #sleep 1;
    }
}

while(1){
    my $sth = $dbh->prepare(
        "SELECT * FROM test WHERE id=?;"
    );
    $sth->execute('test');
    my $val = $sth->fetchrow_hashref()->{value};
    print "$val\n";
}

sub dbwrite {
    my ($dbh, $interval) = @_;
    my $sth = $dbh->prepare(
        'UPDATE test SET value=? WHERE id="test";'
    );
    $sth->execute($interval);
};
[download]

update: I did trigger the below error after some time, but that's a different problem that it appears as though there are built-in workarounds for that I'll play with (mind you, it's probably triggered because we're write/read to the db at full speed):

DBD::SQLite::st execute failed: database is locked at pm.pl line 49.
[download]

/update

Thanks davido, I missed that in the docs. I'll open new db handles within the fork(s), and see how that plays out.

Hey Dave,

I just want to say that having each process use its own DB handle seemed to have corrected my issues.

The way this particular app works is there's a core API class (OO) at its centre. There's also a DB class (OO) that manages all of the database work (it creates the actual DB handle in the new() call). The DB handle gets inserted into the DB object. The API is the only thing that talks to the DB object, so the DB object is inserted into the API object.

So, what I've done is this:

• Dancer2 app library creates a new API object on instantiation, and this is what the Dancer2 routes use to make calls to and get work done
• when the API object is first instantiated, it spins up two events that run in separate processes. When these event objects are created, they are passed in a *copy* of the API object (no sharing between procs)
• the event objects then instantiate their own DB object (which contains a DB handle internally), and inserts that DB object into the API object, overwriting the object that was created during the Dancer2 initial instantiation
• now the Dancer2 process has its own copy of the API with it's own DB object (and therefore its own DB handle), and each event has the same configuration

I know it's not very elegant at the moment, but I have quite a good test suite, so after some more testing, I'll be able to clean it up significantly now that I know what the issue was.

I appreciate the feedback in helping me get this resolved.

ps. The app has been running for 19+ hours as of now with very consistent memory footprint and CPU usage, all of my timers work properly in a real run, and it seems very stable.