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.