Access intent -- isolation levels and update locks

WebSphere Application Server access intent policies provide a consistent way of defining the isolation level for CMP bean data across the different relational databases that might be used in your environment. Within a deployed application, the combination of an access intent policy concurrency definition and access type signifies the isolation level value that WebSphere Application Server sets on the database connection. This combination also signifies the update lock flag that Application Server passes to the database through a JDBC prepared statement. Within the database, the isolation level that is configured by WebSphere Application Server corresponds to a single setting that can differ across database vendors.

The following matrix shows how access intent policies correspond to different database isolation levels and update lock settings:

Access Intent profile	Isolation level						Update lock implementation
	DB2	Oracle*	SyBase	Informix	Cloudscape	SQL Server
wsPessimisticUpdate- Weakest LockAtLoad (Default policy)	RR	RC	RR	RR	RR	RR	No (*Oracle, Yes)
wsPessimisticUpdate	RR	RC	RR	RR	RR	RR	Yes
wsPessimisticRead	RR	RC	RR	RR	RR	RR	No
wsOptimisticUpdate	RC	RC	RC	RC	RC	RC	No
wsOptimisticRead	RC	RC	RC	RC	RC	RC	No
wsPessimisticUpdateNo-Collisions	RC	RC	RC	RC	RC	RC	No
wsPessimisticUpdate- Exclusive	S	S	S	S	S	S	Yes

• RC = JDBC Read committed

• RR = JDBC Repeatable read

• S = JDBC Serializable

• *

  Note: Oracle does not support JDBC Repeatable Read (RR). Therefore, wsPessimisticUpdate-weakestLockAtLoad and wsPessimisticUpdate behave the same way on Oracle as do wsPessismisticRead and wsOptimisticRead. Because of an Oracle restriction, the OracleXADataSource JDBC class cannot run with an S transaction isolation level. Therefore, one cannot use this class to run an application containing enterprise beans with access intent policies that are configured to cause the bean to load with S isolation.

• Setting access intent policies per EJB method support is deprecated for v6. It is recommended that you set access intent only for the entire bean.

 

Structured Query Language (SQL) keywords and restrictions

The following table shows which SQL keywords are used during update intent locking, as well as any restrictions imposed on the SQL.

Database	SQL syntax used for locking update	join restrictions	order by restrictions	subselect restrictions	aggregation restrictions
DB2	FOR UPDATE OF	not allowed	not allowed	not allowed	not allowed
DB2 UDB for iSeries	FOR UPDATE OF	not allowed	allowed with limitations*	allowed with limitations*	not allowed
DB2 on z/OS V8.x	WITH RS/RR USE AND KEEP UPDATE LOCKS	none	none	none	none
Oracle	FOR UPDATE	none	none	none	none
Cloudscape	FOR UPDATE OF	not allowed	not allowed	not allowed	not allowed
Informix	FOR UPDATE	not allowed	not allowed	not allowed	not allowed
Sybase	FOR UPDATE	not allowed	not allowed	not allowed	not allowed
Sqlserver	UPDLOCK	not allowed	not allowed	not allowed	not allowed

*

Note: For details on the limitations for these permitted SQL restrictions, refer to the DB2 Universal Database for iSeries SQL Reference.

 

---