Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

mutiple threading

by perlCrazy (Monk)
on Aug 09, 2007 at 07:08 UTC ( #631477=perlquestion: print w/ replies, xml ) Need Help??
perlCrazy has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,
I want to execute three sql querry parallelly at oracle database. Can this be acheived by using threads module and how ?
Any help will be great favor.
Thanks

Comment on mutiple threading
Re: mutiple threading
by vcTheGuru (Chaplain) on Aug 09, 2007 at 10:57 UTC

    Hi,

    I dont know why you need to execute 3 SQLs parallelly. But you can try something like this.

    use threads; use DBI; my $dbh = DBI->connect( <your connection string> ); $SQL_1 = "SELECT * FROM x"; $SQL_2 = "SELECT * FROM y"; $SQL_3 = "SELECT * FROM z"; sub start_thread { my ($dbh,$SQL) = @_; # print('Thread started: ', SQL, "\n"); my $sth = $dbh->prepare($SQL); $sth->execute(); < Add your code here > } my $thr1 = threads->create('start_thread', $dbh, $SQL_1); my $thr2 = threads->create('start_thread', $dbh, $SQL_2); my $thr3 = threads->create('start_thread', $dbh, $SQL_3); $thr1->join(); $thr2->join(); $thr3->join();

    For more details, refer to threads

    Notes:

    1. This is NOT a fully functional working code. it just gives a brief idea about how to do.
    2. Make sure that 3 SQLs running together wont make any inconsistance. See the example where I am selecting from 3 different tables, which are almost independant and it wont make any inconsistancy or locks. (I cant say that too 100% secure )
    3. Whenvever possible Donot use parallel SQLs
    4. Cheers !

      --VC



      There are three sides to any argument.....
      your side, my side and the right side.

      Using DBI from multiple threads is (or has been) generally deemed not a good idea because some or all implementations of DBI, and/or the underlying C libraries were not thread safe.

      To quote the DBI pod:

      Threads and Thread Safety

      Perl 5.7 and later support a new threading model called iThreads. (The old ``5.005 style'' threads are not supported by the DBI.)

      In the iThreads model each thread has it's own copy of the perl interpreter. When a new thread is created the original perl interpreter is 'cloned' to create a new copy for the new thread.

      If the DBI and drivers are loaded and handles created before the thread is created then it will get a cloned copy of the DBI, the drivers and the handles.

      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.

      Unless you are using a new version of DBI, and the above paragraph has been updated, you should not share db handles across threads. At best it will fail early and obviously. At worst it will run, but give you mixed up or corrupted data.

      It is certainly safe to run overlapping queries concurrently from different processes, so the limitation lies within the libraries/implementation, rather than inherently with threads.

      However, if the parallelised queries are against the same DB and/or tables, then there may be little benefit derivable from running them concurrently, as the RDBMS may need to serialise them at the DB end.


      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.

        as per the suggestions by perlCrazy, I think a possible thread safe re-write of above code can be

        use threads; use DBI; $SQL_1 = "SELECT * FROM x"; $SQL_2 = "SELECT * FROM y"; $SQL_3 = "SELECT * FROM z"; sub start_thread { my ($SQL) = @_; my $dbh = DBI->connect( <your connection string> ); # print('Thread started: ', SQL, "\n"); my $sth = $dbh->prepare($SQL); $sth->execute(); < Add your code here > } my $thr1 = threads->create('start_thread', $SQL_1); my $thr2 = threads->create('start_thread', $SQL_2); my $thr3 = threads->create('start_thread', $SQL_3); $thr1->join(); $thr2->join(); $thr3->join();



        There are three sides to any argument.....
        your side, my side and the right side.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://631477]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (3)
As of 2014-07-14 05:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (255 votes), past polls