Systems and methods for avoiding database anomalies when maintaining constraints and indexes in presence of snapshot isolation
First Claim
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 Assignments
0 Petitions
Accused Products
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.
-
Citations
10 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. - View Dependent Claims (2, 3, 4, 5, 6)
-
-
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. - View Dependent Claims (8, 9, 10)
-
Specification