Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Using fork with DBI to create simultaneous db connections.

by altern8 (Initiate)
on Feb 08, 2005 at 01:51 UTC ( #428884=perlquestion: print w/replies, xml ) Need Help??

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

I'm trying to use perl to help simulate a large number of users accessing an oracle database. The problem I'm running into is during the forking process. I want each db connection to handle a number of SQL commands simultaneously with the others.

I have a package I created which contains a function I wrote for fork, which for forking one instance works fine. I call that method from my script with a for loop wrapped around it to create the number of connections I want to use on the database.

If I use dbi->connect inside the child of the fork, it appears that all the children end up sharing the same DBH. If I create the dbh before the fork and pass it to the child I get strange errors like:

DBD::Oracle::db DESTROY failed: ORA-03113: end-of-file on communication channel (DBD ERROR: OCISessionEnd)

Is there anyone out there who has tried this scenario before and solved it, or who might have some tips on how best to approach this task?

  • Comment on Using fork with DBI to create simultaneous db connections.

Replies are listed 'Best First'.
Re: Using fork with DBI to create simultaneous db connections.
by VSarkiss (Monsignor) on Feb 08, 2005 at 02:03 UTC

    In general, you can't use a DBH across a fork. I don't know of any db clients where the handle will survive a fork.

    You write, however,

    it appears that all the children end up sharing the same DBH
    How did you check for that? The good way to check would be to have your program fork, let each child open a connection, then sleep for a minute or something. During that time, log on to the database using another tool (Toad or DB-Artisan or something) and see how many connections are in your name. If the numbers agree, you're done.

      The good way to check would be to have your program fork, let each child open a connection, then sleep for a minute or something. During that time, log on to the database using another tool (Toad or DB-Artisan or something) and see how many connections are in your name. If the numbers agree, you're done.
      ...or, you could have each child just print the database handle. Like all perl objects, it's just a reference, so it should print something like DBI::db=HASH(0x1223456). If they all print the same reference, done. No monkeying around on the database at all (well, except the DBI calls that is...but you already knew that :P).

      thor

      Feel the white light, the light within
      Be your own disciple, fan the sparks of will
      For all of us waiting, your kingdom will come

        Actually, that's incorrect, and it's probably the same mistake that the original poster made. The flaw is that the memory address shown as part of the stringification of the reference is the *relative* memory address to that process. The operating system uses something called "virtual memory", which abstracts the actual *physical* memory location away from your processes. In truth, the physical memory addresses may change from time to time, as your process is swapped and paged and so on.

        Anyway, here's a quick demo:

        prompt> perl -le 'my $pid = fork; my $x = [$pid]; print "$x->[0] -- $x +"' 0 -- ARRAY(0x80d11ec) 24694 -- ARRAY(0x80d11ec) prompt>
        You can see that each process has it's own array, with the reference stored in $x, yet in both processes, the memory address appears the same.
        ------------ :Wq Not an editor command: Wq
Re: Using fork with DBI to create simultaneous db connections.
by htoug (Deacon) on Feb 08, 2005 at 11:20 UTC
    Each child must have its own connection to the database as the DBD (and possibly the database system) stores data in process-local storage. The poor database system will be very confused when it get requests from different processes in the same connection - each having possibly a different view of the state of the connection. The results are highly unpredictable, time and usage dependent and will cause your hair to fall out and your teeth to rot.

    Either let each child have its own connection, or have a common process (eg the 'mother') handle all the database traffic, and use something else to communicate with this 'db-server-process', some sort of RPC or shared memory or... YMMV.

    Just don't (repeat DON'T) share database connections between processes.

    Did I remember to say that you should not share connections between processes?

      Either let each child have its own connection, or have a common process (eg the 'mother') handle all the database traffic, and use something else to communicate with this 'db-server-process', some sort of RPC or shared memory or... YMMV.

      Okay, I'm having the same error and I'm getting the same with every other thread after the first thread finishes. Any thoughts? I'm doing just that myself; Global dbh in $this->{'dbh'} and each child defines it's own $this->{'_dbh-child'}. As soon as I call the first $this->{'_dbh-child'}->disconnect(), every other disconnect fails or spews out that error above, including the master {'dbh'}.

      -- philip
      We put the 'K' in kwality!

        The above can be omitted. Turns out the children where closing out the Global DBI object every time they where closing themselves out. It was causing the error. Moving the Global DBI call to a localized method fixed it right up.

        -- philip
        We put the 'K' in kwality!

Re: Using fork with DBI to create simultaneous db connections.
by sasikumar (Monk) on Feb 08, 2005 at 04:26 UTC
    Hi

    Its essential that you need to instantiate a new DBI object in all of your childs.



    Thanks SasiKumar

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://428884]
Approved by Errto
Front-paged by monsieur_champs
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (5)
As of 2022-10-07 16:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My preferred way to holiday/vacation is:











    Results (30 votes). Check out past polls.

    Notices?