Systems and methods for avoiding database anomalies when maintaining constraints and indexes in presence of snapshot isolation

• US 7,653,665 B1
• Filed: 09/13/2004
• Issued: 01/26/2010
• Est. Priority Date: 09/13/2004
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Xiao, Wei, Stefani, Stefano
• Primary Examiner(s): Campbell; Joshua D
• Assistant Examiner(s): Parker; Brandon
• Application Number:
  US10/940,891
• Time in Patent Office: 1,961 Days
• Field of Search: 707/203, 707/201, 707/3, 707/5, 707/202, 709/224, 709/203, 717/144, 714/19
• US Class Current: 714/19
• CPC Class Codes: G06F 16/2343 Locking methods, e.g. distributed locking or locking implementation details
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity

Abstract

A system and method avoids anomalies in presence of data manipulation language (DML) plans maintaining dependent objects and snapshot isolation. An anomaly due to using a snapshot isolation level within the transaction is detected within a database transaction and the transaction is aborted based upon that detection. Involved in making the anomaly detection is determining whether a view of particular data accessed during the transaction under a snapshot isolation view to ensure read consistency differs from a view of the data under another isolation level, such as the read committed isolation level. Then a detection is made that an anomaly may occur if it is determined that the view of the data accessed during the transaction under the snapshot isolation differs from the view of the data under the other isolation level. Such anomaly avoidance prevents an indexed view being maintained nor a referential integrity constraint validated based on incorrect data.

Claims

1. A computer-implemented method for determining whether to abort a database transaction, said transaction occurring in a snapshot isolation level, the method comprising:

the computer creating a first database state comprising a virtual snapshot of the database upon initiation of the transaction;

the computer processing the transaction using the first database state and concurrently processing one or more other transactions using the first database state;

the computer determining whether an auxiliary table scan of the first database state is required to process said transaction;

when an auxiliary table scan is required as part of a data modification query to ensure that logical data consistency is preserved, the computer reading said auxiliary table in a first snapshot view associated with said snapshot isolation level, said first snapshot view comprising committed data values existing in the first database state;

the computer comparing at least one of said committed data values to at least one data value in a second snapshot view, said second snapshot view comprising a latest committed version of said auxiliary table;

the computer adding a virtual column to each of one or more data items of the latest committed version of said auxiliary table, the virtual column containing a value indicating whether the data item of the latest committed version of said auxiliary table differs from a corresponding data item of the committed data values;

the computer propagating the virtual column and the second snapshot view through the transaction to an operator responsible for indexed view maintenance or referential integrity constraint validation;

the computer checking the virtual column of each data item of the latest committed version of said auxiliary table to determine if the virtual column value indicates that the data item of the latest committed version of said auxiliary table differs from a corresponding data item of the committed data values; and

the computer aborting said transaction if the virtual column value of any data item of the latest committed version of said auxiliary table indicates that the data item of the latest committed version of said auxiliary table differs from a corresponding data item of the committed data values.

2. The method of claim 1 wherein said aborting the transaction comprises foregoing maintaining an indexed view.

3. The method of claim 1 wherein said second snapshot view is generated using an isolation level selected from a group comprising:

read committed isolation level, repeatable read isolation level and serializable isolation level, wherein each isolation level in the group is implemented using locking.

4. The method of claim 1 wherein the auxiliary table scan is associated with implicitly performing an update action as a result of executing the transaction.

5. The method of claim 1 wherein the auxiliary table scan is associated with maintaining an indexed view.

6. The method of claim 1 wherein the auxiliary table scan is associated with verifying a referential integrity constraint.

7. A computer readable storage medium having stored thereon a plurality of computer-executable instructions that when processed by a processor enable said processor to detect an inconsistency in a database transaction, said computer-executable instructions causing said processor to perform the steps comprising:

creating a first database state comprising a virtual snapshot of the database upon initiation of the transaction;

processing the transaction using the first database state and concurrently processing one or more other transactions using the first database state;

determining whether an auxiliary table scan of a first database state comprising a snapshot of the database at the time of the transaction is required to process said transaction;

when an auxiliary table scan is required as part of a data modification query to ensure that logical data consistency is preserved, reading said auxiliary table in a first snapshot view associated with said snapshot isolation level, said first snapshot view comprising committed data values existing in the first database state;

comparing at least one of said committed data values to at least one data value in a second snapshot view, said second snapshot view comprising a latest committed version of said auxiliary table;

adding a virtual column to each of one or more data items of the latest committed version of said auxiliary table, the virtual column containing a value indicating whether the data item of the latest committed version of said auxiliary table differs from a corresponding data item of the committed data values;

propagating the virtual column and the second snapshot view through the transaction to an operator responsible for indexed view maintenance or referential integrity constraint validation;

checking the virtual column of each data item of the latest committed version of said auxiliary table to determine if the virtual column value indicates that the data item of the latest committed version of said auxiliary table differs from a corresponding data item of the committed data values; and

aborting said transaction if the virtual column value of any data item of the latest committed version of said auxiliary table indicates that the data item of the latest committed version of said auxiliary table differs from a corresponding data item of the committed data values.

8. The computer storage medium of claim 7 wherein the computer-executable instructions are such that neither an indexed view is maintained nor a referential integrity constraint validated when said transaction is aborted.

9. The computer storage medium of claim 7 further comprising computer-executable instructions for generating said second snapshot view using an isolation level.

10. The computer storage medium of claim 9 wherein said second snapshot view isolation level is a read committed isolation level.