Wait Statement for RDBMS
Publication Date: 2017-Mar-02
The IP.com Prior Art Database
Introducing a WAIT statement for flexibly reacting to data changes in an SQL DBMS: zero latency and no polling. Each table has an automatically updated increasing version number, so an application can declare that the WAIT statement execution should complete only if the table version is greater than a specified value (e.g. the number associated with the last data cange which has been already processed).
Wait Statement for RDBMS
Relational Data Base Management Systems (RDBMS) are used to store data, in form of tables containing rows and columns; SQL is the most widely used language to operate on data (reading or writing). Many applications can access the same RDBMS data concurrently. The RDBMS offers guarantees about data integrity, even in the event of parallel execution of writing jobs, by using a model of "transactions" where the evolution of the data happens through transactions, which are conceptually atomic. In many scenarios, one application modifies data and another one is designed so to do something whenever data are changed. For example, when an ecommerce application inserts a new customer into the "customers" table, the "promotional initiatives" application will ship a welcome present to the new customer. This is true also because it often happens that it is not possible to fuse all the functionalities into a single application logic (existing systems, different vendors, ...). In these scenarios, the "follower" application needs a way to follow the changes done in the database in a robust, efficient and low-latency way.
Current state of the art has some drawbacks. A continuous polling strategy on the main data is clearly often unfeasible. A widely used approach is to configure the RDBMS so that some interesting operations done by the "producer" application create additional data, in a form readily consumable by the "consumer" application. For example, by configuring a trigger on the INSERT statement for the customers table, the RDBMS can automatically create a new row in the new_customers table. The consumer application will poll on the new_customers table, read the entries, perform the needful and destroy the entries. This approach uses the new_customers table as a sort of queue, linking the first application flow with the second. It has some useful properties, such as not stopping or slowing done the main application if the second one is slow or inoperative, while at the same time keeping a reliable memory of what is still pending for the second application. The main problem with this approach is that polling is still necessary (on the secondary table). A very frequent poll is needed to reach a good latency of operations, but also creates useless load on the RDBMS in the very likely case that nothing is pending. In some implementations, cross-application notification mechanisms have been implemented (e.g. Notification Services). The notification can be mediated by the RDBMS if the applications are strictly coupled to the RDBMS (specific technologies and development patterns). These techniques have also other drawbacks, such as limitations on the kind of notifiable events, no tolerance for an offline consumer applications, performance considerations (e.g. one event is sent for each change), reliability considerations (e.g. lost events if the processing is not fast enough).
A new technique is hereby proposed. We modify the RDBMS...