[Zope] Database Connections for ZOPE 1.10.3

[Jim Fulton]

Theodore Patrick wrote:
> 
> ZOPE 1.10.3
> 
> I have two separate database connections to an oracle database. The
> connections are in separate folders and are attached to the same ORACLE
> database.
> 
> Are there any hidden do's and don'ts for multiple database connections?

The transaction machinery in Oracle, or any database that supports
transactions can lead to a deadlock situation if you have multiple
connections to the same database.  For example, suppose you have

  - Connections C1 and C2 to database D,

  - SQL method S1 using C1:

       insert into data values (...)

  - SQL method S2 using C2:

       select * from data

  - In a web request, you invoke a method (e.g. a DTMLMethod or
    an ExternalMethod) that calls S1 and then calls S2.

Because S1 and S2 use separate Oracle connections, they
participate in separate Oracle transactions. Let's call these
transactions T1 and T2.  

The operation done by S1 in T1 will lock (at least some part of)
the data table until T1 completes.  The operation done by S2 in T2
must block, since it's result would be affected by the completion, 
or non-completion of T1.  Therefore, the thread executing the request
will block indefinately waiting for T1 to complete.

This makes using multiple connections to the same database in the same 
request rather unattractive. :)

> Are the connections actually one in the same?

No.

> Are there any platform issues where this is a bad idea(Windows ODBC or LINUX
> - NET8)?

I don't know if there are any additional platform issues.

Jim

--
Jim Fulton           mailto:jim@digicool.com   Python Powered!        
Technical Director   (888) 344-4332            http://www.python.org  
Digital Creations    http://www.digicool.com   http://www.zope.org    

Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission.  Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.