[DB-SIG] When must transactions begin?

[M.-A. Lemburg]

Andy Dustman wrote:
> On Mon, Sep 20, 2010 at 12:49 PM, M.-A. Lemburg <mal at egenix.com> wrote:
>>
>>
>> Randall Nortman wrote:
>>> PEP 249 says that transactions end on commit() or rollback(), but it
>>> doesn't explicitly state when transactions should begin, and there is
>>> no begin() method.
>>
>> Transactions start implicitly after you connect and after you call
>> .commit() or .rollback(). They are not started for each statement.
> 
> Did the transaction exist before the first statement, or did executing
> the statement cause it to be created? Doesn't matter. Or does it?

The above is the explanation on the logical level (and a lot easier
to understand, IMHO, since you don't have to explain the existence of
non-transactional behavior on a connection). The implementation can
optimize this in whatever way is necessary or required by the backend.

I just wanted to make the point that a transaction is not started
for each SELECT you execute on the connection.

>>From a server (implementation) perspective, I am pretty sure that
> executing a statement starts a transaction. Otherwise you would have
> open transactions for an extended period of time, even when the client
> has not executed statements, and that has implications for
> concurrency. And this is an effect that *would* be noticeable by
> clients.
> 
> How to test this: Connect to the database with two clients. In one,
> insert a row and commit. In the other, try to select them. If
> transactions begin at connect time, the selecting client should *not*
> be able to see them, because they didn't exist at the start of the
> transaction.
> 
> Test two: Connect to the database with two clients. In one, select
> some rows from a table, but don't commit or rollback. In the other,
> insert a row and commit. The first client should not be able to see
> the inserted row until it does a commit or rollback, even though it
> hasn't modified any data.
> 
> The above of course depends on your isolation level, but I typically
> get a bug report/question every few months from someone who has a loop
> where they try to select newly inserted records by another client, and
> they never show up, and it's because they never closed their
> transaction. (MySQLdb with InnoDB tables)

True. The various isolation levels can have interesting side-effects
on what you see in your application. This is database specific, though,
and cannot be dealt with in the DB-API. I can add a footnote, though,
if you think that would help.

> In MySQL, some statements (primarily DDL, i.e. CREATE TABLE and pals)
> implicitly commit a transaction.

Yep. Other databases insist that you do this explicitly and refuse
to run any other statement until you do (IIRC, PostgreSQL is one such
database). Yet other databases don't have such limitations and even
allow dropping tables in a transaction without affecting the
database until you commit the change (e.g. MaxDB).

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Sep 20 2010)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/