Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #14184 > unrolled thread

JDBC transaction isolation

Back to article view | Back to comp.lang.java.programmer

Contents

  JDBC transaction isolation markspace <-@.> - 2012-05-03 07:30 -0700
    Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 11:53 -0400
      Re: JDBC transaction isolation markspace <-@.> - 2012-05-03 11:43 -0700
        Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 15:00 -0400
          Re: JDBC transaction isolation Lew <lewbloch@gmail.com> - 2012-05-03 14:00 -0700
            Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 17:13 -0400
          Re: JDBC transaction isolation "Richard Maher" <maher_rj@hotspamnotmail.com> - 2012-05-04 06:23 +0800
            Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 19:57 -0400
              Re: JDBC transaction isolation Richard Maher <maherrj@googlemail.com> - 2012-05-03 19:08 -0700
                Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 22:34 -0400
                  Re: JDBC transaction isolation Richard Maher <maherrj@googlemail.com> - 2012-05-03 20:11 -0700
                    Re: JDBC transaction isolation Richard Maher <maherrj@googlemail.com> - 2012-05-03 22:33 -0700
                    Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-04 08:27 -0400
                    Re: JDBC transaction isolation Lew <lewbloch@gmail.com> - 2012-05-04 06:58 -0700
            Re: JDBC transaction isolation Lew <lewbloch@gmail.com> - 2012-05-03 17:15 -0700
              Re: JDBC transaction isolation Arne Vajhøj <arne@vajhoej.dk> - 2012-05-03 21:22 -0400
                Re: JDBC transaction isolation Lew <lewbloch@gmail.com> - 2012-05-03 18:32 -0700

#14184 — JDBC transaction isolation

So I'm looking a bit more at the JDBC, and I realize there's a bit more 
to transactions than turning auto-commit on or off.

Transaction isolation allows the user to select the database locking 
scheme used by the driver.  Basically rather than deal with locks 
yourself you let the driver do it.  However, besides descriptions of the 
transaction levels, I'm not seeing much in-depth discussion how to use 
transaction levels or any examples either.

<http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html>

So here's my example.  I want to manually retrieve all the rows of a 
table, then find the maximum value of column.  Then I increment that 
value, and store a new row with the max+1 value in it.  I.e., I'm 
creating a new unique ID for a row, and not using any "auto" type 
functions to do it.

How does that go in the JDBC?  It seems to require that phantom reads 
are not allowed, the highest level of transaction isolation.  Here's my 
implementation:


    public int createNew( UserBean user )
    {
       Connection conn = null;
       try {
          QueryRunner run = new QueryRunner();
          conn = dataSource.getConnection();

          // IMPORTANT BIT HERE
          // set transaction isolation
          conn.setAutoCommit( false );
          conn.setTransactionIsolation( 
Connection.TRANSACTION_SERIALIZABLE );

          ScalarHandler max = new ScalarHandler();
          Integer ident = (Integer)run.query( conn,
                "select max(id) from UserTable" , max );
          if( ident == null )
             user.setId( 1 );
          else
             user.setId( ident+1 );
          int updates = run.update( conn,
               "insert into UserTable values (?,?,?,?,?)",
               user.getName(), user.getPassword(), user.getScreenName(),
                  user.getPermissions(), user.getId() );
          conn.commit();
          return updates;
       } catch( SQLException ex ) {
          Logger.getLogger( UserDataMapper.class.getName() ).
                  log( Level.SEVERE, null, ex );
          return 0;
       } finally {
          SimpleSql.closeAll( conn );
       }
    }


It uses dbutils, which I posted about earlier.  If you need an SSCCE, I 
can put one together (mostly, you'll need jdbutils and derby/jdb).  But 
I'm hoping this is complete enough for discussion.

<http://commons.apache.org/dbutils/>

[toc] | [next] | [standalone]

#14187

On 5/3/2012 10:30 AM, markspace wrote:
> So I'm looking a bit more at the JDBC, and I realize there's a bit more
> to transactions than turning auto-commit on or off.
>
> Transaction isolation allows the user to select the database locking
> scheme used by the driver. Basically rather than deal with locks
> yourself you let the driver do it.

You let the database do it - not the driver.

And typical you would not really have the option of doing the
locking yourself.

>                                     However, besides descriptions of the
> transaction levels, I'm not seeing much in-depth discussion how to use
> transaction levels or any examples either.
>
> <http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html>

It is a classic database topic.

But yes - there is not that much written about it on the internet.

> So here's my example. I want to manually retrieve all the rows of a
> table, then find the maximum value of column. Then I increment that
> value, and store a new row with the max+1 value in it. I.e., I'm
> creating a new unique ID for a row, and not using any "auto" type
> functions to do it.

If this is a real problem, then you should use either auto increment
(SQLServer, MySQL etc.) or sequence (Oracle, PostgreSQL etc.).

But let us take it as an exercise in transaction isolation level.

> How does that go in the JDBC? It seems to require that phantom reads are
> not allowed, the highest level of transaction isolation.

Yes - I believe that serializable is necessary.

>                                                          Here's my
> implementation:
>
>
> public int createNew( UserBean user )
> {
> Connection conn = null;
> try {
> QueryRunner run = new QueryRunner();
> conn = dataSource.getConnection();
>
> // IMPORTANT BIT HERE
> // set transaction isolation
> conn.setAutoCommit( false );
> conn.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE );
>
> ScalarHandler max = new ScalarHandler();
> Integer ident = (Integer)run.query( conn,
> "select max(id) from UserTable" , max );
> if( ident == null )
> user.setId( 1 );
> else
> user.setId( ident+1 );
> int updates = run.update( conn,
> "insert into UserTable values (?,?,?,?,?)",
> user.getName(), user.getPassword(), user.getScreenName(),
> user.getPermissions(), user.getId() );
> conn.commit();
> return updates;
> } catch( SQLException ex ) {
> Logger.getLogger( UserDataMapper.class.getName() ).
> log( Level.SEVERE, null, ex );
> return 0;
> } finally {
> SimpleSql.closeAll( conn );
> }
> }

The code is good in the sense that it will prevent duplicates.

For more serious usage you should consider to retry a couple of times
in case of a transaction timeout.

> It uses dbutils, which I posted about earlier. If you need an SSCCE, I
> can put one together (mostly, you'll need jdbutils and derby/jdb). But
> I'm hoping this is complete enough for discussion.

The usage of dbutils does not matter. It is obvious what is
happening in the code.

Arne

[toc] | [prev] | [next] | [standalone]

#14206

On 5/3/2012 8:53 AM, Arne Vajhøj wrote:
> You let the database do it - not the driver.


Yes, OK.  Driver/DB thing.  At some lower level than my app.


> It is a classic database topic.


Hmm, I'll try to find some other sources then, if it's a general topic 
and not restricted to JDBC implementations.


> If this is a real problem, then you should use either auto increment
> (SQLServer, MySQL etc.) or sequence (Oracle, PostgreSQL etc.).


Granted.


> The code is good in the sense that it will prevent duplicates.
>
> For more serious usage you should consider to retry a couple of times
> in case of a transaction timeout.


Ah, ok. Something else to check into.  Thanks!


 > Yes - I believe that serializable is necessary.

 > The usage of dbutils does not matter. It is obvious what is
 > happening in the code.


Thanks for taking the time to comment on my little example.

[toc] | [prev] | [next] | [standalone]

#14208

On 5/3/2012 2:43 PM, markspace wrote:
> On 5/3/2012 8:53 AM, Arne Vajhøj wrote:
>> You let the database do it - not the driver.
>
> Yes, OK. Driver/DB thing. At some lower level than my app.
>
>> It is a classic database topic.
>
>
> Hmm, I'll try to find some other sources then, if it's a general topic
> and not restricted to JDBC implementations.

It is not.

ADO.NET has it at provider level:

http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Almost all database have it at the SQL level:

http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html and 
http://www.postgresql.org/docs/9.1/static/transaction-iso.html
http://msdn.microsoft.com/en-us/library/ms173763.aspx
http://docs.oracle.com/cd/B10500_01/server.920/a96524/c21cnsis.htm

>> The code is good in the sense that it will prevent duplicates.
>>
>> For more serious usage you should consider to retry a couple of times
>> in case of a transaction timeout.
>
>
> Ah, ok. Something else to check into. Thanks!

Which will be a good excuse to look into the different
sub classes of SQLException and transient versus
non transient.

Arne

[toc] | [prev] | [next] | [standalone]

#14217

Arne Vajhøj wrote:
> markspace wrote:
>> Arne Vajhøj wrote:
>>> You let the database do it - not the driver.
>>
>> Yes, OK. Driver/DB thing. At some lower level than my app.
>>
>>> It is a classic database topic.
>>
>>
>> Hmm, I'll try to find some other sources then, if it's a general topic
>> and not restricted to JDBC implementations.
> 
> It is not.
> 
> ADO.NET has it at provider level:
> 
> http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx
> 
> Almost all database have it at the SQL level:

But not all DBMSes support all four transaction levels.

> http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
> http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html and 
> http://www.postgresql.org/docs/9.1/static/transaction-iso.html
> http://msdn.microsoft.com/en-us/library/ms173763.aspx
> http://docs.oracle.com/cd/B10500_01/server.920/a96524/c21cnsis.htm
> 
>>> The code is good in the sense that it will prevent duplicates.
>>>
>>> For more serious usage you should consider to retry a couple of times
>>> in case of a transaction timeout.
>>
>> Ah, ok. Something else to check into. Thanks!
> 
> Which will be a good excuse to look into the different
> sub classes of SQLException and transient versus
> non transient.

-- 
Lew

[toc] | [prev] | [next] | [standalone]

#14219

On 5/3/2012 5:00 PM, Lew wrote:
> Arne Vajhøj wrote:
>> markspace wrote:
>>> Arne Vajhøj wrote:
>>>> You let the database do it - not the driver.
>>>
>>> Yes, OK. Driver/DB thing. At some lower level than my app.
>>>
>>>> It is a classic database topic.
>>>
>>>
>>> Hmm, I'll try to find some other sources then, if it's a general topic
>>> and not restricted to JDBC implementations.
>>
>> It is not.
>>
>> ADO.NET has it at provider level:
>>
>> http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx
>>
>> Almost all database have it at the SQL level:
>
> But not all DBMSes support all four transaction levels.

True.

Which is why we have:

http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#supportsTransactionIsolationLevel%28int%29

Arne

[toc] | [prev] | [next] | [standalone]

#14227

"Arne Vajhøj" <arne@vajhoej.dk> wrote in message 
news:4fa2d5c7$0$288$14726298@news.sunsite.dk...
>
> Almost all database have it at the SQL level:
>
> http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
> http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html and 
> http://www.postgresql.org/docs/9.1/static/transaction-iso.html
> http://msdn.microsoft.com/en-us/library/ms173763.aspx
> http://docs.oracle.com/cd/B10500_01/server.920/a96524/c21cnsis.htm
>

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;

>
> Arne
>

Cheers Richard Maher 

[toc] | [prev] | [next] | [standalone]

#14236

On 5/3/2012 6:23 PM, Richard Maher wrote:
> "Arne Vajhøj"<arne@vajhoej.dk>  wrote in message
> news:4fa2d5c7$0$288$14726298@news.sunsite.dk...
>>
>> Almost all database have it at the SQL level:
>>
>> http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
>> http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html and
>> http://www.postgresql.org/docs/9.1/static/transaction-iso.html
>> http://msdn.microsoft.com/en-us/library/ms173763.aspx
>> http://docs.oracle.com/cd/B10500_01/server.920/a96524/c21cnsis.htm
>>
>
> 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.

Arne

[toc] | [prev] | [next] | [standalone]

#14242

On May 4, 7:57 am, Arne Vajhøj <a...@vajhoej.dk> wrote:
> On 5/3/2012 6:23 PM, Richard Maher wrote:
>
>
>
>
>
>
>
>
>
> > "Arne Vajhøj"<a...@vajhoej.dk>  wrote in message
> >news:4fa2d5c7$0$288$14726298@news.sunsite.dk...
>
> >> Almost all database have it at the SQL level:
>
> >>http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
> >>http://www.postgresql.org/docs/9.1/static/sql-set-transaction.htmland
> >>http://www.postgresql.org/docs/9.1/static/transaction-iso.html
> >>http://msdn.microsoft.com/en-us/library/ms173763.aspx
> >>http://docs.oracle.com/cd/B10500_01/server.920/a96524/c21cnsis.htm
>
> > 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

SERIALIZABLE

Specifies the following:

·         Statements cannot read data that has been modified but not
yet committed by other transactions.

·         No other transactions can modify data that has been read by
the current transaction until the current transaction completes.

·         Other transactions cannot insert new rows with key values
that would fall in the range of keys read by any statements in the
current transaction until the current transaction completes.

Range locks are placed in the range of key values that match the
search conditions of each statement executed in a transaction. This
blocks other transactions from updating or inserting any rows that
would qualify for any of the statements executed by the current
transaction. This means that if any of the statements in a transaction
are executed a second time, they will read the same set of rows. The
range locks are held until the transaction completes. This is the most
restrictive of the isolation levels because it locks entire ranges of
keys and holds the locks until the transaction completes. Because
concurrency is lower, use this option only when necessary. This option
has the same effect as setting HOLDLOCK on all tables in all SELECT
statements in a transaction.

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

I think the bullet point 3 is the relevent requirement here that Larry
Ellison omitted from his dumbed down version of "Serializable".
Similar to the way he claims "We *never* promote locks" to be a good
thing. Look, I love Oracle but the facts on this are plain for anyone
to see.

>
> Arne

Cheers Richard Maher

[toc] | [prev] | [next] | [standalone]

#14243

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:
>>> "Arne Vajhøj"<a...@vajhoej.dk>    wrote in message
>>> news:4fa2d5c7$0$288$14726298@news.sunsite.dk...
>>
>>>> Almost all database have it at the SQL level:
>>
>>>> http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
>>>> http://www.postgresql.org/docs/9.1/static/sql-set-transaction.htmland
>>>> http://www.postgresql.org/docs/9.1/static/transaction-iso.html
>>>> http://msdn.microsoft.com/en-us/library/ms173763.aspx
>>>> http://docs.oracle.com/cd/B10500_01/server.920/a96524/c21cnsis.htm
>>
>>> 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

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

Arne

[toc] | [prev] | [next] | [standalone]

#14245

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.

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

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!

If Oracle chooses to say they already have "Serializable" then how
'bout "Really, Really, Bolshie"? (And then maybe a lock promotion on a
sequential table scan?)

> Arne

Cheers Richard Maher

[toc] | [prev] | [next] | [standalone]

#14248

Hi Arne,

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

I think I've found your reference: -
http://en.wikipedia.org/wiki/List_of_Newspeak_words

Larry definitely supports "isolation level is black/white".

>
> Cheers Richard Maher

[toc] | [prev] | [next] | [standalone]

#14255

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

[toc] | [prev] | [next] | [standalone]

#14259

Richard Maher wrote:
> 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!

It's no use trying to make the case that Oracle's DBMS does not support serializable 
isolation when it does, especially with wrong reasoning like that.

> If Oracle chooses to say they already have "Serializable" then how
> 'bout "Really, Really, Bolshie"? (And then maybe a lock promotion on a
> sequential table scan?)

Huh? What? 

Whatever Oracle chooses to say, the fact is that their database supports serializable transaction 
isolation. Your nonsensical babble notwithstanding.

-- 
Lew

[toc] | [prev] | [next] | [standalone]

#14237

Richard Maher wrote:
> Arne Vajhøj wrote ...
> > Almost all database have it at the SQL level:
> >
> > http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
> > http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html and 
> > http://www.postgresql.org/docs/9.1/static/transaction-iso.html
> > http://msdn.microsoft.com/en-us/library/ms173763.aspx
> > http://docs.oracle.com/cd/B10500_01/server.920/a96524/c21cnsis.htm
> >
> 
> 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, 

Are you referring to their database product or some other product, such as Java?

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

They do support SERIALIZABLE isolation, and properly by all evidence:

<http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10005.htm#SQLRF01705>
"ISOLATION LEVEL Clause

"Use the ISOLATION LEVEL clause to specify how transactions containing database
modifications are handled.

"The SERIALIZABLE setting specifies serializable transaction isolation mode as defined in the 
SQL standard. If a serializable transaction contains data manipulation language (DML) that 
attempts to update any resource that may have been updated in a transaction uncommitted at 
the start of the serializable transaction, then the DML statement fails."

See also 
<http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#BABEAFAH>

There's nothing in the definition of SERIALIZABLE transactions that requires locking.
<http://en.wikipedia.org/wiki/Isolation_(database_systems)#Serializable>

So whatever anyone else's opinion, or yours, the facts seem to be that Oracle Database properly supports SERIALIZABLE transaction isolation. Why do you ask for opinion when facts are in evidence?

-- 
Lew

[toc] | [prev] | [next] | [standalone]

#14240

On 5/3/2012 8:15 PM, Lew wrote:
> Richard Maher wrote:
>> Arne Vajhøj wrote ...
>>> Almost all database have it at the SQL level:
>>>
>>> http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
>>> http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html and
>>> http://www.postgresql.org/docs/9.1/static/transaction-iso.html
>>> http://msdn.microsoft.com/en-us/library/ms173763.aspx
>>> http://docs.oracle.com/cd/B10500_01/server.920/a96524/c21cnsis.htm
>>>
>>
>> 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,
>
> Are you referring to their database product or some other product, such as Java?
>
>> 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;
>
> They do support SERIALIZABLE isolation, and properly by all evidence:
>
> <http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10005.htm#SQLRF01705>
> "ISOLATION LEVEL Clause
>
> "Use the ISOLATION LEVEL clause to specify how transactions containing database
> modifications are handled.
>
> "The SERIALIZABLE setting specifies serializable transaction isolation mode as defined in the
> SQL standard. If a serializable transaction contains data manipulation language (DML) that
> attempts to update any resource that may have been updated in a transaction uncommitted at
> the start of the serializable transaction, then the DML statement fails."
>
> See also
> <http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#BABEAFAH>
>
> There's nothing in the definition of SERIALIZABLE transactions that requires locking.
> <http://en.wikipedia.org/wiki/Isolation_(database_systems)#Serializable>
>
> So whatever anyone else's opinion, or yours, the facts seem to be that Oracle Database properly supports SERIALIZABLE transaction isolation. Why do you ask for opinion when facts are in evidence?

They meet the definition for transaction isolation level serializable.

It is more questionable whether MVCC (and Oracle is actually not the
only database vendor using MVCC != meet what most people associate
with serializable.

Well - I think should use the database definition and not the
English definition when it actually is a database.

But I will not be surprised when somebody ask questions about it.

Arne

[toc] | [prev] | [next] | [standalone]

#14241

Arne Vajhøj wrote:
> Lew wrote:
>> Richard Maher wrote:
>>> Arne Vajhøj wrote ...
>>>> Almost all database have it at the SQL level:
>>>>
>>>> http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
>>>> http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html and
>>>> http://www.postgresql.org/docs/9.1/static/transaction-iso.html
>>>> http://msdn.microsoft.com/en-us/library/ms173763.aspx
>>>> http://docs.oracle.com/cd/B10500_01/server.920/a96524/c21cnsis.htm
>>>
>>> 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,
>>
>> Are you referring to their database product or some other product, such as Java?
>>
>>> 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;
>>
>> They do support SERIALIZABLE isolation, and properly by all evidence:
>>
>> <http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10005.htm#SQLRF01705>
>> "ISOLATION LEVEL Clause
>>
>> "Use the ISOLATION LEVEL clause to specify how transactions containing database
>> modifications are handled.
>>
>> "The SERIALIZABLE setting specifies serializable transaction isolation mode as defined in the
>> SQL standard. If a serializable transaction contains data manipulation language (DML) that
>> attempts to update any resource that may have been updated in a transaction uncommitted at
>> the start of the serializable transaction, then the DML statement fails."
>>
>> See also
>> <http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#BABEAFAH>
>>
>> There's nothing in the definition of SERIALIZABLE transactions that requires locking.
>> <http://en.wikipedia.org/wiki/Isolation_(database_systems)#Serializable>
>>
>> So whatever anyone else's opinion, or yours, the facts seem to be that Oracle Database 
>> properly supports SERIALIZABLE transaction isolation. Why do you ask for opinion when 
>> facts are in evidence?
> 
> They meet the definition for transaction isolation level serializable.

Q.E.D.

> It is more questionable whether MVCC (and Oracle is actually not the
> only database vendor using MVCC != meet what most people associate
> with serializable.

What does that matter? The term "serializable transaction isolation" is a term of art, with a 
specific definition. Whatever "most people" associate with it notwithstanding. It's an objective 
term with testable criteria for compliance. Oracle meets the test.

People are not entitled to wrong opinions on the definitions of terms of art, or any opinions, as to their definition.

> Well - I think should use the database definition and not the
> English definition when it actually is a database.

+1

> But I will not be surprised when somebody ask questions about it.

Surprise at the question is one thing. A correct answer is another.

I'm not evincing surprise at the question, only pointing out that the answer is not a matter of opinion.

-- 
Lew

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.programmer

csiph-web