JDBC transaction isolation

Discussion in 'Java' started by markspace, May 3, 2012.

  1. markspace

    markspace Guest

    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/>
    markspace, May 3, 2012
    #1
    1. Advertising

  2. markspace

    Arne Vajhøj Guest

    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
    Arne Vajhøj, May 3, 2012
    #2
    1. Advertising

  3. markspace

    markspace Guest

    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.
    markspace, May 3, 2012
    #3
  4. markspace

    Arne Vajhøj Guest

    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
    Arne Vajhøj, May 3, 2012
    #4
  5. markspace

    Lew Guest

    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
    Lew, May 3, 2012
    #5
  6. markspace

    Arne Vajhøj Guest

    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/doc...a.html#supportsTransactionIsolationLevel(int)

    Arne
    Arne Vajhøj, May 3, 2012
    #6
  7. "Arne Vajhøj" <> wrote in message
    news:4fa2d5c7$0$288$...
    >
    > 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
    Richard Maher, May 3, 2012
    #7
  8. markspace

    Arne Vajhøj Guest

    On 5/3/2012 6:23 PM, Richard Maher wrote:
    > "Arne Vajhøj"<> wrote in message
    > news:4fa2d5c7$0$288$...
    >>
    >> 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
    Arne Vajhøj, May 4, 2012
    #8
  9. markspace

    Lew Guest

    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 modeas 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 transactionuncommitted 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
    Lew, May 4, 2012
    #9
  10. markspace

    Arne Vajhøj Guest

    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
    Arne Vajhøj, May 4, 2012
    #10
  11. markspace

    Lew Guest

    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 foropinion 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
    Lew, May 4, 2012
    #11
  12. On May 4, 7:57 am, Arne Vajhøj <> wrote:
    > On 5/3/2012 6:23 PM, Richard Maher wrote:
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > > "Arne Vajhøj"<>  wrote in message
    > >news:4fa2d5c7$0$288$...

    >
    > >> 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
    Richard Maher, May 4, 2012
    #12
  13. markspace

    Arne Vajhøj Guest

    On 5/3/2012 10:08 PM, Richard Maher wrote:
    > On May 4, 7:57 am, Arne Vajhøj<> wrote:
    >> On 5/3/2012 6:23 PM, Richard Maher wrote:
    >>> "Arne Vajhøj"<> wrote in message
    >>> news:4fa2d5c7$0$288$...

    >>
    >>>> 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
    Arne Vajhøj, May 4, 2012
    #13
  14. On May 4, 10:34 am, Arne Vajhøj <> wrote:
    > On 5/3/2012 10:08 PM, Richard Maher wrote:
    > > On May 4, 7:57 am, Arne Vajhøj<>  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
    Richard Maher, May 4, 2012
    #14
  15. 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
    Richard Maher, May 4, 2012
    #15
  16. markspace

    Arne Vajhøj Guest

    On 5/3/2012 11:11 PM, Richard Maher wrote:
    > On May 4, 10:34 am, Arne Vajhøj<> wrote:
    >> On 5/3/2012 10:08 PM, Richard Maher wrote:
    >>> On May 4, 7:57 am, Arne Vajhøj<> 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
    Arne Vajhøj, May 4, 2012
    #16
  17. markspace

    Lew Guest

    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
    Lew, May 4, 2012
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    5
    Views:
    1,287
    Rob Mitchell
    Jan 27, 2006
  2. John_Woo
    Replies:
    0
    Views:
    311
    John_Woo
    Nov 17, 2006
  3. Vencz Istv?n
    Replies:
    2
    Views:
    273
  4. Eddie
    Replies:
    4
    Views:
    361
    Aaron Bertrand [SQL Server MVP]
    Aug 25, 2005
  5. Replies:
    2
    Views:
    378
    Jochen Lehmeier
    Jan 7, 2010
Loading...

Share This Page