Access the same database in two processes

sachaer has asked for the wisdom of the Perl Monks concerning the following question:

I have a perl program in which I need to fork a child process to do something else. In both the parent process and the child process, there are database interactions. I have a perl module in which the database connection is initiallized. But I don't have disconnection in the same module. I rely on the calling programm to automatically disconnect when the calling program terminates. It works perfect without child process. But with a child process created in the middle of the perl program and exits before the parent process. I have trouble with the database connections. As when the child process terminates, it seems disconnect the database handler therefore the parent process isn't able to continue its job with the database. Here is the program:

--------------------------- mail program --------------
myquery ('3933'); #this is parent process
        
$| = 1; # turn off buffering
$newpid = fork();
if ( not defined $newpid ){
     # if return value of fork() is undef, something went wrong
     die "fork didn't work: $!\n";
}
elsif ( $newpid == 0 ){
     # This is what the child process does
     print "inside child---------\n";
     myquery ('3934');    
     exit( 0 );

}
    
myquery ('3932');

----------------one subroutine---------------------
sub myquery {
    
    my ($target_member_id) = @_;
    my ($query) = "select username from member where id=$target_member
+_id";
    my ($sth) = $mypackage::dbh->prepare ($query);
    $sth-> execute;
    my ($row) = $sth->fetchrow_array;
    if($row) {
        print "row: $row\n";
    }
    else {
        print "empty\n";
    }
    $sth->finish;
}
 ---------------the perl module to establish the db connection ------
package mypackage;
use DBI;

$dbh = DBI->connect("$DB_SERVER:$DB_NAME;$DB_HOST",$DB_USERNAME,$DB_PA
+SSWORD) || die "Content-type: text/html\n\nCan't connect to 
$dbname: $DBI::errstr\n";
...
...
[download]

Thanks.

Comment on Access the same database in two processes Download Code

Replies are listed 'Best First'.
Re: Access the same database in two processes by lestrrat (Deacon) on Oct 18, 2002 at 00:59 UTC
I don't recommend sharing the database handle like that. Think about it, if two sets of instructions are going through the same handle, which one gets to go first? Would they even be valid instructions? One thing to note is, even if you don't share the same database connection between the parent and the child, you need to be careful of the self-clean up that the database drivers perform : `my $parent_dbh = DBI->connect( ... ); if( my $pid = fork() ) { # do stuff... # >>> $parent_dbh may be cleaned up here, # >>> because it is in the child's scope exit 0; }` [download] You could do this: `# fork first if( my $pid = fork() ) { # do stuff, including opening new DBI connection exit 0; } my $parent_dbh = DBI->connect(...); # do parent stuff` [download] or, if you really need to open the connection in the parent beforehand, `my $parent_dbh = DBI->connect(...); $parent_dbh->{ InactiveDestroy } = 0; if(my $pid = fork()) { # do stuff }` [download] Incidentally, setting InactiveDestroy may actually fix your problem, but I still would STRONGLY advise you not to share the same handle between the parent and the child	[reply] [d/l] [select]
Re: Access the same database in two processes by adrianh (Chancellor) on Oct 17, 2002 at 23:41 UTC
The DB connection is shared between the processes. Hence, when the child closes the connection the parent will lose it too. In general having two processes trying to use the same database handle will cause nasty things to happen. The only solution is to not do it :-) You'll need to open a separate database handle for the child process.	[reply]