Re: DB API 2.0 and transactions

You might have spotted a fairly nasty bug there!

Christopher J. Bottaro wrote: 
Hi,
Why is there no support for explicit transactions in the DB API?  I mean
like transaction() to start the trans and commit() and rollback() would end
the trans or something.


To quote from Date & Darwen "A Guide to the SQL Standard, 4th ed.":

"An SQL-transaction is initiated when the relevant SQL-agent executes
a 'transaction-initiating' SQL statement (...) and the SQL-agent does
not already have an SQL transaction in progress. Note, therefore, that
(...) SQL-transactions can't be nested. Note too that transaction
initiation is always implicit--there is no explicit 'BEGIN TRANSACTION'
statement."

The Python DB-API standard matches the SQL standard, and that seems
reasonable.

This gets weird when using the Python DB API to interact with Postgres
because a transaction gets started in 3 places:  connection, commit,
rollback.


That's not how it's supposed to work! Are you sure that you don't
implicitly start transactions by SELECTs etc?

PostgreSQL violates the SQL standards by running in autocommit mode
unless you explicitly perform its non-standard BEGIN command. If you
are right about the behaviour you describe, the PostgreSQL binding
for Python that you use may have taken the easy route, and performs
a "BEGIN" on connect and after every commit or rollback.

If so, this is a serious bug, and should be reported as one. The correct
thing to do is to insert the BEGIN just before the first SQL statement
that is affecting transactions. Of course, this means that the binding
needs to keep track of transaction state, and this makes it a little
bit more complicated. You'd need something like this in the binding:

class connection:
    def __init__(...):
        ...
        self.inTxn = False

    def commit(...):
        ...
        self.inTxn = False

    def rollback(...):
        ...
        self.inTxn = False

    def execute(...):
        ...
        if not self.inTxn:
            perform the BEGIN command against the backend
            self.inTxn = True
        ...

Actually, this isn't perfect either, because not all SQL commands
(should) initate transactions, but it's a lot closer to what we want.

This bug has implications far beyond timestamps. Imagine two transaction
running with isolation level set to e.g. serializable. Transaction A
updates the AMOUNT column in various rows of table X, and transaction
B calculates the sum of all AMOUNTS.

Lets say they run over time like this, with | marking begin and >
commit (N.B. ASCII art, you need a fixed font):

....|--A-->.......|--A-->........
............|-B->.........|-B->..

This works as expected...but imagine transactions implicitly
begin too early:

|-----A-->|---------A-->|-------
|------------B->|----------B->|-

This will cause the aggregations in B to show "delayed" results.
Not at all what one might expect...


For more about isolation levels, see e.g. here:
http://pgsqld.active-venture.com/transaction-iso.html
.

Relevant Pages

  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server Analysis Services Query in a SQL Server job step
    ... My guess is that each request (begin transaction, execute processing, ... Try out the MSDN Forums for Analysis Services at: ... I have a step (SQL Server Analysis Services Query) inside of a 2005 SQL ... begin, commit, rollback transactions all within the code. ...
    (microsoft.public.sqlserver.olap)
  • Re: Behavior of Connection.commit()
    ... Does it say "commit" and "rollback" and other SQL commands that affect the transaction state are not allowed? ... How sure can the driver ever be about the state of the transaction? ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Autonomous Transactions
    ... You mean that I always have to explicitly commit or rollback sql ... You don't have much SQL in your app. ... Now what happens is that at the beginning of each procedure you start a transaction and at the end you either commit or rollback whatever the procedure does. ... The outermost procedure represents the interface to your app and you app does NOT contain any COMMIT/ROLLBACK. ...
    (comp.databases.oracle.server)