Browse Prior Art Database

Checking Transactional Safety For Reuse Of Transient Results Disclosure Number: IPCOM000241409D
Publication Date: 2015-Apr-24
Document File: 4 page(s) / 38K

Publishing Venue

The Prior Art Database


A method and system is disclosed for checking transactional safety for reuse of transient results.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 38% of the total text.

Page 01 of 4

Checking Transactional Safety For Reuse Of Transient Results

Database Management System ("DBMS") query performance can often be significantly improved by saving results of queries and reusing the results for later identical queries. This is called caching.

Reused cache information is not always valid. The output might change responsive to input changes. The validity of a query and its dependence on possibly changing data is described by transaction isolation models. The strictest are "snapshot" and "serializable" isolation models. For a given transaction, these models provide a consistent database state reflecting i) changes due to transactions that committed before the given transaction began, and ii) uncommitted changes due to the given transaction itself.

In this context, an issue arises regarding what query results are worth caching and re-using. In general, the larger and more complex a query, the less likely it is to re-occur, which tends to make its result less valuable to re-use. However, complex queries are usually formed of smaller queries. For example, a SQL SELECT might have a sub select. Within a query, tables may be joined, and each table will have its own row and column filters (RESTRICTS & PROJECTS). Each of these query fragments represents an intermediate result that is more likely to re-occur than larger, more complex queries.

Due to potential re-use, results for these query fragments might be more valuable to cache than are the results of the long queries that the fragments came from. However, there is a limit to the value of caching results for short queries. If a query fragment is too short, it may consume more resources to cache and re-use its result than to simply re-execute the query fragment the next time it is encountered. In deciding what query results to cache and then deciding what results to re-use, it is not necessary to be completely correct, as long as no incorrect results are re-used. False negatives are acceptable (i.e., rejecting re-use of a valid result), while false positives are not (i.e., accepting re-use of an invalid result).

The disclosed method efficiently checks transactional safety for re-use of cached, intermediate results under the "snapshot" and "serializable" transaction isolation models. The method records modification and commit times for tables and cached results to determine when it is safe to reuse a cached result for a given query that happens to use a query fragment matching a fragment from an earlier query.

There are a number of important factors for correct and efficient implementation of such caches. For example, only results of deterministic calculations should be re-used. The identification of matching query fragments should be cheap but safe. There must be a strategy for allocation of the memory used by a cache. Such factors are not described here.

There are also difficulties with the definition of timestamps on distributed, multi-core


Page 02 of 4

systems. For purp...