JP2009169449A - High-reliability database system, synchronizing method thereof, intermediating method, intermediating device, and intermediating program - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide a synchronism maintaining method for a high-reliability database system that always maintains synchronism between database servers even when a plurality of transactions including updating are executed in parallel. <P>SOLUTION: An intermediating device 20 selects one of a plurality of database servers 10 as a leader and others as followers in advance, and then transmits a processing request received from a client computer 50 only to the leader when receiving the processing request and also transmits the processing request to the followers when receiving a response to the processing request from the leader. <P>COPYRIGHT: (C)2009,JPO&INPIT

Description

The present invention relates to a highly reliable database system that operates a plurality of database servers in parallel, and more particularly to a synchronization technique between servers.

As the first conventional highly reliable database system, for example, the one described in Patent Document 1 is known. As shown in FIG. 21, this system relays processing requests from a plurality of database servers (hereinafter referred to as “servers”) and client computers (hereinafter referred to as “clients”) to each server, and from each server, And an intermediary device that returns one of the responses as a processing result to the client.

As a result of executing multiple update queries, synchronization may be lost between servers. This is because the update order for the same data item is not the same on all servers. When the intermediary device detects a loss of synchronization, a server out of synchronization (hereinafter referred to as “out of synchronization server”) is temporarily disconnected from the system and all of the server data is discarded. The intermediary apparatus creates backup data at a certain point in time from a normally operating server (hereinafter referred to as “normal server”), and holds update queries executed by the normal server after that point as difference information. The mediation apparatus uses the backup data to restore the out-of-synchronization server database, and sequentially executes the difference information. If the execution results of all the difference information are the same as the execution results of the normal server, the out-of-synchronization server can return to the synchronization state again. However, if the execution result of the difference information is different from the execution result of the normal server, the synchronization loss occurs again, so the process starts again from the creation of the backup data.

As the second conventional highly reliable database system, for example, the one described in Non-Patent Document 1 is known. As shown in FIG. 22, this system has a plurality of database servers (hereinafter referred to as “servers”), a client computer (hereinafter referred to as “clients”), a query from the client to the server, and a response to the client. It is composed of a scheduler and a proxy that maintains synchronization by so-called two-phase lock (2PL).

An application executed by a client to implement 2PL must acquire a lock with a LOCK instruction for all tables to be accessed at the beginning of a transaction. At the end of the transaction, the table that has been updated must be unlocked with the UNLOCK instruction. These LOCK and UNLOCK commands are for the proxy and are not sent to the server. When the scheduler receives the LOCK command, it assigns a unique number and sends the number to the proxy along with the LOCK command. The proxy allows access to only one client per table. That is, a response is returned only to the LOCK instruction having the smallest number for one table. The client understands that a lock has been secured for the table by returning a response to the LOCK command, and starts sending a query to be executed by the server. When the proxy receives an UNLOCK command, it releases the lock and grants permission to the LOCK command with the next lower number. That is, a response is returned to the LOCK command.

As described above, even if a plurality of clients try to execute transactions that access the same table in parallel, the proxy permits access to only one transaction, so only one update is executed at most. Therefore, if a plurality of updates are not executed simultaneously in parallel, it is unlikely that the order of the plurality of updates will be changed, so that all servers continue to maintain synchronization.
JP 2007-241325 A “Conflict-aware Scheduling for Dynamic Content Applications”, USENIX 2003

However, the first prior art described above is a method for detecting that synchronization between a plurality of database servers is lost and returning the synchronization state again (hereinafter referred to as “resynchronization processing”). It is not a method of maintaining the synchronization of (so that the synchronization is not lost). The resynchronization process takes time to complete, while the out-of-synchronization server is not backed up by the normal server and the reliability of the system is reduced. During the creation of backup data, the load on the normal server becomes heavy and the client The service to the client must be stopped until the service is delayed or the creation of backup data is completed, the resynchronization process may fail, and if it fails, it must be started from the beginning, even if it tries again and again, it will fail There is a possibility that resynchronization processing may be repeated indefinitely, and it is impossible to distinguish between failure occurrence and loss of synchronization, and wasteful resynchronization processing will operate at the time of failure occurrence (resynchronization processing to recover from failure) Does not make sense). Synchronization between servers occurs when a write conflict occurs (update queries issued from multiple clients attempt to update the same data item at the same time). Specific examples are shown below.

FIG. 23 shows a case where transactions T1 and T2 shown in FIG. 24 are executed in parallel with respect to a table named test_table. T1 updates the row with id = 1, and T2 updates the row with id = 1 and 2. That is, a write conflict occurs with id = 1. The final result depends on which transaction first acquires the lock with id = 1.

There are two possible orders for locking, T1 taking first and T2 taking first. For example, when T1 and T2 are executed in parallel and the transaction isolation level is SERIALIZEABLE, if T1 first locks, the final result of id = 1 is dep = 1 (T2 is SERIALIZEABLE failure and T2's UPDATE is Invalid), dep = 2 if T2 has previously locked (T1 is SERIALABLE failure and T1's UPDATE is invalid).

Therefore, when T1 and T2 are executed in parallel on a plurality of servers, if one server acquires the lock first by T1 and another server acquires the lock first by T2, the two databases are synchronized (same data The state of holding the image) is broken, and the resynchronization processing having the above problem is executed.

In addition, this conventional technique also has a problem that a different response is returned although the synchronization is not lost. FIG. 25 shows a case where transactions T3 and T4 shown in FIG. 25 are executed in parallel with the test_table shown in FIG. The result of SELECT changes depending on the timing of execution of COMMIT of T3 and SELECT of T4.

If a T4 SELECT is executed before a T3 COMMIT is executed in a certain server, the SELECT result is dep = 3. On the other hand, if another server executes T3 SELECT after executing T3 COMMIT, dep = 1. When failure detection is performed by comparing results or majority voting in the mediation device, there is a problem that the synchronization is not broken, the failure is not occurring, and it is misunderstood that an abnormality has occurred even though it is in a normal state.

Furthermore, the second prior art described above limits only one transaction that can be accessed to maintain synchronization among a plurality of database servers. Therefore, synchronization is maintained by preventing write conflicts by sequentially executing update queries that perform write conflicts one by one when they are executed in parallel. However, since it is not known if the server does not execute the write conflict (for example, it is not known whether the write conflict occurs simply by comparing T1 and T2 in FIG. 24). Must be executed sequentially one by one. Therefore, there is a problem that the throughput is lowered. In addition, it is necessary to describe a LOCK instruction and an UNLOCK instruction, which are unique instructions only for this system, and there is a problem that existing applications cannot be diverted. Specific examples are shown below.

The case where transactions T5 and T6 shown in FIG. 26 are executed in parallel with the test_table of FIG. Since T5 updates the row with id = 4 and T6 updates the row with id = 5, it indicates that this conventional technique cannot do this even though it can be executed in parallel. First, in order for the proxy to realize 2PL, T5 and T6 in FIG. 26 must be modified to T50 and T60 in FIG. 27, respectively.

It is assumed that the scheduler first receives a T50 LOCK command out of two LOCK commands. The scheduler gives a unique number and forwards it to the proxy. Since there is no transaction holding the lock of test_table, the proxy gives a lock to T50 and replies to the client via the scheduler. A TLOCK LOCK instruction that arrives at the scheduler later is given another number and forwarded to the proxy. Since the lock of test_table is not free, this LOCK instruction is held by the proxy. Since the client executing T60 does not receive a response from the proxy, the client waits for a response without executing the next query.

The client that is executing T50 that has received a response transmits UPDATE and SELECT in order, and finally transmits an UNLOCK command. The proxy receives this UNLOCK instruction, releases the lock on T50, and passes the lock to the transaction with the next lower number. Since there is no transaction waiting for only T60, a lock is given to T60 and a response is returned to the client.

The client that is executing T60 who has received a reply can finally transmit the next UPDATE here.

In other words, by sequentially executing update queries one by one, the replacement of updates is eliminated and synchronization is maintained. However, there is a problem that the performance is deteriorated because an update query that can be executed in parallel without writing conflict like T50 and T60 cannot be executed in parallel. Further, since UPDATE cannot be executed, a query following UPDATE (for example, a SELECT query in the case of T50 and T60) cannot be executed in parallel, and the deterioration in performance cannot be ignored. Furthermore, the server can be used without modification, but the client application must be modified.

The present invention has been made in view of the above circumstances, and an object of the present invention is to provide a highly reliable database that executes a plurality of update queries in parallel to prevent performance degradation and at the same time always maintains synchronization between database servers. It is to provide a synchronization maintaining method in a system. Another object of the present invention is to provide a synchronization method for synchronizing a database server that is out of synchronization with a database server that is operating normally by minimizing the service stop time to the client. Since the present invention can be used without modifying any existing database server or application, it is practical at low cost.

In order to achieve the above object, in the present invention, a plurality of database servers having a function of executing only one processing request and waiting for others when a plurality of processing requests try to access the same data at the same time, and a client computer In the highly reliable database system including the intermediary device that relays the processing request to one or more database servers and returns one of the legitimate responses from the database server as a processing result to the client computer, One of the plurality of database servers is selected in advance as a leader and the rest as a follower. When a processing request is received from a client computer, the processing request is transmitted only to the leader, and a response to the processing request is sent from the leader. Once received A control unit for transmitting the processing request to Ollower.

According to such a system, a plurality of update queries can be executed in parallel to prevent performance degradation, and at the same time, synchronization can be constantly maintained between database servers.

In the present invention, there is a difference information storage unit that stores a processing request from a client computer as difference information, and there is a synchronization request for a database server out of synchronization (hereinafter referred to as “out of synchronization database server”). , Start creating backup data from the database server that is operating normally, store the processing requests received from the client computer in the order in which the responses from the reader are received in the difference information storage unit, and complete the backup data creation The out-of-synchronization database server is restored using data, and when restoration of the database from the backup data is completed in the out-of-synchronization database server, the processing requests stored in the difference information storage unit are sequentially sent to the out-of-synchronization database server.

According to such a system, the out-of-synchronization database server can be synchronized with a normally operating database server while minimizing the service stop time for the client. In addition, the execution order is different from the leader during execution of the processing request stored in the difference information storage unit, and synchronization does not fail.

Furthermore, in the present invention, since existing database servers and applications can be used without modification, they are practical at low cost.

  As described above, according to the present invention, there is provided a synchronization method in a highly reliable database system in which a plurality of update queries are executed in parallel to prevent performance degradation, and at the same time, synchronization is always maintained between database servers. Another object of the present invention is to provide a synchronization method for synchronizing a database server that is out of synchronization with a database server that is operating normally while minimizing the service stop time for the client. Since the present invention can be used without modifying any existing database server or application, it is practical at low cost.

(First embodiment)
A highly reliable database system according to a first embodiment of the present invention will be described with reference to the drawings. FIG. 1 is a block diagram illustrating the overall configuration of a highly reliable database system according to the present embodiment.

  As shown in FIG. 1, this highly reliable database system is a system in which a plurality of database servers (hereinafter referred to as “servers”) 10 and an intermediary device 20 are connected via a network 30. The client computer (hereinafter referred to as “client”) 50 is accessed. In this embodiment, as shown in FIG. 1, two servers 10a and 10b are provided and accessed from two clients 50m and 50n. In the following description, subscripts “a” and “b” are added to distinguish each server 10 from other servers 10. “M” and “n” are also added to the client 50. In the present invention, neither the server nor the client needs to be modified for the present invention, and the existing one can be used without modification.

One of a plurality of servers is selected as a leader and the rest is a follower. The leader may select any server. In this embodiment, 10a is a leader and 10b is a follower. The server 10 is an RDBMS that performs processing via SQL. There are various types of such servers 10, for example, PostgreSQL (http://www.postgres.org/) of open source software and Oracle (registered trademark) by Oracle (http://www.oracle.com). ) And the like.

When a server has a write conflict (update queries issued by multiple clients trying to update the same data item at the same time), only one update query is allowed to execute, and other update queries are blocked. use. A specific example is shown in FIG. FIG. 2 shows an example in which the clients 50m and 50n execute the transactions T1 and T2 of FIG. 24 on the table test_table of FIG. The UPDATE of T1 and T2 has a write conflict with id = 1.

The client 701n transmits BEGIN (step S700), and the server 700 executes it and returns a response (step S701). The client 701m also transmits BEGIN (step S702), and the server 700 executes it and returns a response (step S703). The client 701n and the client 701m transmit UPDATE to the server 700 almost simultaneously (steps S704 and S705). Since two UPDATEs have a write conflict, only one is executed and the rest are blocked. In the case of FIG. 2, the transaction T2 being executed by the client 701n secures the lock of id = 1 and the UPDATE response is returned to the client 701n (step S706), but the transaction T1 of the client 701m cannot secure the lock. Will be blocked and no UPDATE response will be returned. The timing for releasing the lock is when the transaction T2 that secures the lock is COMMIT or ABORT and the transaction ends. In the case of FIG. 2, the client 701n transmits COMMIT to the server 700 (step S707), and the server 700 executes it to release the lock with id = 1. Accordingly, in addition to returning a response to COMMIT to the client 701n (step S708), the server 700 executes the blocked UPDATE and returns the result to the client 701m (step S709).

The server implements several transaction isolation levels. Among them, the strictest data consistency is SERIALIZEABLE. SERIALIZABLE is a kind of snapshot isolation.

A transaction that operates with snapshot isolation creates a snapshot that is a copy of the entire database when the transaction starts. This snapshot includes changes for other committed transactions, but does not include changes for uncommitted transactions. All queries in the transaction are executed on this snapshot. When the transaction is COMMIT, the change to the snapshot is reflected in the database body. If a write conflict occurs, only one can succeed and COMMIT. A specific example is shown using FIG.

FIG. 3 shows that time goes to the right. There are four transactions T10, T11, T12, T13. B, S, C, and A represent BEGIN execution, snapshot acquisition, COMMIT execution, and ABORT execution, respectively. “Id = 1” represents the updated object. The snapshot acquired by the transaction T11 does not include the update of the transaction T10 (because it is not COMMIT at the time of snapshot acquisition). T10 and T11 have a write conflict because the same id = 1 is updated. When T10 is COMMIT, other write conflict transactions fail and T11 aborts. Transaction T12 does not include an update of transaction T10 or an update of T11. Since the update target is different, T12 can be COMMIT without a write conflict. The snapshot of transaction T13 includes an update of transaction T10 and an update of T12. Since id = 1 is updated but no write conflict occurs (because it is not executed in parallel with T10 or T11), COMMIT is possible.

In the present embodiment, PostgreSQL operating in SERIALIZEABLE will be specifically described.

The address disclosed by the mediation device 20 to the network 40 side is a virtual database server for the client 50. That is, the client may perform processing by regarding the intermediary device 20 as a database server itself. This is because, regardless of how many servers 10 are operating, whether there is a server that is down due to a failure, even if it is recovered from a failure and reintegrated into the system, the client 50 is conscious of any system. It is possible to communicate with the intermediary device 20 without having to do so (without changing settings or the like).

The intermediary device 20 transmits a query received from the client to the server 10 according to a synchronization maintaining algorithm described later, and returns one of normal responses to the client.

The client 50 transmits a query to the database system.

Next, the synchronization maintaining algorithm will be described. This algorithm assumes a database system with the following two features.
(A) The client transmits a new query after receiving a response to the already transmitted query.
(B) In the case of a write conflict, only the update query of a transaction that can acquire a lock can be executed, and the update query of a transaction that cannot acquire a lock is blocked until a lock is acquired.

Here, in addition to queries that require row locks when updating data items such as UPDATE and DELETE, queries that affect the operation of these queries are also handled as update queries. For example, SELECT FOR UPDATE does not involve updating data items in its execution, but requires a row lock for its execution, and when it is executed simultaneously with UPDATE or DELETE, a row lock conflict occurs, so it is treated as an update query.

In order to maintain synchronization on all servers, it is necessary to ensure that the update order for the same data item is the same on all servers. This order guarantee needs to guarantee not only update queries belonging to different transactions but also update queries in the same transaction. In the database system having the above two conditions, the server / client query is controlled by the following three rules to ensure the order and maintain the synchronization.
(1) The intermediary device 20 transmits all update queries received from the client 50 only to the leader. The order of transmission to the leader is arbitrary.
(2) When the intermediary device 20 receives a response to the update query from the reader, the mediation device 20 transmits the update query to all followers.
(3) When the intermediary device 20 receives responses from all the database servers, it returns a response to the client.

(A) and (3) guarantee the uniqueness of the order of update queries belonging to the same transaction, and (b), (1) and (2) guarantee the uniqueness of the order of update queries belonging to different transactions. . Queries other than update queries are sent to all servers without distinction between leader and follower.

As shown in FIG. 4, the intermediary device 20 accepts a connection request from the client 50 and connects to all servers 10, a connection processing unit 21 that actually connects to the client and passes connection information to the vacant control unit 22. And a control unit 22 that controls transmission and reception of queries and responses according to the above algorithm. In the present embodiment, there are three control units 22x, 22y, and 22z. In the following description, the subscripts “x”, “y”, and “z” are added to distinguish each control unit 22 from other control units 22.

Next, the operation of the control unit 22 will be described with reference to the flowcharts of FIGS. When receiving the query from the client 50 (step S1), the control unit 22 checks whether or not the snapshot has been created (step S2).

If a snapshot has not been created (step S2), it is confirmed whether the query is a query for creating a snapshot (step S3). A query that creates a snapshot is a query that accesses a data item that appears at the beginning of a transaction. That is, for example, in the case of the transaction T1 in FIG. 24, it is not BEGIN but SELECT. If the query is for creating a snapshot in step S3, a snapshot creation routine is executed (step S4), and the query is received again from the client 50 (step S1). If it is not a query for creating a snapshot in step S3, a normal routine is executed (step S5), and the query is received again from the client 50 (step S1).

If a snapshot has already been created in S2, it is checked whether it is an update query (step S6). If it is an update query, a synchronization maintenance routine (step S7) is executed and a query is received again from the client 50 (step S1). . If it is not an update query in step S6, it is checked whether it is a commit query (step S8). If it is a commit query, a commit processing routine is executed (step S9), and a query is received again from the client 50 (step S1). When the query is not a commit query in S8, the normal routine S5 is executed and the query is received again from the client 50 (step S1).

The normal routine will be described with reference to the flowchart of FIG. The query is transmitted to all servers 10 (step S10), and responses are received from all servers 10 (step S11). A valid response is determined by majority decision (step S12), and one of the valid responses is transmitted to the client 50 (step 13).

The snapshot creation routine will be described with reference to the flowchart of FIG. Snapshot creation and commit execution must be controlled exclusively. For this purpose, a commit counter and a snapshot creation counter are used. When the commit counter is 1 or more, it means that there is a transaction that is being committed. Similarly, if the snapshot creation counter is 1 or more, it means that there is a transaction for which a snapshot is being created. By controlling so that both do not become 1 or more, it is ensured that there are no transactions that are creating snapshots and transactions that are executing commits. A common lock (for example, pthread_lock) is used to read and write these counters.

If the commit counter is not 0 (step S20), there is a commit-executed transaction, and sleeps (step S21). When the wake up occurs, it is checked again whether the commit counter is 0 (step S20). If the commit counter is 0, it means that there is no transaction in the middle of commit execution, so that the snapshot creation counter is incremented by 1 to indicate that the snapshot is being created (step S22), and snapshot creation is started. Snapshot creation is realized by executing a valid query. In the case of an update query, synchronization must be maintained simultaneously with creation of the snapshot. That is, it is checked whether it is an update query (step S23). If it is not an update query, a normal routine is executed (step S5), and if it is an update query, a synchronization maintenance routine is executed (step S7). Since the snapshot creation is completed regardless of which routine is executed, the snapshot creation counter is decremented by 1 to indicate that fact (step S24). If the snapshot creation counter is 0 (step S25), the controller 23 waiting for the snapshot creation counter to become 0 is waked up (step S26).

The synchronization maintaining routine will be described with reference to the flowchart of FIG. A query is transmitted only to the leader among the servers 10 (step S30). When a response is received from the leader (step S31), a query is transmitted to all followers (step S32). When responses are received from all followers (step S33), a correct response is determined by majority vote (step S34), and one of the correct responses is returned to the client 50 (step S35).

The commit processing routine will be described with reference to the flowchart of FIG. As described above, snapshot creation and commit execution are controlled exclusively.

If the snapshot creation counter is not 0 (step S40), there is a transaction for which a snapshot is being created, and the process sleeps (step S41). When the wake up occurs, it is checked again whether the snapshot creation counter is 0 (step 40). If the snapshot creation counter is 0, it means that there is no transaction that is creating a snapshot. Therefore, the commit counter is incremented by 1 to indicate that the commit is being executed (step 42). enter. When the commit execution is finished, the commit counter is decremented by 1 to indicate that fact (step 43). If the commit counter is 0 (step S44), the control unit 23 waiting for the commit counter to become 0 is waked up (step S45).

Next, with reference to the sequence diagram of FIG. 10, it will be described that synchronization is maintained even when a transaction including an update query that causes a write conflict is executed. In other words, it indicates that it is guaranteed that the execution order of update queries that have write conflicts is always the same in the leader and the follower. A case where the clients 50m and 50n are executing the transactions T1 and T2 of FIG. 24, respectively, is shown. Assume that the connection processing unit 21 of the mediation apparatus 20 assigns control units 22x and 22y to the clients 50m and 50n, respectively.

The client 50m transmits a BEGIN query and the control unit 22x receives it (step 50). The control unit 22x transmits a query to all the servers 10 (steps S51 and 52), receives responses from all the servers 10 (steps S53 and 54), and returns one of the valid responses to the client 50m (steps). S55). The same applies to the client 50n (steps S56 to S61).

Next, the clients 50m and 50n transmit an UPDATE query that causes a write conflict almost simultaneously (steps S62 and S63). Receiving the query, the control units 22x and 22y transmit the query only to the leader (steps S64 and S65). The execution order of the queries received at almost the same time in the reader is not necessarily the same as the order of transmission by the control unit 22. The order may be changed in the communication path between the control unit 22 and the server 10, and the order may be changed by the scheduler of the server 10. In this example, the transaction T1 executed by the client 50m indicates that the lock is secured first and the update query is executed, and the control unit 22x receives a response (step S66). On the other hand, the transaction T2 of the client 50n that could not secure the lock blocks until the lock can be secured. That is, the update query is not executed and held by the leader.

The control unit 22x that has received the response then transmits an update query to the follower (step S67). When receiving a response from the follower (step 68), the control unit 22x returns one of the valid responses to the client 50m (step S69).

The client 50m transmits a commit query, and the control unit 22x receives it (step S70). The control unit 22x sends a query to all the servers 10 (steps S71 and 72), receives responses from all the servers 10 (steps S73 and 74), and returns one of the valid responses to the client 50m (steps). S75).

When the commit of the transaction T1 is executed, the write lock of i = 1 held by the T1 is released, the transaction T2 waiting for the release secures the lock, the update query is executed by the reader, and the result is the control unit. Returned to 22y (step S76). The control unit 22y also transfers this query to the follower (step S77) and receives a response (step S78). The control unit 22y returns UPDATE failure to the client 50n (step S79).

As described above, when the response to the update query transmitted to the reader is received, control is performed so that the update query is transmitted to the follower, so that the execution order of the update query that causes a write conflict can be made the same in each server 10. Can be maintained.

Next, it will be described with reference to the sequence diagrams of FIGS. 11 and 12 that the SELECT result is unique without the execution order of SELECT and COMMIT being different for each server. A case where the clients 50m and 50n are executing the transactions T3 and T4 of FIG. 25, respectively, is shown. Assume that the connection processing unit 21 of the mediation apparatus 20 assigns control units 22x and 22y to the clients 50m and 50n, respectively.

The client 50m transmits a BEGIN query, and the control unit 22x receives the response, transfers it to all servers, receives a response, and returns a response to the client 50m (steps S200 to S205). UPDATE is also executed by the leader and follower, and a response is returned to the client 50m (steps S206 to S211). Similarly, BEGIN is executed for the client 50n (steps S212 to S217).

The client 50m transmits COMMIT and the control unit 22x receives it (step S218), and almost simultaneously, the client 50n transmits SELECT and the control unit 22y receives it (step S219). The control units 22x and 22y start to execute the commit processing routine of FIG. 9 and the snapshot creation routine of FIG. 7, respectively, but the snapshot creation counter check (step S40) and the commit counter change (step S42) of FIG. Since the exclusive control is performed with the check of the commit counter (step S20) and the change of the snapshot counter (step S22) in FIG. 7, they are not executed at the same time.

In the case of FIG. 11, it is assumed that the control unit 22x executes this critical region first. That is, since the snapshot creation counter is 0 (step S40), the control unit 22x changes the commit counter to 1 (step S42), and transmits COMMIT to all the servers 10 (steps S220 and 221). When the control unit 22y checks the commit counter (step S20), the control unit 22y sleeps because it is not 0 (step 21).

When the control unit 22x receives COMMIT success (steps S222 and 223), the commit counter is changed to 0 (step S43). Since the commit counter is 0 (step S44), the controller 22y is waked up (step S45), and a COMMIT success is transmitted to the client 50m (step S224).

When the control unit 22y checks the commit counter again (step S20), it is 0, so the snapshot creation counter is changed to 1, and the SELECT is transmitted to all servers (steps S225 and S226). Dep = 1 reflecting the change of T3 is received from all servers (steps S227 and S228), and dep = 1 is returned to the client 50n (step S229).

FIG. 12 illustrates a case where the control unit 22y first executes the critical region. Steps S200 to S219 are the same as those in FIG. Since the commit counter is 0 (step S20), the control unit 22y changes the snapshot creation counter to 1 (step S22), and transmits SELECT to all the servers 10 (steps S250 and 251). When the snapshot creation counter is checked (step S40), the control unit 22x sleeps because it is not 0 (step 41).

Since the server 10a does not execute the COMMIT of the transaction T3, the value of dep remains old. Therefore, the control unit 22y receives dep = 3 from the server 10a (step S252). Similarly, the control unit 22y receives dep = 3 from the server 10b (step S253). When the controller 22y transmits dep = 3 to the client 50n (step S254), it sets the snapshot creation counter to 0 (step S24). Since the snapshot creation counter is 0 (step S25), the controller 22x is waked up (step S26).

When the control unit 22x checks the snapshot creation counter again (step S40), since it is 0, the commit counter is changed to 1, and COMMIT is transmitted to all servers (steps S255 and S256). COMMIT success is received from all the servers (steps S257 and S258), and COMMIT success is returned to the client 50m (step S259).

Next, with reference to the sequence diagram of FIG. 13, it will be described that when a transaction including an update query that does not cause a write conflict is executed, the update query can be executed in parallel while maintaining synchronization. A case where the clients 50m and 50n are executing the transactions T5 and T6 of FIG. 26, respectively, is shown. Assume that the connection processing unit 21 of the mediation apparatus 20 assigns control units 22x and 22y to the clients 50m and 50n, respectively.

The client 50m transmits a BEGIN query, and the control unit 22x receives the response, transfers it to all servers, receives a response, and returns a response to the client 50m (steps S100 to S105). The same applies to the client 50n (steps S106 to S111).

Next, the clients 50m and 50n transmit an UPDATE query that does not cause a write conflict almost simultaneously (steps S112 and S113). The control units 22x and 22y that have received the query transmit the query only to the leader (steps S114 and S115). Since the update target rows of the update queries are different, both the control units 22x and 22y receive responses (steps S116 and S117). The control units 22x and 22y also send a query to the follower, receive the response (steps S118 to S121), and return the response to the client 50 (steps S122 and 123).

As described above, when the response to the update query transmitted to the reader is received, control is performed so that the update query is transmitted to the follower, so that the execution order of the update query that causes a write conflict can be made the same in each server 10. Can be maintained. In addition, since update queries that do not cause write conflicts can be executed in parallel, there is no performance degradation. Furthermore, since the SELECT results returned from the servers match, it is not erroneously determined as a failure. Furthermore, since existing servers and clients can be used without modification, it is practical at low cost.

(Second Embodiment)
A highly reliable database system according to the second embodiment of the present invention will be described. This embodiment is different from the first embodiment in that a server out of synchronization is set in a synchronized state (hereinafter referred to as “synchronization processing”). Reasons for being out of synchronization include a case where the operation has stopped due to a failure or a case where a server 10 is newly added. Since other configurations and operations are the same as those of the first embodiment, only the differences will be described here.

Here, the case where the server 10c is synchronized with 10a and 10b from the state where the server 10c is out of synchronization among the three servers 10a to 10c will be described.

The intermediary device 20 in FIG. 4 is changed to the intermediary device 23 in FIG. The mediation device 23 has a connection processing unit 21 and a control unit 22 in the same manner as the mediation device 20, and also creates a backup data creation unit that creates backup data of a normal server at this point (in this case, the server 10 a or the server 10 b). 24, a buffer 25 for holding queries executed by the reader after that time, and a management unit 26 for managing them.

The backup data creation unit 24 creates a backup of normal server data at the time point specified by the management unit 26. The buffer 25 stores the queries in the order in which the response is received from the leader according to the instruction from the management unit 26 (equal to the order in which the response is transmitted to the follower). When the management unit 26 receives an instruction to start the synchronization process, the management unit 26 gives an appropriate instruction to each unit. In the initial state, the buffer 25 is empty.

As shown in FIG. 15, the client 50m starts a transaction (steps S300 to S305). When the management unit 26 receives the synchronization instruction, the management unit 26 instructs the connection processing unit 21 not to start a new transaction with the new connection from the client 50 being suspended (step S306). The synchronization instruction is sent to the management unit 26 when the system administrator inputs it from a keyboard connected to the mediating apparatus or from a management terminal (not shown) via a network. The management unit 26 waits for the end of the already started transaction. Specifically, the client 50m executes UPDATE (steps S307 to S312), and waits until COMMIT is executed (steps S313 to S318).

Since there is no transaction being executed in the server 10, the management unit 26 instructs the backup data creation unit 24 to take a backup of the normal server (step S319). Upon receiving the instruction, the backup data creation unit 24 selects one of the normal servers 10 and creates backup data. In this example, backup data for the server 10b is created. As a method for creating the backup data, for example, in the case of PostgreSQL, pg_dump is used. This tool has a feature that even if a new update is executed while creating backup data, the update is not reflected in the backup data.

The management unit 26 that has confirmed the start of backup data acquisition instructs the connection processing unit 21 to resume the new connection from the client 50. The connection processing unit 21 processes the connection request from 50n, and the control unit 22 receives BEGIN (step S320). The control unit 22 transmits BEGIN to the server 10 (steps S321 and S322) and receives a response from the server 10 (steps S323 and S324). Upon receiving the response, the control unit 22 saves the BEGIN query in the buffer 25 (step S325) and returns the response to the client 50n (step S326). Here, the timing of saving in the buffer 25 is the time when a response is received from the leader.

Next, as shown in FIG. 16, the client 50n transmits SELECT (step S327), the control unit 22 transfers it to the server (S328, S329), receives a response from the server 10 (S330, S331), and enters the buffer 25. The SELECT is saved (step S332), and a response is returned to the client 50n (step S333).

Here, the backup data creation unit 24 notifies the management unit 26 that the acquisition of the backup data has been completed (step S334), and the management unit 26 that receives the backup data creates a database using the backup data. (Step S335). The server 10c starts database construction using psql or the like (step S336).

The client 50n transmits UPDATE (step S337). The control unit 22 transmits UPDATE to the server 10a, which is a leader (step S338), and receives a response from the server 10a (step S339). At this point, the control unit 22 stores UPDATE in the buffer 25 (step S340). The reason why the responses are accumulated in the buffer in the order in which the responses are received from the leader is to synchronize with the leader by executing a query with the server 10c as a follower.

The control unit 22 transmits UPDATE to the server 10b as a follower (step S341), receives a response from the server 10b (step S342), and returns a response to the client 50n (step S343).

Here, the database construction of the server 10c is completed (step S344), and the management unit 26 knows this (step S345). However, the server 10c has been restored to the databases 10a and 10b reflecting the steps up to step S318, and is not in a state synchronized with the current 10a and 10b. In order to achieve the synchronized state, it is necessary to execute the query after step S320. Those queries are stored in the buffer 25.

The control unit 22 executes the queries in order from the top of the buffer. Since the order stored in the buffer is stored in the order in which the follower should be executed, if the data is transmitted to the server 10c and executed in this order, it is always synchronized with the servers 10a and 10b. The management unit 26 instructs the control unit 22 to execute the query in the buffer (step S346). The control unit 22 executes the queries in order from the top of the buffer 25 (steps S347 to S352). When the buffer 25 becomes empty, the server 10c is synchronized with the servers 10a and 10b and is added to the system.

As described above, by applying the first embodiment, it is possible to synchronize a server that is out of synchronization with the leader with minimal service outage time. That is, it is possible to synchronize with the leader by executing a query after a certain point in the server 10 restored to a certain point using the backup data. Since the query after a certain point here reproduces the execution of the follower, it can be synchronized with the reader without failing in synchronization. Other effects are the same as those in the first embodiment.

In the present embodiment, all queries are stored in the buffer 25, but only queries that change the state of the database may be stored. For example, since simple data read does not change the state of the database, accumulation in the buffer 25 may be omitted. However, even a simple read cannot be omitted if it is the first valid query in the transaction and is a trigger to create a snapshot. With such a device, the buffer 25 can be saved.

In this embodiment, after receiving the synchronization instruction, the creation of the backup data is started after creating a state in which there is no transaction being executed. However, before the synchronization instruction is received, the query is saved in advance. If so, the creation of backup data can be started immediately after receiving the synchronization instruction.

For example, in FIG. 15, if the BEGIN query (step S300) is stored in the buffer in advance, the backup data can be created when the synchronization instruction is received (step S306). This is because the backup data does not include an uncommitted transaction starting from step S300, and it is only necessary that the query of the transaction is stored in the buffer.

As one method of storing in the buffer in advance, a method of storing all the queries in the buffer can be considered. However, this method consumes more and more buffer. Therefore, if the transaction query that has been committed and reflected in the database is deleted from the buffer, the buffer will not be devoured.

With these devices, the service stop time can be completely eliminated.

(Third embodiment)
A highly reliable database system according to the third embodiment of the present invention will be described. This embodiment is different from the first embodiment in query transmission timing. Since other configurations and operations are the same as those in the first embodiment, only the differences will be described here.

In the first embodiment, since majority vote is performed, (3) is a necessary condition. However, due to (3), the performance of the entire system will be pulled to the server with the lowest performance (the response will not be returned to the client unless the slowest response is returned). It becomes. Therefore, this disadvantage can be solved by returning the response to the client immediately when the response from the leader is returned, assuming that the response from the leader is always correct, instead of the majority vote.

However, in the first embodiment, the order of update queries for updating the same data in the same transaction is guaranteed by the conditions (a) and (3). By making the above improvements, it is necessary to guarantee the uniqueness of the order of update queries that update the same data in the same transaction by other methods. Therefore, (a) and (3) are deleted and (1) and (2) are changed to the following (I) and (II), respectively.

(I) If the mediation device 20 receives a new update query from the client 50, there are servers 10 that have not returned a response to another old update query that has already been transmitted to the server 10, and those update queries. Are in the same transaction, the new update query is held in the mediating apparatus 20. When the response is received, a new update query is transmitted only to the leader.
(II)) When the intermediary device 20 receives the update query response from the leader, it transmits the update query to all followers and simultaneously returns the response to the client 50.

Note that the interruption of transmission to the leader in (I) is for update queries in the same transaction. There are cases where the client 50 executes a plurality of transactions, but if the transactions are different, the transactions are executed in parallel without interrupting the transmission in (I) to achieve high performance.

In order to satisfy the above rules (I) and (II), the entire routine of FIG. 5, the snapshot creation routine of FIG. 7, and the commit processing routine of FIG. 9 are not changed, but the normal routine of FIG. 6 and the synchronization of FIG. The maintenance routine changes to FIGS. 17 and 18, respectively.

In the normal processing routine of FIG. 17, the majority vote (step S12) is merely eliminated. In step S13 in which one of the correct responses is transmitted to the client 50, a leader response is transmitted.

The synchronization maintaining routine of FIG. 18 transmits the update query to the leader (step S30) and the same process until the response is received from the leader (step S31), but immediately transmits the response from the leader to the client 50 (step S36). ), It is checked whether there is an update query transmitted earlier (step S37). If there is not, the current update query is transmitted to all followers (step S39). If there is, all the responses of the update query transmitted earlier are received (step S38), and then the current update query is sent to all followers. Transmit (step S39).

In addition, execution of a query that does not change the state of the database such as SELECT may be executed by any one server. As a result, the load can be distributed, and the performance of the entire system is improved. Specifically, as shown in FIG. 19, a query is transmitted to one server 10 (step S80), a response is received from the server 10 (step S81), and the response is transmitted to the client 50 (step S82). ). When the load distribution routine of FIG. 19 is used, the entire routine of FIG. 5 is changed as shown in FIG.

As described above, the response time of the system can be shortened, and the throughput of the entire system can be increased. Further, the throughput can be further increased by introducing load balancing. Other effects are the same as those in the first embodiment.

In addition, although this Embodiment was demonstrated as a modification of 1st Embodiment, it cannot be overemphasized that the same deformation | transformation can be applied in 2nd Embodiment.

As mentioned above, although embodiment of this invention was explained in full detail, the said embodiment is an illustration and this invention is not limited to this. The scope of the invention is set forth in the appended claims, and all modifications that come within the meaning of the claims are intended to be embraced by the invention. Note that the following modifications can be applied to the above-described embodiments in appropriate combinations.

In the above embodiment, SERIALIZEABLE has been described as the transaction isolation level, but other transaction isolation levels may be used. SERIALIZABLE does not change the snapshot after the first snapshot is created, but for example, in the case of READ COMMITTED, it may be modified so that the snapshot is reacquired every time COMMIT is executed. .

In SERIALIZEABLE, the read does not conflict with other queries, but in the case of conflict with the write, the transaction isolation level may be such that the read blocks the write. In this case, not only the update query but also a read query such as SELECT may be handled as an update query and executed by the synchronization maintaining algorithm.

In the above embodiment, the mediation device is described as an independent device. However, the program realizing the function of the mediation device and the database server may be operated on the same computer. This eliminates the need for hardware of the mediation device, and has the effect of reducing costs.

A plurality of mediation devices may be prepared. This further increases the reliability of the entire system.

In the above embodiment, the server selected as the leader or follower is not always changed, but may be changed. For example, when a leader breaks down, the leader may be disconnected from the system, and a new leader may be selected from the followers. As a result, resistance to failure is increased and higher reliability can be realized.

  The present invention can be applied to a highly reliable database system in which a plurality of database servers are operated in parallel.

Configuration diagram of a highly reliable database system according to the first embodiment Diagram explaining write conflict Diagram explaining snapshot isolation The block diagram of the mediation apparatus which concerns on 1st Embodiment The flowchart explaining the whole operation | movement of the mediation apparatus which concerns on 1st Embodiment. Flowchart for explaining a normal routine according to the first embodiment Flowchart for explaining a snapshot creation routine according to the first embodiment Flowchart for explaining a synchronization maintaining routine according to the first embodiment Flowchart for explaining a commit processing routine according to the first embodiment Sequence chart explaining operation in case of write conflict Sequence chart for explaining the operation when the result of SELECT changes depending on the execution order of COMMIT and SELECT Sequence chart for explaining the operation when the result of SELECT changes depending on the execution order of COMMIT and SELECT Sequence chart explaining the operation when there is no write conflict The block diagram of the mediation apparatus which concerns on 2nd Embodiment Sequence chart explaining the operation when synchronizing servers that are out of sync Sequence chart explaining the operation when synchronizing servers that are out of sync Flowchart for explaining a normal routine according to the third embodiment Flowchart for explaining a synchronization maintaining routine according to the third embodiment Flowchart for explaining a load distribution routine according to the third embodiment The flowchart explaining the whole operation | movement of the mediation apparatus which concerns on 3rd Embodiment. Configuration diagram of a conventional highly reliable database system Configuration diagram of a conventional highly reliable database system An example of a table held by the database server An example of a conflicting transaction An example of a transaction whose execution order is a problem An example of a transaction that does not conflict Example of transaction modified from FIG. 26 for a conventional highly reliable database system

Explanation of symbols

DESCRIPTION OF SYMBOLS 10 ... Server, 20, 23 ... Mediation apparatus, 21 ... Connection processing part, 22 ... Control part, 24 ... Backup data creation part, 25 ... Buffer, 26 ... Management part, 30, 40 ... Network, 50 ... Client

Claims (12)

When multiple processing requests try to access the same data at the same time, only one processing request is executed and the others are waiting, and processing requests from client computers are relayed to one or more database servers In addition, in a highly reliable database system including an intermediary device that returns one of the legitimate responses from the database server as a processing result to a client computer, a method of maintaining synchronization between the database servers,
The intermediary device selects in advance one of the plurality of database servers as a leader and the rest as a follower,
When a processing request is received from a client computer,
(A) Send the processing request only to the leader,
(B) When a response to the processing request is received from the reader, the processing request is transmitted to the follower. When the intermediary device receives a processing request (hereinafter referred to as a “new processing request”) from the client computer in step (a), the intermediary device has already transmitted another processing request (hereinafter referred to as an “old processing request”) to the server. If there is a server that has not returned a response to the message and the new processing request and the old processing request belong to the same transaction, the new processing request is held in an intermediary device, and when the response is received, the new processing request is The synchronization method in the highly reliable database system according to claim 1, wherein the synchronization method is transmitted only to the database. 3. The synchronization in the highly reliable database system according to claim 1, wherein the intermediary device returns one of the responses to the client computer when receiving all the responses from the follower that transmitted the processing request. Method. The synchronization method in the highly reliable database system according to any one of claims 1 to 3, wherein the client computer transmits a new processing request after receiving a response to the processing request that has already been transmitted. The intermediary device includes a difference information storage unit that stores processing requests from client computers as difference information, and when there is a synchronization request for a database server that is out of synchronization (hereinafter referred to as “out of synchronization database server”), Start creation of backup data from the operating database server, store processing requests received from the client computer in the difference information storage unit in the order in which the responses from the reader are received, and when the backup data creation is completed, the backup data is stored The out-of-synchronization database server is used, and when restoration of the database from the backup data is completed in the out-of-synchronization database server, the processing requests stored in the difference information storage unit are sequentially sent to the out-of-sync database server To The synchronization method in the highly reliable database system according to any one of claims 1 to 4. When the intermediary device receives a processing request from a client computer, the processing request performs a synchronization maintaining process in the case of a processing request that affects the operation of a processing request involving database update or a processing request involving database update,
In the synchronization maintaining process, the step (a) is performed, and the step (b) is performed.
The synchronization method in the highly reliable database system according to any one of claims 1 to 5. When the intermediary device receives a processing request from the client computer, the processing request performs the synchronization maintaining process in the case of the processing request that affects the operation of the processing request that involves updating the database or the processing request that involves updating the database, If the processing request is neither a processing request involving database update nor a processing request affecting the operation of the processing request involving database update nor a processing request for confirming update to the database, normal processing is performed, and the processing request If the process request is not a process request involving database update but a process request that affects the operation of the process request involving database update, it is a process request for confirming the update to the database, and a confirmation process is performed.
In the normal processing, a processing request from the client computer is transmitted to the server, a response is received from the server that transmitted the processing request,
In the confirmation process, if another process request is being executed, the process waits until the other process request is not being executed, and if the other process request is not being executed, the other process request cannot be executed, The synchronization method in the highly reliable database system according to any one of claims 1 to 6, wherein normal processing is performed and another processing request can be executed again. When the mediation device receives a processing request from the client computer, if a snapshot that is a copy of the database has not been created, and if the processing request is a processing request for creating a snapshot, the mediation device performs a snapshot creation process. Is not created and the processing request is not a processing request for creating a snapshot, normal processing is performed, and when the snapshot has been created and the processing request is a processing request involving database update or processing involving database update In the case of a processing request that affects the operation of the request, the synchronization maintenance processing is performed, and if the snapshot has already been created and the processing request is not a processing request that involves updating the database, it affects the operation of the processing request that involves updating the database. Give processing request not data If it is not a processing request to confirm the update to the base Perform normal processing, and if the snapshot has already been created and the processing request is not a processing request that involves database update, it affects the operation of the processing request that involves database update If it is not a processing request to be given but a processing request to confirm the update to the database, a confirmation process is performed,
In the snapshot creation process, when a process request for confirming an update to the database is being executed, a process for confirming an update to the database is waited until a process request for confirming an update to the database is not being executed. When the request is not being executed, the processing request for confirming the update to the database cannot be executed, and the processing request affects the operation of the processing request with the database update or the processing request with the database update. If this is not the case, it is not a processing request that involves updating the database but a processing request that affects the operation of the processing request that involves updating the database. Allow the request to execute,
In the confirmation process, if a process request to create a snapshot is being executed, the process waits until the process request to create a snapshot is not being executed, and if a process request to create a snapshot is not being executed, The high reliability according to any one of claims 1 to 7, wherein a processing request for creating a shot cannot be executed, normal processing is performed, and a processing request for creating a snapshot can be executed again. A synchronization method in a database system. When multiple processing requests try to access the same data at the same time, only one processing request is executed and the others are waiting, and processing requests from client computers are relayed to one or more database servers And a highly reliable database system including an intermediary device that returns one of the legitimate responses from the database server as a processing result to the client computer,
The intermediary device preselects one of the plurality of database servers as a leader and the rest as a follower, and upon receiving a processing request from a client computer, transmits the processing request only to the leader, and the processing from the leader A high-reliability database system comprising a control unit that transmits a processing request to a follower when a response to the request is received. When multiple processing requests try to access the same data at the same time, only one processing request is executed and the others are waiting, and processing requests from client computers are relayed to one or more database servers And an intermediary method in a highly reliable database system comprising an intermediary device that returns one of the legitimate responses from the database server as a processing result to a client computer,
One of the plurality of database servers is selected in advance as a leader and the rest as a follower. When a processing request is received from a client computer, the processing request is transmitted only to the leader, and a response to the processing request is sent from the leader. An intermediary method characterized by transmitting the processing request to a follower when received. When multiple processing requests try to access the same data at the same time, only one processing request is executed and the others are waiting, and processing requests from client computers are relayed to one or more database servers And an intermediary device in a highly reliable database system comprising an intermediary device that returns one of the legitimate responses from the database server as a processing result to a client computer,
One of the plurality of database servers is selected in advance as a leader and the rest as a follower. When a processing request is received from a client computer, the processing request is transmitted only to the leader, and a response to the processing request is sent from the leader. An intermediary device comprising a control unit that transmits the processing request to a follower when received. When multiple processing requests try to access the same data at the same time, only one processing request is executed and the others are waiting, and processing requests from client computers are relayed to one or more database servers And a program for realizing an intermediary device in a highly reliable database system comprising an intermediary device that returns one of the legitimate responses from the database server as a processing result to a client computer,
A computer is selected in advance as one of the plurality of database servers as leader and the rest as follower. When a processing request is received from a client computer, the processing request is transmitted only to the leader, and the processing request is sent from the leader. An intermediary program that functions as a control unit that transmits the processing request to a follower upon receiving a response to.

Images