Setting Up Default Isolation level on WAS 6.1

Setting a right Isolation level for database operation is critical for performance of the database operation performed in online or batch application.

Following are different isolation levels

1. READ Uncommited
2. READ_COMMITED
3. REPEATABLE_READ
4. Serializable

Read Uncommited provides highest level of concurrancy and best performance, while Serialization provides lowest level of concurrecy , highest level of transaction isolation and lowest performance.

The need of iolstaion level is an application specific requirement and can differ from transaction to transaction.

In J2EE application, isolation level can be set at application server (data source setting level ) as well as application level using declarative configuration. ( In EJB application, it can be defiend in server specific deployment discriptor or in Spring based applciation , it can be defined in the transaction manager configuration using element )

If this is not defined application server defaults it to default transaction level depending upon driver and application server setting

For most drivers, WebSphere Application Server uses an isolation level default of TRANSACTION_REPEATABLE_READ. For Oracle drivers, however, WebSphere Application Server uses an isolation level of TRANSACTION_READ_COMMITTED.

Use the following table for quick reference:
1. Sybase : RR
2. Oracle : RC
3. DB2: RR
4. Informix : RR
5. Cloudscape : RR
6. DB2 : RR
7. SQL Server : RR


• Note: These same default isolation levels are used in cases of direct JNDI lookups of a data source.
• RR = JDBC Repeatable read (TRANSACTION_REPEATABLE_READ)
• RC = JDBC Read committed (TRANSACTION_READ_COMMITTED)

WebSphere 6.1 allows us to specify the default Isolation level as a customproperty on the data source definition.
To Set this Data Source custom property webSphereDefaultIsolationLevel can be used

Data Source custom property : webSphereDefaultIsolationLevel

Possible values : 1, 2, 4, 8

Value JDBC ISOLATION Level DB2 Isolation Level

8 TRANSACTION_SERIALIZABLE Repeatable Read (RR)

4 TRANSACTION_REPEATABLE_READ Read Stability (RS)

2 TRANSACTION_READ_COMMITTED Cursor Stability (CS)

1 TRANSACTION_READ_UNCOMMITTED Uncommitted Read (UR)

Following are the instructions to add custom property for a data source using Administrative console:
1. Configure the JDBC provider using Creating and configuring a JDBC provider using the administrative console and the Data Source using Creating and configuring a data source using the administrative console
2. Expand Resources > JDBC provider >
3. Select the Configured_JDBC_Provider
4. Select DataSource
5. Click on the name of the Data Source for which you want to customize the default Isolation level
6. Under additional properties, click Custom properties
7. Click New to add a new custom property, specifying the name webSphereDefaultIsolationLevel and type the desired value (possible values: 1, 2, 4, 8)
8. Click OK, and save the configuration; resync the node if you are using the Network Deployment Environment.
9. Restart the Application Server to make the custom property active
You can find the details about isolation level to specific to DB2 at following link
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/db2/rbafzmstisol.htm