Perl - Too many files error when using DBD Oracle

Question

Can someone advise on why I get errors opening the file in the code below. The errors start about half way through the 9th iteration of 25 threads, and are "Too many open files" errors. The error only happens when running in threads, and only when the DBI connect/disconnect are used. This shouldn't affect the open file count at all should it? I'm fairly new to Perl so not sure if I've done something weird. This is on Perl 5.8.8. on Solaris 10.

use threads ();
use DBI;
use DBD::Oracle;

my $thrds=25;
my $iter=10;
my @threads;

for (my $j=0; $j<$iter; $j++) {
    &start($j);
}

sub start {
    my $k=$_[0];
    for (my $i=0; $i<$thrds; $i++) {
        $threads[$i] = threads->new(\&RunThread,$k, $i);
    }
    for (my $i=0; $i<$thrds; $i++) { $threads[$i]->join; }
}

sub RunThread {
    my $dbh = DBI->connect("dbi:Oracle:lnrmsd9.world", "rms_reader", "rms_reader") or die "failed connect";
    my ($x, $y)=@_;
    open (my $fh, ">/tmp/da") or die "failed $! at iter $x thread $y";
    close ($fh);
    $dbh->disconnect;
}

Answer 1

You need to use:

use warnings;
use strict;

These would tell you that you're using global variables $i and $j in the subroutine. Since you've got multiple threads accessing the variables, all hell breaks loose. Also, they're all sharing a single FILE, too - another source of trouble. And did you realize you had both a scalar '$threads' and an array '@threads'?

With threads, global variables are ... well, if not exactly the enemy, extremely problematic.

Avoid the FILE handle form of open; use my much more liberally.

And you don't need to say 'use DBD::Oracle;' ever. You might sometimes need to use the variant:

use DBD::Oracle qw( :ora_types );

to gain access to Oracle-specific data types.

---

Untested revision:

use strict;
use warnings;
use threads ();
use DBI;
use DBD::Oracle;

my $threads=25;
my $iter=10;

for ($j = 0; $j < $iter; $j++) {
    &start($j);
}

sub start {
    my($j) = @_;
    my(@threads);
    for (my $i = 0; $i < $threads; $i++) {
        $threads[$i] = threads->new(\&RunThread,$j, $i);
    }
    for ($i=0; $i < $threads; $i++) { $threads[$i]->join; }
}

sub RunThread {
    my $dbh = DBI->connect("dbi:Oracle:ora", "user", "pass") or die "failed connect";
    my($j, $i) = @_;
    open(my $fh, ">/tmp/da") or die "failed $! at iter $j thread $i";
    close $fh;
    $dbh->disconnect;
}

---

One thing I didn't understand - why shouldn't I use use DBD::Oracle;?

If you look at the 'perldoc DBD::Oracle', you will see the Synopsis:

use DBI;

$dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd);

So, the primary documentation for the DBD::Oracle module shows that you do not use it directly.

There is no harm done in using it; there is no need to use it. The DBI module automatically loads the driver implied by the connection string in the call to DBI->connect(). By writing use DBD::Oracle;, you save the DBI from having to actually do the loading (it is already done). I suppose you also get Perl to verify that the module is available to be loaded with the use clause.

Answer 2

Try

my $FILE;
open ($FILE, ">/tmp/da") or die "failed $! at iter $j thread $i";
close ($FILE);

This is in the best practice.