Chapter 4: EJB CMP Tuning

Concurrency control options

Concurrency control prevents overlapping updates from entity instances running in different threads or different servers, or from applications running outside of EAServer. There are two approaches for concurrency control:

In the Pessimistic concurrency control (PCC) model, data rows are locked when read, for the duration of the EAServer transaction. This method can introduce database deadlocks and usually reduces the scalability of the application.
In the Optimistic concurrency control (OCC) model, data rows are not locked when read. Timestamps are used for concurrency control; the timestamp can be a timestamp column in the database that is updated every time the row is modified, or it can be the row data itself. At the end of the transaction, the in-memory timestamp value is compared to the timestamp value in the database, and the transaction rolls back if the values do not match.

OCC allows greater scalability than PCC for most CMP entity beans. However, when using OCC, you must code your application to retry rejected updates, or you must enable automatic transaction retry for the application components as described below.

PCC can perform better than OCC when your beans are mapped to tables with very high update contention. In these cases, the overhead of retrying transactions that fail due to update collisions can outweigh that caused by using database locks. If you have configured OCC as described below, and see many “TRANSACTION_ROLLEDBACK: Optimistic Concurrency Control” messages in the server log, you should try PCC on the component identified in these messages.

Enabling PCC

To configure pessimistic concurrency control, you can do one of the following:

Enable the Select With Lock option on the Persistence/General subtab. When using jagtool or XML configuration files, set com.sybase.jaguar.component.selectWithLock to true.
Enable the Select for Update option by setting the com.sybase.jaguar.component.selectForUpdate property to true on the Advanced tab in the Component Properties dialog box or by using jagtool or an XML configuration file. This setting requests an exclusive database lock be obtained at select time to avoid deadlocks during lock promotion. Also consider configuring the database table for row-level locking.

For databases such as Sybase Adaptive Server Enterprise that do not support select for update locking syntax, EAServer locks rows by issuing a no-change update statement. The component property com.sybase.jaguar.component.touchColumn specifies which column to update. If you do not set this property, EAServer uses the first non-key column. For best performance, specify the column with the datatype that can be updated most quickly. For example, int columns can be updated more quickly than varchar columns.
Configure the table-mapping select queries and add “holdlock” or the appropriate lock syntax for your database. For more information, see “Configuring table-mapping properties” in Chapter 27, “Creating Entity Components,” in the EAServer Programmer’s Guide.

Also make sure that OCC is disabled by setting the Timestamp field to “none” on the Persistence/General subtab in the Component Properties dialog box.

Enabling OCC

When using OCC, each update statement contains SQL logic that determines if the last-read timestamp matches the stored value, and rolls back the transaction if the timestamp does not match. In other words, updates based on stale data are rejected. There are several options for using timestamps:

Use a timestamp column: each table contains a timestamp column, which can be a database timestamp type (if supported) or an integer column that is incremented for every update. This option provides good performance if your database and table schema can support it.
Use all-values comparison: on update, all row values are compared to the last-read values to detect update collisions. OCC with all-values comparison is the default concurrency control model. Performance with this option is worse than when using a single timestamp column, particularly if the table contains many columns or wide columns (such as Sybase text or image columns). Whenever possible, the use of a timestamp column is recommended in these cases.
Use a table-level timestamp: the timestamp is a single integer counter that is incremented for every update, insert, or delete in the main table. This option provides the best performance for CMP entity beans that are mapped to read-mostly (or read-only) tables when verified results are required to meet transaction isolation requirements. For best results, use table-level timestamps with a Sybase CMP wrapper driver to allow verification queries to be batched with other deferred operations. See “Using CMP JDBC wrapper drivers” for more information.

Configuring OCC options

To enable OCC, first verify that PCC is disabled, then configure the timestamp mechanism of your choice.

To check that PCC is disabled, verify that the Select With Lock option on the Persistence/General subtab in the EAServer Manager Component Properties dialog box is disabled. On the Advanced tab, verify that com.sybase.jaguar.component.selectForUpdate is not set or set to false. When using jagtool or XML configuration files, verify the properties com.sybase.jaguar.component.selectWithLock and com.sybase.jaguar.component.selectForUpdate are both false.

Specify the timestamp mechanism in the Timestamp field on the Persistence/General subtab in the EAServer Manager Component Properties dialog box. Table 4-1 describes the allowable values. When using jagtool or XML configuration files, set the com.sybase.jaguar.component.timestamp property. If multiple tables are used and you specify a timestamp column, all tables must contain a column with the same name and datatype.

**Table 4-1: Timestamp field values**
To configure	Set the timestamp value to
A timestamp column	The name of a single column in each table that serves as the timestamp to detect update collisions. If the component uses multiple tables, each must contain a timestamp column with this name. The column type can be: A 4-byte integer – this is the default timestamp column type. All processes that update the table(s) must increment the timestamp with each update, or your DBA can create an update trigger to increment the timestamp automatically. The database timestamp type – you can use the timestamp datatype if using Sybase Adaptive Server Enterprise or Adaptive Server Anywhere version 7.0 or later. You must also define a field mapping property to specify the timestamp datatype as described in “Setting field-mapping properties” in the EAServer Programmer’s Guide. For example, if the column name is ts, specify the mapping as: ts[dbts not null] dbts is a logical type name mapped to the timestamp type in the Sybase_ASE and Sybase_ASA database types. If the database does not support timestamps, a 4-byte integer counter is used instead.
A table level timestamp	A table and column name, in the form `ts_table.ts_column`, where ts_table specifies the timestamp table and ts_column specifies the name of the timestamp column in the timestamp table. The specified timestamp table must be separate from the main table. The timestamp tables can contain multiple columns, to allow use of one timestamp table by multiple entity beans. Timestamp tables are automatically created if they do not exist. A timestamp table can be shared among multiple components even when only one column is present in the timestamp table. In other words, a single timestamp value can be shared by multiple tables. This helps further improve performance for a group of read-mostly tables. However, any insert, delete, or update on any of the tables results in all cache entries being discarded. When using a timestamp table, database triggers are required to increment the timestamp for each update, delete, or insert to tables that are mapped to the component or components that require the timestamp. You can set the component property com.sybase.jaguar.component.ts.triggers property so EAServer creates triggers, create triggers yourself, or add code to existing triggers.
All values comparison	Leave blank.
PCC	Set the value to “none” to disable optimistic concurrency control. In this case, you are strongly advised to configure locking as described in “Enabling PCC”.

Enabling automatic transaction retry

EAServer can automatically retry transactions that are rolled back—method calls back to the last transaction boundary are retried by the stub code. This feature is useful for For EJB CMP entity beans and entity components that use automatic persistence and optimistic concurrency control.

Auto-retry must be enabled for the component that initiates the transaction, which is typically a session bean in EJB applications. Auto-retry works only for intercomponent calls, not for direct invocations of entity beans from the Web tier or base clients.

Auto-retry can be configured in component and server properties as follows:

In component properties, use the Advanced tab to set the property com.sybase.jaguar.component.tx_retry. A value of true enables auto-retry. A value of false disables auto-retry. If this property is not set, the value of the server property com.sybase.jaguar.server.tx_retry is used. If neither the component property or server property is set, the default is false.
In server properties, use the Advanced tab to set the property com.sybase.jaguar.server.tx_retry. The default of false disables auto-retry for all components for which auto-retry is not explicitly enabled. Specify true to enable auto-retry for components for which auto-retry is not explicitly set to false.

Auto-retry is not appropriate for all applications. For example, an end user may want to cancel a purchase if the item price has risen. If auto-retry is disabled, clients must be coded to retry or abort transactions that fail because of stale data. The exception thrown is CORBA::TRANSIENT (for EJB clients, this exception is the root cause of the java.rmi.RemoteException thrown by the EJB stub).

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.

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).

Using soft locking

You can configure in-server soft locking of database rows used by EJB CMP entity beans that use the isolation level repeatable_read_with_cache.

If you enable soft locking for a component, EAServer applies a soft lock to each row selected by an instance, which prevents other component instances running in the server from updating the row. Soft locking must be used with optimistic concurrency control (OCC). The soft lock prevents update collisions between instances in the same server, while OCC prevents update collisions with external applications and instances running in another server.

You can enable soft locking by setting the following component properties:

com.sybase.jaguar.component.softLock – a value of true enables soft locking. The default of false disables soft locking.
com.sybase.jaguar.component.softLock.timeout – the timeout period for soft-locked rows, specified in seconds. Soft locks use a timeout mechanism to avoid deadlock. The default is 5. If too many “soft lock timeout” errors are reported in the server log, increase the timeout.

As an alternative to pessimistic locking, OCC with soft locking may improve performance if there is heavy update contention among entity bean instances running in a single-server deployment or in small-to-medium sized clusters. In clusters, if you see excessive OCC update failures, you may need to switch to pessimistic database locking as described in “Enabling PCC”.