Polling based Database Dependency

NCache supports another database dependency called Polling Based Dependency
which is basically designed to work on large scale. Notification Based
Dependency only works with the database server which provides support for data
change notifications i.e. either with SQL Server(2005 or above) or Oracle (10g
or later). Whereas Polling Based Dependency also works with other databases
which do not provide data change notifications support. Also, Notification Based
Dependency is not as much resource efficient as Polling Based Dependency for
using it on large scale.

In Polling Based Dependency, a table named ncache_db_sync needs to be created
in database which is used by NCache to synchronize the database with cache
store. Also, UPDATE and DELETE triggers need to be created for every
table on which notification is required. These triggers will be scripted to
invalidate corresponding cache keys in this table in case of data change.

When an item is added to the cache with dependency, a row will be created in
this table for this cache key as shown:

In case of data change in the table, corresponding triggers will set modified
flag to true. On cache clean up interval, NCache polls ncache_db_sync table
for modified keys, collects them, sets their work_in_progress flag to true and
finally removes all corresponding keys from the cache. After successfully
removing keys from the cache, all those rows where the work_in_progress
flag is true are removed from the ncache_db_synctable.

Important

In a clustered cache, if a node crashes while it was removing items from
the cache, the next node in the cluster will again start the process.

Polling based dependency enables you to keep the cache items synchronized with
any of the database. SQL and Oracle dependencies explained earlier need
SQL/Oracle databases to notify NCache about any change in the database, however
if database does not provide the Change Notifications feature, NCache provides
the facility to synchronize cache with database using polling based dependency.

Note

In notification based dependencies like Oracle dependency, it is the
responsibility of the database to notify changes to the cache, where as in
polling based dependency, NCache polls the database for any changes.

Prepare Database for Polling Dependency

Since polling based dependency works on the concept of NCache polling the
database for changes, the database has to be configured to keep a record of
those changes in a table. This includes creating a table named ncache_db_sync
and creating a trigger to update this table according to changes in database.

Create UPDATE and DELETE triggers for every table on which notification is
required. They set the modified field of corresponding row in the
ncache_db_sync table to 1. To carry out the task, see the following sample
script that creates a trigger on the Products table:

Important

The syntax of SQL scripts specified here are for the Oracle database. Please
change the syntax according to the database being used in your application.

As soon as an item is added to the cache with a dependency, a row will be
created in table 'ncache_db_sync' for this cache key. We will have to make sure
that the format of the cache key while adding into the cache is exactly the same
as defined in the corresponding trigger. For example in the trigger explained
above, the cache key for product id 10 should be 10:dbo.Products.