Reducing update conflicts when maintaining views
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.
1. A computer storage medium having computer-executable instructions, which when executed perform actions, comprising:
within a transaction, making an update to a table in response to a first data modification that occurs after a beginning of the transaction and a second data modification committed by another transaction after the beginning of the transaction, the transaction having a view of data that existed as of the beginning of the transaction together with any changes the transaction has made to the data after the beginning of the transaction, the update occurring before the transaction completes; and
before the transaction completes, without aborting the transaction, excluding the update from the transaction such that a query of the table 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 as of the beginning of the transaction.
2. The computer storage medium of claim 1 , wherein the table 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 (i) after the another transaction commits the second data modification to a data structure affecting the table but (ii) before the transaction completes.
5. The computer storage medium of claim 1 , wherein excluding the update from the transaction comprises marking the update with a flag that indicates that the update is invisible to the transaction.
6. The computer storage medium of claim 1 , wherein excluding the update from the transaction comprises associating the update with a transaction identifier other than a transaction identifier with which updates made within the transaction are obtained.
7. A method implemented at least in part by a computer, the method comprising:
within a transaction, updating a table as a result of (i) the transaction and (ii) another transaction committed after a beginning of the transaction, the transaction having a view of data that existed as of the beginning of the transaction as modified by a change the transaction has made after the beginning of the transaction;
within the transaction, querying the table; and
in response to the querying, before the transaction completes, without aborting the transaction, excluding the update from the transaction thus returning a result that does not include information associated with the update to the table made by the transaction.
8. The method of claim 7 , wherein the table comprises a result of a query of data, the result being stored for subsequent use.
9. The method of claim 8 , wherein the table is updated incrementally as data that would affect the result of the query if performed again is updated, inserted, or deleted.
10. The method of claim 7 , wherein returning a result that does not include information associated with the update comprises excluding data from a row of the table from the result, the row including additional data that indicates that the update is invisible to the transaction.
11. The method of claim 7 , wherein returning a result that does not include information associated with the update comprises excluding data from a row of the table from the result, the row associated with additional data that indicates that the update is invisible to the transaction.
12. The method of claim 7 , wherein returning a result that does not include information associated with the update comprises excluding data from a row of the table 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 be used to retrieve data from the table.
13. The method of claim 7 , wherein querying the table comprises sending a query to a database management system, the query instructing the database management system to return data associated with the table that meets criteria specified in the query.
14. The method of claim 13 , wherein the query instructs the database management system to join data with the table to obtain the result.
15. The method of claim 7 , further determining whether the transaction is able to view the update by consulting additional information associated with the update, the additional information indicating that the update is unavailable to the transaction.
16. 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 isolation to transactions, the isolation providing a view to each transaction of data that existed at a time said transaction began;
a table updater operable to update a table to include an indication that a transaction in which the table 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 both (i) the transaction and (ii) another transaction committed after a beginning of the transaction; and
a query engine operable to obtain data from the table 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 table within the transaction does not return the update made by the transaction.
17. The apparatus of claim 16 , wherein the indication comprises a flag in additional data associated with a row of the table, the flag indicating whether the row of the table is visible to the transaction.
18. The apparatus of claim 16 , wherein the identifier is different from an identifier the transaction uses to obtain data from the table.
19. The apparatus of claim 16 , wherein the table comprises a result of a query of data, the result being stored for subsequent use.
20. The apparatus of claim 19 , wherein the table is updated incrementally as data that would affect the result of the query if performed again is updated, inserted, or deleted.