Boost cache for SQL queries with write order consistency, data race management, partially materialized views for range queries, and arbitrary filter expressions

A computer implemented method for resolving data races and deduplicating rows between upqueries and updates to an underlying database, in a database caching layer on top of the underlying database, the method comprising:

• maintaining, by the caching layer, a dataflow graph, nodes of the dataflow graph representing row data of the underlying database, database operators, and partially or fully materialized queries, wherein the row data nodes are roots, the query nodes are children, and database operator nodes are between data row nodes and corresponding ones of the query nodes, wherein queries that use common row data and/or database operators share corresponding nodes;
• defining a separate Global Transaction Identifier (“GTID”) tracker as an auxiliary data structure associated with each specific node in the dataflow graph with a partially materialized state;
• separately tag-scope tracking, by each GTID tracker, upqueries occurring within a requisite period of time, for every path of the dataflow graph that traverses the corresponding specific node, such that each GTID tracker is associated with a corresponding tag-scope tracker;
• responsive to an upquery being triggered from a query node, marking the upquery, by a corresponding GTID tracker of the triggering query node, with a corresponding tag in the tag-scope tracker of the corresponding GTID tracker, the tag identifying a unique path through the dataflow graph that the results of the upquery traverse to arrive to the triggering query node;
• responsive to an upquery that results in access of the underlying database in order to be resolved, storing a corresponding record in the GTID tracker of the triggering query node, the corresponding record being shared between all separate replay paths that are tracked, and containing data that was requested from the underlying database for the upquery;
• upon completion of the upquery, before processing the results of the upquery:
• accessing the tag-scoped tracker of the GTID tracker of the triggering query node, and identifying a specific tag representing a unique path through the dataflow graph that the results of the upquery traverse;
• storing every specific record in the result of the upquery in a set of upqueries tracked within a requisite period of time for the specific tag, including a primary key that uniquely identifies the specific record, and the GTID that the underlying database returned for the specific record;
• accessing a global set of in-flight queries for the GTID tracker;
• removing the upquery from the set of in-flight queries;
• responsive to a current record in the result of the upquery having at least one accumulated buffered underlying database change event, yielding the at least one buffered underlying database change event;
• and
• forwarding results of the upquery, unchanged, through the dataflow graph;
• processing an underlying database change event by the GTID tracker for the triggering query node by performing the following steps:
• responsive to an underlying database change event being processed by the GTID tracker for the triggering query node, iterating through all the records in the change event;
• for each specific record in the change event, determining whether any corresponding tag-scoped tracker has tracked the specific record within a requisite period of time;
• for each specific new record in the change event that has been tracked by corresponding tag-scoped tracker within the requisite period of time, responsive to the previously tracked GTID being newer than or our equal to the GTID for the specific new record, tagging the specific new record in the change event with the tag with which it was tracked before;
• and
• for each specific new record in the change event that has been tracked by tag-scoped tracker within the requisite period of time, responsive to the previously tracked GTID being older than the GTID for the specific new record, if the specific new record in the change event is a new record that could have been returned by one of the in-flight upqueries, buffering the full contents of the change event in the inflight set for the GTID tracker, such that buffered events will be yielded after a current query is finished;
• prior to processing any record in a given one the database operator nodes of the dataflow graph, determining each specific tag that belongs to the given database operator node;
• and
• processing only records that are not tagged with any of the specific tags of the given database operator node.