SQL Transaction Misunderstandings

Well, it appears that my understanding of transactions in PostgreSQL and Oracle is somewhat deficiant, but at least I'm in good company.

This recent OpenACS forum thread contains a good discussion - basically being inside a transaction does not guarantee that your view of the database will not change during the time of the transaction.

Yeah - that's what I said as well.

So, it looks like I'll be doing a few more select for update's in the future, but the serializeable level needs to be higher for the behaviour I usually expect. Of course as pointed out by my friend Russell, that will lead to a much higher percentage of aborted transactions, so my code will have to get more robust as well. While that's a good thing, it looks like a traditional RDBMS does less of my work for me than I thought...

Related links:

• openacs.org Q&A forum thread "PostgreSQL MVCC transaction model"
• PostgreSQL 7.4 Docs - 12.2. Transaction Isolation
• Slide set discussing PostgreSQL transactions by Tom Lane