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

I have created 5000 child process from a parent. Made a global database connection. Every child process will share that db connection. Here SIGALARM is handled. If the alarm is generated before the sleep(10000) by manually. The prepared DB query will be executed simultaneously. While passing the signal to the child process. 5000 rows are updated successfully. but it has thrown following error message as

message type 0x5a arrived from server while idle
message type 0x43 arrived from server while idle
message type 0x5a arrived from server while idle

I am using DBI module to connect with database. I wanted to know whether DBI using thread?...

For your reference:- 
#!/usr/bin/perl 

use strict;
use warnings;
use DBI;
use Data::Dumper;


if( $#ARGV < 0 ) {
        die "no sufficient argument.\n";
}
my $fork_count = shift;
#print STDERR $local_time."\n";
#print STDERR $execute_time."\n";

my $user="zivah";
my $password="zivah";
my $database="zivah";
my $schema="public";
my $server="192.168.1.40";
my $port="5432";

my $logfile="Log.txt";
my $LOG_FILE;
open ($LOG_FILE, ">$logfile") or die "Can't open the file, $!!\n";

$SIG{"ALRM"} = \&handler;

sub handler() {
        my $handle=$_[0];
        my $time=localtime;

        if($handle eq "ALRM") {
                print $LOG_FILE "SIGNAL SIG$handle is generated on $ti
+me -- $$\n";
                &Fork_Execute();
        }
}
my $Database_handler;
my $sth;

sub Database_Connect {
        # connect with database.
        #print "Connecting Database\n";
        unless( $Database_handler = DBI->connect("dbi:Pg:database=".$d
+atabase.";host=$server;port=$port", $user, $password,  { RaiseError =
+> 0, AutoCommit => 1, PrintError => 1, InactiveDestroy => 1, pg_serve
+r_prepare => 0}) ) {
                # Print an error message for unsuccessfull connection,
                print "EMERG: connect_database : connecting database f
+ailed, $DBI::errstr" ;
                # Return -1.
                return -1 ;
        }
        # Return the database handler.
        return $Database_handler;
}
sub Fork_Prepare {

        my ($result, $query, $return_value, $delete_time, @tables);
        # Prepare sql queries for finding unwanted tables.
        #$query="INSERT INTO $schema.a(name) values('santhosh');";
        my $id=shift;
        $query="Update $schema.a SET name='single',mtime=now() where n
+o=$id;";
 unless($sth = $Database_handler->prepare($query)){
                # print an error message.
                print "ERROR:can't prepare SQL statement:[ $query ]:[$
+DBI::errstr]";
                # return -1.
                return -1;
        }

}

sub Fork_Execute {
        my ($result);
        unless($result = $sth->execute()){
                # Print an error message
                print "ERROR:can't execute SQL statement::[$DBI::errst
+r]";
                return -1;
        }

}
&Database_Connect();

my $fc=1;
my $cnt=1;
my @pids;
system("date");
while( $fork_count >= $fc ) {
        my $pid = fork();
        if (not defined $pid) {
                print "resources not avilable.\n";
        } elsif ($pid == 0) {
                &Fork_Prepare($cnt);
                #&Database_Connect();
                sleep(1000);
                exit;
        } else {
                $fc++;
                $cnt++;
                next;
        }
}
system("date");

[download]

Replies are listed 'Best First'.
Re: DB update issue
by Gangabass (Vicar) on Oct 08, 2010 at 11:01 UTC
[reply]
Re: DB update issue
by erix (Prior) on Oct 08, 2010 at 13:31 UTC
[reply]
Re: DB update issue
by locked_user sundialsvc4 (Abbot) on Oct 08, 2010 at 15:33 UTC

    “The bottom line™” of that post, of course, being:   Not a bug. Using a connection from more than one thread is not allowed.

    I hope that your notion of “create 5,000 threads” was just a hypothetical experiment.   Or perhaps a “worst-case load” test.

    Surely it was not something that you really contemplated doing ...

    On the assumption that this surely must (not) be your intention, let me make a parenthetical comment. . . not addressed to “you.”

    I shudder at the number of well-intentioned programmers who imagine that “creating hundreds or thousands of threads” multiplies the computer’s time, instead of doing what it actually does; namely, “chop the computer’s time into useless confetti and then shove that confetti up the poor computer's :-O.”   (Ahem...)

    It also matters to consider what is happening over on the server side.   The server is being pounded with requests coming against a single table ... and, let’s face it, only a very few of those requests can be serviced at one time because they will naturally contend with one another for use of the common resource ... the table, and a rapidly spinning piece of metal.   All of the rest is just “plugging up the system.”

    All of us in computer school learned about “thrashing.”   Well, server programs can “thrash” in a great many ways, not just those related to virtual memory (or the lack thereof).   Any accumulation of detrius, beyond the server program’s actual capacity to handle it, causes throughput rates to plummet. And, as with classic virtual-memory thrashing, the decline is exponential, not linear.   (So, the result is not merely “bad.”   It is catastrophic.)   Even if the host computer is busy-as-a-bee and with no excessive paging at all, a server program can be, well, constipated!   We have demanded that it do what it physically cannot, and (in the case of a great many servers) it “dies, trying.”

    This point was hammered-home to me early, when our school’s engineering department was teaching students using an expensive and resource-hungry program on a too-small (mainframe) computer.   The students all tried to run the program at their terminals:   the computer all but dropped dead, finishing the jobs (if at all) in as long as eleven hours.   (But if no one else was using it, the runs completed in about two minutes.)   The little light went on.   I arranged things so that all of the runs were done in batch mode, and I dedicated a special batch queue to those jobs, with adequate resources to run the program and a queue-limit of three, having calculated that the computer could reliably complete jobs in about four minutes each at that MPL.   “This queue,” I assured them, “is exclusively yours.”   Even if 30 students submitted their jobs at exactly the same instant (and of course, they never would), I could guarantee that they would all get their results in less than an hour.   Typically throughout the semester it was about five minutes.   (I checked.)

    Less ... is more.

    Don’t guess ... measure it.   When in doubt, stick a meter on it.

Back to Seekers of Perl Wisdom