DB API 2.0 and transactions

[Christopher J. Bottaro]

<posted & mailed>

Magnus Lycka wrote:

> You might have spotted a fairly nasty bug there!

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

Check this out...

<code>
import pgdb
import time

print time.ctime()
db = pgdb.connect(user='test', host='localhost', database='test')
time.sleep(5)
db.cursor().execute('insert into time_test
                     (datetime)
                     values
                     (CURRENT_TIMESTAMP)')
db.commit()
curs = db.cursor()
curs.execute('select datetime from time_test order by datetime desc limit
1')
row = curs.fetchone()
print row[0]
</code>

<output>
Fri Jun 10 17:27:21 2005
'2005-06-10 17:27:21.654897-05'
</output>

Notice the times are exactly the same instead of 5 sec difference.

What do you make of that?  Some other replies to this thread seemed to
indicate that this is expected and proper behavior.

-- C

> 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