That's what I thought, but I got different behaviour in my testing (the behaviour I saw is supported by the docs, which I'll quote in a bit). I started two separate transactions (read committed, the default) (call them 1 and 2); inserted in 1, inserted in 2, select max in both, each returns the correct id; commit 2 - now select max in 1 returns the incorrect higher result from the committed 2.
Here's why, from the Oracle Docs: "Oracle transactions executing in READ COMMITTED mode are transaction set consistent on a per-statement basis, because all rows read by a query must be committed before the query begins." - Note that this is per statement, not per transaction. "Oracle transactions executing in SERIALIZABLE mode are transaction set consistent on a per-transaction basis, because all statements in a SERIALIZABLE transaction execute on an image of the database as of the beginning of the transaction." - Note the important difference. "READ COMMITTED isolation can provide considerably more concurrency with a somewhat increased risk of inconsistent results (due to phantoms and non-repeatable reads) for some transactions. The SERIALIZABLE isolation level provides somewhat more consistency by protecting against phantoms and non-repeatable reads, and may be important where a read/write transaction executes a query more than once." "Oracle meets the READ COMMITTED isolation standard. This is the default mode for all Oracle applications. Because an Oracle query only sees data that was committed at the beginning of the query (the snapshot time), Oracle actually offers more consistency than is required by the ANSI/ISO SQL92 standards for READ COMMITTED isolation." - Again note that this is for one query (statement), not one transaction, -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Monday, 6 June 2005 8:02 To: CF-Talk Subject: Re: Get New Record ID in mySQL James Holmes wrote: > > This convo has prompetd me to realise that I'm still using a > transaction with select max() in Oracle via the default transaction > behaviour (i.e not serializable) and there is a small chance, as > Jochem said, that this could give an erroneous result. Due to MVCC this is not a problem in Oracle. Only data that was committed before the transaction started will ever be visible in the snapshot. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208656 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
