Re: JDBC transaction isolation

From:

=?ISO-8859-1?Q?Arne_Vajh=F8j?= <arne@vajhoej.dk>

Newsgroups:

comp.lang.java.programmer

Date:

Fri, 04 May 2012 08:27:36 -0400

Message-ID:

<4fa3cb3c$0$282$14726298@news.sunsite.dk>

On 5/3/2012 11:11 PM, Richard Maher wrote:

On May 4, 10:34 am, Arne Vajh?j<a...@vajhoej.dk> wrote:

On 5/3/2012 10:08 PM, Richard Maher wrote:

On May 4, 7:57 am, Arne Vajh?j<a...@vajhoej.dk> wrote:

On 5/3/2012 6:23 PM, Richard Maher wrote:

Is anyone else of the opinion that Oracle really does not support the
serializable isolation level? (At least not in the way that SQL Server, Rdb,
and I'm sure others do) That is, it doesn't prevent inserts to the other
txn's result-set,touched-rows by locking but rather fudges some sort of
snapshot/consistent-view of old data. (And even then with restrictions)
Eg: - select count(*) from employees where dept_code=1;

Oracle use MVCC instead of locking.

And that may seem to be cheating, but I believe it meet
the formal isolation level definitions.

Depends where you get your definition of "formal isolation
definitions" I guess: -

-----------------------------------------

Definitive: -

http://msdn.microsoft.com/en-us/library/ms173763.aspx

That is how SQLServer implements it.

The definitive definition is in the SQL standard.

It says that serilizable prevents:
* dirty reads
* non repeatable reads
* phantom reads

Which ref/link are you using? "Neutral" Ask Tom? I'm interested in the
exact pre-spun wording too.

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Oracle meet that as all reads will return data as they were
at the start of the transaction.

Look, I don't know what Mark's expectations were when he chose to
specify "isolation level serializable" but in reference to Table 7 at
the bottom of: -
http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html

I suspect his business requirement may mandate that Session 2's
"insert into b" be blocked until Session 1's txn commits/rollsback.
Most modern, sophisticated databases are capable of meeting such
requirements. Oracle is not.

Actually the trend seems to be that more and more databases
support MVCC.

It's no use simply hiding the fact that someone else has just
completely invalidated your SUM() or COUNT() or any other range
retrievals and validation, updates, and business decisions will now
based on those stale values!

Updates of stale values will fail.

Arne