Re: JDBC transaction isolation
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