Concurrency control governs how backend resource managers manage concurrent access to data. Concurrency control addresses the competing requirements of applications that require read access to data against those of applications that require update access. Data integrity is essential. Resource managers typically provide mechanisms that enable applications to influence how a resource manager manages concurrency control. Typical resource managers implement some form of locking to provide for concurrency control. An application often can use one of two common concurrency control schemes: pessimistic concurrency control or optimistic concurrency control.
WebSphere Application Server offers a choice between pessimistic and optimistic concurrency control for container managed persistence (CMP) entity beans (EJBs). Pessimistic concurrency control is the default. You choose the concurrency control scheme during application assembly using the Application Assembly Tool (AAT):
The following sections describe how to use the pessimistic and optimistic concurrency control schemes with the WebSphere Application Server using relational resource managers (JDBC):
Pessimistic concurrency control delegates all concurrency control responsibility to the resource manager.
An application uses the JDBC isolation level to control the type of locking the resource manager performs. With WebSphere, IBM Extensions in the Application Assembly Tool control the isolation level on a method level. You can annotate each method on the entity bean's remote (or home) interface with an isolation level. The allowed JDBC isolation levels are as follows:
Relational resource managers typically satisfy the isolation level requirement of the JDBC specification, but their underlying implementations are different. Some of the differences might be visible to the application.
WebSphere also provides extensions to annotate methods defined on an entity bean's remote and home interfaces with an access intent attribute in the Application Assembly Tool. Mark a method with access intent of READ if that method does not update the entity bean's persistent state. This is called a READ method. Mark a method with access intent of UPDATE if that method updates the entity bean's persistent state. This is called an UPDATE method. The default access intent is UPDATE. The access intent attribute communicates a locking hint to the resource manager. Its use governs whether the SQL "FOR UPDATE" clause is included in the SELECT statements executed by the container's persistence manager.
The combination of isolation level, relational resource manager, and access intent work together to influence whether the resource manager can support either a read or update access by an application. The tables in this section detail recommended combinations for read and update access.
Table 1. Pessimistic read access
| Database | Isolation level | First method in transaction | Effect of adding update method |
|---|---|---|---|
| DB2 | TX_REPEATABLE_READ | READ | Update method might cause lock promotion failure |
| Informix | TX_REPEATABLE_READ | READ | Update method might cause lock promotion failure |
| Sybase | TX_REPEATABLE_READ | READ | Update method might cause lock promotion failure |
| SQL Server | TX_REPEATABLE_READ | READ | Update method might cause lock promotion failure |
| Oracle | TX_READ_COMMITTED | READ | Update method might cause the loss of updated data |
As to the Isolation level in Table 1, the CMP persistence manager does not hold JDBC ResultSet open across load and store of an entity bean; lower levels of isolation acquire locks for the life of the ResultSet only. You must use TX_REPEATABLE_READ to maintain locks across load and store. You can use a lower isolation level if the transaction is comprised of entirely READ methods; otherwise, the transaction might lose updated data. Note that Oracle does not support TX_REPEATABLE_READ; use TX_READ_COMMITTED for Oracle because Oracle holds locks acquired by SELECT FOR UPDATE even when the ResultSet is closed.
As to the First method in transaction, the first method causes an entity bean's essential state to be loaded from the underlying resource manager. The method's access intent attribute determines if the SQL SELECT statement includes the SQL FOR UPDATE clause. An UPDATE method adds the FOR UPDATE clause to the SELECT statement. FOR UPDATE is not added to the SELECT statement for a READ method. The FOR UPDATE clause instructs the resource manager to obtain update locks during the SELECT operation. This applies to entity beans configured for commit-time options B and C only; commit-time option A beans are loaded once and only once. The entity bean container is assumed to have exclusive access to the database and, therefore, locking is not required.
As to the Effect of adding update method, a transaction that starts with a READ method and subsequently includes an UPDATE method might deadlock on its lock promotion because the resource manager cannot acquire an update lock due to other transactions holding read locks. A transaction rollback ensures data integrity. For some resource managers, the deadlock is detected and broken by returning an SQL ERROR that indicates a deadlock has occurred. For other resource managers, a timeout occurs waiting for the update lock to be acquired. Further, some resource managers neither detect the deadlock nor timeout the lock waiting; for these resource managers, WebSphere transaction timeout will rollback the transaction.
In the case of Oracle, a transaction that starts with a READ method and subsequently includes an UPDATE method might lose updated data. This is because Oracle does not keep locks unless FOR UPDATE is specified on the SQL statement (that is, start the transaction with an UPDATE method).
The WebSphere runtime monitors transactions involving entity beans using pessimistic concurrency control, watching for the following potential hazards:
If WebSphere detects either condition, the WebSphere runtime issues a warning message. A detected condition is reported once and only once per unique EJB type in a given server instance, no matter how many times the condition might actually occur so that the system is not overwhelmed by redundant messages.
WARNING: You must use the recommended combinations in the following table to ensure that updates are performed with integrity. Failure to follow these instructions might result in lost updates.
Table 2. Pessimistic update access
| Database | Isolation level | First method in transaction |
|---|---|---|
| DB2 | TX_REPEATABLE_READ | UPDATE | Informix | TX_REPEATABLE_READ | UPDATE |
| Sybase | TX_REPEATABLE_READ | UPDATE |
| SQL Server | TX_REPEATABLE_READ | UPDATE |
| Oracle | TX_READ_COMMITTED | UPDATE |
As to the Isolation level in Table 2, the explanation provided for Table 1 applies to Table 2.
As to the First method in transaction in Table 2, the explanation provided for Table 1 applies to Table 2.
With optimistic concurrency, the application shares responsibility for concurrency control with the resource manager. The main intent is to hold database locks for the least time possible. With the database unlocked, an additional technique is required to ensure that updates can occur with integrity. WebSphere uses an optimistic concurrency control scheme based on an over-qualified update.
An over-qualified update is an SQL UPDATE that includes an SQL WHERE clause that specifies a number of table columns in addition to the primary key and is used to check that their values have not changed since the time the record was read. The application is responsible for saving the values read and specifies them in the WHERE clause at update time. A fully qualified update is created by forming the WHERE clause out of all eligible table columns. If any of the table columns change after the application reads them but before updating them, the query predicate will fail to find a matching row in the database and the update will fail. This is known as an optimistic update failure.
Because WebSphere manages persistence for CMP entity beans, it acts as an agent of the application and assumes the application's responsibility in the optimistic concurrency control scheme. WebSphere uses the fully qualified update technique. The update is fully qualified in that WebSphere attempts to use all available columns. Two factors determine whether columns are available:
Note that WebSphere does not use all column types in the formation of the fully qualified update's query predicate. Ineligible column types include--
CMP entity beans that have CMP fields that map to only primary key fields and ineligible column types cannot use the optimistic concurrency control scheme. The section "Choosing a concurrency control scheme" provides alternatives.
The following table details recommended combinations for read and update access:
Table 3. Optimistic read and update access
| Database | Isolation level | First method in transaction |
|---|---|---|
| DB2 | TX_READ_COMMITTED | N/A | Informix | TX_READ_COMMITTED | N/A |
| Sybase | TX_READ_COMMITTED | N/A |
| SQL Server | TX_READ_COMMITTED | N/A |
| Oracle | TX_READ_COMMITTED | N/A |
As to the Isolation level in Table 3, optimistic concurrency requires locks only during the read and update operations. There is no need to hold locks across the read or update periods. You should not use TX_REPEATABLE_READ or TX_SERIALIZABLE because they can cause database locks to be acquired for the life of the transaction, which defeats the purpose of optimistic concurrency. Only readonly transactions should use TX_READ_UNCOMMITTED; transactions that update entity beans should never use TX_READ_UNCOMMITTED.
As to the First method in transaction in Table 3, the explanation provided for Table 1 applies to Table 3. Additionally, for optimistic concurrency control, the access intent of the first method in the transaction does not determine whether FOR UPDATE is used in the SQL SELECT statement. FOR UPDATE is never used for optimistic concurrency control.
WebSphere EJB container reports optimistic update failures by throwing com.ibm.ejs.persistence.OptimisticUpdateFailureException. This causes transaction rollback. For container-demarcated transactions (CMT) the client receives a java.rmi.RemoteException that wraps the OptimisticUpdateFailureException. For bean-demarcated transactions (BMT) the client receives javax.transaction.TransactionRolledBackException.
To choose a concurrency control scheme, first determine whether you can use optimistic concurrency control safely. If not, use pessimistic concurrency control.
WARNING: You cannot use optimistic concurrency control safely under the following circumstances:
You can use optimistic concurrency in this situation if you can add an eligible column type to the CMP mapping. For instance, if you add an "optimistic concurrency update counter" to the CMP mapping and to the underlying table, and increment the counter each time an entity is updated.
Further, entity beans with a bean cache policy set of activate once, load atActivation should not use optimistic concurrency control. You use these bean cache settings for beans that have exclusive access to the database. Because beans configured in this manner are assumed to have exclusive database access, there is no need to employ a particular concurrency control scheme. The default concurrency control scheme of pessimistic with default isolation levels is a reasonable choice for this bean cache configuration. Optimistic concurrency control is not supported for this bean cache configuration.
If you can use either model, assess other factors:
If the application access pattern is predominately read access, then the optimistic approach can improve concurrency and throughput.
If the application access pattern is predominately update access, then pessimistic approach might be advantageous in that it avoids optimistic concurrency update failures.
Either concurrency control model can fail to update. The pessimistic model can fail when locks are not obtained quickly enough. The optimistic model can fail when the underlying data changes after it is read. The difference is that the pessimistic scheme fails at the time of read; optimistic fails at the time of update. This means that pessimistic fails before the work begins; optimistic fails after the work begins but before it can be completed.
Thus, if the cost of recovering from a failed update is relatively high, pessimistic might be a better choice.