Skip Headers

Oracle TopLink Developer's Guide 10g Release 3 (10.1.3) B13593-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# Database Transaction Isolation Levels

Achieving a particular database transaction isolation level in a TopLink application is more involved than simply using the `DatabaseLogin` method `setTransactionIsolation`.

In a typical TopLink application and in J2EE applications that require persistence in general, a variety of factors affect when database transaction isolation levels apply and to what extent a particular database transaction isolation can be achieved.

This section describes these factors and provides guidelines on configuring and using TopLink to achieve each database transaction isolation level to the extent possible given these factors.

This section includes the following:

- General Factors Affecting Transaction Isolation Level

- Read Uncommitted Level

- Read Committed Level

- Repeatable Read Levels

- Serializable Read Levels

## General Factors Affecting Transaction Isolation Level

This section describes some of the important factors and variables that may affect the degree to which your TopLink application can achieve a particular database transaction isolation level. These factors include the following:

- External Applications

- TopLink Coordinated Cache

- DatabaseLogin Method setTransactionIsolation

- Reading Through the Write Connection

- Managing Cache Access

- CMP and External Transactions

### External Applications

In many cases, your TopLink application is not the only application that can update to the database. External, non-TopLink applications, can also update the database at any time.

In this case, your TopLink application must use the `ObjectLevelReadQuery` method `refreshIdentityMapResult` (see "Refreshing the Cache") or `Descriptor` methods `alwaysRefreshCache` and `disableCacheHits` (see "Configuring Cache Refreshing").

For more information, see "Managing Cache Access".

If the external application can update a version field in the database, your TopLink application could use `alwaysRefreshCache` in conjunction with `Descriptor` method `onlyRefreshCacheIfNewerVersion` to ensure that refresh operations are performed only when required.

### TopLink Coordinated Cache

Consider multiple TopLink applications (each running on its own application server instance) configured to use a distributed, coordinated cache (as described in "Understanding Cache Coordination"). A TopLink application instance first commits changes to its own cache before the change is distributed to other caches. Because cache coordination is not instantaneous, there is a possibility that one TopLink application instance may read an older version of an object from its cache before a cache coordination message is received.

The only way to guarantee that an TopLink application gets the most up-to-date version of an object is to use `Descriptor` methods `alwaysRefreshCache` and `disableCacheHits`. For more information on the `disableCacheHits` method, see "Managing Cache Access".

Note: Using `Descriptor` methods `alwaysRefreshCache` and `disableCacheHits` will result in frequent database hits. Use only when absolutely necessary.

### DatabaseLogin Method setTransactionIsolation

Use the `DatabaseLogin` method `setTransActionIsolation` to configure the database transaction isolation level that TopLink applies to any database connection it obtains, for example:

    databaseLogin.setTransactionIsolation(DatabaseLogin.TRANSACTION_SERIALIZABLE);

This method sets the transaction isolation level used for both database read and write operations on the database connections obtained from either an internal or external connection pool (see "Connection Pools"), for both internal transactions and external transactions as in the case of CMP.

However, with TopLink, by default read operations use a different database connection than write operations, typically obtained from an external connection pool, or may use the cache, bypassing the database entirely. Thus, with TopLink, by default, read operations are always performed outside the transaction or unit of work, even if you perform the read operation within a transaction or unit of work. Although database transaction isolation applies to both read and write connections, the read is not performed as part of the transaction. Therefore, the read operation overrides the transaction isolation set on the database.

Depending on the level of transaction isolation you are trying to achieve, you may require that the same transaction isolation be applied to both read and write operations. You must take special action to make TopLink use the same connection for both read and write operations. For more information, see "Reading Through the Write Connection".

### Reading Through the Write Connection

Recall that TopLink, by default, performs read operations with a different database connection than used for write operations ("DatabaseLogin Method setTransactionIsolation"). However, from the perspective of database transaction isolation, there is a one-to-one relationship between transaction and database connection: that is, all database operations (including read operations) must use the same database connection in order to achieve a particular database transaction isolation level.

In general, when TopLink performs a read operation, if a write connection already exists, TopLink will use the write connection for the read operation. This is called "reading through the write connection." If a write connection does not yet exist, TopLink will acquire another connection and use that for the read operation.

You can configure TopLink to allocate a write connection early using any of the following:

- Pessimistic Locking Query

- Unit of Work Method beginTransactionEarly

- ConnectionPolicy Method setShouldUseExclusiveConnection

Caution: Reading through the write connection will lock the object being read. This will affect performance and reduce concurrency. Oracle recommends that you do not use these advanced techniques unless strict database transaction isolation is absolutely necessary.

For more information, see "CMP and External Transactions".

#### Pessimistic Locking Query

When you use pessimistic locking (`ObjectLevelReadQuery` methods `acquireLocks` or `acquireLocksWithoutWaiting` or `Session` method `refreshAndLockObject`), TopLink does the following:

- Allocates a write connection used for both read and write operations.

- Always reads from the database.

- Always updates the cache with the database version.

#### Unit of Work Method beginTransactionEarly

This method is advanced API. If you call `beginTransactionEarly` on an instance of a unit of work, all read operations should be performed through that instance of the unit of work.

This method starts a database transaction immediately: any objects you read will lock data in the database before commit time, reducing concurrency.

Oracle does not recommend using `beginTransactionEarly` if you are using CMT. If a global transaction is already underway, `beginTransactionEarly` does nothing: a write connection is not allocated. You write through the connection associated with the global transaction at commit time only.

#### ConnectionPolicy Method setShouldUseExclusiveConnection

Client sessions can access the data source using a connection pool or an exclusive connection. To use an exclusive connection, acquire your client session using a `ConnectionPolicy` (see "Acquiring a Client Session That Uses Exclusive Connections").

If you are using isolated client sessions (see "Isolated Client Sessions"), Oracle recommends that you use exclusive write connections. In this case, if you are using non-JTA internal connection pools (see "Internal Connection Pools"), you can configure TopLink to acquire an exclusive connection from the write connection pool and use it for both writing and reading isolated data. However, TopLink still acquires a shared connection from the read connection pool for reading non-isolated data unless you configure the read connection pool to allocate exclusive connections (see "Configuring Exclusive Read Connections").

For more information, see "Exclusive Write Connections".

### Managing Cache Access

By default, TopLink uses the shared session cache as much as possible. Doing so increases concurrency and improves performance. However, to achieve a particular transaction isolation level, you may need to avoid the cache using some or all the following:

- Isolated Client Session Cache

- ReadObjectQuery

- ReadAllQuery

- Descriptor Method disableCacheHits

- DatabaseQuery Method dontMaintainCache

#### Isolated Client Session Cache

This method always goes to the database for the initial read operation of an object whose descriptor is configured as isolated. By avoiding the shared session cache, you do not need to use the more complicated descriptor and query APIs to disable cache hits or always refresh. For more information about isolated client sessions, see "Isolated Client Sessions". This is particularly useful for achieving serializable transaction isolation (see "Serializable Read Levels").

#### ReadObjectQuery

This API goes to the database unless it is a primary key-based query, in which case it will go to the cache first. For information on how to avoid the cache entirely in this case, see "Descriptor Method disableCacheHits".

#### ReadAllQuery

This API always goes to the database to get all primary keys. Then it looks up each primary key in the cache and, if found, returns the cache version. For any primary key not found in the cache, TopLink will add the object to the cache with the database version of data. For information on how to avoid the cache entirely in this case, see "Descriptor Method disableCacheHits".

#### Descriptor Method disableCacheHits

This API allows for cache hits on primary key, read-object queries to be disabled. This can be used with the `Descriptor` method `alwaysRefreshCache` to ensure queries always go to the database.

#### DatabaseQuery Method dontMaintainCache

This is a query-level means of preventing objects from being added to the shared session cache. Using an isolated client session (see "Isolated Client Session Cache") is a simpler approach to achieving the same ends.

### CMP and External Transactions

In general, the transaction isolation information in this section applies to both CMP and non-CMP applications, with one exception:

When using a TopLink application with CMP, Oracle recommends that you configure your container to use separate read and write connection pools, and to associate only the write connections with an external transaction. This means the read connections do not participate in the transaction.

However, because TopLink treats EJB finders as just another type of query, you can use your descriptor configuration to exploit the options described in "Reading Through the Write Connection". For example, if you configure a descriptor to use pessimistic locking (see "Configuring Locking Policy"), then when its finder is invoked it will allocate a write connection early and both read and write operations will use the same connection.

Refer to "Externally Managed Transactional Data Sources" for more information on external transactions with transactional data sources.

## Read Uncommitted Level

Oracle does not recommend using this transaction isolation level.

In general, a read uncommitted operation is not necessary. Using TopLink, a transaction isolation of read committed gives you better performance than read uncommitted but with greatly improved data integrity.

## Read Committed Level

Using the unit of work guarantees that you will read only committed data in the shared session cache or committed data in the database.

## Repeatable Read Levels

To achieve repeatable read operations, you must use a unit of work, you must register all objects in the unit of work (both objects you intend to modify and objects you intend only to read), and you must use `ObjectLevelReadQuery` method `conformResultsInUnitOfWork` or `Descriptor` method `alwaysConformResultsInUnitOfWork`.

By doing so, each time you query a registered object, you will get the version of the object as it currently is in your unit of work.

## Serializable Read Levels

To achieve serializable transaction isolation with TopLink, Oracle recommends that you use an isolated client session (see "Isolated Client Sessions") as follows:

1. Configure the database transaction isolation as serializable.

2. Configure objects as isolated (see "Configuring Cache Isolation at the Project Level" or "Configuring Cache Isolation at the Descriptor Level").

3. Use the `UnitOfWork` method `beginTransactionEarly` (see "Unit of Work Method beginTransactionEarly").

If you are only concerned about the write aspect of serializable, optimistic locking is sufficient.

To prevent phantom read transactions (that is, when a transaction detects that new records that have been added to the database after the transaction started), use the `ReadQuery` method `cacheQueryResults`.