Brought to you by EarthWeb
ITKnowledge Logo Login Graphic Forte Software. Click Here for Success!
Forte Software. Click Here for Success!
ITKnowledge
Search this book:
 
Search the site:
 
EXPERT SEARCH ----- nav

EarthWeb Direct

EarthWeb Direct

EarthWeb sites: other sites

Previous Table of Contents Next


Transaction Isolation Levels

ANSI defines three standard levels of transaction isolation. Transaction isolation makes sense when concurrent transactions execute simultaneously. The ANSI specification defines restrictions on the kinds of action permitted in concurrent transactions so as to prevent dirty reads, nonrepeatable reads, and phantoms.

  Level 1: No dirty reads. Dirty reads occur when a transaction updates a row, then a second transaction reads that row before the first transaction commits. If the first transaction rolls back the change, the information read by the second transaction becomes invalid.
  Level 2: No nonrepeatable reads. Nonrepeatable reads occur when a transaction reads a row and then another transaction updates the same row. If the second transaction commits, subsequent reads by the first transaction get different values than the original read.
  Level 3: No phantoms. Phantoms occur when a transaction reads a set of rows that satisfy a search condition and then another transaction updates, inserts, or deletes one or more rows that satisfy the first transaction’s search condition. In this case, if the first transaction performs subsequent reads with the same search condition, it reads a different set of rows.

The higher levels include restrictions imposed by all the lower levels. In practice, compatibility with all the transaction isolation levels is achieved using locking techniques. Check your database documentation for information on these techniques and see how they can affect performances in a multiuser environment. As a general rule, the higher the transaction isolation level, the longer locks are held.

Managing Transactions with JDBC

JDBC always opens connections in autocommit mode. This opening mode means that each statement is executed as a separate transaction without needing to supply commit or rollback commands. In this default mode, it is not possible to perform rollbacks.

JDBC provides methods to turn off autocommit mode, to set the transaction isolation level, and to commit or rollback transactions. JDBC transactions begin as soon as the autocommit mode is disabled. In this case, an implicit transaction is associated with the connection, and it is completed or aborted with commit and rollback methods. The commit or rollback starts a new implicit transaction. The commit and rollback make JDBC close all PreparedStatements, CallableStatements, and ResultSets opened during the transaction. Simple statement objects stay open. This is the default behavior and it may be disabled.

The JDBC methods to manage transactions are these:

Connection

void setTransactionIsolation(int isolationlevel);
int getTransactionIsolation();
void setAutoCommit(boolean autocommit);
boolean getAutoCommit();
void commit();
void rollback();
void setAutoClose(boolean autoClose);
boolean getAutoClose();

void setTransactionIsolation(int isolationlevel);

This method sets the transaction isolation level. The possible JDBC transaction isolation levels are the following:

TRANSACTION_READ_UNCOMMITTED:
Dirty reads are allowed.
TRANSACTION_READ_COMMITTED:
Reads on a row are blocked until the transaction is committed.
TRANSACTION_REPEATABLE_READ:
Repeated reads on a row will return the originally read data, regardless of any updates by other users prior to commitment of the transaction.
TRANSACTION_SERIALIZABLE:
All reads are disallowed until the transaction is committed.
TRANSACTION_NONE:
Transactions are not supported. This method cannot be called while in the middle of a transaction.
int getTransactionIsolation();
It returns the current transaction isolation levels. A value of zero means that transactions are not supported.
void setAutoCommit(boolean autocommit);
The method setAutoCommit(false) implicitly begins a new transaction. Either commit() or rollback() must be used to terminate the transaction.
boolean getAutoCommit();
This method returns the current autocommit state. False means that user transactions are in use.
void commit();
This method completes the transaction. All changes made since the previous transaction termination (committed or rolled back) are made permanent and all transaction locks are released.
void rollback();
All changes made since the previous transaction termination (committed or rolled back) are dropped. This method undoes the current transaction statements and all transaction locks are released.
void setAutoClose(boolean autoClose);
When the connection is in autoclose mode, all its PreparedStatements, Callable Statement, and ResultSets are closed when the transaction is committed or rolled back. This is the default behavior, but it can be disabled by passing false as parameter. Some databases allow these objects to remain open across commits, whereas other databases close them.
boolean getAutoClose();
This method returns the current autoclose state for this connection.

Example

// transactions
import java.sql.*;
class SimpleExample
{
   public static void main(String args[])
   {
      String url = “jdbc:odbc:mysource”;
      try
   {
   Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
                 Connection myConnection =
                         DriverManager.getConnection(url,
                                 “javauser”, “hotjava”);
                 Statement firstStmt =
                         myConnection.createStatement();
                 Statement secondStmt =
                         myConnection.createStatement();
                 myConnection.setTransactionIsolation(
                         Connection.TRANSACTION_SERIALIZABLE);
                 myConnection.setAutoCommit(false);
                 firstStmt.executeUpdate(
                 “DELETE emp_messages WHERE id IN
                         (SELECT id FROM employees WHERE name = ‘Jones’)”);
                 firstStmt.close();
                 secondStmt.executeUpdate(
                 “DELETE employees WHERE name = ‘Jones’”);
                 secondStmt.close();
                 myConnection.commit();
                 myConnection.setTransactionIsolation(
                         Connection.TRANSACTION_NONE);
                 myConnection.close();
          }
          catch(java.lang.Exception ex)
          {
                 ex.printStackTrace();
          }
   }
}


Previous Table of Contents Next
HomeAbout UsSearchSubscribeAdvertising InfoContact UsFAQs
Use of this site is subject to certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb Inc. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.