Oh Great and Wholly Wons,
How may I make disciples of multiple child threads such that they will dutifully carry out my DB access statements in parallel harmony on the same tablet?
Example:
#!/usr/bin/perl
use 5.24.0;
use strict;
use warnings;
use Parallel::ForkManager;
use DBI ':sql_types';
use DBD::Pg qw/:pg_types/;
# connect to Postgres DB
my $DSN = 'DBI:Pg:dbname=db_test';
my $userid = "postgres";
my $sesame = "opensesame";
my $dbh = DBI->connect($DSN, $userid, $sesame, {
AutoCommit => 1,
RaiseError => 1,
PrintError => 1
}) or die "Connection failed!\n" . $DBI::errstr;
my %columns; # hash for persistent column array id mapping
$columns{field1} = 0;
$columns{field2} = 1;
$columns{field3} = 2;
$columns{field4} = 3;
my @columns; # deterministic - $values[$columns{$column}];
$columns[0] = 'field1';
$columns[1] = 'field2';
$columns[2] = 'field3';
$columns[3] = 'field4';
my $placeholders = join(", ", map { '?' } @columns);
# Build the SQL SELECT statement
my $sql_select =
qq(SELECT count(*) FROM mytable WHERE field1 = ?;);
# PREPARE the SELECT statement 'template' handle
my $sth_select = $dbh->prepare_cached($sql_select);
# Build the SQL INSERT statement
my $sql_insert =
"INSERT INTO mytable ("
. join(", ", @columns) # column names
. ") VALUES ($placeholders)";
# PREPARE the INSERT statement 'template' handle
my $sth_insert = $dbh->prepare_cached($sql_insert);
# create array for child threads/processes
my @children;
$children[0] = 'child1';
$children[1] = 'child2';
$children[2] = 'child3';
$children[3] = 'child4';
main();
# close template statement handles and disconnect from db
$sth_select->finish();
$sth_insert->finish();
$dbh->disconnect;
exit;
#--------<subs>----------
main {
# create Parallel::ForkManager object for MAX_PROCESSES
my $parallel = Parallel::ForkManager->new(4);
$parallel->run_on_start(sub{
my ($pid,$ident) = @_;
print "Starting $ident under process id $pid\n";
});
$parallel->run_on_finish(sub{
my ($pid,
$exit_code,
$ident,
$exit_signal,
$core_dump,
$data_structure_reference) = @_;
});
print "Running $child_count threads.\n";
my $thread_count = 0;
NEWPROCESS:
for my $child (@children) {
$thread_count++;
print "Thread $thread_count running for $child\n";
# fork parallel threads - per child
$parallel->start($child) and next NEWPROCESS;
if ($child =~ m/child1/i) {
my $sth_select_child1 = $sth_select; # can I do this?
my $sth_insert_child1 = $sth_insert; # or is it sinful?
# ... get and prepare data for db
# ... check db for existing rows with $sth_select_child1
# ... if not exist then insert with $sth_insert_child1
$sth_select_child1->finish();
$sth_insert_child1->finish();
} elsif ($child =~ m/child2/i) {
my $sth_select_child2 = $sth_select;
my $sth_insert_child2 = $sth_insert;
# ... get and prepare data for db
# ... check db for existing rows with $sth_select_child2
# ... if not exist then insert with $sth_insert_child2
$sth_select_child2->finish();
$sth_insert_child2->finish();
} elsif ($child =~ m/child3/i) {
my $sth_select_child3 = $sth_select;
my $sth_insert_child3 = $sth_insert;
# ... get and prepare data for db
# ... check db for existing rows with $sth_select_child3
# ... if not exist then insert with $sth_insert_child3
$sth_select_child3->finish();
$sth_insert_child3->finish();
} elsif ($child =~ m/child4/i) {
my $sth_select_child4 = $sth_select;
my $sth_insert_child4 = $sth_insert;
# ... get and prepare data for db
# ... check db for existing rows with $sth_select_child4
# ... if not exist then insert with $sth_insert_child4
$sth_select_child4->finish();
$sth_insert_child4->finish();
}
print "\$parallel->finish on child $child\n";
$parallel->finish(0);
}
print "\$parallel->wait_all_children() is waiting...\n";
$parallel->wait_all_children();
} # main
I suppose the key question is this:
how do I create parallel handles for simultaneous read/write access to the same db table safely?
Homage to all ye who read and understand.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.