After my last post regarding threading with Perl against Oracle 9i I decided to go ahead and perform some testing. My system runs Perl 5.8.2 with ithreads compiled using GCC under Solaris 8 for SPARC.

My first test was to have each thread create its own connection the database: 

#!/opt/perl-5.8.2/bin/perl

use threads;
use DBI;

@threads;
printf "Starting thread ";
for ($i = 0; $i < 50; $i++) {
        printf "$i ";
        $threads[$i] = new threads(\&do_db);
}
printf "\n";

printf "Joining thread ";
for ($i = 0; $i < 50; $i++) {
        printf "$i ";
        $thread = $threads[$i];
        $thread->join;
}
printf "\n";

sub do_db {
        my $dbh = DBI->connect("dbi:Oracle:host=localhost;sid=testdb1"
+, "user1", "pass1") || die $!;
}

[download]

Watching the Oracle instances, I could see separate connections to the database being created as expected. All the threads are created but then the program dumps core. This may be something to do with the way I'm joining the threads at the end but that's not the big issue...

I then tried using, as the DBD::Oracle 1.14 documentation describes, the ora_dbh_share connection attribute which means that calls to DBI->connect do not necessarily return a reference to a unique database connection: 

#!/opt/perl-5.8.2/bin/perl

use threads;
use DBI;

our $orashr : shared = '' ;

@threads;
printf "Starting thread ";
for ($i = 0; $i < 50; $i++) {
        printf "$i ";
        $threads[$i] = new threads(\&do_db);
}
printf "\n";

printf "Joining thread ";
for ($i = 0; $i < 50; $i++) {
        printf "$i ";
        $thread = $threads[$i];
        $thread->join;
}
printf "\n";

sub do_db {
        my $dbh = DBI->connect("dbi:Oracle:host=localhost;sid=testdb1"
+, "user1", "pass1", { ora_dbh_share => \$orashr} ) || die $!;
}

[download]

This only gets as far as creating 2-5 threads before dumping core so this can't be anything to do with how I am calling ->join as it's not even getting to that point.

The DBD::Oracle 1.14 documentation implies that threading is safe. It mentions that you need at least Perl 5.8.0 but I fulfil that requirement. The DBI 1.39 documentation mentions the use of ithreads in Perl 5.7 and later and even though it mentions that some database APIs are not thread safe, DBD::Oracle 1.14 claims that its ok.

Can anyone shed any light on what is happening here? The application I am developing is updating or deleting 30,000 records in a database of 6,000,000 or so on a daily basis (read: 6,000,000 in the main table which supplies a foreign key to 5 other tables each containing between 100,000 and 9,000,000 records). The SQL itself is rather slow acting due to the sheer size of the tables and a single threaded application blocks until the SQL is complete. I would like to fire off many SQL statements at once and as the box is a 4 x 750Mhz SPARC with 8Gb physical RAM I should be able to hit it fairly hard.

All comments welcomed.

== fx, Infinity Is Colourless

In reply to Threading (Perl 5.8 ithreads) with Oracle 9i dumps core by fx

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.