Handling an open database connection after a fork?

I have an application that's using oracle (via cx_Oracle) to
log
events (among other things). It runs in multiple processes,
forking
new processes as it needs them.

I.e.

db = cx_Oracle.connect(.....)
cu = db.cursor()

[do various things, including sql inserts and commits]

if fork():
   # Parent wants to keep the existing database connection.
else:
   # Child wants a database connection.

So the question is - what should the child do to get a
database
connection? Can it just keep using the existing db & cu
variables? If
not, does it need to do anything special, or avoid doing
anything, in
order to not disrupt the parent processes use of those
variables?

      thanx,
      <mike
-- 
Mike Meyer <mwmmired.org>		http://www.mired
.org/consulting.html
Independent Network/Unix/Perforce consultant, email for more
information.
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

On 2008-01-11 17:35, Mike Meyer wrote:
> I have an application that's using oracle (via
cx_Oracle) to log
> events (among other things). It runs in multiple
processes, forking
> new processes as it needs them.
> 
> I.e.
> 
> db = cx_Oracle.connect(.....)
> cu = db.cursor()
> 
> [do various things, including sql inserts and commits]
> 
> if fork():
>    # Parent wants to keep the existing database
connection.
> else:
>    # Child wants a database connection.
> 
> So the question is - what should the child do to get a
database
> connection? Can it just keep using the existing db
& cu variables? If
> not, does it need to do anything special, or avoid
doing anything, in
> order to not disrupt the parent processes use of those
variables?

That depends on the database module you're using.

It may be enough to close all connections and reopen them
in the fork. In other cases, you need to reload the
database
module as well (e.g. if the module sets up a work
environment
that holds caches, etc.).

In general, it's better to avoid all this and only load the
module
for the first time after the fork (both in the parent and
child
processes).

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1,
Jan 12 2008)
>>> Python/Zope Consulting and Support ...       
http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
____________________________________________________________
____________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for
free ! ::::


   eGenix.com Software, Skills and Services GmbH 
Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre
Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

On Sat, 12 Jan 2008 14:14:03 +0100 "M.-A. Lemburg"
<malegenix.com> wrote:

> On 2008-01-11 17:35, Mike Meyer wrote:
> > I have an application that's using oracle (via
cx_Oracle) to log
> > events (among other things). It runs in multiple
processes, forking
> > new processes as it needs them.
> > 
> > I.e.
> > 
> > db = cx_Oracle.connect(.....)
> > cu = db.cursor()
> > 
> > [do various things, including sql inserts and
commits]
> > 
> > if fork():
> >    # Parent wants to keep the existing database
connection.
> > else:
> >    # Child wants a database connection.
> > 
> > So the question is - what should the child do to
get a database
> > connection? Can it just keep using the existing db
& cu variables? If
> > not, does it need to do anything special, or avoid
doing anything, in
> > order to not disrupt the parent processes use of
those variables?
> 
> That depends on the database module you're using.

As stated, cx_Oracle.

> In general, it's better to avoid all this and only load
the module
> for the first time after the fork (both in the parent
and child
> processes).

Not possible. Which is why I need to find out what to do to
make
oracle (via cx_Oracle) happy.

       <mike
-- 
Mike Meyer <mwmmired.org>		http://www.mired
.org/consulting.html
Independent Network/Unix/Perforce consultant, email for more
information.
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

Mike Meyer wrote at 2008-1-11 11:35 -0500:
> ... existing connection in forked children ...
>So the question is - what should the child do to get a
database
>connection? Can it just keep using the existing db &
cu variables?

This is very unlikely.

I have had severe problems with different systems (ZODB
connections,
LDAP connections). Not with Oracle connections, but probably
only
because I do not use Oracle.

When the child is forked, it inherits the connections from
the
parent -- but the protocols usually do not expect that
several
processes (parent and child) are using them asynchronously.

In a single process, locks are often used to synchronize
access to a shared connection from different processes --
but
normal locks do not work across different processes -- and
shared
memory semaphores are not that often used.

>If
>not, does it need to do anything special, or avoid doing
anything, in
>order to not disrupt the parent processes use of those
variables?

Open a new connection in your forked child.

It is not garanteed that this is sufficient.
For the ZODB, I have to take additional precautions.

I finally abondoned this approach completely (because, LDAP
was used deeply in my system and I had no control over the
creation
of new connections) and am now using "fork+exec".



-- 
Viele Grüße
Dieter

Tel: 06881-7327 (Festnetz) oder 06881-5590036 (Internet)
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

On Sun, 13 Jan 2008 13:10:48 +0100 Dieter.MaurerHaufe.de
wrote:

> Mike Meyer wrote at 2008-1-11 11:35 -0500:
> > ... existing connection in forked children ...
> >So the question is - what should the child do to
get a database
> >connection? Can it just keep using the existing db
& cu variables?
> This is very unlikely.

That's what I expected.

> When the child is forked, it inherits the connections
from the
> parent -- but the protocols usually do not expect that
several
> processes (parent and child) are using them
asynchronously.

Right. The question is, what's the right way to handle the
connection
on the child side of things?

> >If
> >not, does it need to do anything special, or avoid
doing anything, in
> >order to not disrupt the parent processes use of
those variables?
> Open a new connection in your forked child.

Obvious. What do I do with the old one? I started out by
explicitly
closing it, but that seems to make oracle unhappy (internal
errors of
various kinds).

> I finally abondoned this approach completely (because,
LDAP
> was used deeply in my system and I had no control over
the creation
> of new connections) and am now using
"fork+exec".

Oddly enough, fork+exec doesn't make the problem go away,
just
provides another possible solution. Open fd's can either be
closed on
exec, or not. Hopefully, it's closed because the python
objects that
referred to it are lost across the exec. I'm willing to
believe that
should work. So how do I simulate what happens on exec
without
actually doing the exec?

     Thanks,
     <mike
-- 
Mike Meyer <mwmmired.org>		http://www.mired
.org/consulting.html
Independent Network/Unix/Perforce consultant, email for more
information.
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

Mike Meyer wrote at 2008-1-13 14:07 -0500:
> ...
>On Sun, 13 Jan 2008 13:10:48 +0100 Dieter.MaurerHaufe.de
wrote:
> ...
>> >If
>> >not, does it need to do anything special, or
avoid doing anything, in
>> >order to not disrupt the parent processes use
of those variables?
>> Open a new connection in your forked child.
>
>Obvious. What do I do with the old one? I started out by
explicitly
>closing it, but that seems to make oracle unhappy
(internal errors of
>various kinds).

Your best bet is to leave it alone.

If you are lucky (!) then this will be sufficient.
As I mentioned, for a ZODB it was not sufficient --
because the child intercepted
messages destined for the parent and eat them away.

If you face similar problems, give up and "exec"
in the forked
process.

>> I finally abondoned this approach completely
(because, LDAP
>> was used deeply in my system and I had no control
over the creation
>> of new connections) and am now using
"fork+exec".
>
>Oddly enough, fork+exec doesn't make the problem go
away, just
>provides another possible solution.

Maybe, you state precisely what problem you have.

Usually, it is not a problem that the execed child has some
open fds that it does not need.

When it is, you can explicitely close all connections, e.g.
before you exec.

>Open fd's can either be closed on
>exec, or not. Hopefully, it's closed because the python
objects that
>referred to it are lost across the exec.

No, they remain open (as the complete memory state is
replaces --
there is no way, Python can intercept the
"exec").

>I'm willing to believe that
>should work. So how do I simulate what happens on exec
without
>actually doing the exec?

You do the "exec" -- you cannot similate it
(unless you are
using deep and very low level system magic, not directly
supported by
Python).



-- 
Dieter
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig