Configuring CMP isolation level

When using OCC, you can set the component property com.sybase.jaguar.component.cmp_iso_level to specify the effective transaction isolation level for CMP entity beans. This setting allows the performance benefits of OCC, while also enforcing an effective transaction isolation level as you would use with pessimistic concurrency control. Table 4-2 lists the allowable isolation levels.

**Table 4-2: CMP isolation level values**
Setting	Effect
`read_cache`	ejbLoad is satisfied by reading from the object cache if possible. Otherwise, data is loaded from the remote database. Not recommended, as use of this isolation level can result in “lost” updates. Instead, use `read_cache_verify_updates`.
`read_cache_verify_updates`	ejbLoad is satisfied by reading from the object cache if possible. Otherwise, data is loaded from the remote database. If the entity is changed or removed, the corresponding SQL update or delete verifies that the data was not changed after it was loaded from the DBMS. This setting is suitable when it is acceptable for a read-only transaction to use stale cache data. To limit the use of stale data, specify a cache timeout for the object cache or configure database change notification as described in “Enabling database change notification”.
`read_committed`	ejbLoad is satisfied by reading from the remote database. If the entity is changed or removed, the corresponding SQL update or delete does not verify that the data was not changed after it was loaded from the DBMS. Not recommended, as use of this isolation level can result in “lost” updates. Instead, use `read_committed_verify_updates`.
`read_committed_verify_updates`	ejbLoad is satisfied by reading from the remote database. If the entity is changed or removed, the corresponding SQL update or delete verifies that the data was not changed after it was loaded from the DBMS. This setting provides a good balance of data integrity and performance. However, for some application data models, the maintenance of full data integrity requires a higher isolation level such as `repeatable_read`. `read_committed_verify_updates_with_cache` may provide better performance.
`read_committed_with_cache`	ejbLoad is satisfied by reading from the object cache if possible. Otherwise, data is loaded from the remote database. If the entity is changed or removed, the corresponding SQL update or delete does not verify that the data was not changed after it was loaded from the DBMS. Otherwise, for read-only access, and only if ejbLoad was satisfied from cache, a commit-time verification ensures that the data has not changed since it was originally loaded from the DBMS. This ensures that any cached data that was used is still current at commit time, but does not prevent concurrent or conflicting updates. This setting is not recommended, as it can result in lost updates. Instead, use `read_committed_verify_updates_with_cache`.
`read_committed_verify_updates_with_cache`	ejbLoad is satisfied by reading from the object cache if possible. Otherwise, data is loaded from the remote database. If the entity is changed or removed, the corresponding SQL update or delete verifies that the data was not changed after it was loaded from the DBMS. Otherwise, for read-only access, and only if ejbLoad was satisfied from cache, a commit-time verification ensures that the data has not changed since it was originally loaded from the DBMS. This ensures that any cached data that was used is still current at commit time. This setting does not prevent concurrent updates but does prevent conflicting updates. This setting is suitable when it is not acceptable for a read-only transaction to use stale data, and where commit-time verification is cheaper than satisfying ejbLoad from the DBMS; in particular, where a table timestamp is used, or where a CMP wrapper driver is used (the CMP wrapper drivers can batch verification statements together at commit time).
`repeatable_read`	ejbLoad is satisfied by reading from the remote database. If the entity is changed or removed, the corresponding SQL update or delete will verify that the data was not changed after it was loaded from the DBMS. Otherwise, for read-only access, a commit-time verification ensures that the data has not changed since it was loaded from the DBMS. If pessimistic locking is enabled with the Select With Lock or Select For Update option, and is supported by the DBMS, verification is skipped as the shared/exclusive locks that are obtained at load time will prevent conflicting updates. WARNING! Pessimistic locking may increase the occurrence of deadlock. This setting is suitable for cases where uncontrolled concurrent updates may result in data integrity problems (even for read-only access). `repeatable_read_with_cache` may provide better performance, although if many transactions are updating the same rows, pessimistic locking with no cache is probably preferable.
`repeatable_read_with_cache`	Uses the object cache: ejbLoad is satisfied by reading from the object cache if possible. Otherwise, data is loaded from the remote database. If the entity is changed or removed, the corresponding SQL update or delete verifies that the data was not changed after it was loaded from the DBMS. Otherwise, for read-only access, a commit-time verification ensures that the data has not changed since it was originally loaded from the DBMS. This setting is suitable for cases where uncontrolled concurrent updates may result in data integrity problems (even for read-only access), where it is not acceptable for a read-only transaction to use stale cache data, and where commit-time verification is cheaper than satisfying ejbLoad from the DBMS; in particular, where a table timestamp is used, or where a CMP wrapper driver is used (the CMP wrapper drivers can batch verification statements together at commit time). If there are many concurrent updates from EAServer transactions in the same server, you can configure soft-locking for the component to alleviate update contention—see “Using soft locking”. If many transactions from other sources are updating the same rows, you may get better performance using `repeatable_read` with pessimistic locking.

Table 4-2: CMP isolation level values

Setting

Effect

read_cache

ejbLoad is satisfied by reading from the object cache if possible. Otherwise, data is loaded from the remote database.

Not recommended, as use of this isolation level can result in “lost” updates. Instead, use read_cache_verify_updates.

read_cache_verify_updates

This setting is suitable when it is acceptable for a read-only transaction to use stale cache data. To limit the use of stale data, specify a cache timeout for the object cache or configure database change notification as described in “Enabling database change notification”.

read_committed

ejbLoad is satisfied by reading from the remote database. If the entity is changed or removed, the corresponding SQL update or delete does not verify that the data was not changed after it was loaded from the DBMS.

Not recommended, as use of this isolation level can result in “lost” updates. Instead, use read_committed_verify_updates.

read_committed_verify_updates

ejbLoad is satisfied by reading from the remote database. If the entity is changed or removed, the corresponding SQL update or delete verifies that the data was not changed after it was loaded from the DBMS.

This setting provides a good balance of data integrity and performance. However, for some application data models, the maintenance of full data integrity requires a higher isolation level such as repeatable_read.

read_committed_verify_updates_with_cache may provide better performance.

read_committed_with_cache

ejbLoad is satisfied by reading from the object cache if possible. Otherwise, data is loaded from the remote database. If the entity is changed or removed, the corresponding SQL update or delete does not verify that the data was not changed after it was loaded from the DBMS. Otherwise, for read-only access, and only if ejbLoad was satisfied from cache, a commit-time verification ensures that the data has not changed since it was originally loaded from the DBMS. This ensures that any cached data that was used is still current at commit time, but does not prevent concurrent or conflicting updates.

This setting is not recommended, as it can result in lost updates. Instead, use read_committed_verify_updates_with_cache.

read_committed_verify_updates_with_cache

ejbLoad is satisfied by reading from the object cache if possible. Otherwise, data is loaded from the remote database. If the entity is changed or removed, the corresponding SQL update or delete verifies that the data was not changed after it was loaded from the DBMS. Otherwise, for read-only access, and only if ejbLoad was satisfied from cache, a commit-time verification ensures that the data has not changed since it was originally loaded from the DBMS. This ensures that any cached data that was used is still current at commit time. This setting does not prevent concurrent updates but does prevent conflicting updates.

This setting is suitable when it is not acceptable for a read-only transaction to use stale data, and where commit-time verification is cheaper than satisfying ejbLoad from the DBMS; in particular, where a table timestamp is used, or where a CMP wrapper driver is used (the CMP wrapper drivers can batch verification statements together at commit time).

repeatable_read

ejbLoad is satisfied by reading from the remote database. If the entity is changed or removed, the corresponding SQL update or delete will verify that the data was not changed after it was loaded from the DBMS. Otherwise, for read-only access, a commit-time verification ensures that the data has not changed since it was loaded from the DBMS.

If pessimistic locking is enabled with the Select With Lock or Select For Update option, and is supported by the DBMS, verification is skipped as the shared/exclusive locks that are obtained at load time will prevent conflicting updates.

WARNING! Pessimistic locking may increase the occurrence of deadlock.

This setting is suitable for cases where uncontrolled concurrent updates may result in data integrity problems (even for read-only access).

repeatable_read_with_cache may provide better performance, although if many transactions are updating the same rows, pessimistic locking with no cache is probably preferable.

repeatable_read_with_cache

Uses the object cache: ejbLoad is satisfied by reading from the object cache if possible. Otherwise, data is loaded from the remote database. If the entity is changed or removed, the corresponding SQL update or delete verifies that the data was not changed after it was loaded from the DBMS. Otherwise, for read-only access, a commit-time verification ensures that the data has not changed since it was originally loaded from the DBMS.

This setting is suitable for cases where uncontrolled concurrent updates may result in data integrity problems (even for read-only access), where it is not acceptable for a read-only transaction to use stale cache data, and where commit-time verification is cheaper than satisfying ejbLoad from the DBMS; in particular, where a table timestamp is used, or where a CMP wrapper driver is used (the CMP wrapper drivers can batch verification statements together at commit time).

If there are many concurrent updates from EAServer transactions in the same server, you can configure soft-locking for the component to alleviate update contention—see “Using soft locking”.

If many transactions from other sources are updating the same rows, you may get better performance using repeatable_read with pessimistic locking.

If the isolation level is not explicitly set, the default value depends on other property settings, as follows:

If the component Timestamp property is set to “none”, the default is read_committed.

If pessimistic concurrency control is enabled, the default is repeatable_read.

Otherwise, the default is the value of the server property com.sybase.jaguar.server.cmp_iso_level, if set.

Otherwise, the default is read_committed_verify_updates.

If object caching is enabled for the component (com.sybase.jaguar.component.objectCache is set), and the selected isolation level does not end with “_with_cache”, then EAServer uses the next higher isolation level that has the “_with_cache” suffix.

‘serializable’ isolation level EAServer does not directly support the serializable isolation level. You can achieve this level using a table timestamp and the repeatable_read or repeatable_read_with_cache setting. However, the table timestamp mechanism is not suitable for tables that are frequently changed. You can also achieve this isolation level by using bean-managed transaction demarcation, and setting the isolation level for the JDBC connection before each transaction begins (you cannot change the isolation level during a transaction).