«

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

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

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.

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/libr...tionlevel.aspx

Almost all database have it at the SQL level:

http://dev.mysql.com/doc/refman/5.5/...ansaction.html
http://www.postgresql.org/docs/9.1/s...ansaction.html and
http://www.postgresql.org/docs/9.1/s...ction-iso.html
http://msdn.microsoft.com/en-us/library/ms173763.aspx
http://docs.oracle.com/cd/B10500_01/...4/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 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/libr...tionlevel.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/...ansaction.html
> http://www.postgresql.org/docs/9.1/s...ansaction.html and
> http://www.postgresql.org/docs/9.1/s...ction-iso.html
> http://msdn.microsoft.com/en-us/library/ms173763.aspx
> http://docs.oracle.com/cd/B10500_01/...4/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

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/libr...tionlevel.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...Level%28int%29

Arne

"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/...ansaction.html
> http://www.postgresql.org/docs/9.1/s...ansaction.html and
> http://www.postgresql.org/docs/9.1/s...ction-iso.html
> http://msdn.microsoft.com/en-us/library/ms173763.aspx
> http://docs.oracle.com/cd/B10500_01/...4/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

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/...ansaction.html
>> http://www.postgresql.org/docs/9.1/s...ansaction.html and
>> http://www.postgresql.org/docs/9.1/s...ction-iso.html
>> http://msdn.microsoft.com/en-us/library/ms173763.aspx
>> http://docs.oracle.com/cd/B10500_01/...4/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

Richard Maher wrote:
> Arne Vajhøj wrote ...
> > Almost all database have it at the SQL level:
> >
> > http://dev.mysql.com/doc/refman/5.5/...ansaction.html
> > http://www.postgresql.org/docs/9.1/s...ansaction.html and
> > http://www.postgresql.org/docs/9.1/s...ction-iso.html
> > http://msdn.microsoft.com/en-us/library/ms173763.aspx
> > http://docs.oracle.com/cd/B10500_01/...4/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

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/...ansaction.html
>>> http://www.postgresql.org/docs/9.1/s...ansaction.html and
>>> http://www.postgresql.org/docs/9.1/s...ction-iso.html
>>> http://msdn.microsoft.com/en-us/library/ms173763.aspx
>>> http://docs.oracle.com/cd/B10500_01/...4/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