When must transactions begin?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

When must transactions begin?

Randall Nortman-10
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.  I think the implication is that transactions begin
on the first execute(), but that's not explicitly stated.  At least
one driver, pysqlite2/sqlite3, does not start a transaction for a
SELECT statement.  It waits for a DML statement (INSERT, UPDATE,
DELETE) before opening a transaction.  Other drivers open transactions
on any statement, including SELECT.

My question for the DB-SIG is: Can I call it a bug in pysqlite2 that
it does not open transactions on SELECT?  Should the spec be amended
to make this explicit?  Or are both behaviors acceptable, in which
case perhaps a begin() method needs to be added for when the user
wants control over opening transactions?

TIA,

Randall Nortman
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: When must transactions begin?

M.-A. Lemburg


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.

> I think the implication is that transactions begin
> on the first execute(), but that's not explicitly stated.  At least
> one driver, pysqlite2/sqlite3, does not start a transaction for a
> SELECT statement.  It waits for a DML statement (INSERT, UPDATE,
> DELETE) before opening a transaction.  Other drivers open transactions
> on any statement, including SELECT.
>
> My question for the DB-SIG is: Can I call it a bug in pysqlite2 that
> it does not open transactions on SELECT?  Should the spec be amended
> to make this explicit?  Or are both behaviors acceptable, in which
> case perhaps a begin() method needs to be added for when the user
> wants control over opening transactions?

I should probably add a note to PEP 249 about this.

--
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/
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: When must transactions begin?

Andy Dustman
On Mon, Sep 20, 2010 at 12:49 PM, M.-A. Lemburg <[hidden email]> 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  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: When must transactions begin?

M.-A. Lemburg
Andy Dustman wrote:

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