Database Change Notification and TopLink Cache Invalidation

Database Change Notification and Application Cache Invalidation Solution

A simple two-part solution can be used to minimize, and in some cases eliminate, occurrences of the stale data problem as follows:

• Database change notification: whereby the database notifies applications about changed data.

• Application cache invalidation: whereby the notified application marks the corresponding cached data as invalid. This signals to the application that the data needs to be re-read into the cached application from the database.

Guidelines to which a proper database change notification solution should adhere include:

• The method of providing notification should not be application-dependent and should allow for multiple applications to use it simultaneously.

• Notification should be transaction-based: this implies that the notification is not issued until the change to the database is committed.

• Some applications, including TopLink use the cache not only to store existing database data, but also to write new data into the database. It is necessary for applications with write capability to have an option not to be notified of database changes caused by its write operations.

Database Change Notification

Oracle Database 10g and subsequent versions use triggers and Java Message Service (JMS) messaging implemented by Oracle Advanced Queuing (AQ) as follows:

• On update or deletion of a database row, a trigger creates a message that contains the name of the table and the row's primary key value(s) and places the message into the queue.

• After a transaction is committed to the database, the corresponding JMS message is delivered to all subscribers (applications).

TopLink Cache Invalidation

An application enabled by TopLink extracts the table name and the primary key value(s) from the JMS message and invalidates the corresponding object in the TopLink cache. Note that TopLink 10g Release 3 (10.1.3) or higher is required.

Users

• User dbuser contains the tables that will send change notification messages.

• An application that initiated a database change should not receive notification of this change. One method of enforcing this is to have different applications use different user names. For example, app1, app2,.... The different user names would not use their default schemas but would access the dbuser schema instead.

• User aqadm contains the queue through which notification messages are sent.

The following procedure assumes that the dbuser user already exists and that the TopLink Employee example is set up and populated in its default schema.

1. Connect as a DBA:

   CONNECT SYSTEM/MANAGER
   
   

2. Enable the dbuser to enqueue (send) the messages into any queue:

   EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => ENQUEUE_ANY',grantee
       => 'dbuser',admin_option => FALSE);
   
   

3. Allow the dbuser to call the enqueue method in PL/SQL:

   GRANT AQ_USER_ROLE, EXECUTE ON DBMS_AQ TO dbuser;
   
   

4. Create the app user:

   CREATE USER app IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY
       TABLESPACE temp;
   
   

5. Grant the app user access to all database objects in dbuser schema:

   GRANT SELECT, INSERT, UPDATE, DELETE ON dbuser.address to app;
   ...
   
   

6. Enable the app user to dequeue (receive) the messages from any queue:

   EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => 'DEQUEUE_ANY',
       grantee => 'app', admin_option => FALSE);
   
   

7. Allow the app user to dequeue JMS messages in a Java application:

   GRANT AQ_USER_ROLE, EXECUTE ON DBMS_AQIN, EXECUTE ON DBMS_AQJMS TO app;
   
   

8. Create aqadm - the user to own the queue:

   CREATE USER aqadm IDENTIFIED BY password DEFAULT TABLESPACE users
       TEMPORARY TABLESPACE temp;
   GRANT CONNECT,RESOURCE,AQ_ADMINISTRATOR_ROLE TO aqadm;
   
   

Advanced Queue

The notify_queue allows for multiple consumers (applications that receive messages) and sends Oracle's implementations of JMS messages.

1. Connect as a queue owner:

   CONNECT aqadm/aqadm
   
   

2. Create and start the queue:

   EXEC DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'notify_queue_table',
       multiple_consumers => TRUE, queue payload_type => 'SYQ.AQ$_JMS_TEXT_
       MESSAGE');
   EXEC DBMS_AQADM.CREATE_QUEUE (queue_name => 'notify_queue',queue_table =>
       'notify_queue_table');
   EXEC DBMS_AQADM.START_QUEUE (queue_name => 'notify_queue');
   
   

Alternatively, if there is only a single application to be notified, the queue table may be created with multiple_consumers=>FALSE.

Triggers

A trigger is created for each table that will send one or more notification messages. You can create two helper functions first to simplify the trigger code.

1. Connect as a TopLink user of the schema where the TopLink Employee example is set up:

   CONNECT dbuser/dbuser
   
   

2. Create the notify_make_msg function provided. This function creates the message and populates it with the application ID (app1, app2, ... found in USER) and the table name:

   CREATE OR REPLACE FUNCTION notify_make_msg (table_name VARCHAR2) RETURN
       SYS.AQ$_JMS_TEXT_MESSAGE AS msg SYS.AQ$_JMS_TEXT_MESSAGE;
   BEGIN
       msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT();
       msg.set_string_property('APP', USER);
       RETURN msg;
   END;
   
   

3. Create the notify_enqueue procedure provided. This procedure sends the message to notify_queue. TopLink throws an exception, but the message may be ignored if there are no recipients to receive it:

   CREATE OR REPLACE PROCEDURE notify_enqueue  (msg SYS.AQ$_JMS_TEXT_
       MESSAGE) AS queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
       msg_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;
       msg_id          RAW(16);
       no_recipients_for_message EXCEPTION;
       PRAGMA EXCEPTION_INIT(no_recipients_for_message, -24033);
   BEGIN
       DBMS_AQ.ENQUEUE(queue_name => 'notify_enqueue', enqueue_options =>
       queue_options, message_properties => msg_properties, payload => msg,
       msgid => msg_id);
   EXCEPTION
       WHEN no_recipients_for_message THEN NULL;-- ignore
   END;
   
   

4. Create a trigger for the employee table:

   CREATE OR REPLACE TRIGGER notify_employee AFTER UPDATE OR DELETE ON
       employee FOR EACH ROW DECLARE msg SYS.AQ$_JMS_TEXT_MESSAGE;
   BEGIN
       --a single call per trigger, pass the table name
       msg := NOTIFY_MAKE_MSG('EMPLOYEE');
       --a call for every primary key field
       msg.set_double_property('EMP_ID', :old.EMP_ID);
       NOTIFY_ENQUEUE(msg);
   END;
   
   

   If there is only a single application that requires notification, the trigger need not enqueue the message in the event that it was caused by the application:

   IF USER = 'app' THEN
       RETURN;
   END IF;
   
   

After the transaction is committed, the corresponding JMS message is delivered to all database subscribers.

Connecting the Application

The application enabled by TopLink must be amended to connect using the app user. However, it will work with database objects in the dbuser schema by setting the user to dbuser via either the preLogin() event handler in Example: preLogin event handler or the postLogin() event handler in Example: postLogin event handler.

//The pre-login method
public void preLogin(SessionEvent event){
   event.getSession().getLogin().setTableQualifier("dbuser");
}

//The post-login method
public void postLogin(SessionEvent event){
    event.getSession().executeNonSelectingCall(new SQLCall("ALTER SESSION SET CURRENT_SCHEMA = dbuser"));
}

JMS

An application obtains ConnectionFactory and Destination by invoking a Java Naming and Directory Interface (JNDI) lookup. The code example in Example: How to obtain ConnectionFactory and Destination using the oracle.jms package illustrates how ConnectionFactory and Destination may be obtained with the use of the oracle.jms package.

Note: the condition defined in the selector string filters out messages generated by the app user.

import javax.jms.*;
import oracle.jms.*;

String url = "jdbc:oracle:thin:@localhost:1521:orcl"
String user = "app";
String password = "app";
String selector = "(APP IS NULL) OR (APP <> " + "'" + user + "')";

// multiple_consumers => TRUE case:
TopicConnectionFactory factory =  AQjmsFactory.getTopicConnectionFactory(url,null);
TopicConnection conn = factory.createTopicConnection(user, password);
TopicSession topicSession = conn.createTopicSession(false, Session.AUTO_ACKNOWLEDGE);
Topic topic = ((AQjmsSession) topicSession).getTopic("aqadm", "notify_queue");
Subscriber subscriber = topicSession.createSubscriber(topic, selector, false);
….

// multiple_consumers => FALSE case:
QueueConnectionFactory factory = AQjmsFactory.getQueueConnectionFactory(url,null);
QueueConnection conn = factory.createQueueConnection(user, password);
QueueSession queueSession = conn.createQueueSession(false, Session.AUTO_ACKNOWLEDGE);
Queue queue = ((AQjmsSession) queueSession).getQueue("aqadm", "notify_queue");
QueueReceiver receiver= queueSession.createReceiver(queue, selector);
….