Techniques to preserve data constraints and referential integrity in asynchronous transactional replication of relational tables
First Claim
1. A method for preserving data constraints during parallel apply in asynchronous transaction replication in a database system, comprising:
- (a) receiving and examining a transaction message;
(b) determining if at least one row change in the transaction message has data constraints;
(c) if so, determining if the at least one row change in the transaction message has a constraint violation with a row change in at least one preceding non-completed transaction message; and
(d) if so, holding the transaction message until the at least one preceding non-completed transaction message completes.
1 Assignment
0 Petitions
Accused Products
Abstract
An improved method and system for preserving data constraints during parallel apply in asynchronous transaction replication in a database system have been disclosed. The method and system preserves secondary unique constraints and referential integrity constraints, while also allowing a high degree of parallelism in the application of asynchronous replication transactions. The method and system also detects and resolves ordering problems introduced by referential integrity cascade deletes, and allows the parallel initial loading of parent and child tables of a referential integrity constraint.
-
Citations
63 Claims
-
1. A method for preserving data constraints during parallel apply in asynchronous transaction replication in a database system, comprising:
-
(a) receiving and examining a transaction message;
(b) determining if at least one row change in the transaction message has data constraints;
(c) if so, determining if the at least one row change in the transaction message has a constraint violation with a row change in at least one preceding non-completed transaction message; and
(d) if so, holding the transaction message until the at least one preceding non-completed transaction message completes. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 62)
-
-
18. A method for preserving data constraints during parallel apply in asynchronous transaction replication in a database system, comprising:
-
(a) identifying a transaction message as a cascade delete;
(b) determining that a source of the transaction message is not a leaf table;
(c) placing each subscription for the transaction message onto a stack and placing row operations for each subscription into a reorder list, wherein the subscriptions are placed onto the stack in order of execution, wherein the row operations are placed into the reorder list in the order of execution; and
(d) adding the row operations for each subscription in the stack back to the transaction message, wherein the row operations are added in a reverse order of execution, wherein the subscriptions are added in the reverse order of execution. - View Dependent Claims (19, 20)
-
-
21. A method for preserving data constraints during parallel apply in asynchronous transaction replication in a database system, comprising:
-
(a) receiving a message to perform an initial load of a target table;
(b) determining that the target table is a child table of referential integrity constraints;
(c) saving the referential integrity constraints for the target table;
(d) dropping the referential integrity constraints from the target table;
(e) loading the target table in parallel with a loading of a parent table of the referential integrity constraints;
(f) begin applying change data to the target table once loading is done;
(g) waiting for the parent table to finish loading, if the parent table has not yet finished loading; and
(h) adding the referential integrity constraints back into the target table. - View Dependent Claims (22, 23, 24, 25)
-
-
26. A method for preserving data constraints during parallel apply in asynchronous transaction replication in a database system, comprising:
-
(a) receiving a message to perform an initial load of a target table;
(b) determining that the target table is a parent table of referential integrity constraints;
(c) saving the referential integrity constraints for a child table of the target table;
(d) dropping the referential integrity constraints from the child table;
(e) loading the target table in parallel with a loading of the child table;
(f) begin applying change data to the target table once loading is done;
(g) waiting for the child table to finish loading, if the child table has not yet finished loading; and
(h) adding the referential integrity constraints back into the child table. - View Dependent Claims (27, 28, 29, 30)
-
-
31. A computer readable medium with program instructions for preserving data constraints during parallel apply in asynchronous transaction replication in a database system, comprising:
-
(a) receiving and examining a transaction message;
(b) determining if at least one row change in the transaction message has data constraints;
(c) if so determining if the at least one row change in the transaction message has a constraint violation with at least one preceding non-completed transaction; and
(d) if so, holding the transaction message until the at least one preceding non-completed transaction message completes. - View Dependent Claims (32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 63)
-
-
48. A computer readable medium with program instructions for preserving data constraints during parallel apply in asynchronous transaction replication in a database system, comprising:
-
(a) identifying a transaction message as a cascade delete;
(b) determining that a source of the transaction message is not a leaf table;
(c) placing each subscription for the transaction message onto a stack and placing row operations for each subscription into a reorder list, wherein the subscriptions are placed onto the stack in order of execution, wherein the row operations are placed into the reorder list in the order of execution; and
(d) adding the row operations for each subscription in the stack back to the transaction message, wherein the row operations are added in a reverse order of execution, wherein the subscriptions are added in the reverse order of execution. - View Dependent Claims (49, 50)
-
-
51. A computer readable medium with program instructions for preserving data constraints during parallel apply in asynchronous transaction replication in a database system, comprising:
-
(a) receiving a message to perform an initial load of a target table;
(b) determining that the target table is a child table of referential integrity constraints;
(c) saving the referential integrity constraints for the target table;
(d) dropping the referential integrity constraints from the target table;
(e) loading the target table in parallel with a loading of a parent table of the referential integrity constraints;
(f) begin applying change data to the target table once loading is done;
(g) waiting for the parent table to finish loading, if the parent table has not yet finished loading; and
(h) adding the referential integrity constraints back into the target table. - View Dependent Claims (52, 53, 54, 55)
-
-
56. A computer readable medium with program instructions for preserving data constraints during parallel apply in asynchronous transaction replication in a database system, comprising:
-
(a) receiving a message to perform an initial load of a target table;
(b) determining that the target table is a parent table of referential integrity constraints;
(c) saving the referential integrity constraints for a child table of the target table;
(d) dropping the referential integrity constraints from the child table;
(e) loading the target table in parallel with a loading of the child table;
(f) begin applying change data to the target table once loading is done;
(g) waiting for the child table to finish loading, if the child table has not yet finished loading; and
(h) adding the referential integrity constraints back into the child table. - View Dependent Claims (57, 58, 59, 60)
-
-
61. A system, comprising:
-
a source node, wherein the source node sends a transaction message concerning a committed transaction completed at a source table copy to a target node to asynchronously replicate the transaction; and
the target node, wherein the target node comprises a receive queue, a browser thread, a work queue, and a target table copy, wherein the browser thread receives and examines the transaction message on the receive queue, wherein the browser thread determines if at least one row change in the transaction message has data constraints, wherein if so, the browser thread determines if the at least one row change in the transaction message has a constraint violation with a row change in at least one preceding non-completed transaction message, and wherein if so, the browser thread holds the transaction until the at least one preceding non-completed transaction message completes.
-
Specification