Database Caching and Invalidation for Stored Procedures

Abstract

Database data is maintained reliably and invalidated based on actual changes to data in the database. Updates or changes to data are detected without parsing queries submitted to the database. Requests for stored procedures and their results can be cached without pre-existing knowledge of the stored procedure's operation. A caching system can open a session at the database instructing the database to monitor and log its processes. The caching system can pass the stored procedure request and access the log to determine how the stored procedure is processed and the queries executed as part of the stored procedure. The dependencies of a query can be determined by submitting a version of the received query to the database through a native facility provided by the database to analyze how query structures are processed. The results of the stored procedure can be cached the results of the query dependency analysis can be cached for subsequent request processing and invalidation.

Description

PRIORITY CLAIM [0001] This application claims the benefit of U.S. Provisional Patent Application No. 60 / 684,610, filed May 25, 2005, entitled “Terracotta Virtualization Server”, and incorporated by reference herein in its entirety. CROSS-REFERENCE TO RELATED APPLICATIONS [0002] The following applications are cross-referenced and incorporated by reference herein in their entirety: [0003] U.S. patent application No. ______, filed concurrently, entitled “Database Caching and Invalidation Using Database Provided Facilities for Query Dependency Analysis,” by Harward et al., filed concurrently (Attorney Docket No. TERA-01007USO); and [0004] U.S. patent application No. _____, filed concurrently, entitled “Database Caching and Invalidation Based on Detected Database Updates,” by Harward et al., filed concurrently (Attorney Docket No. TERA-01008US0).BACKGROUND OF THE INVENTION [0005] 1. Field of the Invention [0006] The present invention relates generally to databases and caching systems for...

AI Technical Summary

Benefits of technology

[0016] A database caching system and related techniques are provided that can reliably maintain and invalidate database data based on actual changes to data in the database. Updates or changes to data at the database are detected without parsing queries submitted to the database. The dependencies of a received query can be determined by submitting a version of the received query to the database through a native facility provided by the database itself to analyze how query structures are processed at the database. The caching system can access the results of the facility to determine the tables or rows (or other data partition) a received query is dependent upon or modifies. In addition to the results of a query that can be cached with an indication of the query itself, an abstracted form of the query can be cached with an indication of the tables, rows, etc. that queries of that structure access or modify. The tables a write or update query modifies can be cached with a time of their last modification. When a query is received for which the results are cached, the system can readily determine dependency information for the query, the last time the dependencies were modified, and compare this time with the time indicated for when the cached results were retrieved. By passing versions of write queries to the database, updates to the database can be detected. In one embodiment, a component is implemented at or on the system of the database to directly detect changes to the database data. This component can monitor transactional information maintained by the database itself to determine when changes to the database occur. This component can communicate with the cache to provide notification of changes to the database.

Problems solved by technology

Without database storage in many of these environments, the task of methodically sorting through the amount of information necessary to access that which is needed would likely frustrate the ultimate purpose of the application to the point of ending many operations.

Of course, the complexity of operations performed by and submitted to databases continues to expand.

This will not only increase the performance as seen by requesting applications or entities, but also decrease the amount of load placed on the database.

An inherent difficulty with database caching is the structured nature of both the data storage and retrieval.

The cache simply looks for an update to the location on the memory volume corresponding to the cached data and when the data at that location changes, the cache can simply invalidate or discard its cached results.

These systems do not cache results based on an actual query.

Because queries may combine or access data from various locations in the database, and request data based on logic that is dependent upon the data stored therein, it is difficult to accurately determine when the previously retrieved results for a query are no longer valid because of changes to data in the database.

While in theory such a technique is useful, in practice it presents obstacles.

Because an intimate knowledge of the database's internal operations and code can not be had, parsing the queries is in many instances deficient or even wrong.

Moreover, individual database providers frequently alter their databases, requiring these parsing techniques to continually be updated as the database is updated.

However, the entries are automatically invalidated after a specified amount of time without any regard to actual changes at the database.

If changes to the database are made between invalidation periods and a request is received, the cache may return invalid results.

Additionally, this technique can degrade performance and efficiency by needlessly invalidating accurate query results.

Images