IBM Support

Changing the default isolation level for non-CMP applications and describing how to do so using a new custom property webSphereDefaultIsolationLevel

Troubleshooting


Problem

The isolation level is used to specify the degree of data integrity and concurrency. The isolation level controls the level of database locking. Setting the isolation level too restrictive can lead to performance issues due to rows of a database being locked. Setting the isolation level too lenient may lead to data integrity issues. This document describes how to set the isolation level using existing means and describes a new custom property webSphereDefaultIsolationLevel introduced in WebSphere Application Server V6.0.2 by the fix for APAR PK05841 which allows WebSphere Application Server administrators to change the default isolation level.

Cause

For WebSphere Application Server versions prior to V6.0.2, you could change the default isolation level setting by doing one of the following:

  • Using resource-reference:

    Applications can create resource-reference and set the desired default isolation level. During indirect JNDI lookup , the resource reference properties, including the specified isolation level, will be used for connecting to the database.

    Note: Indirect JNDI look-up is identified as java:comp/env in the specification of the JNDI name.
  • Using access intent:

    Applications can use Access Intent to specify their intents where WebSphere Application Server will interpret that intent to an isolation level.

    More detail on access intent is provided in the WebSphere Application Server V8.5.5 Knowledge Center
  • Custom DataStoreHelper class:

    By extending the DataStoreHelper class and overwriting getIsolationLevel(AccessIntent) to return the desired isolation level.

    More detail on DataStoreHelper class is provided in the WebSphere Application Server V8.5.5 Knowledge Center

If none of the above options are used, WebSphere Application Server will use a default setting that is more aggressive isolation level. The goal is to avoid any data integrity issues, such as "lost updates":
  • The isolation level is TRANSACTION_REPEATABLE_READ for all databases that support the RepeatableRead isolation level.
  • For databases that does not support TRANSACTION_REPEATABLE_READ isolation level, such as Oracle, TRANSACTION_READ_COMMITTED is used as default.

Note: CMP applications have a default Access Intent that results inTRANSACTION_REPEATABLE_READ.

To determine the isolation level being used by your application refer to the document
Requirements for setting data access isolation levels.

The above solution may not be desirable, since they require changing and repackaging applications (option 1 and 2) OR extending WebSphere Application Server code (option 3). That is why the new custom property webSphereDefaultIsolationLevel was introduced.

Resolving The Problem

A new data source property was introduced to allow WebSphere Application Server administrators to customize the default isolation level. This property will have no effect if any of the above options are used.

This document describes a new custom property webSphereDefaultIsolationLevel introduced in WebSphere Application Server V6.0.2 (APAR Number: PK05841) which allows WebSphere Application Server administrators to change the default isolation level. A higher isolation level leads to increased data integrity, while a lower isolation level leads to more concurrency and better performance.

This is a customized property that is only available in IBM WebSphere Application Server. It is provided for those situations where none of the other means of setting the isolation level may be used. It is preferable for you to define the isolation in the deployment descriptor when packaging the ear.

Data Source custom property :  webSphereDefaultIsolationLevel
Possible values :  1, 2, 4, 8

NOTE: These values correspond to the java.sql.Connection constants, as defined in http://java.sun.com/javase/6/docs/api/java/sql/Connection.html.

Possible values
JDBC Isolation Level
DB2 Isolation Level
8
TRANSACTION_SERIALIZABLERepeatable Read (RR)
4 (default)
TRANSACTION_REPEATABLE_READRead Stability (RS)
2
TRANSACTION_READ_COMMITTEDCursor Stability (CS)
1
TRANSACTION_READ_UNCOMMITTEDUncommitted Read (UR)

Following are the instructions to add custom property for a data source using Administrative console:
  1. Configure the JDBC provider using Configuring a JDBC provider using the administrative console and the Data Source using 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

Notes
  • The custom property is not applicable and will be ignored in CMP applications since a default Access Intent (wsPessimisticUpdate-Weakest LockAtLoad) is used.

[{"Product":{"code":"SSEQTP","label":"WebSphere Application Server"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"DB Connections\/Connection Pooling","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF012","label":"IBM i"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF035","label":"z\/OS"}],"Version":"9.0;8.5.5;8.0;7.0","Edition":"Base;Network Deployment","Line of Business":{"code":"LOB45","label":"Automation"}},{"Product":{"code":"SSNVBF","label":"Runtimes for Java Technology"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Java SDK","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
15 June 2018

UID

swg21224492