|
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). |
|
|
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.
|