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 application of asynchronous replication transactions in a database system, the method comprising:
- (a) receiving and examining a transaction message comprising one or more row changes;
(b) determining whether at least one row change in the transaction message is affected by a data constraint;
(c) responsive to at least one row change in the transaction message being affected by a data constraint, determining whether there is a constraint violation between the at least one row change in the transaction message and a row change in at least one preceding non-completed transaction message;
(d) responsive to there being a constraint violation between the at least one row change in the transaction message and the row change in the at least one preceding non-completed transaction message, holding the transaction message until application of the at least one preceding non-completed transaction message is completed;
(e) responsive to application of the at least one preceding non-completed transaction message being completed, placing the transaction message on a work queue; and
(f) subsequent to placing the transaction message on the work queue, applying the transaction message in parallel with one or more other transaction messages on the work queue.
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
61 Claims
-
1. A method for preserving data constraints during parallel application of asynchronous replication transactions in a database system, the method comprising:
-
(a) receiving and examining a transaction message comprising one or more row changes; (b) determining whether at least one row change in the transaction message is affected by a data constraint; (c) responsive to at least one row change in the transaction message being affected by a data constraint, determining whether there is a constraint violation between the at least one row change in the transaction message and a row change in at least one preceding non-completed transaction message; (d) responsive to there being a constraint violation between the at least one row change in the transaction message and the row change in the at least one preceding non-completed transaction message, holding the transaction message until application of the at least one preceding non-completed transaction message is completed; (e) responsive to application of the at least one preceding non-completed transaction message being completed, placing the transaction message on a work queue; and (f) subsequent to placing the transaction message on the work queue, applying the transaction message in parallel with one or more other transaction messages on the work queue. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
-
-
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 encoded with a computer program for preserving data constraints during parallel application of asynchronous replication transactions in a database system, the computer program comprising instructions for:
-
(a) receiving and examining a transaction message comprising one or more row changes; (b) determining whether at least one row change in the transaction message is affected by a data constraint; (c) responsive to at least one row change in the transaction message being affected by a data constraint, determining whether there is a constraint violation between the at least one row change in the transaction message and a row change in at least one preceding non-completed transaction message; (d) responsive to there being a constraint violation between the at least one row change in the transaction message and the row change in the at least one preceding non-completed transaction message, holding the transaction message until application of the at least one preceding non-completed transaction message is completed; (e) responsive to application of the at least one preceding non-completed transaction message being completed, placing the transaction message on the work queue; and (f) subsequent to placing the transaction message on the work queue, applying the transaction message in parallel with one or more other transaction messages on the work queue. - View Dependent Claims (32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47)
-
-
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 work queue; a receive queue storing a transaction message, the transaction message comprising one or more row changes; a browser thread in communication with the receive queue and the work queue, wherein the browser thread examines the transaction message on the receive queue, determines whether at least one row change in the transaction message is affected by a data constraint, responsive to at least one row change in the transaction message being affected by a data constraint, determines whether there is a constraint violation between the at least one row change in the transaction message and a row change in at least one preceding non-completed transaction message, responsive to there being a constraint violation between the at least one row change in the transaction message and the row change in the at least one preceding non-completed transaction message, holds the transaction message until application of the at least one preceding non-completed transaction message is completed, and responsive to application of the at least one preceding non-completed transaction message being completed, places the transaction message on the work queue; and one or more agent threads in communication with the work queue, wherein subsequent to placement of the transaction message on the work queue, the one or more agent threads apply the transaction message in parallel with one or more other transaction messages on the work queue.
-
Specification