[DB-SIG] When must transactions begin?

[Andy Dustman]

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?

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

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