JP3196925B2 - Relational database delay constraint check method - Google Patents

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a relational database system having the following functions (1) to (4): delaying constraint checking at the time of record update by a user's specification, and committing a transaction (COMMIT).
The present invention relates to a relational database delay constraint checking method that sometimes performs constraint checking. Define constraints as part of the table definition. When updating a table, check whether the updated record value violates the constraint. If the updated record value violates a constraint,
Maintain database consistency by setting an error.

[0002]

2. Description of the Related Art In relational database systems,
Depending on the order in which data is updated, there is a case where it is desired to implement a process (transaction process) for guaranteeing that the constraint is satisfied at the time of commit, while temporarily allowing the constraint to be violated.

For example, as such processing, there is an updating processing as shown in FIG. FIG. 6 shows restrictions (underlined portions) defined for salary items in the employee table, and two record update processes (update 1 and update 2) for the employee table.

[0004] The constraints defined for salary items indicate that salary values must be in the range of 150,000 to 2,000,000. Update 1 is processing for changing the salary value to 1.12 times for all records in the employee table. Update 2 shows that the department value in the employee table is 10
This is a process of subtracting 3000 from the salary value for any of the records 3, 105, and 118.

[0005] Here, the section value of the employee table is 103,10.
As for the record which is any one of the records 5 and 118, even if the salary value temporarily exceeds 2,000,000 as a result of the update processing by the update 1, the update 2
May be less than 00. Therefore, update 1 and update 2 are combined into one transaction process,
It is preferable that the constraint is not checked when the update of the update 1 is completed, but the constraint is checked when the update of the update 2 is completed.

When the above processing is performed, a "relational database delay constraint check method" is required, which is a method for delaying the constraint check at the time of record update and performing the constraint check at the time of committing the transaction.

A conventional relational database delay constraint check method of this type is disclosed in, for example, Japanese Patent Laid-Open No. 33665/1990, when updating, an updated value or a record identifier required for constraint check for each updated record. And records that violate the constraints are detected by performing a constraint check based on the information at the time of commit.

[0008]

In the above-described conventional relational database delay constraint check method, it is necessary to store data necessary for constraint check for each updated record. was there.

A first problem is that a large storage capacity is required in proportion to the number of records to be updated. In the conventional method, it is necessary to store data necessary for constraint checking for each updated record, and the amount of data for constraint checking increases in proportion to the number of records. This data (data necessary for constraint checking) is generally stored in the main storage device. However, if the number of records increases, the data may not be able to be stored in the main storage device. In this case, it is possible to temporarily save the data to the secondary storage device, but the access speed to the secondary storage device is much slower than the access speed to the main storage device. The merit of storing data is lost.

[0010] The second problem is that the number of condition determinations for constraint checking increases. When committing, you must check the constraints for each updated record,
When the number of updated records is large, the number of condition determinations for constraint checking increases in proportion to the number of updated records, resulting in a large overhead.

In view of the above, an object of the present invention is to make it possible to effectively use a main storage device by reducing the information amount of data to be stored (registration information amount at the time of updating) to delay a constraint check. To reduce the number of condition determinations required for constraint checking at the time of committing (simplification of processing at the time of constraint checking), thereby reducing overhead for delay constraint checking (attaining faster processing). It is an object of the present invention to provide a relational database delay constraint check method that makes possible.

[0012]

[Means for Solving the Problems]

(1) Configuration of the Present Invention The relational database delay constraint check method according to the present invention provides a relational database system in which a constraint check at the time of record update is delayed by a user's specification and a constraint check is performed at the time of committing a transaction. A constraint delay means for registering information on a constraint to be requested in a delay information table on a main storage device when the request content is a constraint check delay request; Updating a record to be requested and, if information on a constraint related to the update is registered in the delay information table, sets information indicating that constraint checking on the constraint has been delayed in the delay information table. Update processing means and, for a commit request, a constraint related to the commit request; By acquiring information on constraints for which information indicating the delay status is set in the delay information table, and dynamically generating and executing a query for detecting a record that violates the constraints on the table based on the information, the constraint is obtained. A delay constraint checking unit for performing a check, and when the request content from the user is a commit request indicating the end of a transaction, the delay constraint checking unit is called, and commit processing is performed only when a constraint violation is not detected. It has a transaction control unit that continues and a request content analysis unit that analyzes the contents of a request from a user and calls one of the constraint delay unit, the update processing unit, and the transaction control unit according to the request contents.

(2) The relational database delay constraint check method according to the present invention configured as described above will be described below.

In the conventional relational database delay constraint checking method, data necessary for constraint checking is stored for each updated record, and the constraint is checked for each updated record at the time of commit using the data.

On the other hand, the relational database delay constraint check method of the present invention stores a table registered for each delayed constraint and constraint information, and violates a constraint condition on a table to be restricted upon commit. A constraint check (delay constraint check) is performed by dynamically generating and executing a query for detecting a record to be executed.

Thus, according to the present invention, the amount of information to be stored at the time of updating a record can be a fixed amount without depending on the number of records. The amount of use of the area is reduced.

Further, in the relational database delay constraint checking method of the present invention, when a query is dynamically generated and executed, depending on constraint conditions, high-speed processing is performed based on information such as indexes defined in a table. It is possible to do. That is, the constraint check can be performed more efficiently than the conventional method of checking the constraint for each record.

FIG. 7 is a diagram showing an example of a query dynamically generated to detect a record that violates a constraint. In FIG. 7, a constraint (underlined portion) is defined that enforces that the salary value must be in the range of 150,000 to 2,000,000 for the salary item in the employee table.

The inquiry generated based on this has a content for selecting a record whose salary value is not in the range of 150,000 to 2,000,000 from the employee table.
By using such a query, if an index is defined for a salary item, 1
Values not in the range of 50,000 to 2,000,000 (ie, values less than 150,000 or 200
(A value larger than 0000) can be searched very quickly.

[0020]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Next, embodiments of the present invention will be described in detail with reference to the drawings.

[0021]

FIG. 1A is a block diagram showing the configuration of an embodiment of a relational database delay constraint check system according to the present invention.

The relational database delay constraint check method of the present embodiment includes a request answer answer analysis means 101, a constraint delay means 102, an update processing means 103, a transaction control means 104, a delay constraint check means 105, a main memory. It is configured to include a device 110 and a secondary storage device 111.

The main storage device 110 stores the delay information table 1
06.

The secondary storage device 111 stores the database 10
7 and a directory 108.

The request content analysis means 101 inputs a request from a user and analyzes the content. The types of requests from users include those shown in the following (1) to (4). Constraint check delay request Record update request Commit / rollback (ROLLBACK) request (commit request or rollback request)

The request content analysis means 101 calls the restriction delay means 102 when the type of the request from the user is "constraint check delay request", and performs the update processing when the type of request is "record update request". When the request type is “commit / rollback request”, the transaction control unit 104 is called.

Based on the constraint check delay request analyzed by the request content analysis unit 101, the constraint delay unit 102 refers to the directory 108 to check whether there is a constraint requested to be delayed. If it does not exist, an error is returned. If there is a constraint requested to be delayed, more necessary information is read out and registered in the delay information table 106.

The update processing means 103, based on the record update request analyzed by the request content analysis means 101,
Information such as the presence or absence of attributes and restrictions is acquired with reference to the directory 108, and the record stored in the database 107 is updated. At that time, by referring to the delay information table 106, it is checked whether the constraint defined for the table storing the record to be updated is registered,
If registered, the flag indicating that the constraint check of the constraint has been delayed is turned on (ON), and the process ends without performing the constraint check. If the constraint is not registered in the delay information table 106, a constraint check is performed based on the updated answer.

The transaction control means 104 determines the transaction (commit processing) or restores the transaction (rollback processing) based on the commit / rollback request analyzed by the request content analyzing means 101.
I do. Here, only in the case of a commit request, the delay constraint check unit 105 is called before the transaction is determined. When an error (abnormal termination) is returned from the delay constraint checking unit 105, the process is switched to the recovery process without performing the transaction confirmation process. When the delay constraint checking means 105 ends normally, the information registered in the delay information table 106 is deleted, and all the constraints are returned to a state where they are not delayed. In the case of a rollback request, there is no need to perform constraint checking because updating of records is invalidated.

The delay constraint checking means 105 refers to the delay information table 106, and if there is a constraint whose constraint check has been delayed, the presence or absence of a record violating the constraint based on the registered information. Check. If there is a record that violates the constraint, an error is returned.

The delay information table 106 is a table for storing information relating to a constraint for which a constraint check delay has been requested, and is secured on the main storage device 110. The delay information table 106 is referenced / updated by the constraint delay unit 102, the update processing unit 103, and the delay constraint check unit 105.

As shown in FIG. 1B, the delay information table 106 has, for each constraint, an entry including constraint identification information, constraint content information, and a constraint delay flag. The structure of the delay information table 106 is shown in FIG.
It is not limited to the one shown in FIG.

The database 107 stores a table record index and the like. The database 107 is secured on the secondary storage device 111.

One directory 108 exists in the relational database system, and stores information such as tables, indexes, and constraints. The directory 108 is also reserved on the secondary storage device 111.

FIG. 2 is a flowchart (flow chart) showing a processing procedure of the constraint delay means 102. This processing procedure includes a constraint presence / absence determination step 201, a constraint content acquisition step 202, a constraint content registration step 203, and a constraint delay flag off step 204. Note that the processing procedure of the constraint delay unit 102 is not limited to the processing procedure shown in FIG. 2 as long as the same processing content can be obtained (the same applies to the flowcharts of FIGS. 3 to 5).

FIG. 3 is a flowchart showing the processing procedure of the update processing means 103. This processing procedure includes a record update step 301 and a constraint presence / absence determination step 302.
And a delay information table registration presence / absence determination step 303;
Consisting of a constraint check step 304, a constraint delay flag on step 305, and a constraint violation determination step 306.

FIG. 4 shows the transaction control means 104.
6 is a flowchart showing the processing procedure of FIG. This processing procedure includes a commit request / rollback request determination step 40
1 and delay constraint check means calling step 402
, A normal end determination step 403, a transaction confirmation processing step 404, a first transaction recovery processing step 405, and a second transaction recovery processing step 406.

FIG. 5 is a flowchart showing the processing procedure of the delay constraint checking means 105. This procedure is
It comprises a delay information table registration presence / absence determination step 501, a constraint delay flag determination step 502, an inquiry generation step 503, an inquiry execution step 504, and an inquiry result presence / absence determination step 505.

Next, the operation of the relational database delay constraint check method of the present embodiment configured as described above will be described.

(1) The request content analysis means 101 receives a request from a user, and calls the constraint delay means 102 when the type of request is "constraint check delay request", and the request type is "record update request". If the request type is "commit / rollback request", the transaction control means 10 is called.
Call 4

(2) The constraint delay means 102 called by the request content analysis means 101 performs the following processing (see FIG. 2).

First, referring to the directory 108 in the secondary storage device 111, it is determined (confirmed) whether or not there is a constraint requested by the user (step 201).

If it is determined in step 201 that the constraint does not exist, an error is returned and the process ends abnormally.

In step 201, "there is a constraint"
If it is determined, the contents of the target constraint are acquired from the directory 108 (step 202), and the contents are registered in the delay information table 106 in the main storage device 110 (step 203). That is, an entry relating to the constraint is registered in the delay information table 106, the constraint identification information in the entry is set to the constraint identification information, and the content acquired in step 202 is set to the constraint content information in the entry.

Further, the constraint delay flag in the entry registered in step 203 is initialized to off (OFF) (step 204), and the process ends normally.

(3) The update processing means 103 called by the request content analysis means 101 performs the following processing (see FIG. 3).

First, in accordance with a request from the user, the table record in the database 107 in the secondary storage device 111 is updated (step 301).

Next, it is determined (confirmed) whether or not there is a constraint to be checked by referring to the directory 108 based on the updated contents of step 301 (step 302).

If it is determined in step 302 that "there is no constraint to be checked", the process ends normally.

If it is determined in step 302 that "a constraint to be checked exists", the delay information table 106 is searched using the constraint identification information of the constraint, and whether the constraint is registered in the delay information table 106 is determined. Is determined (step 303).

If it is determined in step 303 that the constraint is registered in the delay information table 106,
The constraint delay flag in the entry of the constraint is set to ON (update) (step 305), and then step 302
To confirm whether there is a constraint to be further checked, and continue the subsequent processing.

If it is determined in step 303 that the constraint is not registered in the delay information table 106, the constraint is immediately checked (step 304).
It is determined whether the update record violates the constraint (step 306).

If it is determined in step 306 that "the updated record violates the constraint", an error is returned and the process ends abnormally.

If it is determined in step 306 that “the updated record does not violate the constraint”, step 302
To confirm whether there is a constraint to be further checked, and continue the subsequent processing.

(4) The transaction control means 104 called by the request content analysis means 101 performs the following processing (see FIG. 4).

First, it is determined whether the content of the commit / rollback request is a commit request (whether it is a commit request or a rollback request) (step 4).
01).

If it is determined in step 401 that the request is a rollback request, the database 10
Transaction recovery processing including canceling the update to No. 7 and releasing the lock used for exclusive control is performed (step 405), and the processing ends normally.

If it is determined in step 401 that "the answer is a commit request", the delay constraint checking means 105 is called (step 402), and the result (the processing result of the called delay constraint checking means 105 ( FIG.
) Is normally terminated (step 403).

If it is determined in step 403 that "the processing result of the delay constraint checking means 105 is a normal end", the transaction confirmation processing includes reflection of the update in the database 107, release of the lock used for exclusive control, and the like. (Step 404), and the process ends normally.

If it is determined in step 403 that "the processing result of the delay constraint check means 105 is abnormal termination", the transaction recovery processing includes canceling the update to the database 107 and releasing the lock used for exclusive control. (Step 406), an error is returned, and the process ends abnormally.

(5) Transaction control means 104
The delay constraint checking means 105 called by
The following processing is performed (see FIG. 5).

First, the presence or absence of a registered constraint is determined (confirmed) with reference to the delay information table 106 (step 501).

If it is determined in step 501 that "there is no constraint", the process ends normally.

If it is determined in step 501 that "the constraint is registered", it is determined whether the constraint delay flag of the constraint is on or off (step 50).
2).

If it is determined in step 502 that "the constraint delay flag is off", the flow returns to step 501 to check whether or not any constraint registered in the delay information table 106 exists. Continue processing.

If it is determined in step 502 that “the constraint delay flag is on”, the delay information table 106
A query for detecting a record that violates the constraint condition is generated based on the content of the constraint registered in (step 503), and the query is executed (step 504).

Next, the result of the inquiry (result for the execution of the inquiry in step 504) is confirmed, and it is determined whether or not the inquiry result exists (step 50).
5).

If it is determined in step 505 that "a query result exists", it is recognized that a record violating the constraint exists, an error is returned, and the process ends abnormally.

If it is determined in step 505 that “the inquiry result does not exist”, the flow returns to step 501 to check whether or not there is a constraint registered in the delay information table 106, and to perform the subsequent processing. continue.

[0070]

As described above, according to the present invention,
The following effects are produced.

The first effect is that the effective use of the main storage device can be achieved by reducing the information amount of data to be stored in order to delay the constraint check. This is because, in the conventional method, data necessary for constraint checking is stored in updated record units, whereas in the relational database delay constraint checking method of the present invention, there is no information to be stored in record units. That's why.

The second effect is that it is possible to reduce the number of condition determinations required for constraint checking at the time of commit, and to reduce overhead for delay constraint checking. This is because, in the conventional method, the condition determination for the constraint check is performed for each updated record unit, whereas the relational database delay constraint check method of the present invention executes an inquiry to the table, This is because high-speed processing can be performed using an appropriate index.

[Brief description of the drawings]

FIG. 1A is a block diagram showing a configuration of an embodiment of a relational database delay constraint check method according to the present invention;
(B) is a diagram showing an example of the structure of the delay information table in (a).

FIG. 2 is a flowchart showing a processing procedure of a constraint delay unit in FIG.

FIG. 3 is a flowchart showing a processing procedure of an update processing unit in FIG.

FIG. 4 is a flowchart showing a processing procedure of a transaction control unit in FIG.

FIG. 5 is a flowchart showing a processing procedure of a delay constraint check unit in FIG.

FIG. 6 is a diagram illustrating an example of transaction processing in which it is effective to delay until commit without immediately checking a constraint;

FIG. 7 is a diagram showing an example of an inquiry generated from a constraint by the relational database delay constraint checking method of the present invention.

[Explanation of symbols]

 101 Request Contents Analysis Unit 102 Constraint Delay Unit 103 Update Processing Unit 104 Transaction Control Unit 105 Delay Constraint Check Unit 106 Delay Information Table 107 Database 108 Directory 110 Main Storage Device 111 Secondary Storage Device

──────────────────────────────────────────────────続 き Continued on the front page (58) Fields surveyed (Int. Cl. ⁷ , DB name) G06F 12/00 518 G06F 12/00 512 G06F 12/00 531 G06F 17/30 340

Claims (2)

(57) [Claims] 1. A relational database system which delays constraint checking at the time of record update according to designation by a user and performs constraint checking at the time of committing a transaction. A constraint delay unit for registering information on a constraint to be requested in a delay information table on a storage device; and updating a record to be requested when a request content from a user is a record update request, and relating to the update. An update processing unit configured to set, in the delay information table, information indicating that a constraint check on the constraint has been delayed when the constraint information is registered in the delay information table; A restriction related to the request, wherein the information indicating the delay state is set in the delay information table. Delay constraint checking means for acquiring information about contracts, dynamically generating and executing a query for detecting a record that violates a constraint condition with respect to a table based on the information, and performing constraint checking, and a request from a user If the content is a commit request indicating the end of the transaction, call the delay constraint checking means,
A transaction control means for continuing the commit process only when no constraint violation is detected; a request content from a user is analyzed; and the constraint delay means, the update processing means, and the transaction control means according to the request content. And a request content analyzing means for calling any one of the following. 2. The relational database delay constraint check method according to claim 1, wherein the delay information table has an entry consisting of constraint identification information, constraint content information, and a constraint delay flag.