[DB-SIG] Python 2.0 DB Api - Threading and Transactions not known until connected

M.-A. Lemburg mal@lemburg.com
Thu, 26 Aug 1999 16:05:49 +0200


Brad Clements wrote:
> 
> On 26 Aug 99, at 9:48, M.-A. Lemburg wrote:
> 
> > Well basically, ADO should do all this for you. Here is what the
> > ODBC docs say about transactions:
> 
> According to the Ole DB spec, handling of transactions is pretty much
> up to the DB provider. My interpretation is that I would get best results
> using explicit  begin/commit calls.
> 
> Again, I'm *not* targetting ODBC at all, though you *can* use ODBC via
> OLE DB, I suspect that would be uncommon since it's double layers.

I'm just quoting ODBC stuff because that's where my main
experience lies. I know of the many quirks that can happen
to you in that area -- mxODBC has been out there for almost two
years now.
 
> > Here is what I would suggest:
> > · if the datasource has transactions, put the connection into
> >   manual commit mode (otherwise stay in auto commit mode and
> >   disable the .rollback method)
> > · whenever opening a connection which does support transactions,
> >   do a BeginTransaction
> > · if the user closes a used connection without having done a .commit()
> >   or .rollback(), i.e. if there still is an open transaction,
> >   the interface should try to perform an implicit .rollback();
> >   this is not necessary for auto commit mode connections
> 
> This sounds good but my problem with this is:
> 
> 1. I call ADO.BeginTransaction when issuing a cursor

No, you call it when you create the connection object to inplicitly
start a new transaction.

> 2. I would call ADO.CommitTrans if the  user called connection.commit()
> 3. I would call ADO.RollbackTrans if the user called connection.rollback()

Right.

> 4. Since the 2.0 DB spec says "auto-commit must be initially disabled",
> then I'll never call ADO.CommitTrans unless the user calls
> connection.commit(). Therefore everything the user does on the
> connection is one huge transaction, right?

No, you call CommitTrans when the user calls connection.commit()
and RollbackTrans when he calls connection.close() or deletes
the connection object.

Plus, you call BeginTransaction after every call to connection.commit()
and connection.rollback() to implicitly start a new transaction.
 
> Or, should I commit when the cursor object is destroyed? But you say
> perform a rollback if they didn't call commit.
> 
> This is where my confusion lies. I need to support two types of users,
> those that don't know anything about transactions and hence won't call
> commit() (and wouldn't have called begintrans() if we had that function).
> And those that need explicit transaction control, such as for Zope 2. In
> Z2, I really do need to have an explicit begintrans, commit and rollback.

Transaction control should be kept as simple as possible. This
is what the standard SQL scheme implements -- you don't need an
explicit begintransaction call. Believe me, it has worked for
years like this ;-)
 
> > Perhaps we should add a new section on this topic to a new
> > 2.1 version of the DB API.
> 
> ADO (aka Ole DB) has a ton of new stuff that could be very useful,
> though may be too specific to ms products.
> 
> I think perhaps a clarification of how transactions are handled both when
> using, and not using connection.commit().
> 
> I'd also like to see a connection.begintrans() for completeness, though
> perhaps someone can show me how I've missed the boat on this issue
> entirely. I wouldn't doubt it.
> 
> ;-)
> 
> ps. I'll be working on the Zope 2 DA at the end of the week. I suspect I
> will find that I *must* have a connection.begintrans() implemented to
> support it.

If you do, then something in the design of Zope DAs is broken.
Providing an explicit .begintrans() method would introduce
undefined processing states, e.g. 

connection.begintrans()
c = connection.cursor()
c.execute(...)
del c
connection.commit()
# This is where the problem starts...
c = connection.cursor()
c.execute(...)
# ... and this is where the problem ends :-)
connection.begintrans()
...

-- 
Marc-Andre Lemburg
______________________________________________________________________
Y2000:                                                   127 days left
Business:                                      http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/