Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From: markspace <-@.>
Newsgroups: comp.lang.java.programmer
Subject: JDBC transaction isolation
Date: Thu, 03 May 2012 07:30:07 -0700
Organization: A noiseless patient Spider
Lines: 64
Message-ID: <jnu4pj$ncr$1@dont-email.me>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Date: Thu, 3 May 2012 14:30:11 +0000 (UTC)
Injection-Info: mx04.eternal-september.org; posting-host="zgW2MA4sFrKxp4jMohs6RQ"; logging-data="23963"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19Si2tr44uM5e2BeQbOrabnpR40xbzuYCs="
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:12.0) Gecko/20120428 Thunderbird/12.0.1
Cancel-Lock: sha1:8g6ITN2ulhxj1onjgEGTJ5/bXqE=
Xref: csiph.com comp.lang.java.programmer:14184

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