Sharing DBI handle per each thread

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

Hi all.

How do i let each thread share same DBI handle($dbh)?

Is that impossible? I need your help.

#!/usr/bin/perl -w

use threads;
use threads::shared;
use DBI;
use DBD::mysql;

my $dbh = DBI->connect("dbi:mysql:host=xxx.xxx.xxx.xxx;database=xxx", 
+"xxx","xxx");
share($dbh);            # its not work!!

my $threads_cnt = 4;

my @threads;
for (my $i = 0;$i < $threads_cnt ;$i++) {
    $threads[$i] = threads->create(\&test);
}

for (my $i = 0;$i < $threads_cnt ;$i++) {
    $threads[$i]->detach;
}

while(1) {sleep;}
exit;

sub test()
{
    my $tid = threads->self->tid;

    my $cursor = $dbh->prepare("select * from users");        # each t
+hread can't share $dbh..
    $cursor->execute();
    my $result = $cursor->fetchrow();
    $cursor->finish();

    $dbh->disconnect;

    open (FILE, ">files/test_$tid.txt");
    print FILE $result;
    close (FILE);
}
[download]

Comment on Sharing DBI handle per each thread Download Code

Replies are listed 'Best First'.
Re: Sharing DBI handle per each thread by sauoq (Abbot) on Aug 19, 2003 at 02:10 UTC
Is that impossible? Straight outta the DBI docs: `Because of this (possibly temporary) restriction, newly created + threads must make their own connctions to the database. Handles can't b +e shared across threads.` [download] You should probably `perldoc DBI` and search for the section on threads and thread safety. Note that the documentation's last word on the subject is, "Using DBI with perl threads is not yet recommended for production environments." -sauoq "My two cents aren't worth a dime.";	[reply] [d/l]
Re: Re: Sharing DBI handle per each thread by jdtoronto (Prior) on Aug 19, 2003 at 02:22 UTC
From the DBI docs: However, the internal pointer data within the handles will refer to the DBI and drivers in the original interpreter. Using those handles in the new interpreter thread is not safe, so the DBI detects this and croaks on any method call using handles that don't belong to the current thread (except for DESTROY). Because of this (possibly temporary) restriction, newly created threads must make their own connctions to the database. Handles can't be shared across threads. But BEWARE, some underlying database APIs (the code the DBD driver uses to talk to the database, often supplied by the database vendor) are not thread safe. If it's not thread safe, then allowing more than one thread to enter the code at the same time may cause subtle/serious problems. In some cases allowing more than one thread to enter the code, even if not at the same time, can cause problems. You have been warned. Can't really be much plainer can it! jdtoronto	[reply]
Re: Sharing DBI handle per each thread by htoug (Deacon) on Aug 19, 2003 at 11:41 UTC
<quote>Is that impossible?</quote> At the moment the DBI (as noted in earlier postings) prevents this, but that restriction will probably be lifted in the not too far future for drivers whose underlying libraries support threads - this is not case for all drivers.	[reply]