Reducing update conflicts when maintaining views

Patent metadata

Field	Value
Publication number	US-9483525-B2
Application number	US-79686507-A
Country	US
Kind code	B2
Filing date	Apr 30, 2007
Priority date	Apr 30, 2007
Publication date	Nov 1, 2016
Grant date	Nov 1, 2016

Abstract

Aspects of the subject matter described herein relate to reducing update conflicts when maintaining views. In aspects, a transaction in snapshot isolation may update a materialized view. Instead of being able to view the update within the transaction, the update is hidden to prevent the transaction from obtaining inconsistent data. When a transaction is not supposed to see an update to a materialized view that occurs within the transaction, additional information associated with the materialized view may indicate this. When a query of the materialized view is made within the transaction, the query engine may use the additional information to exclude data from the update in the result.

First claim

What is claimed is:

1. A computer storage medium having computer-executable instructions, which when executed perform actions, comprising:
within a transaction, making an update to a materialized view in response to a data modification that occurs after a snapshot time of the transaction, the transaction having a view of data that existed as of the snapshot time together with any changes the transaction has made to the data after the snapshot time, the update occurring before the transaction completes; and
before the transaction completes, without aborting the transaction, in order to prevent the transaction from obtaining inconsistent data, excluding the update from the transaction such that a query of the materialized view within the transaction does not return the update made by the transaction, returning a result in response to the query based on data that existed before the snapshot time.

2. The computer storage medium of claim 1 , wherein the materialized view comprises a database object in which results from a previous query have been stored.

3. The computer storage medium of claim 2 , wherein the database object is updated after data associated with the query changes.

4. The computer storage medium of claim 3 , wherein the database object is updated after another transaction commits data to a data structure affecting the materialized view but before the transaction completes.

5. The computer storage medium of claim 2 , wherein the database object comprises a table.

6. The computer storage medium of claim 1 , wherein excluding the update from the transaction such that a query of the materialized view within the transaction does not return the update comprises marking the update with a flag that indicates that the update is invisible to the transaction.

7. The computer storage medium of claim 1 , wherein excluding the update from the transaction such that a query of the materialized view by the transaction does not return the update comprises associating the update with a transaction identifier other than a transaction identifier with which updates made within the transaction are obtained.

8. A method implemented at least in part by a computer, the method comprising:
within a transaction, querying a materialized view to which an update was caused to be made within the transaction, the transaction having a view of a database that existed as of a snapshot time as modified by changes the transaction has made after the snapshot time, the snapshot time corresponding to a time before the transaction began; and
in response to the querying, before the transaction completes, without aborting the transaction, in order to prevent the transaction from obtaining inconsistent data, excluding the update from the transaction thus returning a result that does not include information associated with the update made by the transaction.

9. The method of claim 8 , wherein the materialized view comprises a result of a query of one or more tables that, the result being stored for subsequent use.

10. The method of claim 9 , wherein the materialized view is updated incrementally as data that would affect the result of the query if performed again is updated, inserted, or deleted.

11. The method of claim 8 , wherein returning a result that does not include information associated with the update comprises excluding data from a row of the materialized view from the result, the row including additional data that indicates that the update is invisible to the transaction.

12. The method of claim 8 , wherein returning a result that does not include information associated with the update comprises excluding data from a row of the materialized view from the result, the row associated with additional data that indicates that the update is invisible to the transaction.

13. The method of claim 8 , wherein returning a result that does not include information associated with the update comprises excluding data from a row of the materialized view from the result, the row being updated, inserted, or deleted within the transaction, the row associated with additional data that associates the row with a transaction identifier that the transaction is not to use to retreive data from the materialized view.

14. The method of claim 8 , wherein querying a materialized view comprises sending a query to a database management system, the query instructing the database management system to return data associated with the materialized view that meets criteria specified in the query.

15. The method of claim 14 , wherein the query instructs the database management system to join one or more other tables with the materialized view to obtain the result.

16. The method of claim 8 , further determining whether the transaction is able to view the update by consulting additional information associated with the update, the additional information being unavailable to the transaction.

17. An apparatus, comprising:
a computing system environment comprising a processing unit coupled to a system memory, the computing system environment further comprising:
a transaction coordinator operable to provide snapshot isolation to transactions, the snapshot isolation providing a view to each transaction of data that existed at a time said transaction began;
a materialized view updater operable to update a materialized view to include an indication that a transaction in which the materialized view was updated is not allowed to see the update, wherein the indication comprises an identifier associated with the transaction, the identifier indicating that the update was caused by the transaction; and
a query engine operable to obtain data from the materialized view in accordance with the indication, the query engine further operable to, in order to prevent the transaction from obtaining inconsistent data, based upon the identifier indicating that the update was caused by the transaction, exclude the update from the transaction such that a query of the materialized view within the transaction does not return the update made by the transaction.

18. The apparatus of claim 17 , wherein the indication comprises a flag in additional data associated with a row of the materialized view, the flag indicating whether the row of the materialized view is visible to the transaction.

19. The apparatus of claim 18 , wherein the identifier is different from an identifier the transaction uses to obtain data from the materialized view.

Assignees

• Kharatishvili Tengiz
• Xiao Wei
• Stefani Stefano
• Microsoft Technology Licensing Llc

Inventors

• Kharatishvili Tengiz
• Xiao Wei
• Stefani Stefano

Classifications

• G06F17/30457 Primary

  Physics · mapped topic

• G06F16/24539 Primary

  using cached or materialised query results · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/2365

  Ensuring data consistency and integrity · CPC title

• G06F16/2455

  Query execution · CPC title