DB handle errors when forking 1

[Xaqte]

I'm trying to use Parallel::ForkManager to fork off multiple process to perform database queries/updates.

The code snippet:

my $pm = new Parallel::ForkManager($result->rows);
while ( my ( $id, $scheduled, $lastgenerationdate ) =
    $result->fetchrow_array() )
{
    my $pid = $pm->start and next; 
    &process_templates($id, $scheduled, $lastgenerationdate, $dbh, $datetime, $pid, $cgi, $config);
    $pm->finish; 
}

As you can guess the process_templates subroutine is the one that handles the db functionality needed.

Using the above code, I get the following error:

DBD::mysql::db prepare failed: handle 2 is owned by thread 223eec not current th
read 210b014 (handles can't be shared between threads and your driver may need a
 CLONE method added) at ../cgi-bin/lib/RTH_Template.pm line 251.

Now, I did find some mention of the "clone" method:

http://search.cpan.org/~timb/DBI-1.53/DBI.pm#clone

However, I'm a little unsure of the usage.

I did try:

my $pm = new Parallel::ForkManager($result->rows);
while ( my ( $id, $scheduled, $lastgenerationdate ) =
    $result->fetchrow_array() )
{
    my $pid = $pm->start and next; 
    my $newdbh = $dbh->clone();
    &process_templates($id, $scheduled, $lastgenerationdate, $newdbh, $datetime, $pid, $cgi, $config);
    $newdbh->disconnect();
    $pm->finish; 
}

But, this results in a different error:

DBD::mysql::db clone failed: handle 2 is owned by thread 223eec not current thre
ad 210ba64 (handles can't be shared between threads and your driver may need a C
LONE method added) at C:\dev\executor.pl line 81.

Any thoughts/expierences will greatly be appreciated!

X

 

[mlibeson]

DB is not thread safe. You should not use forking in the middle of a DB connection. Fork the process which will connect to the DB, run queries, and then disconnect. It is recommended that you do not for at all when connecting to a DB, but if you really want to, make sure not to split the process up. You will need to create a new connection for each forked process.


Michael Libeson

 

[Xaqte]

You will need to create a new connection for each forked process.

Thanks for the confirmation, this is what I had started doing in the meantime.

I must ask, how is the "clone" method used?

I ask this for benchmarking purposes as this will receive very heavy usage. If someone can provide me a small sample of how this method is to be used it would be greatly appreciated!

Thanks,
X

 

[mlibeson]

The "Clone" makes a replica of the environment. When you create the db connection outside the forking process it clones the reference and not the procedure of creating the connection. What happens is that you wind up making several query calls to the same db connection handle and that is where it breaks and that is why you have to place the connection routine within the forking process.


Michael Libeson

 

[Xaqte]

Thanks for the explanation!