Notifications and the Advantage Database Server

The Advantage Database Server (ADS) is a high-performance, low maintenance, remote database server from Sybase iAnywhere. In addition to being one of the fastest database servers available, it is mind-numbingly simple to deploy. This, along with its ability to self-configure, and with no need for a formal database administrator in most situations, makes the Advantage Database Server a favorite with vertical market developers deploying small to medium size applications.

Over the past decade, ADS has gained one or more impressive new features associated with enterprise-level database servers with each major release. Advantage 6 introduced stored procedures, views, users and groups, and data dictionaries. Advantage 7 added triggers to the mix, while Advantage 8 provided replication and online backup services.

ADS 9 was no different. Advantage Database Server 9.0 added support for notifications. (Note that the current release is ADS 9.1, which is a free upgrade for 9.0 users.)

Notifications provide a mechanism for communicating to client applications that something has occurred in the database. For example, notifications can be used to inform a client application that data in a critical table has changed. The client application can then use this information to refresh its view of that table, providing the end-user real-time access to the most current data. Or a notification might be used to signal a client that a record has been added to a special table created for the purpose of communicating messages from the system administrator to the end users. The client can then read the latest message and display it within its interface.

Client applications subscribe to an event by calling the sp_CreateEvent system stored procedure, to which it passes a string identifying the event of interest. The events themselves are created with calls to the system stored procedure sp_SignalEvent, and can only be executed from within a stored procedure or trigger.

Like sp_CreateEvent, sp_SignalEvent is passed a string that corresponds to events that one or more clients are expected to subscribe to. The call to sp_SignalEvent is passed an additional parameter that determines whether subscribing clients should be signaled immediately, or only after the current transaction is committed (assuming that the call to sp_SignalEvent was performed within a transaction), permitting the database to signal events that might be rolled back, in which case the client will not receive the notification.

Clients receive an event by calling either sp_WaitForEvent or sp_WaitForAnyEvent. Both of these procedure calls are synchronous, or blocking. Specifically, the call to either of these methods will not return until an event is signaled by the database, or the call times out. The timeout, which is specified by a parameter passed to the wait procedures, can either be infinite, or limited to a specified number of milliseconds. (Note that the wait call times out immediately if the database has already signaled the event since the last wait call.)

Because sp_WaitForEvent and sp_WaitForAnyEvent are blocking calls (the call does not complete until a signal is received or the timeout expires, whichever comes first), most developers who need real-time notifications from the database will employ multithreaded techniques. In other words, a call to sp_WaitForEvent or sp_WaitForAnyEvent will almost always be performed by a secondary thread, which is designed to specifically await the notification without interfering with the primary thread of execution.

Here is an example of a typical notification scenario. Before a client application begins displaying a table that must always display the most current data to the user, the client will subscribe to an event associated with updates to that table. The client does this by calling sp_CreateEvent, passing the string you have created to uniquely identify the event.

On the server, you create AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the underlying table. From these triggers, you call sp_SignalEvent, again using the same string that the client applications use to subscribe to the event.

As soon as one of the client applications begins displaying the critical data, the client creates a new thread that then calls either sp_WaitForEvent or sp_WaitForAnyEvent. Code that immediately follows the call to the wait procedure typically tests whether the call has timed out or has been signaled, which can be determined by the values returned by this stored procedure call.

If the call was signaled, the thread will attempt to lock a synchronization object (such as a critical section or use a monitor to lock an object) and update the display of the data. This synchronization object must also be shared with the thread responsible for the user interface, in order to prevent the multiple threads from interfering with each other.

Once the display has been updated, the notification thread releases the synchronization object and loops back to another call to the wait procedure.

While this type of programming requires careful consideration of shared resources, such as the user interface elements that display the data, when done properly, any signals received by the clients can be reacted to immediately as opposed to enduring the delays inherent with polling (the periodic check for a change in some state).

Like ADS, ALS also supports notifications. However, due to the local server nature of ALS, and the lack of a centralized service, ALS notifications are much less responsive than those supported by ADS.

Copyright © 2009 Cary Jensen. All Rights Reserved.