Does anyone know of any gotachs or problems when writing multithreaded Perl applications using the Oracle DBI? Each thread would have it's own connection to Oracle.
For the longest time I was told multithreading was not supported in Perl with Oracle.
The Perl DBI enforces single-threading through its interior, so the drivers will only be active on one session ($dbh) at a time. Regardless of how many CPUs you have. So, multi-threading is not supported (because everything inside DBI is single-threaded), but it is safe to use DBI (and hence DBD::Oracle) in a multi-threaded application.
Yes, using threads in Perl is an extremely bad idea, regardless of whether you use Oracle or not.
While in theory, provided each uses its own connection, it should work, perl 5.8 threads are fundamentally flawed.
If you understand that article and still want to use Perl threads, good luck.
Well, the DBI documentation says not to use a threaded Perl, and points to a Perlmonks post that explains that. The documentation telling you not to do it is a pretty good reason.
However, I've seen it work just fine on some platforms but fail miserably on others. It's certainly not portable even if you do get it to work.
A while back I mannaged to get an ad-hock implementation working... I treated the connection w/ dbi as a limited resource and shared it among the various threads in Perl using a file locking mechanism. My multi-threaded app only ever connected to the dbi through a seperate perl-script running as a daemon.
On Linux, the multithreaded end was done via fork, on windows I used whatever came with the default activeperl implementation ( I forget )
I tried having them communicate via shared memory, but ended up just using a shared file instead. Linux has reliable append mode, so it was a piece of cake. On windows it was much more difficult to get them synchronized.
Recently I've looked into database transactions, with each instance of the thread having it's own connection to the database, and letting the database handle the connection details.
This is with mysql, but i'm sure oracle supports transactions.
apache::dbi works/plays well with mod_perl in keeping these connections alive between each run-through of the script (before I used this, each connection made was quite time consuming).
Your results will vary.
You can try using shell "multithreading" instead. Described in http://alexhanin.blogspot.com/2010/07/multithreading-with-nothing-but-korn.html You can put your perl inside shell wrapper, then use each shell "thread" as a separate instance of a program.