Transaction Isolation Levels and WebSphere Application Server

Technote

Connections used by other 2.x enterprise beans and other non-CMP components

Note: These are the most common types of connections. For other types of connections, refer to the following URL based on your version of WebSphere Application Server:

WAS v6.0 http://publib.boulder.ibm.com/infocenter/wsdoc400/v6r0/index.jsp?topic=/com.ibm.websphere.iseries.doc/info/ae/ae/cdat_isolevel.html

WAS v6.1 http://publib.boulder.ibm.com/infocenter/wasinfo/v6r1/index.jsp?topic=/com.ibm.websphere.express.iseries.doc/info/iseriesexp/ae/cdat_isolevel.html

WAS v7.0 http://publib.boulder.ibm.com/infocenter/wasinfo/v7r0/index.jsp?topic=/com.ibm.websphere.express.iseries.doc/info/iseriesexp/ae/cdat_isolevel.html

For all other JDBC connection instances (connections other than those used by CMP beans), you can specify anisolationlevelon the data sourceresourcereference. For shareable connections that run in global transactions, this method is the only way to set the isolationLevel for connections. Trying to directly set theisolationlevelthrough thesetTransactionIsolation() method on a shareable connection that runs in a global transaction is not allowed. To use a differentisolationlevelon connections, you must provide a differentresourcereference. Set these defaults through your assembly tool.

Eachresourcereferenceassociates with oneisolationlevel. When your application uses thisresourcereferenceJava Naming and Directory Interface (JNDI) name to look up a data source, every connection returned from this data source using thisresource referencehas the sameisolationlevel.

Components needing to use shareable connections with multipleisolationlevels can create multipleresourcereferences, giving them different JNDI names, and have their code look up the appropriate data source for theisolationlevelthey need. In this way, you use separate connections with the differentisolationlevels enabled on them.

It is possible to map these multipleresourcereferences to the same configured data source. The connections still come from the same underlying pool; however, the connection manager does not allow sharing of connections requested byresourcereferences with differentisolationlevels. Consider the following scenario:

o A data source is bound to tworesourcereferences:jdbc/RRResRef andjdbc/RCResRef .
o RRResRef has theRepeatableRead isolationleveldefined. RCResRef has theReadCommitted isolationleveldefined.

If your application wants to update the tables or a BMP bean updates some attributes, it can use the jdbc/RRResRef JNDI name to look up the data source instance. All connections returned from the data source instance have a RepeatableReadisolationlevel. If the application wants to perform a query for read only, it is better to use the jdbc/RCResRef JNDI name to look up the data source.

The resource references can be identified in the WEB-INF/web.xml file of the application. The web.xml will contain something similar to the following to identify the resource reference(s):

<resource-ref id=" ResourceRef_1102108961944 ">
        <res-ref-name> jdbc/RRResRef </res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Application</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
  </resource-ref>

  <resource-ref id=" ResourceRef_1102108961964 ">
        <res-ref-name> jdbc/RCResRef </res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Application</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
  </resource-ref>

The customization of the transaction isolation level is shown in the WEB-INF/ibm-web-ext.xmi file of the application. The ibm-web-ext.xmi will contain something similar to the following to specify the custom isolation level:

<resourceRefExtensions xmi:id="ResourceRefExtension_1" isolationLevel="TRANSACTION_REPEATABLE_READ" >
    <resourceRef href="WEB-INF/web.xml# ResourceRef_1102108961944 "/>
  </resourceRefExtensions>
  <resourceRefExtensions xmi:id="ResourceRefExtension_2" isolationLevel="TRANSACTION_READ_COMMITTED" >
    <resourceRef href="WEB-INF/web.xml# ResourceRef_1102108961964" />
  </resourceRefExtensions>

Note the isolationLevel parameter. This is where the custom isolation level is set. Also note the bolded ResourceRef tag. This references the specific resource reference defined in the web.xml file.

If you do not specify theisolationlevel

The product does not require you to set theisolationlevelon a data sourceresourcereferencefor a non-CMP application module. If you do not specifyisolationlevelon theresourcereference, or if you specify TRANSACTION_NONE, the WebSphere Application Server run time uses a defaultisolationlevelfor the data source. Application Server uses a default setting based on the JDBC driver.

For most drivers, WebSphere Application Server uses anisolationleveldefault of TRANSACTION_REPEATABLE_READ. Use the following table for quickreference:

Database:	DB2
Default isolation level: (for connections used by non-CMP entities)	RR = JDBC Repeatable read (TRANSACTION_REPEATABLE_READ) = *RS (Read Stability) on the IBM i server.

Note: These same defaultisolationlevels are used in cases of direct JNDI lookups of a data source.

o RR = JDBC Repeatable read (TRANSACTION_REPEATABLE_READ)
o RC = JDBC Read committed (TRANSACTION_READ_COMMITTED)

Available beginning in WebSphere Application Server v7.0 all editions

To customize the defaultisolationlevel, you can use the webSphereDefaultIsolationLevel custom property for the data source. In most cases, you should define theisolationlevelin the deployment descriptor when you package the EAR file; however, in certain situations, you might need to customize the defaultisolationlevel. This property will have no effect if any of the above options are used, and this custom property is provided for those situations in which there is no other means of setting theisolationlevel.

Use the following values for webSphereDefaultIsolationLevelcustom property:

Possible values	JDBCisolationlevel	DB2isolationlevel
8	TRANSACTION_SERIALIZABLE	Repeatable Read (RR)
4 (default)	TRANSACTION_REPEATABLE_READ	Read Stability (RS)
2	TRANSACTION_READ_COMMITTED	Cursor Stability (CS)
1	TRANSACTION_READ_UNCOMMITTED	Uncommitted Read (UR)
0	TRANSACTION_NONE	No Commit (NC)

Note : If TRANSACTION_NONE is used, the DB file does not have to be journaled.

To define this custom property for a data source, you should do the following:

1.	ClickResources >JDBC provider >JDBC_provider .
2.	ClickData sources in the Additional Properties section.
3.	Click the name of the data source.
4.	ClickCustom properties .
5.	Create the webSphereDefaultIsolationLevelcustom property a. ClickNew . b. Enter webSphereDefaultIsolationLevelfor the name field. c. Enter one of the possible values in the value field.

Application Server sets theisolationlevelby prioritizing the available settings. Application Server will set theisolationlevelbased on the values for the following, in this order:

1. Resourcereferenceisolationlevel
2. Isolationlevelthat is specified by the access intent policy
3. Custom property that configures anisolationlevel
4. Application Server's default setting.






System i Support

IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright © 1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information.