John Reynolds's Blog

It's time for RDBMS Change Notification Services

It is a safe bet that relational databases will be around for a very long time. The relational model is well past middle age (it was introduced by E.F. Codd in 1970), but it exhibits no loss of vigor despite repeated challenges. Upstarts like Object and XML databases have garnered some support, but they haven’t yet made a dent in the preeminence of the RDBMS. Layers may be erected between the RDBMS and the application logic, but the roots of most applications will still be in systems that execute SQL commands.

As Jonathan Bruce points out in his recent blog, the Java community's interest in SQL (via JDBC) is far from stagnant.

Records managed by an RDBMS are often long lived and are seldom accessed by a single application. Applications written in different languages often manipulate the same records, and this shared access leads to a fundamental problem: How do you know that your data is in sync with the RDBMS?

There is a tendency to deal with shared data problems by building layers on top of the RDBMS. In the Java world, several very good object caches exist and many of these provide replication across clusters to insure that applications will operate on identical data (like JBoss Cache and Turbine JCS).

The problem with these solutions is fundamental. Applications can bypass these mechanisms and go directly to the RDBMS. If a change is made directly to the database, the caching mechanism will be ignorant until a change is attempted or the cache is refreshed.

I think that the fix belongs in the RDBMS itself. Change notification needs to become a standard RDBMS feature. The RDBMS should publish changes to registered subscribers.

Most popular RDBMS offerings already provide for update triggers that execute commands when changes occur. If your application needs to be aware of specific changes, then you can add a trigger to the RDBMS. The downside of this approach is that many triggers must be generated to deal with very similar concerns (and improperly written triggers can severely impact DB performance).

RDBMS authors should build on the trigger technology to implement publish and subscribe change notification services. Applications (and object caches) would subscribe to change notifications by issuing statements very similar to update triggers with the addition of a callback. When the trigger is fired, the callback is invoked.

The advantage to this approach is that it cannot be bypassed. A change notification service that is integral to the RDBMS will catch any changes to the data (including those caused by stored procedures). Applications will still have to deal with the changes, but at least they will know about them.

The APIs for the RDBMS Change Notification Services should be standardized, and the callback mechanisms should be flexible to support multiple languages and protocols (like XML over HTTP). I'm probably naive, but like SQL itself, agreement on a common standard will benefit all RDBMS vendors. There should be little incentive to implement proprietary APIs.

Comments

• Oracle has had this since 9.2 R2
  They call it Oracle Streams and it lets you capture changes to a row, table, or database.

  http://www.cise.ufl.edu/help/database/oracle-docs/server.920/a96571/strmover.htm

  Sam
  

  Posted by: spullara on June 09, 2004 at 04:44 PM

• Even earlier, as I recall
  Before Streams was Advanced Queueing, which also had a publish/subscribe model. Advanced Queueing goes way back to Oracle8i, or maybe even to Oracle8.

  Posted by: gennick on June 09, 2004 at 08:07 PM

• Active Databases
  Look into the area of Active Database Management Systems and you will be surprised how much work has been done in the 90s:

  http://www.ida.his.se/ida/adc/intro.html

  PostgreSQL for example does support ECA-Rules which go well beyond simple triggers:

  http://www.postgresql.org/docs/7.4/static/rules.html

  And triggers can be written in C:

  http://www.postgresql.org/docs/7.4/static/trigger-interface.html

  Of course that doesn't really help much in Java -- what you would need is support for triggers in JDBC. I must admit I don't know if that does exist or is planned, but the DBMS people did their work. MySQL is probably not a good place to look if you are looking for advanced features. It is fast on large tables, but it lacks many features other systems do offer.

  Posted by: peterbecker on June 09, 2004 at 08:55 PM

• Oracle has had this since 9.2 R2
  Thanks! I've updated my blog to include this link. It's a good start, but we need a standard so that packages like JCS can start using these features.

  Posted by: johnreynolds on June 10, 2004 at 06:16 AM

• I used them on interbase
  Hi,
  In my old Delphi days I used the "Intervase events" that told me when something changed on the database.

  see:
  http://www.borland.com/interbase/
  or the open source version:
  http://firebird.sourceforge.net
  

  Posted by: jdavi on June 10, 2004 at 01:02 PM

• Common API?
  Ah Delphi! I never got to do more then play with it, but it sure looked like fun.

  So is all we need a common API? Would it be practical to abstract or wrap the Interbase and Oracle Streams APIs to a common publish/subscribe API?

  Posted by: johnreynolds on June 10, 2004 at 01:42 PM

• Works with DB2 I am working on a project that needs to extract updates from several RDBMS... and I really agree for the need of standard. It works well with Oracle Streams, but you have the same feature with DB2 Q-Capture.
  I have not been able to find this kind of feature for Postgres, the replication projects are too "Postgres centric" to be used as publish/subscribe processes.
  

  Posted by: oliv on February 23, 2006 at 08:40 AM

• I know this is an older thread but I thought I would post a comment in case someone else comes across this. The author suggests that Oracle might integrate this functionality with TopLink. While this functionality is not yet part of the TopLink product the Oracle DB's Data Change Notification (DCN) functionality and TopLink's cache invalidation/refresh API can be used together today to deliver what is suggested.

  This blog post covers the basics of DCN

  This existing cache invalidation strategy using DB triggers, OracleAQ->JMS->MDB provides the rest of what is needed.

  I am working on a more comprehensive demo that I will publish on my blog when finished. This is very Oracle DB specific and does address the portability issue. I believe that should be addressed through the JCP where JDBC drivers or data sources could generically facilitate exposing this type of functionality to the middle tier.

  Doug

  Posted by: djclarke on September 06, 2007 at 10:31 AM

This work is licensed under a Creative Commons License.