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

[M.-A. Lemburg]

Brad Clements wrote:
> 
> On 25 Aug 99, at 18:44, M.-A. Lemburg wrote:
> 
> > I'd suggest setting the module level variable to the lowest
> > level and then add a per connection attribute as you indicated.
> 
> Ok.
> 
> > When the cursor is created an implicit transactions start is
> > performed. This is normal SQL behaviour. You don't need to do
> > a .commit() to start a new transaction.
> 
> True but, I need to tell ADO that I want to support transactions. If the
> user never calls commit, how do I know that I should enable
> transactions?

If transactions are supported on the connection you should
always enable them -- they are *very* useful to handle error
conditions in DB code and not having them calls for an extra
level in DB abstraction or other more direct means of restoring
modified data.

> > Auto-commit is enabled per default for ODBC. Since it renders
> 
> ADO doesn't have to use ODBC, in fact I suspect most folks won't be
> using the ODBC driver via ADO.
> 
> The "Ole DB SQL Provider" and also, I think, the Oracle one, assume
> that transactions are not used unless I turn them on.

Hmm, seems to be a general MS style to run in auto-commit mode
(not only for database connections ;).
 
> If I just enable transactions, then do a  BeginTransaction when issuing a
> cursor, how do I know when to do a Commit() since auto-commit is
> supposed to be disabled, and the user may never call
> connection.commit()?

Well basically, ADO should do all this for you. Here is what the
ODBC docs say about transactions:
"""
Performing Transactions

     In auto-commit mode, every SQL statement is a complete transaction, which is automatically
     committed. In manual-commit mode, a transaction consists of one or more statements. In
     manual-commit mode, when an application submits an SQL statement and no transaction is
     open, the driver implicitly begins a transaction. The transaction remains open until the
     application commits or rolls back the transaction.
"""

Note that transactions are connection based, not cursor based.
OTOH, the only way to do anything on a connection is by opening
the first cursor. So the appropriate action would probably be
to do the BeginTransaction call when the connection is created
or when the first cursor is created.
 
> > Make the .rollback() method dynamically defined, i.e. have it
> > disabled on connections that do not support transactions. An explicit
> > "begin transaction" is not needed since this always happens
> > implicitly when you call connection.cursor() or cursor.commit()/
> > .rollback().
> 
> Er, well, I can't find in the 2.0 spec anywhere that it says that
> constructing a new cursor automatically begins a transaction.

It doesn't say this anywhere because SQL itself defines the behaviour.
I have to correct myself here: opening a cursor does not *always*
mean an implicit BeginTransaction. This is only true for the
first cursor opened on the connection. And it's connection.commit()/
.rollback(), not cursor.commit()... wasn't my day yesterday, I guess.
 
> My DA has to explicitely call the underlying begin, commit and rollback
> as appropriate. Either I'm blind, or the DA 2.0 spec doesn't say enough
> about transactions, automatic or otherwise, for me to know when I
> should begin a new transaction.
> 
> Can anyone point to archived discussion that talks about this subject, I
> couldn't find any.

I guess there wasn't any...
 
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

Perhaps we should add a new section on this topic to a new
2.1 version of the DB API.

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