Server supporting a consistent client-side cache

• US 10,296,629 B2
• Filed: 10/22/2007
• Issued: 05/21/2019
• Est. Priority Date: 10/20/2006
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Chidambaran, Lakshminarayanan, Bastawala, Mehul Dilip, Krishnaswamy, Srinath, Lahiri, Tirthankar, Loaiza, Juan R., Sinha, Bipul, Vemuri, Srinivas S.
• Primary Examiner(s): Shanmugasundaram, Kannan
• Application Number:
  US11/876,679
• Publication Number:
  US 20080098041A1
• Time in Patent Office: 4,229 Days
• Field of Search: 707613
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application

Abstract

A method, system, and computer program product is disclosed for interacting with a client supported by a client-side cache. Embodiments of a method, a system, and a computer program product are disclosed that retrieve a first snapshot, indicating a state of the database after a last database request by the client, associated with the client, determine any number of invalid cached results for the client based on the first snapshot, and transmit the any number of invalid cached results and a second snapshot, an update for the first snapshot.

Claims

1. A computer implemented method for a database server to interact with a client supported by a client-side cache, comprising:

using a computer system which comprises at least one processor to perform a process, the process comprising;

maintaining a series of state identifiers that reflect a state of a database that comprises a first table and a second table on the database server and are updated according to database transaction commits;

determining, at the database server, whether one or more database queries are cacheworthy in a client-side cache based at least in part upon one or more criteria, wherein the one or more database queries are requested by a client for execution on the first table, and the client-side cache resides in memory of and is accessible by a client computing system of the client;

in response to a determination that the one or more database queries are cacheworthy, registering the one or more database queries as one or more registered database queries at the database server with respect to at least the first table;

generating one or more result sets by executing the one or more database queries on the first table, wherein the one or more database queries refer to the first table but not the second table to make at least one first change to the first table on the database server;

transmitting the one or more result sets to the client-side cache on the client computing system based at least in part upon the determination that the one or more database queries are cacheworthy, wherein network roundtrips are eliminated for repeated retrievals of the one or more result sets from servers;

in response to the at least one first change to the first table, executing a trigger that automatically performs one or more additional database transactions that make at least one second change to the second table in the database, wherein the one or more database queries are directly directed to the first table but not the second table;

determining, at the database server, an updated state identifier for the series by changing a current state identifier in the series into the updated state identifier based at least in part on the one or more additional database transactions;

receiving a message or a request from the client computing system, the message or request including a snapshot identification associated with the client;

determining cache invalidation at least by identifying at least one registered database query of the one or more registered database queries and by incorporating information about the at least one registered database query into the cache invalidation based in part or in whole upon comparing the snapshot identification associated with the client against the updated state identifier for the series; and

transmitting the cache invalidation and the updated state identifier in response to the message or the request to the client computing system to update the client-side cache.

2. The computer implemented method of claim 1, further comprising:

receiving a database server request for servicing the client, wherein the database server request includes the state identifier in the series; and

creating the updated state identifier in the series, wherein the updated state identifier in the series comprises a snapshot of the database upon processing of the database server request.

3. The method of claim 1, further comprising transmitting either one or more cached result set identifiers or one or more cached results that relate to one or more database transactions that occurred between the state identifier in the series and the updated state identifier in the series, wherein the cache invalidation comprises either one or more cached results or one or more cached result identifiers for a client session that relates one or more database queries to uncommitted database changes made by the client with the client session.

4. The method of claim 1, wherein

associating the one or more result sets with a first state identifier in the series of state identifiers based at least in part upon the determination that the one or more database queries are cacheworthy, wherein

the one or more criteria comprise at least one of respective sizes of the one or more result sets, or computational costs of executions of the one or more database queries,

the at least one first change in the first table automatically causes an execution of a first trigger that modifies the second table on the database server with one or more changes, and

the one or more result sets correspond to the at least one first change in the first table and the one or more changes in the second table on the database server;

and

determining whether an existing result set in the client-side cache is invalid by using at least the information about the at least one registered database query.

5. The method of claim 4, determining whether the existing result set in the client-side cache is invalid comprising:

accessing a set of registered database queries including the one or more database queries, wherein

a registered database query from among the set of registered database queries comprises a query identification and one or more respective object identifications of one or more database objects that correspond to the query identification, and

the one or more database objects that correspond to the query identification are associated with first snapshot data;

comparing the first snapshot data with second snapshot data, wherein the second snapshot data is associated with a latest version of the one or more database objects that correspond to the query identification; and

in response to the message or the request received from the client computing system, transmitting a response back to the client computing system to invalidate the existing query result set based at least in part upon a comparison between the first snapshot data and the second snapshot data that indicates a change to at least one database object of the one or more database objects corresponding to the query identification that is specified to cause an invalidation of the existing result set for the query identification.

6. The method of claim 2, further comprising:

invalidating at least one result set that is cached in the client-side cache of the client, wherein the at least one result set corresponds to an uncommitted database transaction;

requesting the database execute a query;

determining if the query is cacheworthy;

registering the query with a database change notification module if the query is cacheworthy; and

transmitting a query result to the client.

7. The method of claim 5, further comprising:

transmitting first query information for a first registered database query that has been determined at the server to be cacheworthy to the client computing system;

receiving a different message or a different request from the client computing system indicating that a first result set corresponding to the first registered database query is not cached in the client-side cache due to one or more constraints on the client computing system;

preventing transmissions of the cache invalidation related to the first registered database query to the client computing system based at least in part upon the different message or the different request received from the client computing system;

identifying second query information for a second registered database query of the one or more registered database queries that have been determined to be cacheworthy;

determining that the second registered database query is no longer cache worthy;

incorporating the second query information into the cache invalidation; and

transmitting the cache invalidation to the client computing system in response to a separate message or a separate request received from the client computing system.

8. The computer implemented method of claim 1, in which a second item of data related to the updated state identifier comprises a second system commit identifier that is associated with the updated state identifier.

9. The computer implemented method of claim 1, in which a first item of data associated with a database query requested by the client or a result set corresponding to the database query comprises one or more queries or a first identifier for each of the one or more queries, wherein the one or more queries were previously requested by the client and are now invalidated.

10. The computer implemented method of claim 2, further comprising:

transmitting the at least result set in response to a database operation from the client-side cache; and

associating a unique identifier with the result set.

11. The computer implemented method of claim 10, further comprising:

storing the unique identifier in a persistent computer readable storage medium or a persistent computer storage device.

12. The computer implemented method of claim 2, further comprises:

issuing, by the database server, a database operation for servicing the client, wherein the client does not issue the database operation.

13. The computer implemented method of claim 1, wherein the database server comprises one or more relationships which require making one or more changes that are transparent to the client.

14. The computer implemented method of claim 6, further comprises:

causing the query result of the query not to be stored in the client-side cache, wherein the query is determined not to be cacheworthy.

15. A computer implemented method for a database interface supporting a client cache, comprising:

using a computer system which comprises at least one processor to perform a process, the process comprising;

determining, at the database server, whether one or more database queries are cacheworthy in a client-side cache based at least in part upon one or more criteria, wherein the one or more database queries are requested by a client for execution on a first table in a database on a database server, and the client-side cache resides in memory of and is accessible by a client computing system of the client;

in response to a determination that the one or more database queries are cacheworthy, registering the one or more database queries as one or more registered database queries at the database server with respect to at least the first table;

generating one or more result sets at least by executing the one or more database queries on the first table, wherein the database further comprises a second table, and the one or more database queries refer to the first table but not the second table to make at least one first change to the first table on the database server;

maintaining the one or more result sets in the client-side cache at least by transmitting the one or more result sets to the client computing system based at least in part upon the determination that the one or more database queries are cacheworthy, wherein

network roundtrips are eliminated for repeated retrievals of the one or more result sets from servers,

the one or more database queries are directly directed to the first table but not the second table in the database,

the one or more result sets are associated with a first system commit identifier in a series of system commit identifiers, and

the series of system commit identifiers reflect a respective state of the database on the database server and are updated by the database server according to database transaction commits;

in response to the at least one first change to the first table, executing a trigger that automatically executes one or more additional database transactions that make at least one second change to the second table in the database;

determining and transmitting an updated system commit identifier from the database server to the client computing system based at least in part upon execution of the one or more additional database transactions;

receiving a message or a request from the client after the execution of the one or more additional database transactions, the message or the request including a snapshot identification associated with the client;

determining cache invalidation based at least in part upon the execution of the one or more additional database transactions at least by identifying at least one registered database query of the one or more registered database queries and by incorporating information about the at least one registered database query into the cache invalidation based in part or in whole upon comparing the snapshot identification associated with the client against the updated system commit identifier; and

determining at least one result set of the one or more result sets in the client-side cache to be invalid or dirty based at least in part upon the cache invalidation.

16. The method of claim 15, further comprising:

receiving a query result set for a query in the database operation; and

receiving one or more keys for caching the query result set in the client-side cache.

17. The computer implemented method of claim 15, in which the updated state identifier is compared with the first state identifier to identify one or more first result sets in the client-side cache to invalidate.

18. The computer implemented method of claim 17, in which the updated state identifier comprises one or more query identifiers and one or more transaction identifiers that are associated with the one or more first result sets in the client-side cache.

19. The computer implemented method of claim 17, in which the updated state identifier is further used to identify one or more second result sets in the client-side cache to invalidate.

20. The computer implemented method of claim 15, the process further comprising:

identifying a constraint that is associated with the client;

identifying a first query that is cache-worthy, but a first corresponding result set in response to the first query is not cached in the client-side cache due to the constraint; and

transmitting an identifier of the first query to the database server to prevent the database server from sending the updated state identifier for one or more results sets related to the first query.

21. A database management system that interacts with a client supported by a client-side cache, comprising:

a database server which comprises at least one processor to maintain a series of state identifiers that reflect a state of a database on the database server and are updated according to database transaction commits, the database comprising a first table and a second table;

the database server further configured to determine whether one or more database queries are cacheworthy in a client-side cache based at least in part upon one or more criteria, wherein the one or more database queries are requested by a client for execution on the first table, and the client-side cache resides in memory of and is accessible by a client computing system of the client;

in response to a determination that the one or more database queries are cacheworthy, the database server further configured to the one or more database queries as one or more registered database queries at the database server with respect to at least the first table;

the database server further configured to one or more result sets by executing the one or more database queries on the first table, wherein the one or more database queries refer to the first table but not the second table to make at least one first change to the first table on the database server;

the database server comprising the at least one processor further configured to transmit the one or more result sets to the client-side cache on the client computing system based at least in part upon the determination that the one or more database queries are cacheworthy, wherein network roundtrips are eliminated for repeated retrievals of the one or more result sets from servers;

in response to the at least one first change to the first table, the database server further configured to execute a trigger that automatically performs one or more additional database transactions that make at least one second change to the second table in the database, wherein the one or more database queries are directly directed to the first table but not the second table;

the database server further configured to;

determine an updated state identifier for the series by changing a current state identifier in the series into the updated state identifier based at least in part on the one or more additional database transactions at the database transactions;

receive a message or a request from the client computing system, the message or the request including a snapshot identification associated with the client, and

determine cache invalidation at least by identifying at least one registered database query of the one or more registered database queries and by incorporating information about the at least one registered database query into the cache invalidation based in part or in whole upon comparing the snapshot identification associated with the client against the updated state identifier for the series; and

the database server further configured to transmit the cache invalidation and the updated state identifier in response to the message or the request to the client to update the client-side cache.

22. A computer program product comprising a non-transitory computer readable storage medium having stored thereupon a sequence of instructions which, when executed by a processor, causes the processor to perform a method for a database server to interact with a client supported by a client-side cache, the method comprising:

using a computer system which comprises at least one processor that is to perform a process, the process comprising;

maintaining a series of state identifiers that reflect a state of a database that comprises a first table and a second table on the database server and are updated according to database transaction commits;

determining, at the database server, whether one or more database queries are cacheworthy in a client-side cache based at least in part upon one or more criteria, wherein the one or more database queries are requested by a client for execution on the first table, and the client-side cache resides in memory of and is accessible by a client computing system of the client;

in response to a determination that the one or more database queries are cacheworthy, registering the one or more database queries as one or more registered database queries at the database server with respect to at least the first table;

generating one or more result sets by executing the one or more database queries on the first table, wherein the one or more database queries refer to the first table but not the second table to make at least one first change to the first table on the database server;

transmitting the one or more result sets to the client-side cache on the client computing system based at least in part upon the determination that the one or more database queries are cacheworthy, wherein network roundtrips are eliminated for repeated retrievals of the one or more result sets from servers;

in response to the at least one first change to the first table, executing a trigger that automatically performs one or more additional database transactions that make at least one second change to the second table in the database, wherein the one or more database queries are directly directed to the first table but not the second table;

determining, at the database server an updated state identifier for the series by changing a current state identifier in the series into the updated state identifier based at least in part on the one or more additional database transactions;

receiving a message or a request from the client computing system, the message including a snapshot identification associated with the client;

determining cache invalidation at least by identifying at least one registered database query of the one or more registered database queries and by incorporating information about the at least one registered database query into the cache invalidation based in part or in whole upon comparing the snapshot identification associated with the client against the updated state identifier for the series; and

transmitting the cache invalidation and the updated state identifier in response to the message or the request to the client computing system to update the client-side cache.

23. The computer program product of claim 22, in which the updated state identifier is used to identify a first result set in the client-side cache to invalidate.

24. The computer program product of claim 23, in which the updated state identifier comprises a query identifier and a transaction identifier that are associated with the first result set.

25. The computer program product of claim 24, in which the updated state identifier is further used to identify a second result set in the client-side cache to invalidate.

26. The computer program product of claim 22, the process further comprising:

identifying a constraint that is associated with the client;

identifying a first query that is cache-worthy, but a first corresponding result set in response to the first query is not cached in the client-side cache due to the constraint; and

transmitting an identifier of the first query to the database server to prevent the database server from sending the updated state identifier for one or more results sets related to the first query.

27. A computer program product comprising a non-transitory computer readable storage medium having stored thereupon a sequence of instructions which, when executed by a processor, causes the processor to perform a method for a database interface supporting a client cache, the method comprising:

using a computer system which comprises at least one processor to performs a process, the process comprising;

determining, at the database server, whether one or more database queries are cacheworthy in a client-side cache based at least in part upon one or more criteria, wherein the one or more database queries are requested by a client for execution on a first table in a database on a database server, and the client-side cache resides in memory of and is accessible by a client computing system of the client;

in response to a determination that the one or more database queries are cacheworthy, registering the one or more database queries as one or more registered database queries at the database server with respect to at least the first table;

generating one or more result sets at least by executing the one or more database queries on the first table, wherein the database further comprises a second table, and the one or more database queries refer to the first table but not the second table to make at least one first change to the first table on the database server;

maintaining one or more result sets in a client-side cache at least by transmitting the one or more result sets to the client computing system based at least in part upon the determination that the one or more database queries are cacheworthy, wherein

network roundtrips are eliminated for repeated retrievals of the one or more result sets from servers,

the one or more database queries are directly directed to the first table but not the second table in the database,

the one or more result sets are associated with a first system commit identifier in a series of system commit identifiers, and

the series of system commit identifiers reflect a respective state of the database on the database server and are updated by the database server according to database transaction commits;

in response to the at least one first change to the first table, executing a trigger that automatically executes one or more additional database transactions that make at least one second change to the second table in the database;

determining and transmitting an updated system commit identifier from the database server to the client computing system based at least in part upon execution of the one or more additional database transactions;

receiving a message or a request from the client, the message or the request including a snapshot identification associated with the client;

determining cache invalidation based at least in part upon the execution of the one or more additional database transactions at least by identifying at least one registered database query of the one or more registered database queries and by incorporating information about the at least one registered database query into the cache invalidation based in part or in whole upon comparing the snapshot identification associated with the client against the updated system commit identifier; and

determining at least one result set of the one or more result sets in the client cache to be invalid or dirty based at least in part upon the cache invalidation.

28. The computer program product of claim 27, in which the updated state identifier is used to identify a first result set in the client-side cache to invalidate.

29. The computer program product of claim 28, in which the updated state identifier comprises a query identifier and a transaction identifier that are associated with the first result set.

30. The computer program product of claim 29, in which the updated state identifier is further used to identify a second result set in the client-side cache to invalidate.

31. The computer program product of claim 27, the process further comprising:

identifying a constraint that is associated with the client;

identifying a first query that is cache-worthy, but a first corresponding result set in response to the first query is not cached in the client-side cache due to the constraint; and

transmitting an identifier of the first query to the database server to prevent the database server from sending the updated state for one or more results sets related to the first query.

32. A system for a database interface supporting a client cache, comprising:

a computer system which comprises at least one processor to;

determine, at a database server, whether one or more database queries are cacheworthy in a client-side cache based at least in part upon one or more criteria, wherein the one or more database queries are requested by a client for execution on a first table in a database on a database server, and the client-side cache resides in memory of and is accessible by a client computing system of the client;

in response to a determination that the one or more database queries are cacheworthy, register the one or more database queries as one or more registered database queries at the database server with respect to at least the first table;

generate one or more result sets at least by executing the one or more database queries on the first table, wherein the database further comprises a second table, and the one or more database queries refer to the first table but not the second table to make at least one first change to the first table on the database server;

maintain the one or more result sets in the client-side cache at least by transmitting the one or more result sets to the client computing system based at least in part upon the determination that the one or more database queries are cacheworthy wherein

network roundtrips are eliminated for repeated retrievals of the one or more result sets from servers,

the one or more database queries are directly directed to the first table but not the second table in the database,

the one or more result sets are associated with a first system commit identifier in a series of system commit identifiers, and

the series of system commit identifiers reflect a respective state of the database on the database server and are updated by the database server according to database transaction commits;

in response to the at least one first change to the first table, execute a trigger that automatically executes one or more additional database transactions that make at least one second change to the second table in the database;

determining and transmit an updated system commit identifier from the database server to the client computing system based at least in part upon execution of the one or more additional database transactions;

receive a message or a request from the client, the message or the request including a snapshot identification associated with the client;

determine cache invalidation based at least in part upon the execution of the one or more additional database transactions at least by identifying at least one registered database query of the one or more registered database queries and by incorporating information about the at least one registered database query into the cache invalidation based in part or in whole upon comparing the snapshot identification associated with the client against the updated system commit identifier; and

determine at least one result set of one or more result sets in the client cache to be invalid or dirty based at least in part upon the cache invalidation.

33. The system of claim 32, in which the updated state identifier is used to identify a first result set in the client-side cache to invalidate.

34. The system of claim 33, in which the updated state identifier comprises a query identifier and a transaction identifier that are associated with the first result set.

35. The system of claim 34, in which the updated state identifier is further used to identify a second result set in the client-side cache to invalidate.

36. The system of claim 32, in which the at least one processor is further to:

identify a constraint that is associated with the client;

identify a first query that is cache-worthy, but a first corresponding result set in response to the first query is not cached in the client-side cache due to the constraint; and

transmit an identifier of the first query to the database server to prevent the database server from sending the updated state identifier for one or more results sets related to the first query.