How to Avoid Database Resource Contention

In order to avoid resource contention and overwriting each others changes to database tables while allowing concurrent execution, entity bean concurrency and database isolation modes are provided.

Using Database Isolation Modes to Protect Against Resource Contention
Configuring Entity Bean Concurrency Modes For Handling Resource Contention
Specifying Exclusive Write Access to the Database
Effects of the Combination of the Database Isolation and Bean Concurrency Modes
Affects of Concurrency Modes on Clustering

Using Database Isolation Modes to Protect Against Resource Contention

The java.sql.Connection object represents a connection to a specific database. Database isolation modes are provided to define protection against resource contention. When two or more users try to update the same resource, a lost update can occur. That is, one user can overwrite the other user's data without realizing it. The java.sql.Connection standard provides four isolation modes, of which Oracle only supports two of these modes. These are as follows:

TRANSACTION_READ_COMMITTED: Dirty reads are prevented; non-repeatable reads and phantom reads can occur. This level only prohibits a transaction from reading a row with uncommitted changes in it.
TRANSACTION_SERIALIZABLE: Dirty reads, non-repeatable reads and phantom reads are prevented. This level includes the prohibitions in TRANSACTION_REPEATABLE_READ and further prohibits the situation where one transaction reads all rows that satisfy a WHERE condition, a second transaction inserts a row that satisfies that WHERE condition, and the first transaction rereads for the same condition, retrieving the additional "phantom" row in the second read.

Note:
You cannot set the isolation level to serializable if you are using a non-emulated data source. If you do, the non-emulated data source will not work.

You can configure one of these database isolation modes for a specific bean. That is, you can specify that when the bean starts a transaction, the database isolation mode for this bean be what is specified in the OC4J-specific deployment descriptor. Specify the isolation mode on what is important for the bean: parallel execution or data consistency. The isolation mode for this bean is set for the entire transaction.

The isolation mode can be set for each entity bean in the isolation attribute of the <entity-deployment> element. The values can be committed or serializable. The default is committed. To change it to serializable, configure the following in the orion-ejb-jar.xml for the intended bean:

<entity-deployment ...  isolation="serializable"
 ...
</entity-deployment>

There is always a trade-off between performance and data consistency. The serializable isolation mode provides data consistency; the committed isolation mode provides for parallel execution.

Note:

There is a danger of lost updates with the serializable mode if the max-tx-retries element in the OC4J-specific deployment descriptor is greater than zero. The default for this value is zero. If this element is set to greater than zero, then the container retries the update if a second blocked client receives a ORA-8177 exception. The retry would find the row unlocked and the update would occur. Thus, the second client's update succeeds and overwrites the first client's update. If you use serializable, you should consider leaving the max-tx-retries element as zero for data consistency.

If you do not set an isolation mode, you receive the mode that is configured in the database. Setting the isolation mode within the OC4J-specific deployment descriptor temporarily overrides the database configured isolation mode for the life of the global transaction for this bean. That is, if you define the bean to use the serializable mode, then the OC4J container will force the database to be serializable for this bean only until the end of the transaction.

You can specify both entity bean concurrency modes and database isolation modes, where the combination effects the outcome of your resource contention. See "Effects of the Combination of the Database Isolation and Bean Concurrency Modes" for more information.

Configuring Entity Bean Concurrency Modes For Handling Resource Contention

OC4J also provides concurrency modes for handling resource contention and parallel execution within container-managed persistence (CMP) entity beans. Bean-managed persistence entity beans manage the resource locking within the bean implementation themselves. The concurrency modes configure when to block to manage resource contention or when to execute in parallel.

The concurrency modes are as follows:

PESSIMISTIC: This manages resource contention and does not allow parallel execution. Only one user at a time is allowed to execute the entity bean at a single time.
OPTIMISTIC: Multiple users can execute the entity bean in parallel. It does not monitor resource contention; thus, the burden of the data consistency is placed on the database isolation modes.
READ-ONLY: Multiple users can execute the entity bean in parallel. The container does not allow any updates to the bean's state.

To enable the CMP entity bean concurrency mode, add the appropriate concurrency value of "pessimistic", "optimistic", or "read-only" to the locking-mode attribute of the <entity-deployment> element in the OC4J-specific deployment descriptor (orion-ejb-jar.xml). The default is "optimistic". To modify the concurrency mode to pessimistic, do the following:

<entity-deployment ...  locking-mode="pessimistic"
 ...
</entity-deployment>

These concurrency modes are defined per bean and the effects of locking apply on the transaction boundaries.

Parallel execution requires that the pool size for wrapper and bean instances are set correctly. For more information on how to configure the pool sizes, see "Configuring Environment References".

Specifying Exclusive Write Access to the Database

The exclusive-write-access attribute of the <entity-deployment> element states that this is the only bean that accesses its table in the database and that no external methods are used to update the resource. It informs the OC4J instance that any cache maintained for this bean will only be dirtied by this bean. Essentially, if you set this attribute to true, you are assuring the container that this is the only bean that will update the tables used within this bean. Thus, any cache maintained for the bean does not need to constantly update from the back-end database.

This flag does not prevent you from updating the table; that is, it does not actually lock the table. However, if you update the table from another bean or manually, the results are not automatically updated within this bean.

The default for this attribute is false. Because of the effects of the entity bean concurrency modes, this element is only allowed to be set to true for a read-only entity bean. OC4J will always reset this attribute to false for pessimistic and optimistic concurrency modes.

<entity-deployment ...  exclusive-write-access="true"
 ...
</entity-deployment>

Effects of the Combination of the Database Isolation and Bean Concurrency Modes

For the pessimistic and read-only concurrency modes, the setting of the database isolation mode does not matter. These isolation modes only matter if an external source is modifying the database.

If you choose optimistic with committed, you have the potential to lose an update. If you choose optimistic with serializable, you will never lose an update. Thus, your data will always be consistent. However, you can receive an ORA-8177 exception as a resource contention error.

Differences Between Pessimistic and Optimistic/Serializable

An entity bean with the pessimistic concurrency mode does not allow multiple clients to execute a bean (either on the same or on different instances of the same primary key). Only one client is allowed to execute the instance at any one moment.

An entity bean with the optimistic concurrency mode allows multiple instances of the bean implementation to execute in parallel. However, it could result in potential lost updates (and conflicts), because two different transactions may update the same row simultaneously.

Setting the transaction isolation mode to serializable allows the detection of conflicts when they occur. At that moment, the update from one of the transactions raises a SQLException and that transaction is rolled back.

Optionally, you may set the tx-retries attribute of the <entity-deployment> element to a value more than one, so that the transaction is retried.

Affects of Concurrency Modes on Clustering

All concurrency modes behave in a similar manner whether they are used within a standalone or a clustered environment. This is because the concurrency modes are locked at the database level. Thus, even if a pessimistic bean instance is clustered across nodes, the moment one instance tries to execute, the database locks out all other instances.