WebSphere® Application Server access intent policies provide a consistent way of defining the isolation level for CMP bean data across the different relational databases 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 Application Server sets on a database connection. See the articles, Concurrency control, and Access intent and isolation, for more information on concurrency and access type. This combination of properties also signifies the update lock flag that Application Server passes to the database through a JDBC prepared statement.
Databases do not provide as many isolation level definitions as WebSphere Application Server. 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, WebSphere Application Server does not map access intent policies to the parameter values. Instead, Application Server maps access intent policies to the types of isolation level that are common across all database vendors.
Access Intent profile | Isolation level | Update lock implementation | |||||
---|---|---|---|---|---|---|---|
DB2® | Oracle* | SyBase | Informix® | Apache Derby | 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 |
wsPessimisticUpdate No-Collisions | RC | RC | RC | RC | RC | RC | No |
wsPessimisticUpdate- Exclusive | S | S | S | S | S | S | Yes |
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 |