CN113282600A - Batch primary key updating processing method and system under Oracle database synchronization environment - Google Patents

Abstract

The invention discloses a batch primary key updating processing method and a system under an Oracle database synchronization environment, wherein the method comprises the following steps: step S1, when the source end executes the batch primary key updating operation, the backup end analyzes the batch primary key updating operation into a URP sequence according to the source end; step S2, performing normal URP operation processing on the first URP operation of the URP sequence; step S3, sequentially execute the next URP operation, query the conflict history with the source key value in the URP operation, change the update operation into the insert operation when the record is found and the source Rowid of the record is not consistent with the source Rowid of the current URP operation, and execute the normal URP operation processing when the record is found and the source Rowid of the record is consistent with the source Rowid of the current URP operation.

Description

Batch primary key updating processing method and system under Oracle database synchronization environment

Technical Field

The invention relates to the technical field of Oracle database synchronization, in particular to a batch primary key updating processing method and system under an Oracle database synchronization environment.

Background

The Oracle database is computer software widely used, and database synchronization software is often deployed in a production environment to meet requirements of disaster tolerance, read-write separation and the like, so that a source library is synchronized to a target library in real time. The data of the source library changes in real time, and the target library is updated in real time. It should be noted that, for convenience of describing the processing procedure, the following description labels the modification, deletion and insertion operations of the record as urp (update Row piece), drp (delete Row piece) and irp (insert Row piece), respectively.

When the current Oracle database synchronization software modifies the primary key, the following situations often occur:

1) at the production side, i.e. the source library, when table tab1 is created by creating table statement create table tab1(key1 int primary key, age number, name varchar2(10)), assuming that fifteen pieces of data are inserted into the current table, the records meeting the where condition are updated simultaneously using 'Update tab1 set key1 ═ key1+1 where key1> 10' similar statement, because the source library is executed once, no problem is caused, but at the target library, because the Oracle synchronization software grabs multiple rows of data changes in the redo data, but cannot resolve the where condition in the original data, the where condition cannot be pieced together into one statement, but the sql statement parsed into one row is executed, which may cause the conflict of the primary keys loaded at the target side. For example: when the first line of statements analyzed by the backup execution modifies the record value 11 of the primary key1 field to 12, because the record with the key1 value of 12 already exists in the original record of the backup, a primary key conflict will be generated, at this time, although the delay constraint option of the backup table can be opened to enable the first statement to be executed smoothly, the subsequent update statement will not locate the correct record under the condition that the first record has generated the repeated key value, technically, although the prior art can solve the problem temporarily through the full field index + delay constraint loading, this premise is to open the full field additional log and the delay constraint, but firstly opening the full field additional log will greatly reduce the performance in production, the full field additional log is unlikely to be opened in production, secondly, there is a conflict when the transaction is finished during the open delay constraint loading, the whole transaction can be rolled back, for example, the standby database is changed, the cost is very high, and the processing is not easy;

2) more complex cases: assuming that the user sets the delay constraint load for the source library at the production end, the primary key content can be changed in one transaction at will, as long as the transaction is not conflicted when submitted, in this case, the standby library also opens the delay constraint, but the primary key mapping still faces the above problems.

For example, the following steps are carried out: table tab1 is built according to table building statement create table tab1(key1 int primary key, age number, name varchar2(10)), and two pieces of data are now inserted into the tab1 table:

Rowid	key1 int	age number	Name varchar2(10)
				r1 (example 1)	1	13	a
R2 (example 2)	2	13	b

As shown in the above example, now there are two records in the source library, corresponding rowids are R1 and R2, and original primary key1 has values of 1 and 2, when an application program wants to modify the value 1 of the first record key1 to 2, and modify the corresponding field value 13 to 14 by using record R1, and then modify the value 2 of the record key1 to 3 by using record R2, this modification will not cause problems, but in the target library, the Oracle synchronization software will parse out three URP statements, which will pass through successfully when executing the first URP statement, but will generate the same key value as that in the record when executing the second URP statement, because the first URP record has generated duplicate key values, the later update statement will not locate the correct record.

3) In the third case: for the partition table with the main key or the unique key as the partition condition, the row move switch is turned on, that is, the row move is set to enable. When table tab2 is created by a table building statement and partitioned with primary key1, for example:

create table tab2(key1 number,b number)partition by range(key1)

(partition p1 values less than(10),

partition p2 values less than(20),

partition pmax values less than(maxvalues))enable row movement；

alter table tab2 add constraint PK_R primary key(key1)；

now two pieces of data are inserted into table tab 2:

	key1 number	b number
			example 1	1	13
Example 2	2	13

For Oracle synchronization software, this table structure modification primary key would result in two DMLs (Data management Language) for DRP + IRP, instead of 1 URP, for example: update tab2 set key1 ═ key1+1 where key1 ═ 1.

In addition, this is also the case when the IOT (Index Organized Table) Table modifies the primary key. Therefore, the primary key conflict is not only considered in handling URP, but also includes IRP and DRP.

Disclosure of Invention

In order to overcome the defects in the prior art, the present invention provides a batch primary key update processing method and system in an Oracle database synchronization environment, so as to solve the problem that batch primary key update cannot be performed due to target-end primary key conflict caused by batch primary key modification for a source end in the Oracle database synchronization environment.

In order to achieve the above object, the present invention provides a batch primary key update processing method in an Oracle database synchronization environment, comprising the following steps:

step S1, when the source end executes the batch primary key updating operation, the backup end analyzes the batch primary key updating operation into a URP sequence according to the source end;

step S2, performing normal URP operation processing on the first URP operation of the URP sequence;

step S3, sequentially execute the next URP operation, query the conflict history with the source key value in the URP operation, change the update operation into the insert operation when the record is found and the source Rowid of the record is not consistent with the source Rowid of the current URP operation, and execute the normal URP operation processing when the record is found and the source Rowid of the record is consistent with the source Rowid of the current URP operation.

Preferably, the processing procedure of the normal URP operation is as follows: and judging whether a conflict is generated according to the current URP operation, if so, reading a record corresponding to the target update key value in the standby end table, storing the record into a conflict history, deleting the record in the standby end table, and then executing the current URP operation.

Preferably, in step S2, the first URP operation in the URP sequence is obtained, the target update key is obtained, whether a conflict occurs is determined in the backup table according to the target update key, if a conflict occurs, the corresponding record in the backup table is read, the target update key is used as the key to update the record into the conflict history, the record is deleted in the backup table, and finally the URP operation is executed to return to the source-backup Rowid, and the source-backup Rowid is recorded in the record in the conflict history.

Preferably, the step S3 further includes:

step S300, inquiring conflict history by using a source key value in the current URP operation, entering step S301 when a record is inquired and the source Rowid of the record is inconsistent with the source Rowid of the URP, or processing the record according to the common URP if the record is consistent with the source Rowid of the URP;

step S301, changing the update operation into an insert operation, and merging the target update key value of the URP into the conflict history;

step S302, executing the changed inserting operation, judging whether a conflict exists, if so, processing the conflict according to a common URP operation processing method and then executing the inserting operation;

step S303, after a certain cache record of the conflict history is inserted successfully, deleting the corresponding cache in the conflict history.

Preferably, in step S302, if there is a conflict, the record corresponding to the IRP-inserted operation key value in the backup table is saved in the conflict history, the record is deleted in the backup table, and then the inserting operation is executed, and the source backup Rowid is recorded in the record corresponding to the conflict history.

Preferably, the method comprises the steps of:

step S4, when updating the IOT table and the table with the primary key as the partition condition in batch, generating a DRP and IRP operation for each URP operation of the source end;

step S5, the DRP operation and the IRP operation are sequentially executed.

Preferably, in step S5, when performing the DRP operation, querying the conflict history with the primary key value of the current DRP operation, and if there is no record or there is a record but the source Rowid of the current DRP operation is not consistent with the source Rowid of the corresponding key value record in the conflict history, directly deleting the record corresponding to the current primary key value in the backup table; and if the conflict history has records and the source Rowid of the current DRP operation is consistent with the source Rowid of the corresponding key value record in the conflict history, deleting the record corresponding to the key value in the conflict history and simultaneously deleting the corresponding record in the standby table.

Preferably, in step S5, when the IRP operation is performed, an attempt is made to insert the record, if there is a conflict, the normal URP operation is performed, if there is no conflict, the insertion is successful, and the Rowid is returned.

In order to achieve the above object, the present invention further provides a batch primary key update processing system in an Oracle database synchronization environment, including:

the batch updating operation analyzing unit is used for analyzing the batch updating operation of the source end into a URP sequence according to the batch updating operation of the source end when the source end executes the batch updating operation of the master key under the synchronous environment;

a URP operation sequential execution unit, configured to execute the URP operations in the URP sequence in sequence, and execute a normal URP operation process on a first URP operation, when a conflict occurs, store a record corresponding to a target update key of the URP operation in a conflict history, and execute a current URP operation after deleting the record in a backup table; and then executing the next URP operation, inquiring the conflict history by using the source key value in the URP operation, changing the updating operation into an inserting operation when a record is inquired and the source Rowid of the record is not consistent with the source Rowid of the current URP operation, and executing the ordinary URP operation processing when the record is inquired and the source Rowid of the record is consistent with the source Rowid of the current URP operation.

Preferably, the system further comprises:

the special analysis processing unit is used for analyzing each URP operation at the source end to generate a DRP and IRP operation when batch primary key updating is carried out on the IOT table and the table taking the primary key as the partition condition;

a DRP operation execution unit, configured to execute a DRP operation, query the conflict history with the primary key value of the current DRP operation, and directly delete the record corresponding to the current primary key value in the standby table if there is no record or there is a record but the source Rowid of the current DRP operation is inconsistent with the source Rowid of the corresponding key value record in the conflict history; if a record exists in the conflict history and the source Rowid of the current DRP operation is consistent with the source Rowid of the corresponding key value record in the conflict history, deleting the record corresponding to the key value in the conflict history and deleting the corresponding record in the standby terminal table;

and the IRP operation execution unit is used for executing the IRP operation, trying to insert the record and returning to Rowid, if conflict exists, processing according to a conflict processing mode of the common URP operation, and if no conflict exists, inserting successfully.

Compared with the prior art, the batch primary key updating processing method and system in the Oracle database synchronization environment solve the problem that batch primary key updating cannot be performed due to target end primary key conflict caused by batch modification of primary keys for source ends in the Oracle database synchronization environment.

Drawings

FIG. 1 is a flowchart illustrating steps of a batch primary key update processing method in an Oracle database synchronization environment according to the present invention;

FIG. 2 is an architecture diagram of a batch primary key update processing system under an Oracle database synchronization environment according to the present invention.

Detailed Description

Other advantages and capabilities of the present invention will be readily apparent to those skilled in the art from the present disclosure by describing the embodiments of the present invention with specific embodiments thereof in conjunction with the accompanying drawings. The invention is capable of other and different embodiments and its several details are capable of modification in various other respects, all without departing from the spirit and scope of the present invention.

FIG. 1 is a flowchart illustrating steps of a batch primary key update processing method in an Oracle database synchronization environment according to the present invention. As shown in fig. 1, the method for batch update processing of the master key in the Oracle database synchronization environment of the present invention is applied to the backup side, and includes the following steps:

step S1, when the source executes the batch primary key update operation, the backup parses the batch primary key update operation into the URP sequence according to the source.

In the invention, the batch of batch primary key updating refers to the batch primary key of the source database, and the invention mainly relates to a processing process of the backup end when the batch primary key of the source database is updated. Under the synchronous environment, a source library (a source database) is synchronized to a standby target library in real time. After the source end operates, the backup end target library is analyzed into a URP sequence according to the operation of the source end.

Specifically, when the source peer:

create table tab1(key1 int primary key,age number,name varchar2(10))；

get table tab1 and insert two pieces of data into tab1 table:

table tab1

Rowid	key1 int	age number	Name varchar2(10)
				R1 (example 1)	1	13	a
R2 (example 2)	2	13	b

That is, there are now two records at the source end, corresponding rowid to R1 and R2, and original master key1 having values of 1 and 2.

When the source end executes a URP operation, namely two records of example 1 and example 2 in the table of Update tab1 in batches of Update tab1 set key1 ═ key1+1 where key1>0, at the backup end, the source end resolves to the following URP sequence according to the batch primary key Update operation of the source end:

URP 1->2

(update tab1 set key1＝2 where key1＝1)

URP 2->3

(update tab1 set key1＝3 where key1＝2)。

in step S2, normal URP operation processing is performed on the first URP operation. The ordinary URP operation processing specifically comprises: and judging whether a conflict is generated in the standby end table according to the target update key value of the current URP operation, if so, reading a record corresponding to the target update key value in the standby end table, storing the record into a conflict history, deleting the record in the standby end table, and then executing the current URP operation. Specifically, in step S2, the first URP operation in the URP sequence is obtained and executed, and when a conflict occurs, for the current URP operation, the record corresponding to the target update key value of the current URP operation is read and stored in the conflict history, and the current URP operation is executed after the record is deleted in the current table.

In the embodiment of the present invention, it is assumed that the table tab1 in the backup target library is:

Rowid	key1 int	age number	Name varchar2(10)
				r. 1 (example 1)	1	13	a
R. sub.2 (example 2)	2	13	b

The first URP operation in the URP sequence is sequentially acquired, first URP operation URP1- >2 is acquired, whether conflict occurs is judged, because the target update key value is 2, and records with key values of 2 exist in the backup table, whether conflict occurs is judged (actually, the oracle database reports the error of the primary key conflict, the bottom layer inquires whether records with key values of 2 exist in the backup table, so the invention can directly judge whether conflict exists), so the records with primary keys of 2 are read, records with 2 as key values are stored in the conflict history, then records with primary keys of 2 are deleted in tab1, finally, the URP operation URP1- >2 is executed, backup end Rowid (namely, Rowid R backup 1) is returned, and source backup Rowid is recorded in the conflict history, namely, Rowid R1 of the source library and Rowid R backup 1 of the backup library, in the invention, the source Rowid refers to the key value record of the source library corresponding to the current URP operation source, i.e., R1 in this embodiment, is sent from the source during synchronization.

Step S3, sequentially execute the next URP operation, query the conflict history with the source key value in the URP operation, if a record is queried in the conflict history and the source Rowid of the record is inconsistent with the source Rowid of the current URP operation, change the update operation into an insert operation, and when a record is queried and the source Rowid of the record is consistent with the source Rowid of the current URP operation, execute the normal URP operation process. Because the invention considers the batch main key updating condition, namely all records of the where condition are changed once, as long as the record is not the last record in the table, the conflict exists, the record exists can be inquired when the conflict history is inquired, and the condition that the record does not exist does not need to be considered.

Specifically, step S3 further includes:

step S300, inquiring conflict history by using the source key value in the current URP operation, when a record is inquired and the source Rowid of the record is not consistent with the source Rowid of the URP, entering step S301, otherwise, if the record is consistent, indicating that the URP is the updated just one URP record, and processing according to the common URP.

Taking the execution of the second URP operation URP2- >3 as an example, then query the conflict history with the source key value 2 in the URP operation URP2- >3, in this embodiment, query the conflict history with 2 as the key value, and query the corresponding record with the source Rowid of R1 and the source Rowid of the URP operation being R2, if the two are not identical, go to step S301, update and insert; if a match is found, the URP is said to update the last URP record, and normal URP processing is required, for example, when URP1- >2- >3 is executed, and URP2- >3 is executed, the record whose modified key 2 is 3 is the first record whose modified key1 is 2, that is, the record whose key1 is 2, which indicates that the Rowid R1 was modified by the source library, is modified, then the key 2 of the record whose key is Rowid R1 is modified again is 3, in this case, the conflict history is queried with 2 as the key, the source Rowid is R1 is queried, and the source Rowid of the URP is also R1 (the record whose key is currently 2 is a key, which modifies the key1, so its source Rowid is still R1), the queried source Rowid is consistent, and normal URP processing is required, and according to URP2- >3, the target record whose updated key is 3, and the updated key is read, storing 3 as a key value into a conflict history, deleting a record with a primary key of 3 in a standby table, finally executing URP operation URP2- >3, returning Rowid R standby 1 of the record in the standby table (namely the record with the primary key value of 1, only the key value of 3), and recording a source standby Rowid in the conflict history, namely Rowid R1 of a source library and Rowid R standby 1 of a standby library.

Step S301, change the update operation into insert operation, and merge the new value of URP into the conflict history.

That is, when a conflict history that matches the source Rowid of the current URP operation is not found in the conflict history, the current URP update operation is changed to the IRP operation, for example, the current update operation is URP2- >3, and the current update operation is changed to IRP 3. For example, URP1- >2 and URP2- >3, when URP1- >2 is executed, the record with 2 as the key value is deleted from the table and stored in the conflict history, when URP2- >3 is executed, it is necessary to change the key value of the record with 2 as the key value to 3, and integrate the values of the other columns of the records with 2 as the key value to 3 into one record.

Step S302, executing the changed inserting operation, determining whether there is a conflict, if there is a conflict, processing the conflict according to a normal URP operation processing method, that is, saving the record corresponding to the inserted IRP operation key value in the standby table into a conflict history, deleting the record in the standby table, and then executing the inserted IRP operation (also returning the Rowid of the inserted IRP operation record and recording the Rowid into the conflict history), where it is noted that deleting the record in the standby table requires adding the condition of Rowid < > previous Rowid. For example, if the URP1- >2, the URP2- >3, and the URP3- >4 are executed, after the first URP1- >2, 1 is changed to 2, the conflict record already has a record with 2 as the key value, the source Rowid of the record is the Rowid with the original key1 being 1, when the second URP2- >3 is executed, the record with conflict history is searched by using the source key value 2, at this time, the source Rowid is not consistent, the change is updated and inserted to be IRP3, since the key value 3 is already existed in the backup table before, at this time, a conflict is generated, the key value of 3 is recorded in the conflict history according to the processing steps just started, namely, the source backup Rowid is recorded, that is, the Rowid R2 of the source table and the Rowid R of the backup table are 2, then the record with 3 as the key value is deleted in the backup table, and the record merged in the conflict history is inserted into the backup table

Step S303, after a certain cache record of the conflict history is inserted successfully, deleting the corresponding cache in the conflict history. For example, when the key value of the record with the key value of 2 becomes 3 and is successfully inserted into the backup table, the record is deleted from the conflict history because the record has been successfully put in the database.

When the whole batch of gradually updated transactions is finished, the conflict history is cleared, in the invention, the conflict history is only effective in the transactions, and just so, the primary key mapping can not adopt a primary key hash mode any more, because the continuous conflict processing can only be completed in one transaction, if one transaction is dynamically changed from the hash mode to the non-hash mode, the realization needs to consider the clearing and submission of the existing queue, and the transaction boundary is broken.

Preferably, the method for batch updating of the master key in the Oracle database synchronization environment further includes the following steps:

step S4, when updating the IOT table and the table with the primary key as the partition condition in batch, generating a DRP and IRP operation for each URP operation of the source end.

Specifically, for the IOT table and the table with the primary key as the partition condition, when the source executes a URP, a DRP + IRP operation is generated in the log of the Oracle database. To illustrate according to the foregoing partition table, when the source executes the URP operation to update two records of example 1 and example 2 in the tab2 table in bulk

TABLE Tab2

	key1 number	b number
			Example 1	1	13
Example 2	2	13

For example: update tab2 set key1 ═ key1+1 where key1> 0;

the backup side will perform the following operations:

DRP 1

Delete from tab2 where key1＝1；

IRP 2

Insert into tab2 values(2,13)；

DRP 2

Delete from tab2 where key1＝2；

IRP 3

Insert into tab2 values(3,13)；

step S5, executing DRP operation, using the primary key value of current DRP operation to inquire conflict history, if there is no record or there is record but the source Rowid is not consistent, directly deleting the record corresponding to the current primary key value in the backup table; and if the conflict history has records and the source Rowid of the current DRP operation is consistent with the source Rowid of the key value record corresponding to the conflict history, deleting the record corresponding to the key value in the conflict history and deleting the corresponding record in the standby table.

When handling DRP operations, several situations may be faced:

1) normal delete operation; there will not be any conflicting data and no associated cache records.

2) The first DRP + IRP case; there will be no conflicting data and no buffering.

3) DRP + IRP or URP corresponding to the same main key is arranged in the front; there may be conflicting data, there may be cached records (generated by IRP or URP).

Based on the above analysis, in step S5, the following steps are taken:

1) and querying a conflict history by using the primary key value operated by the current DRP, if no record exists in the conflict history or records exist in the conflict history but the Rowids of the sources are inconsistent, indicating that the records are not the same, and directly deleting the record corresponding to the current primary key value in the standby table.

2) If there is a record in the conflict history and the source Rowid of the current DRP operation is consistent with the source Rowid of the corresponding key value record in the conflict history, that is, the current DRP operation is a deleted record of a URP conflict in the front, the conflict history is cleared, and the record corresponding to the key value in the conflict history is deleted. In this case, the record of the current DRP operation is just the deleted record of the previous URP conflict, for example, when 2 is inserted in the previous record, the record with the source key value of 2 is deleted, the record is stored in the conflict history, and when the deletion operation is executed at this time, the conflict history of the record is directly cleared just after the record is queried in the conflict history.

And step S6, executing IRP operation, trying to insert record, if there is conflict, processing according to conflict processing mode of common URP, if there is no conflict, inserting successfully, and returning to Rowid after inserting successfully.

Specifically, when processing an IRP operation, the following situations may be faced:

1) and in the normal insertion operation, no relevant cache record exists, and the insertion can be successfully performed with high probability.

2) Part of the DRP + IRP, the original record line (old primary key) has been deleted, there is no associated cached record, but it may not be possible to insert a success (such as in the case of a bulk-change primary key).

Based on the above analysis, the following steps were taken:

1) an attempt is made to insert a record, and if there is no conflict, the insertion is successful, returning to Rowid.

2) If there is conflict, then it is processed according to the conflict processing mode of normal URP.

FIG. 2 is an architecture diagram of a batch primary key update processing system under an Oracle database synchronization environment according to the present invention. As shown in fig. 2, the system for batch updating of the master key in the Oracle database synchronization environment of the present invention is applied to the backup side, and includes:

a batch update operation parsing unit 201, configured to, in a synchronous environment, parse a URP sequence according to a batch primary key update operation of a source end when the source end performs the batch primary key update operation.

In the invention, the batch of batch primary key updating refers to the batch primary key of the source database, and the invention mainly relates to a processing process of the backup end when the batch primary key of the source database is updated. Under the synchronous environment, a source library (a source database) is synchronized to a standby target library in real time. After the source end operates, the backup end target library is analyzed into a URP sequence according to the operation of the source end.

Specifically, when the source peer:

create table tab1(key1 int primary key,age number,name varchar2(10))；

get table tab1 and insert two pieces of data into tab1 table:

table tab1

Rowid	key1 int	age number	Name varchar2(10)
				R1 (example 1)	1	13	a
R2 (example 2)	2	13	b

That is, there are now two records at the source end, corresponding rowid to R1 and R2, and original master key1 having values of 1 and 2.

When the source performs the URP operation: when Update tab1 set key1 is key1+1 where key1>0 batch Update tab1 table, at the backup side, the batch Update operation parsing unit 201 will parse the following URP sequence according to the batch primary key Update operation at the source side:

URP 1->2

(update tab1 set key1＝2 where key1＝1)

URP 2->3

(update tab1 set key1＝3 where key1＝2)。

a URP operation sequential execution unit 202, configured to execute the URP operations in the URP sequence in sequence, and execute a normal URP operation process on a first URP operation, when a conflict occurs, store a record corresponding to a target update key of the URP operation in a conflict history, and execute the current URP operation after deleting the record in a backup table; and then executing the next URP operation, inquiring the conflict history by using the source key value in the URP operation, changing the updating operation into an inserting operation when a record is inquired and the source Rowid of the record is not consistent with the source Rowid of the current URP operation, and executing the ordinary URP operation processing when the record is inquired and the source Rowid of the record is consistent with the source Rowid of the current URP operation.

In the present invention, the general URP operation process specifically includes: and judging whether a conflict is generated in the standby end table according to the target update key value of the current URP operation, if so, reading a record corresponding to the target update key value in the standby end table, storing the record into a conflict history, deleting the record in the standby end table, and then executing the current URP operation. Specifically, the URP operation sequential execution unit 202 first obtains and executes a first URP operation in the URP sequence, and when a conflict occurs, reads a record corresponding to a target update key of the current URP operation, stores the record in a conflict history, deletes the record in a current table, and then executes the current URP operation.

In the embodiment of the present invention, it is assumed that the table tab1 in the backup target library is:

Rowid	key1 int	age number	Name varchar2(10)
				r. 1 (example 1)	1	13	a
R. sub.2 (example 2)	2	13	b

The URP operations in the above-mentioned URP sequence are executed in sequence, first, for the first URP operation URP1- >2, the target update key value is obtained as 2, so the record with the primary key of 2 is read out, the record with 2 as the key value is stored in the conflict history, then the record with the primary key of 2 is deleted in the table tab1, finally, the URP operation URP1- >2 is executed and returned to the backup end Rowid (i.e., Rowid R backup 1), and the source backup Rowid, i.e., the Rowid R1 of the source library and the Rowid R backup 1 of the backup library, is recorded in the last conflict history.

Then, the URP operation sequential execution unit 202 executes the next URP operation, queries the conflict history with the source key value in the URP operation, changes the update operation to the insert operation when a record is queried and the source Rowid of the record is inconsistent with the source Rowid of the current URP operation, and executes the normal URP operation processing when a record is queried and the source Rowid of the record is consistent with the source Rowid of the current URP operation.

The specific process of the URP operation sequential execution unit 202 performing the next URP operation is as follows:

step 1, inquiring conflict history by using a source key value in the current URP operation, entering step 2 when a record is inquired and the source Rowid of the record is inconsistent with the source Rowid of the URP, otherwise, if the record is consistent, indicating that the URP is the updated just-before URP record and needing to be processed according to the common URP.

Taking the execution of the second URP operation URP2- >3 as an example, then query the conflict history with the source key value 2 in the URP operation URP2- >3, in this embodiment, query the conflict history with 2 as the key value, and query the corresponding record with the source Rowid of R1 and the source Rowid of the URP operation being R2, if the two are not identical, go to step 2, and update and insert; if the matching is found, the URP is described as updating the last URP record, then normal URP processing is performed, for example, when URP1- >2- >3 is executed, and URP2- >3 is executed, the modified key 2 of 3 is the first record with 1 being 2 modified, i.e. the key1 of R1 is modified to be 2, and then the record 2 of R1 is modified to be 3, in this case, the conflict history is queried with 2 as the key, the queried source Rowid is identical, normal URP processing is performed, the target update key 3 is obtained according to URP2- >3, the record with 3 as the primary key is read out, the record with 3 as the key is stored in the conflict history, then the record with 3 as the primary key is deleted in the backup table, and finally URP operation URP2- >3 is executed and returned to the rowd of the record in the backup table, and the source rowd history is recorded in the backup history, namely Rowid R1 of the source library and Rowid R standby 1 of the standby library.

And 2, changing the updating operation into an inserting operation, and merging the new value of the URP into the conflict history.

That is, when a conflict history that matches the source Rowid of the current URP operation is not found in the conflict history, the current URP update operation is changed to the IRP operation, for example, the current update operation is URP2- >3, and the current update operation is changed to IRP 3. For example, URP1- >2 and URP2- >3, when URP1- >2 is executed, the record with 2 as the key value is deleted from the table and stored in the conflict history, when URP2- >3 is executed, it is necessary to change the key value of the record with 2 as the key value to 3, and integrate the values of the other columns of the records with 2 as the key value to 3 into one record.

And 3, executing the changed inserting operation, judging whether a conflict exists, if so, performing conflict processing according to a URP processing method, namely saving a record corresponding to the IRP inserting operation key value in the standby list into a conflict history, deleting the record in the standby list, and then executing the IRP inserting operation (also returning Rowid of the IRP inserting record and recording the Rowid into the conflict history), wherein the condition that the Rowid < > is added to the previous Rowid when deleting the record in the standby list is required to be explained. For example, if URP1- >2, URP2- >3, and URP3- >4 are executed, after the first URP1- >2, 1 is changed to 2, a record with 2 as a key value is already in the conflict record, the source rowid of the record is the rowid with the original key1 being 1, when the second URP2- >3 is executed, the record with the conflict history is queried by using the source key value 2, at this time, the source rowids are inconsistent, the change is updated and inserted to be IRP3, since 3 key values already exist in the backup table before, at this time, a conflict occurs, it is necessary to record the key value of 3 in the conflict history according to the processing steps just started, then delete the record with 3 as a key value in the backup table, and then insert the record merged in the conflict history into the backup table.

And 4, deleting the corresponding cache in the conflict history after a certain cache record in the conflict history is successfully inserted. For example, when the key value of the record with the key value of 2 becomes 3 and is successfully inserted into the backup table, the record is deleted from the conflict history because the record has been successfully put in the database.

Preferably, the system for batch updating of the master key in the Oracle database synchronization environment of the present invention further comprises:

and the special analysis processing unit is used for analyzing each URP operation at the source end to generate a DRP and IRP operation when the IOT table and the table taking the primary key as the partition condition are subjected to batch primary key updating.

Specifically, for the IOT table and the table with the primary key as the partition condition, when the source executes a URP, a DRP + IRP operation is generated in the log of the Oracle database. To illustrate according to the foregoing partition table, when the source executes the URP operation to update two records of example 1 and example 2 in the tab2 table in bulk

Tab2 table

	key1 number	b number
			Example 1	1	13
Example 2	2	13

For example: update tab2 set key1 ═ key1+1 where key1> 0;

the backup side will perform the following operations:

DRP 1

Delete from tab2 where key1＝1；

IRP 2

Insert into tab2 values(2,13)；

DRP 2

Delete from tab2 where key1＝2；

IRP 3

Insert into tab2 values(3,13)；

the DRP operation execution unit is used for executing DRP operation, inquiring conflict history by using the primary key value of the current DRP operation, and directly deleting the record corresponding to the current primary key value if no record exists or records exist but the rowid is inconsistent; and if the conflict history has records and the source Rowid of the current DRP operation is consistent with the source Rowid of the key value record corresponding to the conflict history, deleting the record corresponding to the key value in the conflict history and deleting the corresponding record in the standby table.

When handling DRP operations, several situations may be faced:

1) normal delete operation; there will not be any conflicting data and no associated cache records.

2) The first DRP + IRP case; there will be no conflicting data and no buffering.

3) DRP + IRP or URP corresponding to the same main key is arranged in the front; there may be conflicting data, there may be cached records (generated by IRP or URP).

Based on the above analysis, the DRP operation execution unit takes the following steps:

1) and querying the conflict history by using the primary key value operated by the current DRP, if no record exists in the conflict history or records exist in the conflict history but Rowid is inconsistent, indicating that the records are not the same, and directly deleting the record corresponding to the current primary key value in the standby table.

2) If there is a record in the conflict history and the source Rowid of the current DRP operation is consistent with the source Rowid of the corresponding key value record in the conflict history, that is, the current DRP operation is a deleted record of a URP conflict in the front, the conflict history is cleared, and the record corresponding to the key value in the conflict history is deleted. In this case, the record of the current DRP operation is just the deleted record of the previous URP conflict, for example, when 2 is inserted in the previous record, the record with the source key value of 2 is deleted, the record is stored in the conflict history, and when the deletion operation is executed at this time, the conflict history of the record is directly cleared just after the record is queried in the conflict history.

And the IRP operation execution unit is used for executing the IRP operation, trying to insert the record and returning to Rowid, if conflict exists, processing according to a conflict processing mode of the common URP operation, and if no conflict exists, inserting successfully.

Specifically, when processing an IRP operation, the following situations may be faced:

1) and in the normal insertion operation, no relevant cache record exists, and the insertion can be successfully performed with high probability.

2) Part of the DRP + IRP, the original record line (old primary key) has been deleted, there is no associated cached record, but it may not be possible to insert a success (such as in the case of a bulk-change primary key).

Based on the above analysis, the IRP operation execution unit takes the following steps:

1) attempt to insert a record and return a Rowid, if there is no conflict, insert successfully, return a Rowid.

2) If there is conflict, then it is processed according to the conflict processing mode of normal URP.

Examples

1. URP sequence:

when the source performs a URP operation, as shown in the tab1 table above

When the Update tab1 set key1 ═ key1+1 where key1>0 updates the two records of example 1 and example 2 in the table in batch, the backup side executes the URP sequence processing method as follows:

URP 1->2

(update tab1 set key1＝2 where key1＝1)

URP 2->3

(update tab1 set key1＝3 where key1＝2)

1) when the first URP is executed, a 001 conflict is generated, and the following steps are executed:

a) reading 2 records, storing in conflict history, and using 2 as key value

b) Deletion 2

c) URP1- >2 is executed and rowid is returned, the source backup rowid is recorded in the just conflict history.

2) Next, a second URP is executed, which performs the following steps:

a) querying the conflict history by using 2 as a key value, finding that a record exists, and the source rowid is inconsistent with the source rowid of the URP (if consistent, the URP is an updated URP record, and the processing according to the common URP, namely the processing of 001 conflict, is required)

b) Update change insertion and incorporate new values of URP

c) Performing insert operations, e.g. without conflict, handling is done

d) Still having 001 conflict, according to the method of the first URP, delete first and insert later, and make record, delete record need add rowid one rowid condition on rowid < >

e) After confirming that 2 successfully changed to 3, the record line cache of 2 could be deleted because it was successfully binned.

The invention can avoid opening the full-field additional log by a method of caching records.

After the whole transaction is finished, the conflict history is cleared, and the history is only valid in the transaction. And because of this, the primary key mapping can not adopt the primary key hash mode any more, because the continuous conflict processing can only be completed in one transaction, if a transaction is dynamically changed from the hash mode to the non-hash mode, the realization needs to consider the existing queue emptying and submission, and the transaction boundary is broken.

2. DRP + IRP case:

from the foregoing, when the DRP and IRP appear in the IOT table and the table with the primary key as the partition condition, special processing is required.

When the source executes a URP, Oracle generates a DRP + IRP operation in its log. Taking the foregoing example operation according to the partition table, when the source executes the URP operation to update two records of example 1 and example 2 in the tab2 table in bulk

update tab2 set key1＝key1+1 where key1>0；

The backup side can generate the following operations:

DRP 1

Delete from tab2 where key1＝1；

IRP 2

Insert into tab2 values(2,13)；

DRP 2

Delete from tab2 where key1＝2；

IRP 3

Insert into tab2 values(3,13)；

in handling DRP, several situations may be faced:

1) normal delete operation; there will not be any conflicting data and no associated cache records.

2) The first DRP + IRP case; there will be no conflicting data and no buffering.

3) DRP + IRP or URP corresponding to the same main key is arranged in the front; there may be conflicting data, there may be cached records (generated by IRP or URP).

Based on the above analysis, the present embodiment takes the following steps:

1) and querying a conflict history by using the primary key value operated by the current DRP, if no record exists in the conflict history or records exist in the conflict history but the Rowids of the sources are inconsistent, indicating that the records are not the same, and directly deleting the record corresponding to the current primary key value in the standby table.

2) If there is a record in the conflict history and the source Rowid of the current DRP operation is consistent with the source Rowid of the corresponding key value record in the conflict history, that is, the current DRP operation is a deleted record of a URP conflict in the front, the conflict history is cleared, and the record corresponding to the key value in the conflict history is deleted.

In handling IRPs, several situations may be faced:

1) and in the normal insertion operation, no relevant cache record exists, and the insertion can be successful at a high probability.

2) Part of the DRP + IRP, the original record line (old primary key) has been deleted, there is no associated cached record, but it may not be possible to insert a success (such as in the case of a bulk-change primary key).

Based on the above analysis, the following steps were taken:

1) an attempt is made to insert a record, and if there is no conflict, the insertion is successful, returning rowid.

2) If there is conflict, it is processed according to the conflict processing mode of the normal URP.

The foregoing embodiments are merely illustrative of the principles and utilities of the present invention and are not intended to limit the invention. Modifications and variations can be made to the above-described embodiments by those skilled in the art without departing from the spirit and scope of the present invention. Therefore, the scope of the invention should be determined from the following claims.

Claims (10)

1. A batch primary key updating processing method under an Oracle database synchronization environment comprises the following steps:

step S1, when the source end executes the batch primary key updating operation, the backup end analyzes the batch primary key updating operation into a URP sequence according to the source end;

step S2, performing normal URP operation processing on the first URP operation of the URP sequence;

step S3, sequentially execute the next URP operation, query the conflict history with the source key value in the URP operation, change the update operation into the insert operation when the record is found and the source Rowid of the record is not consistent with the source Rowid of the current URP operation, and execute the normal URP operation processing when the record is found and the source Rowid of the record is consistent with the source Rowid of the current URP operation.

2. The method of claim 1, wherein the ordinary URP operation processing procedure is as follows: and judging whether a conflict is generated according to the current URP operation, if so, reading a record corresponding to the target update key value in the standby end table, storing the record into a conflict history, deleting the record in the standby end table, and then executing the current URP operation.

3. The method for batch primary key update processing in an Oracle database synchronization environment of claim 2, wherein: in step S2, a first URP operation in the URP sequence is obtained, a target update key of the first URP operation is obtained, whether a conflict occurs is determined in the backup table according to the target update key, if a conflict occurs, a corresponding record in the backup table is read, the record is updated to a conflict history with the target update key as the key, the record is deleted in the backup table, and finally the first URP operation is executed to return to the source-backup Rowid, and the source-backup Rowid is recorded in the record in the conflict history.

4. The method for batch updating the master key in the Oracle database synchronization environment as claimed in claim 3, wherein the step S3 further comprises:

step S300, inquiring conflict history by using a source key value in the current URP operation, entering step S301 when a record is inquired and the source Rowid of the record is inconsistent with the source Rowid of the URP operation, or processing the record according to the common URP operation if the record is consistent with the source Rowid of the URP operation;

step S301, changing the update operation into an insert operation, and merging the target update key value of the URP into the conflict history;

step S302, executing the changed inserting operation, judging whether a conflict exists, if so, processing the conflict according to a common URP operation processing method and then executing the inserting operation;

step S303, after a certain cache record of the conflict history is inserted successfully, deleting the corresponding cache in the conflict history.

5. The method for batch primary key update processing in an Oracle database synchronization environment of claim 4, wherein: in step S302, if there is a conflict, the record corresponding to the IRP-inserted operation key value in the backup table is saved in the conflict history, the record is deleted in the backup table, and then the inserting operation is executed, and the source backup Rowid is recorded in the record corresponding to the conflict history.

6. The method for batch primary key update processing in an Oracle database synchronization environment as claimed in claim 1, wherein said method further comprises the steps of:

step S4, when updating the IOT table and the table with the primary key as the partition condition in batch, generating a DRP and IRP operation for each URP operation of the source end;

step S5, the DRP operation and the IRP operation are sequentially executed.

7. The method according to claim 6, wherein in step S5, when performing DRP operation, the method queries the conflict history with the primary key value of the current DRP operation, and if there is no record or there is a record but the source Rowid of the current DRP operation is not consistent with the source Rowid of the corresponding key value record in the conflict history, directly deletes the record corresponding to the current primary key value in the standby table; and if the conflict history has records and the source Rowid of the current DRP operation is consistent with the source Rowid of the corresponding key value record in the conflict history, deleting the record corresponding to the key value in the conflict history and simultaneously deleting the corresponding record in the standby table.

8. The method for batch primary key update processing in an Oracle database synchronization environment of claim 7, wherein: in step S5, when an IRP operation is performed, an attempt is made to insert a record, if there is a conflict, the normal URP operation is performed, if there is no conflict, the insertion is successful, and the process returns to Rowid.

9. A batch primary key update processing system in an Oracle database synchronization environment, comprising:

the batch updating operation analyzing unit is used for analyzing the batch updating operation of the source end into a URP sequence according to the batch updating operation of the source end when the source end executes the batch updating operation of the master key under the synchronous environment;

a URP operation sequential execution unit, configured to execute the URP operations in the URP sequence in sequence, and execute a normal URP operation process on a first URP operation, when a conflict occurs, store a record corresponding to a target update key of the URP operation in a conflict history, and execute a current URP operation after deleting the record in a backup table; and then executing the next URP operation, inquiring the conflict history by using the source key value in the URP operation, changing the updating operation into an inserting operation when a record is inquired and the source Rowid of the record is not consistent with the source Rowid of the current URP operation, and executing the ordinary URP operation processing when the record is inquired and the source Rowid of the record is consistent with the source Rowid of the current URP operation.

10. The system of claim 9, wherein said system further comprises:

the special analysis processing unit is used for analyzing each URP operation at the source end to generate a DRP and IRP operation when batch primary key updating is carried out on the IOT table and the table taking the primary key as the partition condition;

a DRP operation execution unit, configured to execute a DRP operation, query the conflict history with the primary key value of the current DRP operation, and directly delete the record corresponding to the current primary key value in the standby table if there is no record or there is a record but the source Rowid of the current DRP operation is inconsistent with the source Rowid of the corresponding key value record in the conflict history; if a record exists in the conflict history and the source Rowid of the current DRP operation is consistent with the source Rowid of the corresponding key value record in the conflict history, deleting the record corresponding to the key value in the conflict history and deleting the corresponding record in the standby terminal table;

and the IRP operation execution unit is used for executing the IRP operation, trying to insert the record and returning to Rowid, if conflict exists, processing according to a conflict processing mode of the common URP operation, and if no conflict exists, inserting successfully.

Images