Sync NCache with Oracle Database

Caching frequently used data of your application is an extremely competent move. But making sure that the cached data stays synchronized with your Oracle database is the dilemma. The trick to overcoming this is to use data expirations where you guess the TTL (Time To Live) of the item and then cache it.

No matter how popular this scheme is, this remains a mere educated guess on how long that item is going to stay unchanged in the database. And guesses are not always accurate. If your application is data sensitive where fetching and processing inaccurate data refutes its purpose, then using expiration will cost you a great deal.

What saves you from this predicament is using a database synchronization mechanism in your cache such as NCache, that automatically removes the corresponding cache data if it is updated in the Oracle database.

NCache Details Cache Dependency on Database Data Expiration Techniques

Using NCache to Sync with Oracle Server

Oracle Dependency uses event notifications that notify all database clients when a dataset changes in the database. Back-end information: NCache uses this feature to register itself as Oracle Server’s client. This means whenever data changes in the Oracle dataset, NCache is automatically notified. Using this information, NCache keeps a map of all cached items with their corresponding datasets. So, whenever NCache gets notified of a change, it invalidates the corresponding cache item from the cache and fetches the updated one the next time the client app asks for it.

Figure: Synchronize NCache with Oracle Server

Let’s Sync NCache with Oracle Server

I’m going to take a simple example of an e-commerce application (.NET Core based) whose entire success lies with its accuracy. Thousands of clients access this application for online shopping every minute so this application’s data has been cached.

Now, let us take a very common scenario where CustomerA bought the last Xbox Series X that the store had. The app server removes the quantity of that item from the database but the cache is still unaware of this change. This inconsistency leads to customer rage when CustomerB likes the same product, pays for it, and gets notified with a successful payment message but does not get his latest Xbox Series X.

Here, to make sure the application does not face data integrity problems every time data changes in the main Oracle database, NCache provides support of Oracle Dependency.

To use this dependency feature, NCache allows you to parameterize your Oracle query with OracleCacheDependency method that takes parameter values at runtime. Doing this will reduce the number of times your query has to be compiled on the Oracle Server hence improving the application’s performance by tenfold.

string connectionString = ConfigurationManager.AppSettings["connectionstring"];

string query = "SELECT ROWID, ProductID, ProductName, UnitPrice FROM Products WHERE ProductID = :productID";

var param = new OracleCmdParams();

param.Type = (OracleCmdParamsType.VarChar);

param.Value = "XBoxX";

param.Direction = OracleParameterDirection.Input;

// Adding the populated parameter to a dictionary

Dictionary<string, OracleCmdParams> oracleParam = new Dictionary<string, OracleCmdParams>();

oracleParam.Add("productID", param);

// Create Oracle Dependency

var oracleDependency = new OracleCacheDependency(connString, query, OracleCommandType.Text, oracleParam);

Product product = FetchProductFromDB(param.Value);

string key = $"Product:{product.ProductID}";

// Create a CacheItem, add Oracle dependency, and cache it

var cacheItem = new CacheItem(product);

cacheItem.Dependency = oracleDependency;

cache.Insert(key, cacheItem);

NCache Details Cache Dependency on Oracle Setup Oracle Environment

There’s so much more that you can do with OracleCacheDependency. Here’s a list of a few very useful features supported with this Oracle Dependency.

Auto-Reload Cache Item through Read-Thru Provider

OracleCacheDependency is responsible for invalidating a cache item once it changes in the data source. NCache provides Read Through Backing Source Provider in case you want the cache to not just invalidate, but also fetch the latest version of the changed item from the Oracle server. With Read Through option enabled, NCache calls the ReadThru provider to fetch the updated data whenever Oracle Server notifies the cache of a change.

The following code explains how you can achieve this functionality:

// Create a new cache item and add oracle dependency to it

CacheItem item = new CacheItem(product);

item.Dependency = oracleDependency;

// Resync if enabled, will automatically resync cache with Oracle server

item.ResyncOptions = new ResyncOptions(true);

//Add cache item in the cache with Oracle Dependency and Resync option enabled

cache.Insert(key, item);

NCache Details Cache Dependency on Oracle Read-Through Provider

Stored Procedure Based Oracle Dependency

NCache supports Stored Procedure based Oracle Dependency if your preference lies with keeping all your Oracle queries inside the Oracle database. These stored procedures are precompiled on the Oracle server and run much faster than dynamic Oracle queries.

To create a Stored Procedure in Oracle, see this example:

CREATE PROCEDURE SelectProduct (ProductID IN VarChar)

BEGIN

SELECT ProductID, Quantity, Price FROM Products

WHERE ProductID = :productID;

END;

Calling this Stored Procedure in your .NET application is easy. Use the following snippet as an example.

OracleCmdParams param = new OracleCmdParams();

param.Type = (OracleCmdParamsType.VarChar);

param.Value = "XBoxX";

param.Direction = OracleParameterDirection.Input;

var oracleCmdParams = new Dictionary<string, OracleCmdParams>();

oracleCmdParams.Add("productID", param);

// Create Oracle Dependency

var oracleDependency = new OracleCacheDependency(connString, SelectProduct, OracleCommandType.StoredProcedure, oracleCmdParams);

NCache Details Cache Dependency on Oracle Oracle Dependency Using Stored Procedure

Why Sync NCache with Oracle Server?

Nobody wants their application that they have worked so hard on to fail just because it’s processing outdated data. You lose both business and face that way. But NCache, much to your ease, provides you with Oracle Dependency that keeps your cache synchronized with your Oracle database to ensure that the cache you are using to benefit you does not fail you instead.

NCache Details Download NCache Edition Comparison