databases and multithreading

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

Hi, I'm trying to make a threaded app with database connections. I was having low performance (20% cpu usage) because the site sending the data is limited in speed. So I tried launching another process of my program and I got to do twice as much,as expected. So I tried to make my app multithreaded. To feed the threads, I need to query a database (READ), and the threads themselves do database access too (Write). So I went with something like this:

$dbh=...
$sth->execute()
$sth->bind_columns(...)

t1= new Thread \&processor;
t2= new Thread \&processor;

t1->join();
t2->join();
#ends here#
sub hitMe { # :locked? 
   $sth->fetch();
   return (columns...);
}

sub processor {
   my $dbconn....
   my $dbh...

   while(@data=hitme()) {
     process stuff;
     $dbh->something();
   }
}
[download]

But it complains that the connection is already being used by thread N (program's main thread) and that db handlers aren't shared. So how can I feed my threads with data from the db in a synchronized manner? Thanks

Comment on databases and multithreading Download Code

Replies are listed 'Best First'.
Re: databases and multithreading by Fletch (Bishop) on Dec 01, 2008 at 19:34 UTC
Personally I'd be worried that the underlying database libraries were reentrant. Safer might be to have a single DB interaction thread pushing units of work onto a shared queue that separate worker threads then handle (your typical producer/consumers "pattern"). The cake is a lie. The cake is a lie. The cake is a lie.	[reply]
Re: databases and multithreading by zentara (Cardinal) on Dec 01, 2008 at 18:41 UTC
Your code isn't complete, and it looks like you define $dbh before you spawn your threads. Threads get a copy of the parent when spawned, so your my $dbh in the thread may not be working. Try to isolate all the db stuff into the thread code block. Or try to undef $dbh first thing in your thread code block, then redeine it with my. Otherwise, play it safe and fork your code. I'm not really a human, but I play one on earth Remember How Lucky You Are	[reply]
Re^2: databases and multithreading by hjf (Initiate) on Dec 01, 2008 at 18:56 UTC
there is a $dbh (just to test, I called it $dbg) in a main thread and another $dbh for each thread, both $dbg and $dbh are declared "my". (so 1 main + 3 threads = 4 independent DB connections). Each threads calls the method "hitMe" every time they end processing the data from the DB, and I need to process about 70.000 rows in the DB, but I can only process 3 at a time, so that's why I thought of threading it.	[reply]
Re: databases and multithreading by Joost (Canon) on Dec 01, 2008 at 22:29 UTC
If you instantiate and use separate database handles in each thread and use thread-safe DBD drivers with recent client libraries (like recent DBD::mysql and libmysqlclient) you can make something like this work. But since your code just seems to share handles over different threads, chances of it working at all with any driver are slim. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re: databases and multithreading by Anonymous Monk on Dec 02, 2008 at 11:35 UTC
If you use 'locked' on a subroutine you might want to consider 'yield;' aswell. As for threaded database access read DBIx:Threaded Hope it helps.	[reply]
Re^2: databases and multithreading by hjf (Initiate) on Dec 02, 2008 at 12:08 UTC
DBIx::Threaded sounds exactly like what I'm looking for, thanks!	[reply]