[Zope-DB] question on zope DAs

[Matthew T. Kromer]

Tom Jenkins wrote:

>Hello all,
>We're trying to track down a problem that may be related to a database
>adapter (DA).  We've looked at a couple of different DA's and noticed
>discrepancies between them in the _begin method.
>

[...]

>
>looks like zpsycopg rollbacks any uncommited transactions before
>starting another, both zpopy & DCOracle2 ignore calls to _begin,
>zpygresql issues a begin transaction call (?) but gvibda is
>interesting.  It does a count query against a known database to see if
>the connection is still up; if not it will create another connection and
>cursor.
>
>Which is more correct?  The interbase (gvibda) is interesting as it
>looks like it will reconnect if it can't get a response.
>


We've had some interesting discussions about RDBMS support w.r.t. the 
Zope transaction machinery.  The problem (as you notice) has to do with 
trying to synchronize transactions.

Generally, there's going to be some kind of commit() function called 
when Zope commits a transaction, or an abort() when Zope rolls it back. 
 OK, so far so good... :)  A side effect of this means that as long as a 
connection object is not shared between threads (and thus between 
transactions) except via a commit/abort boundary, you dont have to be 
precise about starting a new transaction (IMHO -- I dont know that all 
DB systems dont want an explicit "transaction start" mechanism to be 
invoked.)

The kicker is that rollback is really two-phase in Zope, and that some 
RDBMS DAs may do the wrong thing, or the right thing at the wrong time. 
 Here's a message URL that was referred to in an e-mail recently:

    http://aspn.activestate.com/ASPN/Mail/Message/525729

in which some similar issues are discussed.  The gist of the argument is 
that the DA shouldnt commit data until the ZODB has done its first phase 
commit (ie no conflicts or errors), and instead should work on the 2nd 
half.  Aborts though, need to abort everything, including ZODB.

Techically, the DA should support a two-phase commit protocol, where it 
gets a "prepare" to commit and a final "commit."  I dont know of any 
which do this, though.

And as an aside, database connection/recovery for detecting stale or 
lost connections is very ad hoc.   Its something that I want to see 
changed sometime.  Some DA's (ZOracleDA is one) just try to open the 
connection again on an error.

I'd also endorse a notion of setting transaction ids for RDBMS systems 
which support it, so that a single connection object can participate in 
multiple transactions.  This is not a high priority in Fredericksburg, 
though.