[Zope] FW: [Zope] Database Connections for ZOPE 1.10.3

[Marten Quadland]

Jim,

Regarding the indefinite block on data due to multiple requests on the same
table, I have two questions:

1) Does it matter if the Database connection is on two different Machines or
on machine?

2) Where does this problem occur, the single threaded ODBC connection,
Oracle, Zope, or where?  If I understand Oracle correctly, as soon as the
insert on T1 has been commited, then the lock will be released on the page
and the select from the T2 query should run without a problem.

Quad
-----Original Message-----
From: Jim Fulton [mailto:jim@digicool.com]
Sent: Tuesday, August 10, 1999 8:07 AM
To: Theodore Patrick
Cc: 'zope@zope.org'
Subject: Re: [Zope] Database Connections for ZOPE 1.10.3


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.

_______________________________________________
Zope maillist  -  Zope@zope.org
http://www.zope.org/mailman/listinfo/zope

(To receive general Zope announcements, see:
http://www.zope.org/mailman/listinfo/zope-announce

For developer-specific issues, zope-dev@zope.org -
http://www.zope.org/mailman/listinfo/zope-dev )