Something new under the Sun: A new way of doing a cache invalidation protocol with Oracle 11g

Just when you think the database will never get smarter, it does. And the database that matters most to Enterprise Developers is Oracle.

What happens to coherency between your distributed cache and the database when another application changes the database? If it is a Java application, you can add Ehcache to it and configure it to connect to the distributed cache. But this might require work you do not want to do right now. Or there might be tens of apps involved. Or there might be scripts which are run by DBAs from time to time. Or it could be another language. We have the Cache Server, where you can expose a distributed cache via REST or SOAP and then invalidation becomes as simple as sending a HTTP Delete to the Element resource. See the Cache Server Documentation for more on this.

For a few years, MySQL users have had access to cache invalidation for Memcached using libmemcached. The database calls back to memcached. Brian Aker mentioned to me he was adding this into MySQL a few years ago. See http://www.mysqlconf.com/mysql2009/public/schedule/detail/6277 for a decent introduction.

For Oracle, back in 2005 I tried to use their message queue integration to achieve the same effect. Back then it didn’t even work. I have heard that it works now, but it is a very messy solution with lots of moving parts.

Fortunately, starting from 11g Release 1 (11.1), the Oracle JDBC driver provides support for the Database Change Notification feature of Oracle Database. Using this functionality of the JDBC drivers, multitier systems can take advantage of the Database Change Notification feature to maintain a data cache as up-to-date as possible, by receiving invalidation events from the JDBC drivers. See  the Database Change Notification chapter in the Oracle docs for details.

This lets you achieve a new and very simple way of doing a cache invalidation protocol to keep your cache and the database in sync.

You create a DatabaseChangeListener and register it with your connection:

// conn is a OracleConnection object.
// prop is a Properties object containing the registration options.
DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotifictaion(prop);
...
// Attach the listener to the registration.
// Note: DCNListener is a custom listener and not a predefined or standard 
// lsiener
DCNListener list = new DCNListener();
dcr.addListener(list);

I like this so much I think we might add a standard DatabaseChangeListener to Ehcache for it.