WO2018216839A1

WO2018216839A1 - Database replication method and database replication system

Info

Publication number: WO2018216839A1
Application number: PCT/KR2017/005573
Authority: WO
Inventors: 이현철
Original assignee: (주)선재소프트
Priority date: 2017-05-24
Filing date: 2017-05-29
Publication date: 2018-11-29
Also published as: KR101936787B1; CN110300964A; KR20180128580A; CN110300964B

Abstract

The present invention relates to a data replication method and a database replication system, wherein the data replication method of the present invention comprises: a step (A) of detecting a query to be processed among first databases of a main device so as to transmit the query to a receiver of a replication device including a second database; a step (B) of extracting, from the query, a slot having a transaction ID and a slot ID having a primary key to be applied to a record of a relation stored in the first database; a step (C) of locking the slot; a step (D) of putting a pre-slot, which has the same primary key in the slot and is processed at a higher priority, on standby at a main distributor and transmitting a post-slot, which has the same primary key and is processed at a lower priority, to a sub distributor; a step (E) of allocating the pre-slot and the post-slot to an applier; a step (F) of increasing a main reference count to an applier to which the pre-slot is allocated and increasing a sub reference count to an applier to which the post-slot is allocated; and a step (G) of releasing the lock of the pre-slot and the lock of the post-slot. In addition, the database replication system of the present invention comprises: a detector for detecting a query to be processed in one relation stored in the first database; a transmitter for transmitting the query; a receiver for receiving the query transmitted by the transmitter; a main distributor for forming a slot having a slot ID and a transaction ID in the query received by the receiver, putting a pre-slot, which has the same primary key in the formed slot and is processed at a higher priority, on standby and transmitting a post-slot, to be processed at a lower priority, to a sub distributor; and an applier for applying the pre-slot and the post-slot to a second database.

Description

Database Redundancy Method and Database Redundancy System

The present invention relates to a database redundancy method and a database redundancy system, and more particularly, to a database redundancy method and a database redundancy system capable of guaranteeing data integrity while processing transactions in parallel and processing transactions in parallel. .

A database is a set of logically related data that is recorded on a computer. Logical relationships between data elements allow you to edit, remove, or modify specific information from the database based on the content of the query.

The database can be used to ensure that the edited data is error free, and that new data can be inserted, deleted, or updated. It also allows multiple users to access their data at the same time.

Many data stored in the database are transacted by guaranteeing the integrity of the data through various queries.

This type of data processing is widely used to handle tasks throughout the industry of society. In particular, it is considered important in the field of modifying and generating data quickly and accurately.

Currently, in order to increase the stability of data processing, a process of synchronizing data stored in the main database with a backup database for several physically separated databases is actively developed. That is, a lot of data duplication techniques have been developed.

Banks and Korean exchanges that transact important data use data duplication technology to prepare for computer errors and accidents caused by virus intrusions.

As the use of data duplication in the financial sector is increasing, researches on methods and systems for enhancing the safety of duplication-based data processing are being actively conducted. As an example, there is Republic of Korea Patent No. 10-1605455.

However, many of the technologies developed, including the aforementioned patents, are only technologies for preventing the loss of data to be processed, and there is no way to improve the integrity of data and the processing speed of data.

SUMMARY OF THE INVENTION The present invention has been made in an effort to solve such a problem, and to provide a database redundancy method and a database redundancy system capable of improving a transaction processing speed and ensuring data integrity.

The technical problem of the present invention is not limited to the above-mentioned problems, and other technical problems not mentioned will be clearly understood by those skilled in the art from the following description.

The database duplication method of the present invention for achieving the technical problem is to detect a query (Query) to be processed among the first database of the main equipment, and transmits the query to the receiver of the duplication equipment including a second database (A) step;

(B) extracting, from the query, a slot having a primary key and a transaction ID having a primary key to be applied to a record of the relation stored in the first database;

(C) locking the slot;

(D) transmitting a primary slot having the same primary key among the slots but processing the previous slots in the main distributor, and transmitting the subsequent slots having the same primary key but the lower slots to the partial distributors;

Assigning the preslot and the postslot to a reflector (E);

(F) increasing a main reference count to the reflector to which the previous slot is assigned, and increasing a sub reference count to the reflector to which the rear slot is assigned; And

(G) releasing the lock of the previous slot and the lock of the rear slot.

The main distributor transmits the previous slots to the reflector, respectively, so that the main distributors can simultaneously perform the previous slots, and when the previous slots are processed, the main distributors can simultaneously process the rear slots.

The partial distributor may transmit the rear slot having the same primary key as the processed front slot to the reflector that has processed the front slot.

When the extracted slot is not locked, the main distributor may wait until the slot is locked.

A detector for detecting a query to be processed in one relation stored in the first database;

A transmitter for transmitting the query;

A receiver for receiving the query transmitted from the transmitter;

In the query received from the receiver, a slot having a slot ID and a transaction ID is formed, and among all the slots formed, the primary key is the same but all slots processed in the previous order are waiting. A main distributor which transmits the subslot to the partial distributor; And

It includes a reflector reflecting the front slot and the back slot in a second database.

The main distributor may perform a commit query after the order query is performed and generate a slot having one transaction ID.

The partial distributor may process the rear slot at the same time when the front slot is processed.

The partial distributor may transmit the rear slot having the same basic key as the primary key of the previous slot to the reflector which processed the previous slot.

The database duplication method and the database duplication system according to the present invention can increase the transaction speed of data, so that a transaction bottleneck does not occur in a remote server.

In addition, the present invention guarantees data integrity to remote servers and enables parallel processing of data.

1 is a block diagram of a database redundancy system according to an embodiment of the present invention.

FIG. 2 is a table illustrating a slot ID and a transaction ID extracted from the query received by the duplication equipment of FIG. 1.

3 is a conceptual diagram conceptually illustrating operations of a main distributor and a partial distributor of the database redundancy system of FIG. 1.

FIG. 4 is a relation in which a query is processed by the operations of the main distributor and the partial distributor of FIG. 3.

5 is a flowchart illustrating an operation process of a main distributor of the database replication system of FIG. 1.

FIG. 6 is a flowchart illustrating an operation process of a partial distributor of the database replication system of FIG. 1.

FIG. 7 is a flowchart illustrating a process of recovering reflectors of the database replication system of FIG. 1.

8 is a flowchart schematically illustrating a point at which an operation process of the main distributor of FIG. 4 and a recovery process of the reflector of FIG. 6 are combined.

9 is a view showing the time spent performing transactions of the prior art and database replication system.

Advantages and features of the present invention and methods for achieving them will be apparent with reference to the embodiments described below in detail in conjunction with the accompanying drawings. However, the present invention is not limited to the embodiments disclosed below, but may be implemented in various forms, and only the present embodiments make the disclosure of the present invention complete and the general knowledge in the technical field to which the present invention belongs. It is provided to fully inform those who have the scope of the invention.

The invention may be defined by the claims, including the claims, and the description supporting the claims. In addition, the same reference numerals throughout the specification refer to the same components.

Hereinafter, a database duplication method and a database duplication system according to an embodiment of the present invention will be described in detail with reference to FIGS. 1 to 9.

However, after the database replication system is described in detail with reference to FIGS. 1 to 4 so that the description can be simplified and clear, the database replication method will be described with reference to FIGS. 5 to 9.

First, a database redundancy system 1 according to an embodiment of the present invention will be described with reference to FIG. 1.

In the database redundancy system 1 according to an embodiment of the present invention, the main distributor 202 of the duplication equipment 20 receives a slot ID, a transaction, in a query transmitted from a transmitter of the main equipment 10. A slot S having an ID (Transaction ID) is formed. And, the primary key (PK: Primary Key) among the formed slots are the same, but the first slot (FS, see FIG. 3) to be processed in the prior order is to be processed in the main distributor 202, the back slot (PS) is processed in the subsequent order , See FIG. 3) to be processed in partial distributor 203.

In this case, the database redundancy system 1 simultaneously processes a plurality of front slots FS having different primary keys in the main distributor 202, and then simultaneously processes a plurality of back slots PS in the partial distributor 203. The slot is quickly reflected in the second database 205.

That is, the database redundancy system 1 may preprocess the redundant data to separate the processing process and thereby duplicate the data without a bottleneck.

The database redundancy system 1 includes a detector 102 for detecting a query to be processed in a relation stored in a first database, a transmitter 103 for transmitting a query, and a receiver for receiving a query transmitted from the transmitter 103 ( 201), in the query received from the receiver 201, a slot S having a slot ID and a transaction ID is formed, and among the slots formed, the primary key is the same, The pre-slot FS to be processed is queued, and the post-slot PS to be processed in the subordinate order is the main distributor 202 and the pre-slot FS and the back slot PS to be transmitted to the partial distributor 203 in the second database. And a reflector 204 reflecting in 205.

Here, the first database 101 may be installed in the main equipment 10 which is a database management system, and the second database 205 may be installed in the duplication equipment 20 which is a database management system. The database management system can generate and manipulate information according to queries between the user and the database.

Hereinafter, the components constituting the database redundancy system 1 will be described in detail.

The detector 102 detects a query to be processed in a relation stored in the first database 101 included in the main device 10. That is, the detector 102 detects the redundant data. In one example, the detector 102 may be formed as a query processor.

The detector 102 may transmit the detected query to the transmitter 103 included in the main device 10.

The transmitter 103 transmits the received query to the receiver 201 included in the duplication equipment 20. The receiver 201 is included in the duplication equipment 20 and receives a query, that is, order data transmitted from the transmitter 103. In this case, the transmitter 103 and the receiver 201 may be connected to a network to smoothly transmit / receive order data.

The main distributor 202 determines what data is to be reflected in the duplication equipment 20 and distributes it to the reflector 204. The main distributor 202 may be included in the duplication equipment 20 and may extract at least one slot S having a slot ID and a transaction ID from a query received from the receiver 201. have.

At this time, the main distributor 202 proceeds with one commit query after one order query, and generates a slot having one transaction ID. The generated slot may be locked to prevent other transactions from accessing data used by one transaction.

In addition, the main distributor 202 forms a slot with a slot ID equal to the previously formed slot ID of a slot for which a query including a content for changing previously processed contents is formed. That is, the main distributor 202 may form a back slot for changing a query processed before the same primary key.

The main distributor 202 waits for the preslot including the previously processed query and transmits the postslot including the post processed query to the partial distributor 203. That is, the main distributor 202 creates a slot having a slot ID and a transaction ID in the received query, and then waits for some slots and passes the remaining slots to the partial distributor 203.

Here, the slot refers to a space capable of storing one record. Such a slot may include a primary key, a transaction ID, and find a slot having the same primary key and change the content of the relation.

The main distributor 202 and the sub-distributor 203 prevent transactions with different IDs from simultaneously processing records when transactions with different transaction IDs want to modify a relation with the same primary key.

As such, the main distributor 202 and the partial distributor 203 can process the transaction and ensure independence of the transaction. The operation of the main distributor 202 and the partial distributor 203 will be described in detail later.

The reflector 204 serves to reflect the record stored in the slot to the second database 205. That is, the reflector 204 reflects the front slot FS and the rear slot PS in the second database 205. In response to the number of such reflectors 204, a transaction can be processed simultaneously. For example, if the number of reflectors is four, four transactions can be processed simultaneously.

Hereinafter, a state in which a database duplication system transacts a plurality of processing contents will be described with reference to FIGS. 2 to 4.

FIG. 2 is a table illustrating a slot ID and a transaction ID extracted from a query received by the duplication equipment of FIG. 1, and FIG. 3 is a conceptual diagram conceptually illustrating operations of a main distributor and a partial distributor of the database replication system of FIG. 1. FIG. 4 is a relation in which a query is processed by the operations of the main distributor and the partial distributor of FIG. 3.

As an example, as shown in (a) of FIG. 2, if twelve queries are received at the receiver 201, the main distributor 202 may determine the slot ID as shown in (b) of FIG. 2 in the received query. Extract the transaction ID. For example, as shown in FIG. 2 (b), the main distributor 202 executes a query for 'inserting a new record having a column value of AA' in the first query and a transaction in the second query. Commit. ', The transaction ID and the slot ID are extracted as 1 for the first query and the second query.

When the query to insert a new record with the column value BB in the third query and the query to commit the transaction to the fourth query are received, the transaction is executed for the third and fourth queries. Extract the ID and slot ID values as 2.

When the fifth query receives the query 'Change the second column of the record whose column is AA to BB' and the query 'Commit the transaction' to the sixth query, the fifth query and For the sixth query, the transaction ID value is extracted as 3, and the slot ID value is extracted as 1.

When the query 'insert a record whose column is the value CC' in the seventh query and the query 'commit the transaction' are made in the eighth query, the value of the transaction ID for the seventh and eighth queries is received. Is extracted as 4, and the value of the slot ID is extracted as 3.

In response to a query for changing the second column of the record whose column has the value CC to DD in the ninth query, and receiving the query to commit the transaction in the tenth query, the ninth query is received. For the query and the tenth query, the transaction ID value is extracted as 5, and the slot ID value is extracted as 3.

When the query to insert a new record whose column value is EE in the 11th query and the query to commit the transaction to the 12th query are received, the transaction is executed for the 11th and 12th queries. The ID value is extracted as 6 and the slot ID value is extracted as 4.

In this manner, the main distributor 202 may extract one transaction ID and one slot ID in two queries. In this case, the ID formed in the slot is formed to have the same value as the ID formed in the slot when the content processed by the slot is associated with the previously processed content.

The main distributor 202 has the same primary key (PK) among the slots from which the transaction ID and the slot ID are extracted, but waits for the preslot FS processed in the prior order and processes the back slot PS processed in the subordinate order. ) Is transmitted to the partial distributor 203.

That is, the main distributor 202 transmits the slots that can be immediately distributed to the reflector 204 to the reflector 204 and otherwise transmits the slots to be processed to the partial distributor 203.

The partial distributor 203 transmits the rear slot PS having the same basic key as the primary key of the previous slot to the reflector 204 which processed the front slot FS so that the partial slot 203 can be processed. At this time, the partial distributor 203 waits while the slots passed from the main distributor 202 to the reflector 204 are fully reflected in the relation, and when completed, the partial distributor 203 simultaneously passes the rear slot PS to the reflector 204. It can be processed at the same time.

As such, the main distributor 202 and the partial distributor 203 pass the slot from which the transaction ID and the slot ID are extracted to the reflector 204 to be processed, and as shown in FIG. 4 in the second database 205. The AA in the β column, the second column with the primary key of 1, is changed to BB, and the relation in which the CC, the second column with the 4 is changed to DD, is reflected.

Hereinafter, the database duplication method according to an embodiment of the present invention will be described based on the description of the database duplication system 1 described so far.

The database duplication method detects a query to be processed in the first database 101 of the main device, and starts step (A) of transmitting the query to a receiver of the duplication device including the second database 205.

After step (A), step (B) of extracting a slot having a primary key and a transaction ID having a primary key to be applied to the record of the relation stored in the first database is performed from the query.

When the slot to which the slot ID and the transaction ID are allocated is extracted, step (C) of locking the slot is performed.

Thereafter, the primary key is the same among the slots, but the previous slot (FS) processed in the prior order is waited at the main distributor, and the subsequent slot processed in the next priority is transferred to the partial distributor (D).

When step (D) is completed, step (E) of assigning the front slot FS and the rear slot PS to the reflector 204 is performed. Subsequently, the main reference count is increased to the reflector 204 to which the previous slot FS is assigned, and the sub reference count to the reflector 204 to which the rear slot PS is assigned. Proceed to step (F) to increase.

Thereafter, step (G) of releasing the lock of the previous slot and the lock of the next slot is performed.

The database duplication method proceeds from the steps (A) to (G) in a series of steps, and can speed up the transaction of the query, so that the bottleneck of the transaction does not occur on the remote server.

In such a database duplication method, the operation of the main distributor 202 is operated based on the flowchart of FIG. 5, and the operation of the partial distributor 203 is operated based on the flowchart of FIG. 6.

FIG. 5 is a flowchart illustrating an operation process of a main distributor of the database replication system of FIG. 1, and FIG. 6 is a flowchart illustrating an operation process of a partial distributor of the database replication system of FIG. 1.

Operation of the main distributors and partial distributors shown in FIGS. 5 and 6 will be described with reference to FIG. 2.

As shown in FIG. 2B, the main distributor 202 extracts the slot ID and the transaction ID from the received query (S110). In this case, the query may include specific information so that the main distributor 202 may extract the transaction ID and the slot ID.

Thereafter, the main distributor 202 proceeds with the step of locking the slot for controlling the extracted slot concurrency (S120). At this time, if the slot is not locked due to the partial distribution 203 or the processing of the commit, the lock is applied to the slot after waiting for a predetermined time (S121). Thereafter, among the locked slots, the primary key is the same, but the previous slot (FS) processed in the previous rank is waited, and the primary slot is the same, but the subsequent slot PS processed in the downstream rank is transmitted to the partial distributor 203.

At this time, if the slot is waiting to be processed in the partial distributor 203, the sub reference count is increased to allow the post slot PS to be transmitted.

In addition, the main distributor 202 determines whether there is an appropriate reflector 204 with the slot ID and the transaction ID set in all slots FS (S140). In this case, when the former slot FS is not in use, the reflector 204 information is stored in the former slot FS (S141). Thereafter, the reflector 204 compares the transaction ID set in the query with the transaction ID extracted from the main distributor to determine whether they are the same (S150). At this time, if the reflector is the same because the transaction ID set in the query and the extracted transaction ID are the same, the main reference count is increased. On the other hand, if the reflector 204 is different, the Sub Referebce Count is increased.

Thereafter, the lock of the slot in which the main reference count and the subreference count are increased is released, so that other transactions can access it.

The partial distributor 203 starts from the above-mentioned main distributor 202, where the bowl is classified and operated as the partial distributor 203 (S151). Subsequently, the partial distribution 203 proceeds to lock the rear slot PS for concurrency control of the classified slots (S161).

At this time, if the lock is not applied to the rear slot PS due to the process of commit, after waiting for a predetermined time, the lock is applied to the rear slot PS (S183).

If the rear slot PS is not reflected in the rear slot PS that is locked, the reflector 204 information is stored in the rear slot PS (S172). On the other hand, if the back slot (PS) is reflected in the reflector 204, it is determined by comparing the transaction ID set in the query and the transaction ID of the back slot (S181). At this time, if the transaction ID set in the query and the transaction ID of the backseat are different, the lock is released and then waited and retried.

On the other hand, when the reflectors 204 are the same, the next processing is performed after increasing the sub reference count.

In the partial distributor 203, the transaction that was waiting is processed in the reflector 204, and the sub-lapperus count, which is information that it was waiting, is reduced (S191). The main lever count is increased to change the state during processing (S201).

Thereafter, the lock is released so that the back slot PS can process another transaction.

That is, the partial dispatcher 203 passes the reflector to process the transaction when the waiting transaction becomes available.

Hereinafter, referring to FIGS. 7 and 8, the operation of the reflector and the operation of the main distributor and the operation of the reflector will be described.

FIG. 7 is a flowchart illustrating a process of recovering the reflector of the database redundancy system of FIG. 1, and FIG. 8 is a flowchart schematically illustrating a point at which an operation process of the main distributor of FIG. 4 and a process of recovering the reflector of FIG. 6 are combined.

If the transaction is reflected in the second database 205, the transaction commit is completed (S210). Thereafter, a process of obtaining all slot information used by the current transaction with reference to the distributor's memory and the like (S220). Next, a step of locking the extracted slot is performed (S230).

At this time, if the lock is not applied to the slot, the lock is applied to the slot after waiting for a predetermined time (S231). On the other hand, when the lock is applied to the slot, the main reference count and the sub reference count is reduced (S240). At this time, the main reference count and the subreference count are reduced by the number of queries executed in the transaction.

In the present invention, since one transaction ID includes one order query, the number of main reference counts and subreference counts of 1 is reduced by one.

Thereafter, the method determines whether the main reference count and the sub reference count are 0 (S250). In this case, if each reference count is 0, the process of deleting reflector information of the corresponding slot (S260) and unlocking the slot (S270) are sequentially performed.

On the other hand, if each reference count is not 0, the step of unlocking the slot is immediately performed. After that, check whether there are any more queries to process.

Furthermore, if the reflector information is deleted from the slot in S260, the reflector information may be reassigned in step S140 of FIG. 5 and the query may be continuously performed. Again, with reference to FIG. 7, it is then checked whether the processing is completed for the slot of the query included in the current transaction.

This method is repeated to retrieve all the reflectors allocated in the slot and to allow new transactions to access each slot.

9 (a) is a view showing the time according to the transaction execution according to the conventional method, (b) is a view showing the time according to the transaction execution according to the present invention.

As shown in (a) of FIG. 9, in processing one transaction, the method of waiting for processing the previous transaction uses one reflector even though the records to be processed are different. Thus, after one transaction is processed, the remaining transactions are processed, so it takes a long time to complete the entire transaction.

On the other hand, as shown in Figure 9 (b), in the database replication method of the present invention, transaction ID 1, transaction ID 2, transaction ID 4 and transaction ID 6 having different primary keys are performed in parallel in the main distributor 202. In addition, the transaction ID 2 and the transaction ID 5 having the same primary key and the same transaction ID 5 as the transaction ID 4 are performed in parallel in the partial distributor 203.

As such, the main distributor 202 and the partial distributor 203 process the transaction in parallel, but the partial distributor 203 processes the transaction of the main distributor 202 after completion. Therefore, the database duplication method can reduce the overall processing time of a transaction.

In particular, the database duplication method of the present invention can significantly reduce the time spent in duplication of the entire database when there are a lot of transactions and a large number of transactions being executed at the same time by generating a large number of reflectors.

Although embodiments of the present invention have been described above with reference to the accompanying drawings, those skilled in the art to which the present invention pertains may implement the present invention in other specific forms without changing the technical spirit or essential features thereof. I can understand that. Therefore, it should be understood that the embodiments described above are exemplary in all respects and not restrictive.

Claims

(A) detecting a query to be processed among the first databases of the main equipment and transmitting the query to a receiver of the redundant equipment including a second database;

(B) extracting, from the query, a slot having a primary key and a transaction ID having a primary key to be applied to a record of the relation stored in the first database;

(C) locking the slot;

(D) transmitting a primary slot having the same primary key among the slots but processing the previous slots in the main distributor, and transmitting the subsequent slots having the same primary key but the lower slots to the partial distributors;

Assigning the preslot and the postslot to a reflector (E);

(F) increasing a main reference count to the reflector to which the previous slot is assigned, and increasing a sub reference count to the reflector to which the rear slot is assigned; And

(G) releasing the lock of the previous slot and the lock of the rear slot.
The method of claim 1,

And the main distributor transmits the previous slots to the reflectors, respectively, to perform the same time, and the partial distributors process the rear slots simultaneously when the previous slots are processed.
The method of claim 2,

And the partial distributor transmits the rear slot having the same primary key as the processed front slot to the reflector that has processed the front slot.
The method of claim 3,

And when the extracted slot is not locked, waiting for the slot to be locked.
A detector for detecting a query to be processed in one relation stored in the first database;

A transmitter for transmitting the query;

A receiver for receiving the query transmitted from the transmitter;

In the query received from the receiver, a slot having a slot ID and a transaction ID is formed, and among all the slots formed, the primary key is the same but all slots processed in the previous order are waiting. A main distributor which transmits the subslot to the partial distributor; And

And a reflector for reflecting the first slot and the second slot in a second database.
The method of claim 5,

The main distributor performs a commit query after an order query is processed, and creates a slot having a single transaction ID.
The method of claim 5,

And the partial distributor performs simultaneous processing of the rear slot when the front slot is processed.
The method of claim 7, wherein

And the partial distributor transmits the rear slot having the same primary key as the primary key of the previous slot to the reflector which processed the previous slot.