Determining and changing the isolation level
 Technote (troubleshooting)
 
Problem(Abstract)
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 deadlock is reduced.

WebSphere® Application Server V4 and V5 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_SERIALIZABLE Repeatable Read (RR)
TRANSACTION_REPEATABLE_READ Read Stability (RS)
TRANSACTION_READ_COMMITTED Cursor Stability (CS)
TRANSACTION_READ_UNCOMMITTED Uncommitted 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 WebSphere Application Server V4 (or a V4 data source that is used in WebSphere Application Server V5):

  • 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 in the Application Assembly Tool (AAT) or in WebSphere Studio Application Developer. In the AAT, expand the EJB, and click on Method Extensions. You can then select All Methods or individual methods, and set the isolation level on the General tab.

For WebSphere Application Server V5:

  • 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, which can be edited in the AAT, WebSphere Studio Application Developer, or the Application Server Toolkit (ASTK). When the indirect JNDI lookup is done, the resource reference properties, including the specified isolation level, are used to connect to the database.

  • 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:

    EJB_Container/swg21162747.html

    The access intent policy can be changed by editing the EJB deployment descriptor in the AAT, WebSphere Studio Application Developer, or ASTK.
 
 
Cross Reference information
Segment Product Component Platform Version Edition
Data & Information Management DB2 Universal Database for Linux- UNIX and Windows Application Programming - JDBC Platform Independent 8, 7 Edition Independent
Application Servers Runtimes for Java Technology Java SDK
 
 


Document Information


Product categories: Software > Application Servers > Distributed Application & Web Servers > WebSphere Application Server > DB Connections/Connection Pooling
Operating system(s): Windows
Software version: 5.1.1.2
Software edition:
Reference #: 1190874
IBM Group: Software Group
Modified date: Jul 31, 2005