Pro*C Question: Forking Processes
Frank-Michael Zimmer
Well, while I'm at it:
I have a serious program with a Pro*C program that is to connect to the
database, and then fork via the unix system call.
Does anybody know what _exactly_ will happen, especially if one of the
processes terminates while the other one is to keep on running?
I have experienced the strangest effects, like pipes misteriously
refilling themselves, even if they have been emptied _before_ the fork,
and other strangenesses...
Well, here's a problem to earn you the title "Oracle Guru"... ;)
Thanks,
Frank-Michael Zimmer
Brian P. Mac Lean
I don't think you should be connecting to the db prior to the fork. Logically
it's not sound so I have always avoided it. If it's necessary to connect then
fork, do a connect/disconnect/fork/connect parent & child. To disconnect I believe
(and the sql documentation states) you use "EXEC SQL COMMIT WORK RELEASE;"
Oracle 7.3 OCI supports threads with multiple connections but without a working
example I bet it would be a nightmare to develop.
I see by your other posting in this group that you are having trouble with the
"EXEC SQL COMMIT WORK RELEASE;" statement which in effect is related to my
response here. Do you have any information on return codes after the commit and after
the reconnect. Just an idea....can you slip a "select 'x' from dual;"
statement into a test version of the program between the commit and reconnect
and see what error code is returned?
Good luck and I'm looking forward to hearing the solution.
\\|//
(0-0)
+-----oOO----(_)-----------+
| Brian P. Mac Lean |
| Database Analyst |
| brian....@teldta.com |
| http://www.teldta.com |
+-------------------oOO----+
|__|__|
|| ||
ooO Ooo
Scott Urman
In article <3225CDCE...@dkrz.de>, Frank-Michael Zimmer <zim...@dkrz.de> writes:
|> Well, while I'm at it:
|>
|> I have a serious program with a Pro*C program that is to connect to the
|> database, and then fork via the unix system call.
|> Does anybody know what _exactly_ will happen, especially if one of the
|> processes terminates while the other one is to keep on running?
|> I have experienced the strangest effects, like pipes misteriously
|> refilling themselves, even if they have been emptied _before_ the fork,
|> and other strangenesses...
This can be summed up by one simple piece of advice:
Don't fork while connected to Oracle. Period.
If you do, you can get various problems, as you have observed. When you connect
to Oracle, a shadow process is started. If you fork, then you have two user
processes, one shadow process. Oracle doesn't expect this. The two user
processes can interfere with each other very easily. If one exits, then the
connection could be lost from the other. SQL statements from each could
interfere with each other. Et cetera, et cetera. The simplest thing to do is
disconnect, then fork, then have the parent and child each establish their own
connections.
|>
|> Well, here's a problem to earn you the title "Oracle Guru"... ;)
|>
|> Thanks,
|> Frank-Michael Zimmer
------------------------------------------------------------------------
Scott Urman Oracle Corporation sur...@us.oracle.com
------------------------------------------------------------------------
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
Oracle Corporation"
------------------------------------------------------------------------
Donne You
In article <3225CDCE...@dkrz.de> Frank-Michael Zimmer <zim...@dkrz.de> writes:
>Well, while I'm at it:
>
>I have a serious program with a Pro*C program that is to connect to the
>database, and then fork via the unix system call.
>Does anybody know what _exactly_ will happen, especially if one of the
>processes terminates while the other one is to keep on running?
>I have experienced the strangest effects, like pipes misteriously
>refilling themselves, even if they have been emptied _before_ the fork,
>and other strangenesses...
>
>
>Thanks,
> Frank-Michael Zimmer
I think this is a UNIX question rather than Oracle question. the two
processes are independent execept for a parent-child relationship. if
child terminates, parent is not affected unless it's waiting on the
child. if parent exits child becomes orphaned.
Don
Frank-Michael Zimmer
Well, thanks a lot for the comments and suggestions - I think I should
specify my problem a bit more:
The program is written in Pro*C and runs on Solaris, with Oracle version
7.1.4.
The idea is of something like a demon that wait at the lower end of a
pipe to accept orders. The orders are inserted into the upper pipe end
by routines from a package that resides in the database. If you have an
order, just call the package procedure, it will turn it into a message,
throw that down the pipeline, and the 'demon' will take care of its
execution.
To execute the order, the 'demon' has to spawn a child, for the orders
might take long to execute (fetch (large!) datasets from the database
and write them to a directory - the data may well be migrated into a
unitree system), and doing the work himself will block him for too long.
Also, the 'demon' is expected to deliver some kind of receipt upon
completion of his task. The method implemented calls another package
function. The important point is just that whoever writes the receipt
has to be connected to Oracle.
The _real_ problems ;) now are:
1) Who writes the receipt, and when?
Forking while connected means that Oracle sees the last of the two
processes that used the database connection as the legitimate owner.
Usually, the son writes his receipt and terminates, thereby killing the
father's connection, too.
On the other hand, if the son does not use his connection but hands his
data to the father to write the receipt and simply terminates
afterwards, the connection is ok, but the father has to keep track of an
undetermined number of children. I did this by having the father use a
list of his sons' data, with the effect that a very quickly terminating
son will cause the father to remove his son from the list before he has
entered him into it! So this does not work, either.
2) To disconnect, or not to disconnect?
Disconnecting, forking and then connecting again so each of the two
processes had his own session is a good idea for a solution, but there
have been problems in the praxis: After the sons terminate, the father
receives a SIGCHLD signal to inform him that there may be a data
heirloom for him, to be collected by the system call wait(). The first
dead child is removed all right, but any further children that die
suffer the grisly fate of a unix zombie... ;) This happens
independently of the actual actions the children take with Oracle. I
suspect that there is a problem with the father's waiting at the pipe
for a message to pop out (a blocking procedure call) being interrupted
by the SIGCHLD signal and entering the signal handler.
As this mechanism works well when Oracle is not involved, I doubt that
it's simply a unix problem.
Ah, well. If you have read this far, I thank you for your patience and
would appreciate your thoughts on the subject, just like any ideas or
suggestions.
Have a nice weekend,
Frank-Michael :D
nchintala
Frank,
I have a similar (same???) problem.
Oracle 7.2.3 on Solaris 2.5
From a Pro*C code if I get an oracle error, I do a connect to oracle
again. When this happens, the child (oracle shadow process) becomes
defunct (ps -ef shows this info). If I do n connects then there are n
defunct child processess. These defunct process die only when the
parent dies. Contacted Oracle support. They said that this is a BUG
in 7.2.3. They said the problem is with the SQL*NET V2 bequeath pipe
driver.
They suggested the following workarounds.
1. To include the signal(SIGCHLD,SIG_IGN) before connecting -- this
does not work. On solaris SIGCHLD is ignored by default (check
man -s 5 signal).
2. Set the TWO_TASK variable to P: (this uses the SQL*NET V1 pipe
adapter) -- This WORKS.
I still don't know if setting the TWO_TASK=P: has any performance
impact. What is the difference between the SQL*NET V2 bequeath pipe
adapter and SQL*NET V1 pipe adapter?
I can mail you the info about this BUG if you need it.
ANY SUGGESTIONS.
Thanks a lot for any help. Please email also.
Naren Chintala
na...@mink.att.com
nchi...@mink.att.com
Suresh Easwar
on solaris 2.x use
sigset(SIGCHLD, SIG_IGN);
the signal system call (if i remember right) mimics the old Unix SVR3
semantics. sigset uses SVR4 semantics.
at any rate, even if SIG_IGN is the default for SIGCHLD, you must
explicitly call it to prevent the creation of zombies. if you do not
expilicitly ignore SIGCHLD, then upon the termination of the child,
the child becomes a zombie and the parent (if still alive) is expected
to execute wait() and clean up the zombie child.
Suresh
nchintala wrote:
> I have a similar (same???) problem.
>
> Oracle 7.2.3 on Solaris 2.5
>
> From a Pro*C code if I get an oracle error, I do a connect to oracle
>
> ... rest deleted ...
Brian P. Mac Lean
Would somethig like this work:
+-----------------------------------------------------------+
| USER CONNECTIONS TO THE DB |
+--------------+-----+-----+-----+-----+--------------------+
^ ^ ^ ^ ^
| | | | |
| | | | |
V V V V V
+--------+-----+-----+-----+-----+-------------+
| ORACLE KERNEL +-<-+
+---------------------+------------------------+ |
| |
DB | |
PIPE | |
V |
+------------------+---------------------+ |
+------------->+ ORDER SERVER PARENT/LISTENER | |
| +--+---------------+-------------------+-+ |
+----+----+ | | | |
| UNIX | | UNIX | | |
| MESSAGE | | PIPES | | |
| QUEUE | | 1 PER CHILD | | |
+----+----+ V V V |
^ +--+---------------+-------------------+-+ |
+--------------+ 1 to N ORDER SERVER CHILDREN +-<----+
+----------------------------------------+
.The ORDER SERVER PARENT/LISTENER process is started. It creates a UNIX MESSAGE QUEUE
forks 1 to N ORDER SERVER CHILDREN each with its own UNIX PIPE, connects to the
ORACLE KERNEL, creates a DB PIPE, then listens and waits for an order on same.
.Each ORDER SERVER CHILD, connects to the ORACLE KERNEL, connects to the UNIX MESSAGE QUEUE
and writes a message to it identifying who they are and that they are ready to service
an order.
.A USER CONNECTION TO THE DB creates an order and the PL/SQL block writes said to the
DB PIPE.
.The ORDER SERVER PARENT/LISTENER reads the message from the DB PIPE and reads the first message
from the UNIX MESSAGE QUEUE. He now has an order and knows who is available to service
this order. He writes the order to the UNIX PIPE of the available ORDER SERVER CHILD
and goes back to listening on the DB PIPE. If by chance when he reads an order from the
DB PIPE and finds no available message/child on the UNIX MESSAGE QUEUE, he can be programmed
to sleep/loop/read on the UNIX MESSAGE QUEUE or.........
.Each ORDER SERVER CHILD does a blocking read on it's UNIX PIPE. When an order is read, it
completes it as necessary, then writes a message identifying who they are and that
they are ready to service another order on the UNIX MESSAGE QUEUE.
There are a lot of things I left out here but the general concept to solid. I hope you
find that it's what you need.
otie...@freenet.edmonton.ab.ca
I missed the start of the post but
FYI
Oracle client processes (Pro*C , sqlplus etc) fork off and have to use
SIGPIPE and/or SIGCHLD only on non listener loopback connections ie
v1 P: (default until 7.1.6 or thereabouts)
and v1 beq (default after 7.1.6 or thereabouts)
so if you use v2 tcp , v2 ipc what you do with SIGCHLD and your own forked
processes is your own problem....
just my 2 cents,
Turloch O'Tierney
email otie...@freenet.edmonton.ab.ca
Almut Herzog
Hi,
I had a similar problem and instead of forking processes I used threads.
Pro*C 2.2 is modified to handle threads better than 2.1 but even with
Pro*C 2.1 it solved my problems. Threads are neat!
--
Almut Herzog
SECTRA-Imtec AB e-mail: al-...@sectra.se
Teknikringen 2 voice: ++46 13 235221
583 30 Linkoping fax: ++46 13 212185
SWEDEN