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

Groups > comp.lang.java.programmer > #14187

Re: JDBC transaction isolation

Show all headers | View raw

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

Back to comp.lang.java.programmer | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web