Database management system for implementing independent database actions in response to events of interest

A method and apparatus for managing a database is provided. A database management system conducts a plurality of database transactions with a plurality of clients. An event may be detected as a result of one of the transactions. The event is mapped to an independent database action. In response to detecting the event, the independent database action is executed without interference from the transaction that caused the event.

FIELD OF THE INVENTION

The present invention relates to database management systems. In particular, the present invention relates to a database management system that executes independent database actions in response to events of interest.

BACKGROUND OF THE INVENTION

Database management systems manage access between clients and databases. With increasing database usage, database management systems are required to handle a larger number of transactions between clients and databases. The interactions between clients and databases are also becoming more complex. Database transactions often involve multiple clients. Furthermore, the type of actions that are typically performed in one transaction increasingly requires additional user-involvement.

Frequently, clients want the database management system to perform some action automatically in response to an event of interest. Consequently, various approaches have been developed to allow database management systems to perform actions in response to events of interest. One such approach involves the use of triggers, alternatively referred to as “synchronous actions”. Specifically, users can define triggers that (1) specify an operation, and (2) specify an event. The trigger is then stored in a database system. When the specified event occurs, the trigger is “fired” and the database system performs the specified operation. Unfortunately, the specified operation is performed synchronously relative to the process that caused the event that fired the trigger. Consequently, the performance of the operation that caused the trigger to fire suffers.

Another approach to allow database management systems to perform actions in response to events of interest involves notifications. Such actions are performed asynchronously relative to the database transactions that cause the events of interest to occur. For example, in response to an event of interest caused by a transaction, the database management system signals a notification out to a designated user or client. The sending of the message occurs independent of the transaction that caused the event of interest. The designated user or client may respond to the notification by performing some action indicated by the notification. For example, the user may complete a transaction with the database management system.

Based on the foregoing, it is clearly desirable to provide a mechanism and techniques, for allowing database management systems to perform actions in response to events of interest, that avoid the disadvantages of existing approaches.

SUMMARY OF THE INVENTION

Techniques are provided for performing actions, within a database management system, in response to an event, where the actions are performed independently relative to the operation that caused the event.

According to one aspect of the invention, an event is detected in a transaction between a client and a database. The database management system responds to the event by initiating an independent database action.

Because independent database actions are performed by the database management system automatically in response to the occurrence of their corresponding event of interest, it is not necessary for a notification to be sent to any client. Consequently, the round-trip communication associated with the notification approach is avoided. Further, because the independent database action is performed without interference to the triggering transaction, the triggering transaction does not suffer the performance penalty associated with the trigger approach. Other advantages provided by embodiments of the invention will be made clear herein.

DETAILED DESCRIPTION OF THE INVENTION

DEFINITIONS

A “transaction” is a set of instructions executed by servers of a database management system to affect data objects stored by the database management system. Transactions are initiated by requests, typically from users operating clients. The set of instructions in a transaction are executed as an atomic unit. Therefore, initiating execution of a transaction causes either all instructions in the instruction set of the transaction to be executed, or all of the instructions in the instruction set to be aborted or not executed. If the transaction cannot be completed, executed instructions in that transaction's instruction set are rolled-back, or undone.

An “event” is an occurrence of a transaction containing an item that has some predetermined value, characteristic, or aspect. An “event of interest” is an event that the database server has been configured to detect, and to perform some action when detected.

The transaction that causes the event of interest to occur is referred to herein as the “triggering transaction”. Actions performed by a database management system (1) in response to a designated event, and (2) independently relative to the database transaction that triggered the designated event, are referred to herein as “independent database actions”. An independent database action is (1) asynchronous relative to the triggering transaction, and (2) not executed as part of the triggering transaction. Because the independent database action does not have to be synchronized with the operations performed in the triggering transaction, and it is not part of the triggering transaction, it can, for example, be performed when sufficient resources become available. This may be done even if sufficient resources become available after the triggering transaction has completed. Therefore, the timing of when the independent database action is to be performed is not dictated by the timing of when operations in the triggering event are executed.

For the purpose of explanation, the client that issues the transaction that causes the event of interest to occur is referred to herein as the “triggering client”. The client that defines an independent database action for an event may be different than the client that initiates the triggering transaction. The client that defines the independent database action is referred to as the “action-defining client”.

Overview

Embodiments of the invention enable a database management system that processes transactions from different clients to perform independent database actions in response to detecting events caused by the database transactions.

The independent database actions allow clients to automate interactions with a database system according to a specified set of instructions. The instructions are executed upon the database management system detecting an event in a triggering transaction. The triggering transaction is processed by the database system independently of the independent database action's execution.

As an example, instructions provided by one client may be performed in response to specific events contained in subsequent triggering transactions. As a result, one client may involve other clients when conducting a transaction with a database system, but the involvement by other clients may be automated according to instructions specified by those other clients.

Among other advantages, executing independent database actions in response to triggering events avoids the use of notification and notification responses. Resources used to notify other clients, and to receive responses to those notifications, are conserved. Examples of these resources include network bandwidth and queue allocation.

Furthermore, a database management system under an embodiment of the invention can perform actions on a database using previously specified instructions. These actions may be performed independently of other transactions that may be processed concurrently. This reduces delays and performance problems that result when the database management system is forced to execute instruction sets concurrently with their triggering transactions.

In contrast, database management systems that offer only synchronous triggers require the instructions associated with the triggers to be executed in response to certain communications of an ongoing transaction. Therefore, the database management system cannot manage its internal resources to plan for the occurrence of the synchronous actions. When synchronous actions do occur, the database management system may not have sufficient resources available to efficiently execute the corresponding action while processing other current transactions and processes. As a result, the occurrence of synchronous triggers may slow processing of other transactions in progress, including the triggering transaction.

Another advantage provided is that clients may specify independent database actions to be performed on their behalf in response to triggering transactions conducted by other clients. In this way, one client may configure the database management system to perform a certain action on the database if another client accesses the database in a particular manner. Furthermore, the independent transaction is performed independently of the triggering transaction. In contrast, other systems currently in use do not allow clients to automate independent database actions that are triggered by a transaction initiated by another client. Rather, database transactions are automated to occur with the triggering transaction, so that the action is conducted synchronously with the transaction that caused the action to be triggered.

If other clients are to be involved in an operation, prior systems use the notification approach. However, use of the notification approach may be problematic if the other client is not on-line.

As an example, one client (Client X) may configure the database management system to enter an approval into the database if a certain event of interest occurs. The event of interest may correspond to an occurrence of an item contained in a transaction of another client (Client Y). For example, assume that event corresponds to an item in the transaction of Client Y having some value that meets a minimum criteria. According to an embodiment of the invention, Client X selects to automate his approval of the item contained in Client Y's transaction if that item meets the meets the minimum value.

In contrast, under prior art systems, Client X must use the notification approach, Client X must configure the database management system so that the database management system will send a notification message to Client X if a certain event of interest occurs. The event of interest may be contained in a transaction performed by Client Y. Client X would then have to review the notification message and manually approve the item in Client Y's transaction. Once Client X responds to the message, Client X's approval is entered into the database. The notification message to Client X would use resources, including network bandwidth to notify Client X. Client X may be off-line, in which case the database management system must check for Client X to become on-line. Moreover, Client X has no mechanism to automate his approval of the item in Client Y's transaction. The result is that the approval needed from Client X is delayed, especially if Client X is off-line, even though the condition for receiving the approval is met.

In one application of an embodiment, a user of the client may program an action that is to be executed when an event is detected. The detection of the event may be set to occur when a second user conducts a transaction with the database management system. The independent database action may be performed for the first user, even though the triggering transaction is between a second user and the database management system. For example, the first user and the second user may each have an account. The independent database action may affect the account of the first user, based on an event that is detected in the transaction affecting the account of the second user.

System Description

FIG. 1is a block diagram of a database management system for performing an independent database action in response to a designated event. A database management system100includes a server component110, an event monitor120, a register130and a queue140. A plurality of clients115communicate with an interface112of the database management system100. The database management system100communicates with a database150to process database transactions and other client communications.

The clients115may, for example, correspond to terminals executing a database application. Users may operate clients115to signal transaction requests to database management system100via interface112. The server110processes the transaction requests for database management system100.

In one application, interface112processes communications from a wide area network such as the Internet. Other components may be used instead of interface112. For example, load balancers may be used to process web-based requests from clients, and to select one database management system from a cluster of database management systems.

Events of Interest

Using the techniques described herein, users or managers of the database management system100may associate independent database actions with certain events. The events of interest are the events that are associated with some action that has to be performed by the database system100. When an event of interest occurs, the server component110performs the independent database action that has been defined for that event of interest.

For example, an event of interest may be defined to occur if any future transaction makes a selection from certain data objects in database150. The event of interest may be detected when a transaction actually selects those data objects from database150. In another example, an event of interest may be defined to occur if a transaction conducted with the database system is from a certain source (client terminal or user), so as to have a particular source identification. The event of interest may be detected if a subsequent transaction actually contains that specified source identification.

As another example, an event of interest may be defined as a specified user or client terminal being affected by a given transaction from anyone of the clients that can conduct transactions with the database system100. If a subsequent transaction is detected as affecting the designated client or terminal in the manner specified, then the event of interest is detected as occurring.

In another example, an event of interest may be designated to occur when server component110processes a transaction that affects a data structure of the database system in a particular manner. For example, data entered into the data structure by a particular user may be removed as a result of a transaction. The removal of the data may be the occurrence of the event.

Events of interests may also occur because a transaction is conducted with the database management system that has a particular feature or property. For example, a first user may program an event of interest to be the occurrence of a second user conducting a transaction with the database management system. The execution of transactions containing particular metadata information may similarly be designated to be events of interests. Specific examples where metadata information about a transaction may correspond to an event of interest include situations in which the metadata specifies a particular transaction time or a particular job size.

An attempt to use more than a certain threshold of resources may also be an event of interest. If a user, for example, makes a large data entry requiring too many database resources, then the creation of that entry may be detected as an event of interest, so that the database management system automatically performs an action to prevent problems that may result from the large entry.

The various components that participate in the detection of events of interest, and the execution of the corresponding independent database actions, shall now be described in greater detail.

The Event Monitor

The event monitor120monitors communications processed by server component110to detect when events of interest occur. The events of interest may occur as a result of transactions performed by database management system100by clients115. The event monitor120may include a memory to identify when events of interest occur while server component110processes transactions with a particular client.

The Register

Register130maps events of interests to actions that are to be performed by database management system100when those events of interest occur. An action that is mapped to an event of interest is referred to herein as a “triggered action”. In an embodiment, at least three types of triggered actions are possible in response to event monitor detecting an event of interest: (a) synchronous actions (b) notifications and (c) independent database actions. The register130may cause a job corresponding to one of the actions to be entered into queue140, where it will be executed by server110at a particular time as an automatic response to the identified event of interest being detected. Other components in system100that may execute or partially perform the triggered action may include, for example, a messaging application that sends a notification to a predetermined recipient.

The Queue

In an embodiment, queue140is a software driven system that lists jobs that need to be processed by database system100for accessing database150. Each job may correspond to one or more instructions of a transaction, independent database action, synchronous action, notification or other operation that is to be performed by server component110and/or other components of database system100. The queue140may structure jobs for subsequent execution by the database system100according to some order, such as a first-in, first-out execution order.

Queue140prioritizes execution of transactions, independent database action, synchronous actions, and notifications by server component110, or other components of database system100. If event monitor120does not detect an event of interest in one of the transactions being executed, the transaction is received and processed by server110according to a priority established by queue140. For example, queue140may access database150for a transaction that seeks to read data objects without affecting other clients115.

In an embodiment, queue140structures jobs so that mechanisms server110for performing transactions and actions are proportioned to perform different processes while executing transactions. Specifically, queue140may structure the jobs so that some of the resources of server110are used to perform processes for executing synchronous actions, notifications, and independent database actions in response to event monitor120identifying events in some or all of the transactions. Other resources of server110are used to execute transactions between multiple clients and the database system.

Independent Database Actions

Independent database actions cause one or more programmatically controlled operations to be performed on selected data objects of database150. In one embodiment, register130matches an event of interest to a set of instructions that are then executed by server110. The execution of instructions may correspond to operations performed on database150. The instruction set may be specified by the triggering client, or by another client. Likewise, the performance of the independent database action may affect the triggering client, or another client.

Several advantages are provided by a database management system that includes independent database actions. One advantage is that actions that would otherwise be performed as a result of notifications to other clients are performed automatically, without notifications. By using independent database actions, database management system100can avoid having to send notifications and receiving notification responses. As a result, database management system100does not have to wait for a notified client to respond to the notification before completing the triggered action. Among other advantages, this saves database system resources, limits use of networking bandwidth, and avoids situations where actions must be postponed until a response to a notification is received.

Independent database actions also allow the database management system to distribute internal resources so as to avoid having to complete the triggered action when internal resources are limited. For example, if transaction activity is heavy, the database management system150may provide for a triggered independent database action to be performed at a later time, in order to free up resources to overcome the heavy transaction activity.

Synchronous Actions

Synchronous actions are performed as part of the triggering transaction. As a result, the synchronous actions force server110to access database150for purpose of performing the synchronous action while the corresponding triggering transaction is being conducted.

In general, a synchronous action is triggered when a transaction from one client causes event monitor120to detect an event previously associated with that synchronous action. The register130may match a detected event to a synchronous action. The synchronous action may require server110to access database150to modify or retrieve data objects while the triggering transaction is being conducted.

Furthermore, the triggering transaction may also dictate the specific manner in which the synchronous action is performed. Therefore, if the transaction requires several communications between server110and database150, each communication may be accompanied by a corresponding act performed by server110on the database150as part of the identified synchronous action.

As an example, if a transaction triggers an event by modifying a first set of data object in database150, a corresponding synchronous action may require a similar modification to a second set of data objects. The second set of data objects are modified as part of the same transaction that modifies the first set of data objects. As a result, a transaction request from application115to modify the first set of data objects results in the second set of data objects being modified concurrently, and possibly simultaneously.

Notifications

Notifications cause server110or other components of database management system100to notify pre-designated clients115bor users of an event of interest. The notified user or client115bis typically different than the client/user who conducts the transaction that causes the event of interest to occur.

A notification requires one or more notified clients115bto interact with the database150. According to an embodiment, the notified clients115bmay conduct a separate transaction with the database150in response to being notified. The notification may be sent in response to the event of interest being detected on the triggering transaction. In some configurations, a triggering transaction may be precluded from completion until a transaction specified by the notification is completed.

It is possible for the client being notified to be off-line when the event of interest occurs. In such instances, the database system100messages the notified client115b. When the notified client115bcomes on-line, the notified client can be prompted to interact with database150in order to complete the transaction.

Operation of Independent Events

FIG. 2is a flowchart that illustrates a method for managing a database utilizing independent database actions. A method such as described withFIG. 2may be performed in a database management system such as described withFIG. 1. Reference to numerals inFIG. 1is intended to illustrate exemplary components for performing the method.

In step210, database management system100performs a transaction in response to a request from one of the clients115. The database management system100may perform numerous transactions with many other clients simultaneously. The transaction involves communications signaled and received by both the client115and the database management system100.

In step215, a determination is made as to whether the transaction is a triggering transaction by determining whether the transaction specifies operations, which when executed, cause an event of interest to occur. For example, with reference toFIG. 2, an event monitor120monitors transactions processed by database management system100to detect events of interest.

If the determination of step215is that the particular transaction is not a triggering transaction, step220provides that the database150is accessed to process the transaction without any other actions being performed as a result of that transaction.

If the determination is step215is that the transaction is a triggering transaction, then step225makes a determination as to whether an action or notification is to be performed. The action involves accessing the database being managed and performing some function associated with the event of interest without sending a notification. The notification involves sending notifications to one or more clients or users according to a previous designation. The notified client may then elect to perform some action in response to the notification. The notification may specify the manner in which the notified client should respond to the notification.

If in step225the event is matched to a notification, the step230provides that the appropriate clients are identified. For example, register130may match a detected event with identifications of previously identified clients115b, or of previously identified users operating clients115b.

If in step225the event is matched to an action, step235makes a determination as to whether the action is synchronous, or an independent database action. If the determination is that the action is synchronous, then step240provides that the synchronous action is executed concurrently with or immediately, as part of the triggering transaction, in response to detecting the event of interest.

If the determination in step235is that the action is an independent database action, then step245provides that the independent database action is executed independently of the triggering transaction. For example, queue140may manage instructions for executing the independent database action so as to load the instructions into the server110upon a determination that sufficient database resources are available. As another example, the instructions may be executed at a designated time after the triggering transaction, regardless of whether the triggering transaction is in process or completed.

FIG. 3is a block diagram illustrating a portioning of resources within a database server300. InFIG. 3, a queue340orders multiple transactions and/or actions for subsequent execution by corresponding execution mechanisms of database server300. In an embodiment, queue340may structure jobs that specify instructions stored within the database server300. The instructions may correspond to operations for performing transactions, synchronous actions, or independent database actions.

The database transactions are provided by a server component310, which receives and dispatches communications from clients. According to an embodiment, execution mechanisms of database server300are distributed to perform processes for synchronous actions, notifications, and independent database actions. These execution mechanisms may include, for example, processing resources, memory and cache. Some of the execution mechanisms of database server300include transaction mechanisms342for processing database transactions. Other mechanisms of database server300include (i) synchronous action mechanisms344for processing actions synchronously in response to event monitor320detecting events of interest; (ii) independent database action mechanisms346to perform actions on the database350independent of the triggering transaction or event of interest; and (iii) notification mechanisms348for notifying previously designated recipients of an event of interest.

As mentioned, some of the database transactions may be triggering transactions that cause events of interest to occur. An event monitor320may couple to a register330to match events of interest with actions or notifications. The register330may signal queue340to structure or order a job corresponding to, synchronous action, independent database actions, or notification. The register330may also cause the notification mechanism348of the database server300to send notifications corresponding to the detected events. If an event of interest is detected, the corresponding instructions are signaled to one of either the synchronous action mechanisms344, independent database action mechanisms346, or notification mechanisms348, based on the action identified by register330.

The synchronous action mechanisms344may identify corresponding instructions in order for synchronous actions (SYNC) to be executed on database350. The instructions for a synchronous action are executed on database350at the same time as the process for executing the triggering transaction. For example, a user may configure synchronous actions to be performed immediately whenever that user accesses database350. The user may then access the database350to operate on certain data objects. The synchronous action may correspond to operations that are performed on data objects of the database350concurrently while the database system300is conducting the triggering transaction with that user.

If the event of interest is associated with an independent database action, the independent database action mechanisms346identify instructions for executing independent database actions (IDA) on database350. The instructions accessed by independent database mechanisms346are executed on database350independently of the processes performed by transaction mechanisms342that execute the corresponding transaction. As an example, a user may configure database system300to execute instructions upon or after another user or client performs a specified operation on database350. When that specified operation is detected as being performed by the other user or client, the instructions for performing the independent database action are identified by the independent database action mechanisms346and executed on database350. These instructions are executed independently of the triggering transaction, so they may be executed after a determination is made that the queue340is available to execute the independent database action.

Both synchronous actions and independent database actions may be performed to modify, create, delete, update or perform other operations on selected data objects in database350.

If the event of interest is associated with a notification, the notification mechanisms348identifies instructions for sending notifications (NOT) to clients identified by that notification. The instructions for the notification mechanisms348may also specify how queue340is to perform responses from notified clients318.

In an embodiment, queue340is configured to efficiently manage the synchronous action mechanisms344, the independent database action mechanisms346, and the notification mechanisms348, so as to prevent congestion or other problems that would affect performance of the database system300. One advantage provided by processes performed by independent database action mechanisms346is that the database system300can be configured to perform the independent database action at any time. In contrast, processes for performing synchronous actions are executed immediately upon detecting the triggering event. Notification mechanisms348handle responses from notified client, which are not within the queue's control. But queue340can manage independent database action mechanisms346so that such actions are executed when sufficient resources are available to database server300. The overall resource availability of database server300may be determined from some predetermined factor, such as the size of all the jobs awaiting to be processed.

According to an embodiment, at least 50% of the overall resources in database server300is dedicated for the transaction mechanisms342. Thus, the predetermined factor for determining availability of the database server300would be whether 50% of the memory and processing resources of the database system are dedicated for processing transactions. The remaining portion of the overall resources can be divided amongst synchronous action mechanisms344, independent database action mechanisms346, and notification mechanisms348. Transaction execution mechanisms342borrow resources from independent database action mechanisms346in order to maintain sufficient resources to process all transactions during heavy use. Thus, independent database actions may be executed less often in times of heavy use. This allows for the database system300to be more flexible in handling all the transactions and required operations. The queue340may structure jobs to facilitate borrowing of resources amongst the different mechanisms.

The transactions, actions and notifications that are required to be processed by queue340may be managed to insure proper sequence and timing of executions. In an embodiment, a buffer345is integrated or otherwise accessible to database server300for the purpose of ordering and timing the transactions, actions and notifications. The buffer345may store a set of instructions in order to time their execution to be concurrent with communications of a corresponding transaction.

The independent database action mechanisms346have particular use for buffer345. In one embodiment, buffer345stores independent database actions using a first-in, first-out (FIFO) protocol for the detected events and/or instructions sets. Independent database action may, for example, comprise of several sets of instructions, involving more than one client. The buffer345orders the sets of instructions so that the instruction set for each client is executed in the appropriate order. For example, an outcome from a set of instructions for an independent database action of one client may require a result of another independent database action or notification response provided by another client.

The buffer345may perform similar functions for notifications being processed by notification mechanisms348. For example, the FIFO configuration of buffer345may cause responses from notified clients to be processed according to an order required by the triggering transaction.

To provide a more specific example, data objects in database350may be modified pending approval by two supervisors, designated as Supervisor A and B. Supervisor A can approve of a proposed modification only if Supervisor B first approves the proposed modification. Supervisor A may configure an independent database action to occur and perform the approval (or rejection) if Supervisor A wishes to completely automate the approval pending satisfaction of some conditions. Supervisor A may, for example, set an independent database action to approve the modification automatically if (i) Supervisor B approves the proposed modification, and (ii) if the proposed modification satisfies some criteria specified by Supervisor A. The buffer345may store instructions for holding Supervisor A's approval until Supervisor B's approval is received. Then, database system300may determine if the criteria specified by Supervisor A is met by the proposed modification. The instruction set for Supervisor A's approval may be maintained in buffer345until both of these conditions are met.

Hardware Description