dealing with lengthy (time) query sets

I currently have script/program that simply takes input lines and does
work on them.
updating tables accordingly and all is good.
however every 'x' input lines i need to call a subroutine that does some
work on the database that takes a while, like 1 or 2 min. however i
can't have the program become unresponsive for that long.

My original idea was to spawn a thread off the subroutine and then it
could go do all the work it likes leaving the main section to continue
on chugging through input. However when I tried this i got errors about
"""handle 3 is owned by thread 61f010 not current thread 11deb70
(handles can't be shared between threads and your driver may need a
CLONE method added) """

So i gave up on that and figured i do it with fork, however there are
apparently issues with keeping the database connection associated with that.

My last resort which i know works is to make another script that makes
its own database connection does its work and dies. I would run this
script when i need to by making exec "..." calls and passing my
parameters via the command line args. I'v done this before and i know it
works, but it just seems ugly and hacked together and i can't help but
think there must be a better way, i can't be the only person that has to
deal with situations like this..

--
------------------------------------
Alex Anagnos
Student: Northern Michigan University
woodshop2300@gmail.com
------------------------------------

RE: dealing with lengthy (time) query sets

Since you don't give any information about your BD, I will give you the
way I would do it with Oracle.

I would put the 'x' records into a QUEUE table. Upon entry into the
queue, a DBMS_JOB would start and process the row. Errors can be
generated to the instance alert log, or notification could be sent to a
management console, etc. In any case, by pushing the row into the QUEUE
table, further processing is asynchronous and your program would not
have a delay.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc. =20

-----Original Message-----
From: WoodShop [mailto:woodshop2300@gmail.com]=20
Sent: Saturday, April 07, 2007 1:03 AM
To: dbi-users@perl.org
Subject: dealing with lengthy (time) query sets

I currently have script/program that simply takes input lines and does
work on them.
updating tables accordingly and all is good.
however every 'x' input lines i need to call a subroutine that does some
work on the database that takes a while, like 1 or 2 min. however i
can't have the program become unresponsive for that long.

My original idea was to spawn a thread off the subroutine and then it
could go do all the work it likes leaving the main section to continue
on chugging through input. However when I tried this i got errors about
"""handle 3 is owned by thread 61f010 not current thread 11deb70
(handles can't be shared between threads and your driver may need a
CLONE method added) """

So i gave up on that and figured i do it with fork, however there are
apparently issues with keeping the database connection associated with
that.

My last resort which i know works is to make another script that makes
its own database connection does its work and dies. I would run this
script when i need to by making exec "..." calls and passing my
parameters via the command line args. I'v done this before and i know it
works, but it just seems ugly and hacked together and i can't help but
think there must be a better way, i can't be the only person that has to
deal with situations like this..

--
------------------------------------
Alex Anagnos
Student: Northern Michigan University
woodshop2300@gmail.com
------------------------------------


This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: dealing with lengthy (time) query sets

WoodShop wrote:
>=20
> I currently have script/program that simply takes input lines and does
> work on them. updating tables accordingly and all is good. however
> every 'x' input lines i need to call a subroutine that does some work
> on the database that takes a while, like 1 or 2 min. however i can't
> have the program become unresponsive for that long.
>=20
> My original idea was to spawn a thread off the subroutine and then it
> could go do all the work it likes leaving the main section to continue
> on chugging through input. However when I tried this i got errors
> about """handle 3 is owned by thread 61f010 not current thread 11deb70
> (handles can't be shared between threads and your driver may need a
> CLONE method added) """

Combining threads with DBI is generally not a great idea yet. They can
work to some degree, but they're explicitly unsupported for production
use in the DBI docs.

> So i gave up on that and figured i do it with fork, however there are
> apparently issues with keeping the database connection associated
> with that.

Just don't reuse the same $dbh. Create a new one in the child process.

You should also set $dbh->{InactiveDestroy} =3D 1 on the parent's $dbh =
in
the child process after you've forked. This prevents the child process
from closing the parent's database connection during the cleanup phase:

# untested, but hopefully gets the point across

local $SIG{CHLD} =3D 'IGNORE';
while (process_next_batch($dbh)) {
next if my $pid =3D fork;
die "can't fork: $!" unless defined $pid;
=20
# child process
# don't close parent's dbh at child exit
$dbh->{InactiveDestroy} =3D 1

my $child_dbh =3D DBI->connect(@params);
do_child_work($child_dbh);
exit; # child
}
1 while wait() !=3D -1;

- Philip