Re: DB API 2.0 and transactions
- From: "Christopher J. Bottaro" <cjbottaro@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 10 Jun 2005 16:25:20 -0500
<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
.
- Follow-Ups:
- Re: DB API 2.0 and transactions
- From: Magnus Lycka
- Re: DB API 2.0 and transactions
- References:
- DB API 2.0 and transactions
- From: Christopher J. Bottaro
- Re: DB API 2.0 and transactions
- From: Magnus Lycka
- DB API 2.0 and transactions
- Prev by Date: Re: IMAP Proxy
- Next by Date: re - multiple results
- Previous by thread: Re: DB API 2.0 and transactions
- Next by thread: Re: DB API 2.0 and transactions
- Index(es):
Relevant Pages
|
