in reply to multiprocessing in perl

Something as simple as this could be all you need (untested): 

use threads;
use threads::shared;
use DBI;

my $dbh = DBI->connect( );

## Do stuff

my $done :shared = 0;

async {
    $dbh->ping while !$done and sleep 300; ## Adjust frequency to suit
}->detach;

## system 'yourLongRunningCmd';

$done = 1; sleep 1; ## Thread disappears

## The rest of your script

[download]

but you'll have to see if pinging from another thread keeps the connection alive on your system.

Alternatively, if the command runs so long, why not just disconnect from the DB and re-establish a connection when the command is finished?

Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
"Science is about questioning the status quo. Questioning authority".
In the absence of evidence, opinion is indistinguishable from prejudice.
"Too many [] have been sedated by an oppressive environment of political correctness and risk aversion."

Replies are listed 'Best First'.
Re^2: multiprocessing in perl
by shijumic (Novice) on Apr 15, 2009 at 18:09 UTC
    why I am not disconnect and reconnect to db because, every entry to the database marked as each transactions. So i need to keep the connection and report the full loading into the database as one transaction

    I tried approach explained by BrowserUK,in the following way in test_connection.pl

    $dbh = $conn->databaseHandle();# I am calling this from my connection 
+class

print "dbh: ". $dbh."\n";

async {
    $dbh->ping while !$done and sleep 15; ## Adjust frequency to suit
    }->detach;

# system 'yourLongRunningCmd';
&long_running_script;
sub long_running_script {
 print "Testing... long_running test is beign called. will sleep for 3
+0 sec\n";
 sleep(30);
 }

$done = 1; sleep 1; ## Thread disappears
    
    
 print "Thread disappears..Done";

    [download]
    ing i But I am getting the following error:

    thread failed to start: DBD::Oracle::db ping failed: handle 2 is owned by thread 814e008 not current thread 852d948 (handles can't be shared between threads and your driver may need a CLONE method added) at test_connection.pl line 30.
    how can I use clone() to resolve this problem.
    Thanks
[reply]
[d/l]
      Shijumic, thanks for the additional information. I think I understand your problem a little better now. I think that your program flow is something like:
      1. Create Oracle connection
      2. Do a bunch of work in perl (not using Oracle connection)
      3. Insert entry into Oracle table indicating #2 is done
      4. Do a bunch of work in perl (not using Oracle connection)
      5. Insert entry into Oracle table indicating #4 is done
      6. etc...

      Because the work being done in #2, #4, etc does not use the Oracle connection, the connection is idle and get's dropped by the Oracle server. At some point (say step #7) when you try to use the Oracle connection again, you get a Does this accurately describe your program?

      If this is correct, then you should be getting an Oracle error "ORA-02396: exceeded maximum idle time, please connect again"

      Assuming that you need to solve this problem in perl only, then there are a couple of options:
      1: break your long-running tasks into a loop and periodically use the Oracle connection to reset the IDLE counter. Hopefully you can break your long-running task into a loop over smaller tasks.
      2. use threading or forking to allow periodic Oracle connection exercising in parallel with the long-running task. My concern about this approach is that I think perl forking/threading will not work with a single network connection handle to the Oracle session, and that you will be forced to create a new, separate Oracle connection...which is a waste of time because each session will have its own IDLE counter.

      Please look into your USER_RESOURCE_LIMITS in Oracle and capture the perl error message from Oracle so that we know what the real problem is here.

      You mention that you want all of the postings of information to Oracle to be "one transaction". Presumably you want to do this so that if part of your perl job fails, you can use Oracle's ROLLBACK functionality to undo any uncommited work in the transaction. Transactional work is a cool feature of database software like Oracle, but if you have very long-running processes, you should not use this, because you are holding many Oracle resources (temp talbes, rollback segments) idle for long time periods. In many instances, Oracle may clobber these idle resources, resulting in other errors (ROLLBACK segment too old).

      Instead, I would recommend an approach that I have used in the past. I capture the perl script start time and process number and use it as a key in my Oracle log table. I connect to Oracle, add entries to my Oracle log table under my key, then disconnect. I do this repeatedly for all the tasks in my long-running perl job. At the very end of my perl job, I reconnect to Oracle one last time and update the log table, setting a column like UPDATED='Y' for all records under my key. If my perl job had failed, then all my Oracle log table entries would not have had UPDATED='Y' and I would know that the job had failed.

      Are there some other requirements you are subject to which prevent you from using this approach?

[reply]
        Thanks much fzellinger
[reply]
      how can I use clone() to resolve this problem.

      Unfortunately, that means that the DBD driver would need, possibly extensive modifications. Which unless you are well versed in DBD internals, you should probably leave to the authors.

      However, perhaps you can turn things around and put the long running part in a thread, and ping the DB from the main thread like so (also untested): 

      use threads;
use threads::shared;


$dbh = $conn->databaseHandle();# I am calling this from my connection 
+class

print "dbh: ". $dbh."\n";

my $done :shared = 0;
async {
    # system 'yourLongRunningCmd';
    &long_running_script;
    $done = 1; 
}->detach;

$dbh->ping while !$done and sleep 15; ## Adjust frequency to suit
    
    
print "Thread disappears..Done";
exit;

sub long_running_script {
    print "Testing... long_running test is beign called. will sleep fo
+r 30 sec\n";
    sleep(30);
}

      [download]
      Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
      "Science is about questioning the status quo. Questioning authority".
      In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]
[d/l]
        Thanks much, I used fork insted of threads and it works fine..
[reply]

      Read DBI on threads. Basically, you will need to open a second connection from within your second thread, and then it's up to DBD::Oracle to be threadsafe.

[reply]
        I didn't get that. Would you explain that using an example.I am very new to perl and especially to threads.
[reply]

In Section Seekers of Perl Wisdom