IBM Support

Determining and changing the isolation level

Troubleshooting


Problem

Determining and changing the isolation level for WebSphere Application Server applications connecting to IBM DB2 databases When an application connects to a DB2 database, the isolation level that it uses specifies the degree of data integrity and concurrency. A higher isolation level leads to increased data integrity, while a lower isolation level leads to more concurrency and better performance. A lower isolation level also decreases row locking, so the probability of database deadlock is reduced. WebSphere Application Server applications can specify a specific isolation level for connecting to a DB2 database. This technote lists which isolation levels are used by default, and how to change the defaults. The content of this technote is based on DB2 for distributed platforms (Windows, AIX, Solaris, HP-UX, or Linux).

Resolving The Problem

DB2 databases support four isolation levels:

  • Repeatable Read (RR)
  • Read Stability (RS)
  • Cursor Stability (CS)
  • Uncommitted Read (UR).

The default isolation level is CS. A JDBC™ application that connects to DB2 can specify one of four JDBC isolation levels, each of which maps to a different DB2 isolation level:

JDBC Isolation Level
DB2 Isolation Level
TRANSACTION_SERIALIZABLERepeatable Read (RR)
TRANSACTION_REPEATABLE_READRead Stability (RS)
TRANSACTION_READ_COMMITTEDCursor Stability (CS)
TRANSACTION_READ_UNCOMMITTEDUncommitted Read (UR)

TRANSACTION_SERIALIZABLE isolation level is the highest, most restrictive, isolation level. It prohibits dirty reads, nonrepeatable reads, and phantom reads.

TRANSACTION_REPEATABLE_READ isolation level is the second highest isolation level. It prohibits dirty reads and nonrepeatable reads, but allows phantom reads.

TRANSACTION_READ_COMMITTED isolation level prohibits dirty reads only.

TRANSACTION_READ_UNCOMMITTED isolation level permits dirty reads, nonrepeatable reads, and phantom reads.

The default isolation level used by WebSphere Application Server is as follows:

For V4 data source (for compatibility with older Java EE applications):

  • If the database connection is obtained in a servlet or JSP™, TRANSACTION_READ_COMMITTED is used by default. This can be changed by calling the setTransactionIsolation method on the connection after it is obtained from the data source.

  • If the database connection is obtained in a session bean or an entity bean (CMP or BMP), TRANSACTION_REPEATABLE_READ is used by default. This can be changed by editing the EJB™ deployment descriptor.

For a standard data source:

  • If the database connection is obtained in a servlet, JSP, or session bean, TRANSACTION_REPEATABLE_READ is used by default. To change this, an indirect JNDI™ lookup (through the java:comp/env context) of the data source must be done and a resource reference must be created. The isolation level can be set as an IBM® extension on the resource reference. The resource reference is created in the Web, EJB, or Application Client deployment descriptor. When the indirect JNDI lookup is done, the resource reference properties, including the specified isolation level, are used to connect to the database.

    In WebSphere Application Server V6.1 and above, the webSphereDefaultIsolationLevel custom property can be set on a data source to change the default isolation level that is used even if indirect JNDI lookups and resource references are not used by the application.

    Applications can also call the setTransactionIsolation method on a connection directly to change the isolation level on the connection after it is obtained from the data source.

  • If the database connection is obtained in an entity bean (CMP or BMP), TRANSACTION_REPEATABLE_READ is used by default. The isolation level is determined by the access intent policy that is configured for the entity bean or entity bean method. To see what isolation level is used by each access intent policy, refer to:

    Access intent -- isolation levels and update locks

    The access intent policy can be changed by editing the EJB deployment descriptor.

[{"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":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.0;8.5.5;8.0;7.0","Edition":"Base;Edition Independent;Network Deployment","Line of Business":{"code":"LOB45","label":"Automation"}},{"Product":{"code":"HWQQQ","label":"PRODUCT NOT FOUND"},"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Component":"Application Programming - JDBC","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8;7","Edition":"Edition Independent","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSNVBF","label":"Runtimes for Java Technology"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Java SDK","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB36","label":"IBM Automation"}}]

Document Information

Modified date:
15 June 2018

UID

swg21190874