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

[Brad Clements]

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.


> 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
2. I would call ADO.CommitTrans if the  user called connection.commit()
3. I would call ADO.RollbackTrans if the user called connection.rollback()
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?

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. 


> 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.



Brad Clements,                bkc@murkworks.com   (315)268-1000
http://www.murkworks.com                          (315)268-9812 Fax
netmeeting: ils://ils.murkworks.com               ICQ: 14856937