share MySQL resources among threads

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

Dear Monks

I've created a server-script that puts client calls into a thread. However, each thread has to communicate with a DB. Because clients are always very impatient I had the briliant idea to connect to the DB during initialization of the server-script, and then share the connection between the threads. The code of the script

..... init stuff ......
my $db = DBI->connect(.....)
my $manage = MyPackage->new( $db ) ;
..... end init stuff .....
..... usage .......
push @threads, new Thread \&start_thread, $manage ;
.....
[download]

However, this is what happens when I run the code:

thread failed to start: DBD::mysql::db quote failed: handle 2 is owned
+ by thread 8166008 not current thread 9178d90 (handles can't be share
+d between threads and your driver may need a CLONE method added) at /
+usr/lib/perl5........
[download]

So, I cannot share the database connection, right ?
It suggests cloning or should I make a poule of DB connections and give each thread a connection of its own (and when the thread stops the DB connection will be available for an other thread). As I said before I don't want to make a DB connection at the moment I create the thread!

Any suggestions ?

Thnx
LuCa

Comment on share MySQL resources among threads Select or Download Code

Replies are listed 'Best First'.
Re: share MySQL resources among threads by BrowserUk (Patriarch) on Sep 06, 2007 at 10:46 UTC
Two options: Confine all your interactions with the DB to a single thread. Have your other threads make requests to that thread, and retrieve results from it, via queues. Use DBIx::Threaded. If you have a degree in over-engineering and performance is not an issue, this may be the route for you. Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice. "Too many [] have been sedated by an oppressive environment of political correctness and risk aversion."	[reply]
Re^2: share MySQL resources among threads by jeanluca (Deacon) on Sep 06, 2007 at 11:39 UTC
I just tried option 2, because I get the impression that I only have to change the connect statement, right ? Anyway, it worked. However when the server stops I get the following: (in cleanup) Can't call method "FETCH" on an undefined value a +t /usr/lib/perl5/site_perl/5.8.8/DBIx/Threaded.pm line 1727 during gl +obal destruction. (in cleanup) Can't call method "FETCH" on an undefined value a +t /usr/lib/perl5/site_perl/5.8.8/DBIx/Threaded.pm line 1727 during gl +obal destruction. (in cleanup) Can't call method "FETCH" on an undefined value a +t /usr/lib/perl5/site_perl/5.8.8/DBIx/Threaded.pm line 1727 during gl +obal destruction. (in cleanup) Can't call method "FETCH" on an undefined value a +t /usr/lib/perl5/site_perl/5.8.8/DBIx/Threaded.pm line 1727 during gl +obal destruction. (in cleanup) Can't call method "FETCH" on an undefined value a +t /usr/lib/perl5/site_perl/5.8.8/DBIx/Threaded.pm line 1727 during gl +obal destruction. A thread exited while 4 threads were running. [download] Any suggestions what might be the reason of this message ?	[reply] [d/l]
Re^3: share MySQL resources among threads by renodino (Curate) on Sep 06, 2007 at 20:19 UTC
Looks like another instance where the object and tied hash get DESTROYed in an odd order. Note that the DESTROY() for DBIx::Threaded::db wraps the same code in an eval{} to make it go away, so I'd suggest trying the same. Just edit your copy of Threaded.pm at line 1727 to `eval { $obj->{_inner}--; $obj->_send_simplex('DESTROY') unless $obj->{_inner}; };` [download] and that should silence the errors. Perl Contrarian & SQL fanboy	[reply] [d/l]
Re^3: share MySQL resources among threads by BrowserUk (Patriarch) on Sep 06, 2007 at 11:43 UTC
Sorry no. I've never managed to get it to work here. If you are lucky, renodino will notice this thread and help you out. Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice. "Too many [] have been sedated by an oppressive environment of political correctness and risk aversion."	[reply]
Re: share MySQL resources among threads by Anonymous Monk on Sep 06, 2007 at 10:49 UTC
Upgrade? MULTITHREADING The multithreading capabilities of DBD::mysql depend completely on the underlying C libraries: The modules are working with handle data only, no global variables are accessed or (to the best of my knowledge) thread unsafe functions are called. Thus DBD::mysql is believed to be completely thread safe, if the C libraries are thread safe and you don't share handles among threads. The obvious question is: Are the C libraries thread safe? In the case of MySQL the answer is "mostly" and, in theory, you should be able to get a "yes", if the C library is compiled for being thread safe (By default it isn't.) by passing the option -with-thread-safe-client to configure. See the section on How to make a threadsafe client in the manual.	[reply]
Re^2: share MySQL resources among threads by moritz (Cardinal) on Sep 06, 2007 at 11:33 UTC
Thus DBD::mysql is believed to be completely thread safe, if the C libraries are thread safe and you don't share handles among threads. But that's exactly what happend in jeanluca's example Perl 6 in German	[reply]
Re^3: share MySQL resources among threads by Anonymous Monk on Sep 06, 2007 at 13:18 UTC
You cannot determine if he's using old DBD::mysql or libmysql. The obvious question is: Are the C libraries thread safe? In the case of MySQL the answer is "mostly" and, in theory, you should be able to get a "yes", if the C library is compiled for being thread safe (By default it isn't.) by passing the option -with-thread-safe-client to configure. See the section on How to make a threadsafe client in the manual.	[reply]