Replicating large statements with low latency

Patent metadata

Field	Value
Publication number	US-11308119-B2
Application number	US-201816208538-A
Country	US
Kind code	B2
Filing date	Dec 3, 2018
Priority date	Dec 3, 2018
Publication date	Apr 19, 2022
Grant date	Apr 19, 2022

Abstract

A method, system and computer program product is provided. The method includes identifying a large statement, writing the rows that are changed by the large statement into a set of files outside a database management system recovery log, and writing log records for the set of files to the database management system recovery log, such that the log records provide ordering of row changes made by the large statement. The log records include a log sequence number corresponding to a first row changed and a format and encoding used for the row changes. The method further includes streaming, by the one or more processors of the computer system, the set of files out-of-band by a replication capture process to a target database management system.

First claim

What is claimed is:

1. A computer-implemented method comprising:
when a large statement is a single statement that is larger than a threshold, receiving, by one or more processors of a computer system, the large statement as a set of files by a streamed transaction in an out-of-band transmission queue, each file of the set of files including at least one row that is changed, the set of files originating from the large statement that is at least 10 megabytes in size received at a source database management system that has identified the large statement is the single statement that is larger than the threshold;
continuously applying, by the one or more processors of the computer system, changes in the set of files as they are received via the out-of-band transmission queue;
when an additional transaction is a single statement that is smaller than a threshold, receiving, by the one or more processors of the computer system during the continuously applying changes in the set of files as they are received via the out-of-band transmission queue, the additional transaction via an inline transmission queue from the source database management system that has identified that the additional transaction is smaller than the threshold; and
applying, by the one or more processors of the computer system, the additional transaction received via the inline transmission queue as the additional transaction is received during receiving the set of files by the streamed transaction or the continuously applying; and
after the continuously applying, applying, by the one or more processors of the computer system, a commit or a rollback after receiving a large statement completion message by the source database management system, the large statement completion message reflecting an outcome at the source database management system.

2. The method of claim 1 , wherein the additional transaction has already been committed by the source database management system.

3. The method of claim 2 , further comprising:
receiving, by the one or more processors of the computer system, a message that marks the beginning of the streamed transaction via the inline transmission queue;
marking, by the one or more processors of the computer system, the streamed transaction as being streamed.

4. The method of claim 3 , further comprising:
receiving, by the one or more processors of the computer system, a first log sequence number associated with the streamed transaction with the message that marks the beginning of the streamed transaction;
receiving, by the one or more processors of the computer system, a second log sequence number associated with the additional transaction;
determining, by the one or more processors of the computer system, that the additional transaction modifies at least one table also modified by the large statement;
determining, by the one or more processors of the computer system, that the second log sequence number associated with the additional transaction comes after the first log sequence number associated with the streamed transaction;
waiting, by the one or more processors of the computer system, to start the continuous applying until after the determining that the second log sequence number associated with the additional transaction is after the first log sequence number associated with the streamed transaction.

5. The method of claim 2 , wherein rows from a statement in any transaction sent via either the inline or out-of-band transmission queue are not applied by the target database management system until all commit or abort source transactions pertaining to the same row or table as the statement and having an log sequence number less than an log sequence number of the statement have been committed or aborted at the target database management system.

6. The method of claim 1 , further comprising:
using, by the one or more processors of the computer system, a dedicated streaming agent associated with the streamed transaction;
ensuring, by the dedicated streaming agent, order is respected associated with the streamed transaction using coordination messages that are transmitted over the inline transmission queue; and
enabling, by the dedicated streaming agent, the streamed transaction to be applied under a single commit scope.

7. The method of claim 1 , further comprising:
conducting, by the one or more processors of the computer system, a dependency analysis at a table-level for the streamed transaction.

8. The method of claim 1 , further comprising:
holding back committing, by the one or more processors of the computer system, a transaction that is committing after the commit or the rollback of the streamed transaction until after the commit or the rollback of the streamed transaction.

9. The method of claim 1 , further comprising:
applying, by the one or more processors of the computer system, transactions that are committed at the source database management system while the streamed transaction is being applied in parallel at the target database management system; and
applying, by the one or more processors of the computer system, any subsequent files from the set of files after the transactions that are committed at the source database management system are applied and committed by the target database management system.

10. The method of claim 1 , further comprising:
receiving, by one or more processors of a computer system, a second set of files associated with a second streamed transaction in the out-of-band transmission queue, each file of the second set of files including at least one row that is changed, the set of files originating from a second large statement received at a source database management system;
identifying, by the one or more processors of the computer system, transactions in the second set of files that are insert-only;
applying, by the one or more processors of the computer system, the transactions identified as insert-only in parallel to the continuously applying the changes in the set of files from the large statement.

11. The method of claim 1 , further comprising:
conducting, by the one or more processors of the computer system, a dependency analysis at a table-level if the streamed transaction is determined to be larger than a configurable threshold; and
conducting, by the one or more processors of the computer system, the dependency analysis at a row-level if the streamed transaction is determined to be smaller than the configurable threshold; and
holding back operations of another transaction, by the one or more processors of the computer system, as a result of the dependency analysis.

12. The method of claim 1 , further comprising:
scheduling to be applied, by the one or more processors of the computer system, held back operations as a result of the dependency analysis; and
applying in parallel to the streamed transaction, by the one or more processors of the computer system, operations of the other transaction determined to not have dependencies as a result of the dependency analysis.

13. The method of claim 1 , wherein the large statement completion message is either a commit instruction or a rollback instruction.

14. The method of claim 1 , further comprising:
transmitting the large statement to the target database management system and applying the large statement at the target database management system in parallel to execution at the source database management system without impacting the parallelism of applying other transactions between the source database management system and the target database management system;
achieving a replication latency of less than a few seconds whil

Assignees

• IBM

Inventors

• Bourbonnais Serge
• D'Costa Austin
• Zheng Roger
• Zentgraf Christian
• Mooney David

Classifications

• G06F16/24532

  of parallel queries · CPC title

• G06F16/27 Primary

  Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor · CPC title

• G06F16/2379 Primary

  Updates performed during online database operations; commit processing · CPC title

• G06F16/24568

  Data stream processing; Continuous queries · CPC title