dbi & threads

artemave has asked for the wisdom of the Perl Monks concerning the following question:

Hi, all.

I use ActivePerl 5.8.8.817. The latest Class::DBI::MSSQL, DBD::ODBC on Win2000 sp4, connecting to MS SQL Server 2000.

The following code:

use strict;
use warnings;

use XML::Simple;
use Probe::ProbeUser; #uses base 'Class::DBI::MSSQL'
use threads;

my $config = XMLin('./probe.xml', ForceArray => 1);

while (my ($type_name, $user_type) = each %{$config->{user}}) {
 for (my $i = 0; $i < $user_type->{quantity}; $i++) {
  threads->create( 
   sub {
    my $user = Probe::ProbeUser->create(
     {
      pu_login => $user_type->{login},
      pu_paswd => $user_type->{password},
      pu_type => $type_name,
     }
    );
    
    $user->execute(tasks => $user_type->{task});
   }
  )->join();
 }
}
__END__
[download]

produces:

Thread failed to start: Can't insert new Probe::ProbeUser: DBD::ODBC::db FETCH failed: handle 2 is owned by thread 15d4924 not current thread 391429c (handles can't be shared between threads and your driver may need a CLONE method added) at c:/Perl/site/lib/Ima/DBI.pm line 312. at ./probe.pl line 31 Scalars leaked: 4

I didn't find anything on how to overcome this. Could anyone help?

Thanks,
Artem A. Avetisyan.

P.S.
The driver does have CLONE method.

Comment on dbi & threads Download Code

Replies are listed 'Best First'.
Re: dbi & threads by Corion (Patriarch) on Jul 08, 2006 at 07:53 UTC
I think your problem is still that handles can't be shared between threads. DBI handles cannot be shared across threads. So don't do that. Set up a Threads::Queue and one DB worker thread and do all the DB work through the one thread.	[reply]
Re^2: dbi & threads by artemave (Beadle) on Jul 10, 2006 at 16:05 UTC
Right. Now i'm trying to set up threads::shared $dbh. No good so far. This class is used as a base for all my cdbi classes: `package Probe::DBI; use strict; use warnings; use threads; use threads::shared; use base qw(Class::DBI::MSSQL); my $dbh; share($dbh); $dbh = &share({}); bless($dbh, __PACKAGE__.'::db'); sub db_Main { unless ($dbh->{Active}) { $dbh = &share(DBI->connect_cached('dbi:ODBC:DRIVER={SQL Server +};Server=moproj24;TargetDSN=probe', 'pr_user', 'pr_user', { __PACKAGE +__->_default_attributes() })); } return $dbh; }` [download] The problem, i suppose, that $dbh is counted as a scalar, not as db handle object. dbih_getcom handle threads::shared::tie=SCALAR(0x200f4d4) is not a DBI handle at c:/Perl/site/lib/Im a/DBI.pm line 381. Compilation failed in require at Probe/ProbeTask.pm line 10. BEGIN failed--compilation aborted at Probe/ProbeTask.pm line 10. Compilation failed in require at Probe/ProbeUser.pm line 11. BEGIN failed--compilation aborted at Probe/ProbeUser.pm line 11. Compilation failed in require at ./probe.pl line 9. BEGIN failed--compilation aborted at ./probe.pl line 9. Can you explain me how to get work? Thanks, Artem A. Avetisyan.	[reply] [d/l]
Re^3: dbi & threads by Corion (Patriarch) on Jul 10, 2006 at 16:13 UTC
You cannot share DB-handles (`$dbh`) across threads. No matter what you do, you can't. What you're currently running afoul of is the fact that, as far as I understand the threads::shared documentation, it does not share variables deeply, and likely the hash will contain nested subhashes. Even if you succeeded with that, you will run against the problem that the XS portions of DBI and the DBD are not threadsafe. Also read the below remarks that using multiple threads for concurrent DB access will not improve performance anyway.	[reply] [d/l]
Re: dbi & threads by BrowserUk (Patriarch) on Jul 08, 2006 at 09:56 UTC
If your hope is to cut the overall query time by issuing multiple concurrent queries to your DB, it it very unlikely to suceed even if you work around the problem of sharing DBI handles. Multiple queries to a given database will usually be serialised by the DBM at the server end. Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. Lingua non convalesco, consenesco et abolesco. -- Rule 1 has a caveat! -- Who broke the cabal? "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice.	[reply]
Re^2: dbi & threads by renodino (Curate) on Jul 08, 2006 at 15:47 UTC
...not to mention that MSSQL (or maybe its ODBC driver ?) doesn't seem to support multiple open requests on a single connection (a problem I recently encountered and had to workaround in DBIx::Chart). I'll inject an obligatory plug for DBIx::Threaded here, but it likely (a) wouldn't work with Class::DBI and (b) would just serialize the query processing on the client side.	[reply]