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

Hernán Martínez Foffani hernan.foffani@iname.com
Fri, 27 Aug 1999 12:53:35 -0300


To get things right (or to add confusion... :-)
[we: specific DB module developers.
 users: programmers calling DBApis from their python app]

A) IF the DBMS requires explicit "Begin Transaction"

1. we should call BeginTransaction when the connection object is created.
2. we should call BeginTransaction after every Commit or
RollbackTransaction.
3. we should call RollbackTransaction when closing/deleting the connection
object.

B) IF the DBMS does not require explicit "Begin Transaction"
1. we should call RollbackTransaction when closing/deleting the connection
object.

Being A3 and B1 worth for sanity reasons.

In this way users doesn´t have to special things, and the same program works
across
implementations.
The DBApi follows ANSI SQL in this aspect which, as Marc-Andre wrote, "you
are always
inside a transaction".
The "Begin Transaction" issue was "raised" by DBMS because they try to
"save" resources
(transaction processing is expensive)
If users try to "save resources" then they must turn AutoCommit ON. In this
mode users
doesn't use explicit Commit or Rollback methods.

For Brad now,
> I think we are getting somewhere.
>
> You said:
>
> 1. When issuing a cursor, I should call the underlying ADO.BeginTrans
NO, when creating the connection object. BUT, I don´t know ADO!

> 2. I should never auto-commit, so the user must call
> connection.commit()
YES.

> 3. If the user never calls commit, I'll rollback all of their
> changes when
> they close th connection object
You ALLWAYS rollback when closing. If the user had previously commited,
DBMS won't rollback any transaction.

>
> 4. Hence, users that never call commit(), can not really use the DB
> because none of their changes will ever be saved.
well, they CAN use it. As a matter of fact it is a trick that many
programmers, me included, use: You can "try and see (and time!)"
different algorithms in production environment safely.

>
> Is this correct? Should I therefore assume that all DB users will
> ALWAYS call connection.commit (or  rollback), and that NO ONE will
> ever try to use the DB without using connection.commit?
Don't forget that users can turn AutoCommit ON.

Regards,
Hernán.

PS. Hope I got it right!