Access intent -- isolation levels and update locks
WAS access intent policies provide a consistent way of defining the isolation level for CMP bean data across the different relational databases in the environment.
Within a deployed application, the combination of an access intent policy concurrency definition and access type signifies the isolation level value that WAS sets on a database connection. This combination of properties also signifies the update lock flag that WAS passes to the database through a JDBC prepared statement.
Databases do not provide as many isolation level definitions as WAS. Databases define an isolation level as one of only three types. Furthermore, only one parameter indicates the type of isolation level that the databases set on incoming connections. Each of the three types can be represented by a different parameter value, as determined by each database vendor.
For example, one database might define an isolation level as RR (JDBC Repeatable read), whereas a different database might define the same isolation level as RC (JDBC Read committed).
Because of this inconsistency, WAS does not map access intent policies to the parameter values. Instead, WAS maps access intent policies to the types of isolation level that are common across all 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 Apache Derby SQL Server wsPessimisticUpdate- Weakest LockAtLoad (Default) 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 wsPessimisticUpdate No-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
* Oracle does not support 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, we 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.
Set access intent policies per EJB method support is deprecated for Version 6.0. IBM recommends set access intent only for the entire bean.
New for MS SQL Server 2005: MS SQL Server 2005 offers a new option for the Read Committed isolation level and a new option for the Serializable isolation level:
- Read Committed with Snapshots
- Transaction Snapshot (for Serializable)
Both options use optimistic locking. To use Read Committed with Snapshots instead of Read Committed, enable the READ_COMMITTED_SNAPSHOT setting for the database according to the MS SQL Server 2005 documentation. To use Transaction Snapshot instead of Serializable, configure the custom data source property, snapshotSerializable, to "true" and enable the ALLOW_SNAPSHOT_ISOLATION setting for the database according to the MS SQL Server 2005 documentation.
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.
Table 2. SQL keywords and restrictions
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(V5R3 and earlier) FOR UPDATE OF not allowed allowed with limitations* allowed with limitations* not allowed DB2 UDB for iSeries (V5R4 and later) WITH RS/RR USE AND KEEP EXCLUSIVE LOCKS 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 DB2 UDB workstation V8.2 WITH RS/RR USE AND KEEP UPDATE LOCKS none none none none Oracle FOR UPDATE none none none none Apache Derby 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 *
For details on the limitations for these permitted SQL restrictions, refer to the DB2 Universal Databasefor iSeries SQL Reference. We can find this document in the iSeries Information Center, V5 Release 4. In the Contents navigation area, click Database > Reference > SQL Reference.
Related concepts
Access intent and isolation level
Concurrency control