Query based invalidation subscription

• US 7,962,481 B2
• Filed: 06/10/2004
• Issued: 06/14/2011
• Est. Priority Date: 09/04/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Vemuri, Srinivas, De Groot, Michael, Bing, Eric W., Sinha, Bipul, Ganesh, Amit
• Primary Examiner(s): Cottingham; John R.
• Assistant Examiner(s): Reyes; Mariela D
• Application Number:
  US10/866,344
• Publication Number:
  US 20050055384A1
• Time in Patent Office: 2,560 Days
• Field of Search: 707/721, 707/732, 707/784, 707/740
• US Class Current: 707/732
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 16/24568 Data stream processing; Continuous queries

Abstract

A query result set is an example of a type of body of data whose content is defined by a query. Modifications to a database can change the result of a query and therefore what the content of such a body of data, such as a result set, should be. Active Queries is an approach that enables a database server to detect database changes that alter the result of a query registered for a client and to notify a client of those changes. The notifications may be sent to, for example, a cache manager responsible for maintaining coherency of a result set cache generated by the query. The notification notifies the cache manager of when and/or what changes are needed to maintain the coherency of the cache.

Claims

1. A computer-implemented method for tracking changes to data, the method comprising the steps of:

receiving, at a database server that manages access to a database, a request from a first subscriber to register a first query to receive change notifications of modifications to the database that may change the result of the first query;

said database server registering said first query in response to said request from said first subscriber;

receiving, at said database server, a request from a second subscriber to register a second query to receive change notifications of modifications to the database that may change the result of the second query;

said database server registering said second query in response to said request from said second subscriber;

receiving, at said database server, a request from said first subscriber to make a modification, as part of a database transaction, to the database;

said database server determining that said modification may change a result of said first query and said second query;

returning to said first subscriber, before said database transaction commits, a notification to indicate that said modification may have changed the result of said first query; and

after returning said notification to said first subscriber, committing said database transaction and, after said database transaction commits, transmitting a notification to said second subscriber to indicate that said modification, requested by said first subscriber, may have changed the result of said second query;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, wherein the steps further include:

computing at least one of said first or second queries to generate the result as a body of data; and

maintaining coherency between the database and the body of data.

3. The method of claim 2,

wherein the body of data contains first data items that are copies of a current version of second data items in the database;

and

wherein the step of maintaining coherency includes invalidating, in response to changing at least one second data item of said second data items, a first data item of said first data items that correspond to said at least one second data item.

4. The method of claim 3, wherein invalidating includes marking said first data item as an invalid copy.

5. The method of claim 3, wherein:

said first data item is associated with a coherency interval; and

wherein the step of invalidating includes establishing a new coherency interval for said first data item.

6. The method of claim 2, wherein the step of maintaining coherency includes maintaining coherency between said body of data and said database according to ACID principles.

7. The method of claim 2,

wherein said database server belongs to a multi-tier database system that includes a middle tier;

and

wherein said body of data is a result set generated for a client of the database server and stored in a result set cache in the middle tier.

8. The method of claim 1, wherein:

a first cache copy in a result set cache corresponds to a first data item in said database;

the method further includes the steps of;

before committing said database transaction;

as part of said transaction, said database server receiving a request to change said first data item;

said database server making said change to said first data item, wherein making said change causes said database server to make another change to another data item that belongs to the result of said first and second queries; and

wherein the notifications to the first and second subscriber indicate that said another change may have changed the result of the query.

9. The method of claim 1, wherein:

the method further includes;

said database server committing a plurality of transactions, including said database transaction.

10. The method of claim 1, wherein at least one of said first or second queries contains data that specifies that said database server should inform a client of changes to the result of said at least one of said first or second queries.

11. The method of claim 10, wherein the at least one of said first or second queries specifies a period of time during which said database server should inform the client of changes to the result of the at least one of said first or second queries.

12. The method of claim 10, wherein the at least one of said first or second queries specifies a pseudo-column that contains an identifier that the database server uses to identify the at least one of said first or second queries.

13. The method of claim 10, wherein the at least one of said first or second queries specifies a routine that said database server may invoke to transmit a notification.

14. The method of claim 1, wherein the step of said database server determining that said modification may change a result of said first query and said second query is based on one or more filtering conditions.

15. The method of claim 14, further comprising changing at least one of the one or more filtering conditions in response to said modification.

16. A non-transitory computer-readable storage medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform:

receiving, at a database server that manages access to a database, a request from a first subscriber to register a first query to receive change notifications of modifications to the database that may change the result of the first query;

said database server registering said first query in response to said request from said first subscriber;

receiving, at said database server, a request from a second subscriber to register a second query to receive change notifications of modifications to the database that may change the result of the second query;

said database server registering said second query in response to said request from said second subscriber;

receiving, at said database server, a request from said first subscriber to make a modification, as part of a database transaction, to the database;

said database server determining that said modification may change a result of said first query and said second query;

returning to said first subscriber, before said database transaction commits, a notification to indicate that said modification, requested by said first subscriber, may have changed the result of said first query; and

after returning said notification to said first subscriber, committing said database transaction and, after said database transaction commits, transmitting a notification to said second subscriber to indicate that said modification may have changed the result of said second query.

17. The non-transitory computer-readable storage medium of claim 16 further comprising instructions which, when executed by the one or more processors, cause the one or more processors to perform:

computing at least one of said first or second queries to generate the result as a body of data; and

maintaining coherency between the database and the body of data.

18. The non-transitory computer-readable storage medium of claim 17,

wherein the body of data contains first data items that are copies of a current version of second data items in the database;

and

wherein the instructions for maintaining coherency include instructions for invalidating, in response to changing at least one second data item of said second data items, a first data item of said first data items that correspond to said at least one second data item.

19. The non-transitory computer-readable storage medium of claim 18, wherein instructions for invalidating include instructions for marking said first data item as an invalid copy.

20. The non-transitory computer-readable storage medium of claim 18, wherein:

said first data item is associated with a coherency interval; and

wherein the instructions for invalidating include instructions for establishing a new coherency interval for said first data item.

21. The non-transitory computer-readable storage medium of claim 17, wherein the instructions for maintaining coherency include instructions for maintaining coherency between said body of data and said database according to ACID principles.

22. The non-transitory computer-readable storage medium of claim 17,

wherein said database server belongs to a multi-tier database system that includes a middle tier;

and

wherein said body of data is a result set generated for a client of the database server and stored in a result set cache in the middle tier.

23. The non-transitory computer-readable storage medium of claim 16, wherein:

a first cache copy in a result set cache corresponds to a first data item in said database;

the non-transitory computer-readable medium further comprising instructions which, when executed by the one or more processors, cause the one or more processors to perform;

before committing said database transaction;

as part of said transaction, said database server receiving a request to change said first data item;

said database server making said change to said first data item, wherein making said change causes said database server to make another change to another data item that belongs to the result of said first and second queries; and

wherein the notifications to the first and second subscriber indicate that said another change may have changed the result of the query.

24. The non-transitory computer-readable storage medium of claim 16 further comprising instructions which, when executed by the one or more processors, cause the one or more processors to perform:

said database server committing a plurality of transactions, including said database transaction.

25. The non-transitory computer-readable storage medium of claim 16, wherein at least one of said first or second queries contains data that specifies that said database server should inform a client of changes to the result of said at least one of said first or second queries.

26. The non-transitory computer-readable storage medium of claim 25, wherein the at least one of said first or second queries specifies a period of time during which said database server should inform the client of changes to the result of the at least one of said first or second queries.

27. The non-transitory computer-readable storage medium of claim 25, wherein the at least one of said first or second queries specifies a pseudo-column that contains an identifier that the database server uses to identify the at least one of said first or second queries.

28. The non-transitory computer-readable storage medium of claim 25, wherein the at least one of said first or second queries specifies a routine that said database server may invoke to transmit a notification.

29. The non-transitory computer-readable storage medium of claim 16, wherein said database server determining that said modification may change a result of said first query and said second query is based on one or more filtering conditions.

30. The non-transitory computer-readable storage medium of claim 29, further comprising instructions which, when executed by the one or more processors, cause the one or more processors to perform changing at least one of the one or more filtering conditions in response to said modification.