1. Cache Guide
  2. Cache Performance
  3. Improving Performance for Autorefresh Operations
  4. Reducing Lock Contention for Read-Only Cache Groups with Autorefresh and Dynamic Load

Reducing Lock Contention for Read-Only Cache Groups with Autorefresh and Dynamic Load

Your application can time out because of a lock contention between autorefresh and dynamic load requests.

An autorefresh operation automatically loads committed changes on cached Oracle Database tables into the cache tables in TimesTen. A dynamic load operation requests data from the Oracle database (originating from a SELECT statement) and inserts the rows into the cache group. Both the autorefresh and dynamic load operations require access to the cache metadata, which could cause a lock contention.

At the end of an autorefresh operation, TimesTen updates the metadata to track the autorefresh progress. If you have requested guaranteed durability by setting the DurableCommits connection attribute to 1, then the autorefresh updates to the metadata are always durably committed. If you have requested delayed durability by setting the DurableCommits connection attribute to 0 (the default), then TimesTen must ensure that the autorefresh updates to the metadata are durably committed before the garbage collector can clean up the autorefresh tracking tables stored in the Oracle database.

When a durable commit is initiated for the metadata, any previous non-durable committed transactions in the transaction log buffer that have not been flushed to the file system are also a part of the durable commit. On hosts with busy or slow file systems, the durable commit could be slow enough to lock out dynamic load requests for an undesirable amount of time.

If you notice that your application is timing out because of a lock contention between autorefresh and dynamic load requests, you can set the CacheCommitDurable cache configuration parameter to 0 with the ttCacheConfig built-in procedure. This reduces the occurrence of lock contention between autorefresh and dynamic load requests in the same application by:

  • Running a non-durable commit of the autorefresh changes made to the metadata.

  • Using a separate thread in the cache agent to durably commit the autorefresh changes before the garbage collector cleans up the autorefresh tracking tables stored in the Oracle database. This results in a slight performance cost as garbage collection is delayed until after the durable commit completes.

The lock is removed after the non-durable commit of the autorefresh changes to the metadata. After which, there is no longer a lock held on the metadata and any dynamic load requests for the recently refreshed tables can continue processing without waiting. However, if there is an error and database recovery starts, autorefresh may need to reapply any committed transactions that did not flush to disk before a failure.

The following example sets CacheCommitDurable=0: 

call ttCacheConfig('CacheCommitDurable',,,'0');

You can query the current value of the CacheCommitDurable parameter. 

call ttCacheConfig('CacheCommitDurable');

See ttCacheConfig in the Oracle TimesTen In-Memory Database Reference.