On Mon, Sep 20, 2010 at 12:49 PM, M.-A. Lemburg <m...@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 _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
