Database Sessions and Transactions

[Paul Boddie]

"DeepBlue" <DeepBlue at DeepBlue.org> wrote in message news:<tt93sp992i9j6c at corp.supernews.com>...
>
> Say you have a database component.
> Database.StartTransaction 'packages' a transaction.  However, I can see now
> that we do not need this.  The question is:  What are the advantages of
> StartTransaction on a database connection?  I can see that in Python one
> issues sql commands to a database and one can 'envelope' them in some form
> of a transaction.  So instead of asking where is the transaction in Python,
> I have turned to think about the use of StartTransaction in
> Delphi.............. [continued below]

Explicit transaction marking was something that someone was interested
in, with respect to the DB-API. As I understand it, with many current
DB-API compliant modules (there's always an exception), transactions
are typically associated with the opening and closing of connections,
along with judicious use of the commit and rollback methods. You can
therefore think of opening a connection as implicitly starting a
transaction, closing a connection as implicitly rolling back a
transaction, and committing and rolling back as doing exactly what
they say, but also starting a new transaction implicitly.

> But to summarize:  when I posted my question I was thinking in Delphi 5
> terms where one can use a Session component or when a session component is
> created by default for every database connection.  A session manages an
> individual database connection.  So when many users are using the same
> program at the same time, every user is associated with a session to ensure
> that multiple connections are kept independent from one another.

If sessions are mapped to connections on a one-to-one basis, then
perhaps session objects are just wrappers around connections. However,
if sessions are just separate "workspaces" which can share a single
connection, then I would suspect that some means of "enhanced"
transaction handling is in use.

It wouldn't surprise me if certain database systems permitted named
transactions which operate independently of each other. However, it's
a while since I've played with any to be able to say. This could
provide the means to share connections in the way you imply, however.

> My concern is what happens when many people log into a database through a
> Python cgi program (I plan to use DCOracle2), how are these connections
> managed so that we can ensure the integerity of independence of different
> transactions performed.  In Python:  is the connection created with every
> logon denotes a separate independent session to ensure integrity of data and
> transactions?

With any "decent" database system, the answer is "yes". Of course,
going to more advanced levels and attempting things like connection
pooling or sharing, poses many more difficult questions; it would be
great to see the Python community tackle these kinds of issues -
they're certainly more important than conditional expressions to me,
anyway.

Paul