Using fork with DBI to create simultaneous db connections.

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. Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l]
Re^2: Using fork with DBI to create simultaneous db connections. by thor (Priest) on Feb 08, 2005 at 04:26 UTC
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`	[reply]
Re^3: Using fork with DBI to create simultaneous db connections. by etcshadow (Priest) on Feb 08, 2005 at 05:34 UTC
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>` [download] 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` [download]	[reply] [d/l] [select]
Re^4: Using fork with DBI to create simultaneous db connections. by thor (Priest) on Feb 08, 2005 at 12:46 UTC
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?	[reply]
Re^2: Using fork with DBI to create simultaneous db connections. by guice (Scribe) on Mar 23, 2005 at 14:55 UTC
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!`	[reply]
Re^3: Using fork with DBI to create simultaneous db connections. by guice (Scribe) on Mar 23, 2005 at 21:48 UTC
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!`	[reply]
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	[reply]


Think about Loose Coupling
	PerlMonks