JP2006040064A - Database access apparatus - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To access a generation management type database by using a query, which is not defined so as to specify a generation number, and further to perform reference and alteration many times regarding the generation management type database during one transaction. <P>SOLUTION: A client interface part 122 receives a query described by an SQL (Structured Query Language) from a client 110 and provides the client 110 with an interface such as a JDBC (Java Database Connectivity). A query converting part 124 converts the query obtained from the client 110 through the client interface part 122 into a query of a generation control type database native. A transaction management part 126 has a transaction management table 128 for managing a series of queries from the client 110 as a transaction and a generation control table 130 for controlling the generation numbers to be related to the respective queries in the transaction. <P>COPYRIGHT: (C)2006,JPO&NCIPI

Description

  The present invention relates to a database access apparatus, and more particularly to an apparatus for performing access such as data reference and update to a database that manages data in units such as generations and editions.

  FIG. 21A and FIG. 21B are diagrams for explaining the concept of database access.

  As shown in FIG. 21A, when a client 3 connected to the database 1 issues an inquiry (query) for referring to data to the database 1, the database 1 matches the condition indicated by the query. Data is returned to the client 3 as a result of the inquiry.

  Further, as shown in FIG. 21B, when a query for changing data such as data update, addition, or deletion is issued to the database 1, the database 1 meets the conditions indicated in the query. Data is updated, added, or deleted, and the result of the inquiry is returned to the client 3. At this time, when the database 1 is a database that manages data in units such as generations and editions (generation management type database), the database 1 accepts a query for changing data, thereby indicating a new database state after the change. Information called “generation” is created.

  More specifically, in the generation management database, the database state at an arbitrary time can be specified by the generation number (generation number) that is the management unit. The generation number can be specified by assigning a generation number to the query. For example, for a generation that shows the database status before accepting a query to change data, if you specify a generation number that indicates the generation and perform a search, the query result in the database status that reflects the change is naturally Can't get. On the other hand, when a search is performed by specifying a generation number indicating a generation indicating the database state after the change, an inquiry result in the database state reflecting the data change content can be obtained. Assigning a generation number to a query depends on the implementation of the generation management database, but if a generation number is not assigned to a query, it is usually the same as the generation number indicating the current latest generation. Works.

  As described above, in the generation management database, by specifying the generation number in the query, the query can be issued for the database state indicated by the generation number. That is, reference (search) to any database state and change (update, addition, deletion) to any database state are possible. In particular, in the case of a change, a new generation indicating the database state after the change and a generation number indicating the state are created.

  In addition, many generation management databases can issue queries for inquiring about differences between generations. For example, in order to count “Today's sales” at the end of the day, the difference between the two generations, the status at the time of aggregation (latest generation number) and the status at the start of the day (generation number immediately after opening the store) Then, a query for inquiring sales information may be issued.

  In contrast to such a generation management database, there is a non-generation management database (relational database management system: RDBMS (Relational Data Base Management System)), which has a large share in the database market. In addition, Structured Query Language (SQL) is widely used as an industry standard as a query description language for databases (Non-Patent Document 1), and there are many resources for using SQL. In addition, as an API (Application Programming Interface) for operating a database from a Java (registered trademark) application, a JDBC (Java Database Connectivity) is widely used (Non-patent Document 2).

  RDBMS has data inconsistency even if an error occurs during the execution of multiple operations (reference, update, addition, deletion, etc.) on the database when multiple users perform database operations simultaneously. There is a mechanism called “transaction” to ensure that no occurrence occurs. In addition, SQL, JDBC, etc. can set isolation (isolation level) between their own transaction and other transactions as its standard.

RDBMS combines safety, reliability, flexibility, and high speed with these transaction mechanism and isolation level concepts.
Martin Gruber, "SQL 92/99 Standard Reference Book", Document System Inc. "Java (TM) JDBC (TM) Data Access API Specification (" Specification ")", Version 3.0, page 63-64

  However, since SQL, JDBC, etc. are standardized based on RDBMS, which is a non-generation management database, there is no provision for designating a generation number as described above. On the other hand, since the generation management database needs to give a generation a generation number as described above, the API specification related to the query is highly unique. There is also no conceptual transaction mechanism used as a basis in RDBMS.

  For this reason, in the generation management database, it is not easy to use resources abundantly in RDBMS and to link or link with other systems when constructing a database system.

  The present invention has been made in view of the above points, and accesses a generation management database using a query that does not specify a generation number, such as SQL or JDBC. It is an object of the present invention to provide a database access apparatus having a transaction mechanism that can refer to and change a managed database within a transaction multiple times.

  A database access device according to the present invention receives a query (for example, written in SQL or the like) from a client and provides an interface (for example, JDBC or the like) for performing database access to the client. And a query conversion unit that converts a query obtained from the client interface unit into a query in a native format of a generation management database. Preferably, the database access device is adapted by a transaction management table for managing a series of queries issued for each client as one transaction, a generation number to be associated with each query constituting each transaction, and the query. It further includes a transaction management unit having change information and a generation management table for managing the commit state of the transaction, and makes it possible to configure a transaction for each client. That is, the transaction management unit grasps the latest generation number of the database before and after the issuance of each query, and presents the generation number to be given to the query conversion unit when the query conversion unit converts the query to the native format. Preferably, the transaction management unit performs the generation number to be presented to the query conversion unit according to the isolation level set for each transaction.

  According to the present invention, a generation management database is accessed using a query that does not specify a generation number such as SQL or JDBC, and as a result, within one transaction for the generation management database. Can be referenced and updated multiple times. It is also possible to set an isolation level for each transaction. Furthermore, it becomes possible to combine safety, reliability, flexibility, and high speed with a conventional generation management database.

  Hereinafter, embodiments of the present invention will be described in detail with reference to the drawings.

(Embodiment 1)
FIG. 1 is a block diagram showing a configuration of a database system including a database access apparatus according to Embodiment 1 of the present invention.

  The database system 100 shown in FIG. 1 roughly includes a client 110, a database access device 120, and a database 140.

  The client 110 issues a query written using a query language such as SQL, calls an API such as JDBC, and accesses the database 140 via the database access device 120. The database 140 performs processing according to the contents of the query and returns the result to the client 110. The database 140 is a generation management database. The database access device 120 includes a client interface unit 122, a query conversion unit 124, and a transaction management unit 126. The transaction management unit 126 is provided with a transaction management table 128 and a generation management table 130.

  Here, a configuration example of the client, the database access device, and the database in this embodiment will be described with reference to FIGS.

  In FIG. 2A, the database access device 120 is implemented as driver software for the application software 150 to access the database 140, and operates by installing the driver software in the client 110a. The application software 150 has application logic that uses information in the database 140, and accesses the database 140 by calling an interface of the driver software as necessary, and acquires necessary information.

  In FIG. 2B, the database access device 120 is implemented as a library called from application software 154 called a servlet operating on the Web server 152. A browser 156 for accessing the Web server 152 is operating on the client 110b, and calls the servlet 154 as necessary (or issues a URL for calling the servlet 154 to the Web server 152). The servlet 154 has application logic that uses information in the database 140, and accesses the database 140 by calling an interface of the database access device 120, which is a library, as necessary, and acquires necessary information.

  In FIG. 2C, the database access device 120 is implemented as software such as a daemon process that operates on the database 140a, and operates by installing daemon process software on the database 140a. The application software 158 has application logic using the information in the database 140a, and accesses the database 140a by calling an API for connecting to the daemon process as necessary, and acquires necessary information.

  In this embodiment, the database access device 120 is configured as software. However, the present invention is not limited to this, and a hardware board mounted on the client 110, the Web server 152, the database 140, or the like is not limited thereto. You may comprise as hardware.

  In FIG. 1, the client interface unit 122 accepts a query written using a query language such as SQL from the application software 150, 154, 158 in FIGS. Provide interfaces such as JDBC. It also provides an interface for returning the processing results of the database 140 to the application software. The client interface unit 122 passes the received query and information (attached information) acquired via the interface to the query conversion unit 124. The client interface unit 122 can accept queries from a plurality of clients, and each of these queries can be identified by a unique client ID value. All the elements 122 to 126 constituting the database access device 120 including the client interface unit 122, and the interfaces between the elements can process and transmit the client ID, respectively. It is possible to increase or decrease the number of elements and inter-element interfaces for each client ID.

  The query conversion unit 124 converts the query and attached information received from the client interface unit 122 into a query in which the native description of the database 140 that is a generation management database is written. The pre-conversion query received from the client interface unit 122 does not include information unique to the generation management database such as the generation number. On the other hand, the post-conversion query is semantically equivalent to the pre-conversion query and is given information unique to the generation management database such as a generation number.

  The transaction management unit 126 manages a series of queries issued for each client ID as one transaction, and a generation management table 130 that manages generation numbers to be associated with each query constituting each transaction. And have. When the query is converted, the query conversion unit 124 requests the transaction management unit 126 for information such as a generation number to be given to the query. In response to this request, the transaction management unit 126 refers to the transaction management table 128 and the generation management table 130, and returns information such as a generation number necessary for giving to the query to the query conversion unit 124.

  The query converted into the native description of the database 140 by the query conversion unit 124 is passed to the database 140. In the database 140, a search processing unit 142, which is a basic part of the database, operates and performs processing (search, update, addition, deletion, etc.) according to the contents of the query. The result of this processing is passed to the query converter 124. The processing results include the record set that results from the search and the success or failure of the processing, as well as information provided by the database (logs, statistical information, frequency information, relevance information, database status, generation numbers on the database before and after processing, etc.) Can be included.

  Note that the query conversion unit 124 does not necessarily pass one converted query to the database 140 for one query from the client 110, and is unique to the database 140 at an arbitrary timing as necessary. A query may be created to obtain a processing result. For example, in the case of the latest generation number on the database 140, it is possible to create a native query in the database 140 for acquiring the latest generation number, and thereby acquire the latest generation number from the database 140.

  The query conversion unit 124 converts the processing result received from the search processing unit 142 into a format from which information unique to the generation management database such as a generation number is removed, and is a query issuer via the client interface unit 122. Return to the application software 150, 154, 158.

  Hereinafter, the operation of each unit will be described in detail.

  First, the case where the isolation level is “SERIALIZABLE” is described. For specifying the isolation level, for example, an input as shown in FIG. FIG. 3 is a diagram illustrating an example of setting description of an isolation level by SQL and JDBC.

  As shown in Figure 3, SQL and JDBC have "SERIALIZABLE", "READ UNCOMMITED (uncommitted read)", "READ COMMITED (committed read)", "REPEATABLE_READ (rereadable read)" There are four types of isolation levels.

  The meaning of each isolation level means that each of the three types of reading states “Dirty Read”, “Non-Repeatable Read”, and “Phantom Read” shown in the table of FIG. ) Or not (denoted as “none”).

  “Dirty Read” means that if one transaction is reading from the same table while another transaction is writing to the table, the latter transaction may read content that has not yet been committed (committed). Indicates that there is.

  “Non-Repeatable Read” is different from the first when a transaction reads from the table and then another transaction updates the contents of the same table and commits. Indicates that the contents may be read.

  "Phantom Read" means that after a transaction reads from a table, another transaction changes, inserts, or deletes a row that matches the conditions when the former transaction reads the table for the same table. If the former transaction reads the same table again, it indicates that there is a possibility of reading only the contents of an unexpected appearance.

  As described above, a plurality of clients or application software can be uniquely identified by the client ID. Examples of the client ID include an ID address and a port number, a session ID issued and managed by the servlet container, and authentication information obtained from authentication information separately sent from the client. In this embodiment, a case is described in which one application software operates in a certain client and one client ID is assigned to the application software, but actual implementation is not limited to this.

  The isolation level specified via the client interface unit 122 is set in the transaction management unit 126 via the query conversion unit 124.

  FIG. 4 is a diagram showing a detailed configuration of the transaction management table 128 and a registration method in the transaction management table 128 according to the present embodiment.

  The transaction management unit 126 searches the client IDs in the transaction management table 128 for a client ID associated with the application software. In the example of FIG. 4, since there are cases where the client IDs are “A” and “B”, the value of the isolation level corresponding to the matched client ID is set to the designated isolation level. In the case of a new client (client ID = C), since the corresponding client ID does not exist in the transaction management table 128, a record is newly added to the transaction management table 128. At this time, “C” that is the client ID of the new client is set as the client ID of the additional record, and “READ_COMMITED” that is the default is set as the isolation level. As a matter of course, in addition to the isolation level, information that can correspond one-to-one with the client ID may be added to the transaction management table 128 for management. Further, the default value of the isolation level is not limited to “READ_COMMITED”. With the above operation, the transaction management unit 126 can manage the correspondence between the client ID and the transaction and the correspondence between each transaction and the isolation level.

  Next, a case where an operation indicating search, update, addition, or change is performed on the client interface unit 122 will be described.

FIG. 5 is a diagram showing a first operation example of database access in the present embodiment.
First, at time T00, the latest generation number on the database 140 is 01.

  At time T01, a search operation is performed from client ID = A.

  At time T02, an operation of changing B1 from client ID = B is performed, and as a result, the latest generation number becomes 02. Here, “B1” indicates the specific change contents for changing the generation 01 database.

  At time T03, an operation of changing C1 from client ID = C is performed. As a result, the latest generation number becomes 03. Here, “C1” indicates a specific change content for changing the generation 02 database.

  At time T04, a search operation is performed from client ID = B.

  At time T05, an operation for changing B2 from client ID = B is performed. As a result, the latest generation number becomes 04. Here, “B2” indicates specific change contents for changing the generation 03 database.

  At time T06, an operation of changing C2 from client ID = C is performed, and as a result, the latest generation number becomes 05. Here, “C2” indicates a specific change content for changing the generation 04 database. Further, the client ID = C performs a commit operation. Here, the “commit operation” refers to an operation for confirming that the database changes (C1, C2) made by the client with ID = C before this time (T06) are reflected in the database.

  At time T07, an operation of changing A1 from client ID = A is performed. Here, “A1” indicates the specific change contents for changing the generation 05 database. As a result, the latest generation number becomes 06. Further, the client ID = A performs a commit operation.

  At time T08, an operation of changing B3 from client ID = B is performed. Here, B3 indicates a specific change content for changing the generation 06 database. As a result, the latest generation number becomes 07.

  At time T09, client ID = B performs a rollback operation. Here, “rollback operation” means canceling a series of operations on the database performed in the transaction.

  A transaction begins with the first access or the next access after the end of the previous transaction and ends with a commit or rollback. Therefore, client ID = A has one transaction from T01 to T07, client ID = B has one transaction from T02 to T09, and client ID = C has one transaction from T03 to T06.

  Since the latest generation number on the database 140 is changed as described above by the change operation, the change information used in each change operation is “02-01” for B1, “03-02” for C1, and B2 for B2, respectively. “04-03”, C2 is “05-04”, A1 is “06-05”, B3 is “07-06”, and can be obtained by calculating the generation number. Here, for example, “02-01” means the difference contents obtained by subtracting the contents of the generation 01 database from the contents of the generation 02 database. On the other hand, in the case of a search operation, the latest generation number does not change before and after the search.

  FIG. 6 is a flowchart showing an example of operations of the query conversion unit 124 and the transaction management unit 126 (in the case of “SERIALIZABLE”) in the present embodiment. FIGS. 7A to 7C are diagrams illustrating an example of the generation management table 130 according to the present embodiment. FIG. 7A is a diagram showing the contents of the generation management table 130 at time T00 in FIG. 5, and shows that the records in the table (meaning each row in the table) are empty. The records in the generation management table 130 are associated with the records (identified by client ID), change information when performing a change operation, and a database before and after performing operations such as a search operation and a change operation. 140 holds the latest generation number on 140 and a flag indicating whether or not the operation corresponding to the record has been committed. FIG. 7B shows the contents of the generation management table 130 at the time T04 in FIG. 5, and FIG. 7C shows the contents of the generation management table 130 at the time T09 in FIG. FIG.

  Hereinafter, the operations of the query conversion unit 124 and the transaction management unit 126 including the control method of the generation management table 130 will be described with reference to FIGS. 6 and 7A to 7C.

  First, the operations of the query conversion unit 124 and the transaction management unit 126 will be described with reference to FIG.

  In step S1000, the query conversion unit 124 analyzes the query and attached information received from the client interface unit 122, and the operation type is a search operation, a change operation (update, addition, deletion), and other operations (rollback, commit, etc.). ).

  In step S1100, it is determined whether or not the determination result in step S1000 is a rollback operation. If the result of this determination is a rollback operation (S1000: YES), the process proceeds to step S1200, and if it is not a rollback operation (S1000: NO), the process proceeds to step S1500.

  In step S1200, the transaction management unit 126 issues an operation for acquiring the latest generation number at that time to the database 140.

In step S1300, a rollback generation calculation formula is calculated. The generation formula for rollback is, for example,
Generation formula = (Latest generation number)-(Uncommitted for local transaction)
Given in.

  In step S1400, the query conversion unit 124 issues a query to which the rollback generation arithmetic expression calculated in step S1300 is assigned to the database 140, and then proceeds to step S1600.

  On the other hand, in step S1500, it is determined whether or not the determination result in step S1000 is a commit operation. If the result of this determination is a commit operation (S1500: YES), the process proceeds to step S1600, and if it is not a commit operation (S1500: NO), the process proceeds to step S1800. If it is not a commit operation, it is either a search operation or a change operation.

  In step S1600, after transmitting the client ID that has performed the rollback operation or commit operation to the transaction management unit 126, the query conversion unit 124 ends the operation.

  In step S 1700, the transaction management unit 126 sets the value of the field of the committed flag to “Yes” for all records having the client ID passed from the query conversion unit 124 from the generation management table 130. Thus, setting the value of the committed flag field to “Yes” means that the transaction of the client ID is ended.

  On the other hand, in step S1800, the query conversion unit 124 determines whether the determination result in step S1000 is a search operation. If it is a search operation as a result of this determination (S1800: YES), the process proceeds to step S1900. If it is not a search operation, that is, if it is a change operation (S1800: NO), the process proceeds to step S2100.

  In step S1900, the transaction management unit 126 determines whether the client ID exists in the generation management table 130. As a result of this determination, if the client ID exists in the generation management table 130 (S1900: YES), the process proceeds to step S2000. If the client ID does not exist in the generation management table 130 (S1900: NO), step The process proceeds to S2100.

  In step S2000, a generation operation formula for search operation is calculated. This calculation takes, for example, the following three stages. First, as the first stage, the generation number registered most recently with respect to the client ID is acquired from the generation management table 130. Next, as a second step, a record other than the transaction is obtained between the records registered immediately after the start of the transaction. Then, in the final third stage, calculate the result of subtracting the change information of each record obtained in the second stage from the contents of the generation number database obtained in the first stage, and use this to calculate the generation formula for the search operation And When the calculation of the generation expression for search operation is completed, the process proceeds to step S2300.

  On the other hand, in step S2100, an operation for obtaining the latest generation number at that time is issued to the database 140.

  In step S2200, the generation management number acquired from the database 140 is registered in the generation management table 130, and the process proceeds to step S2300. Here, the series of processing in steps S2100 and S2200 has a meaning of recording the latest generation number on the database 140 at the start of the transaction corresponding to the client ID.

  In step S2300, the query conversion unit 124 converts the query received from the client interface unit 122. At this time, processing is performed so that the generation formula calculated in step S2000 or the generation number acquired in step S2100 is added to the converted query.

  In step S2400, it is determined whether or not the change operation is a determination result in step S1800. If the result of this determination is a change operation (S2400: YES), the process proceeds to step S2500. If it is not a change operation, that is, if it is a search operation (S2400: NO), the operation is immediately terminated. .

  In step S2500, the transaction management unit 126 acquires the latest generation number at that time from the database 140, as in step S2100.

  In step S2600, the generation management number acquired in step S2500 is recorded in the generation number field after processing of the record registered in step S2200. Then, all the series of operations are completed.

  Next, an example of a specific operation will be described with reference to FIGS. 5 and 7A to 7C.

  The query conversion unit 124 analyzes the query and attached information received from the client interface unit 122, and the operation type is any of search operation, change operation (update, addition, deletion), and other operations (rollback, commit, etc.). It is determined whether it exists (S1000).

  At time T01 in FIG. 5, since the search operation is performed from the client ID = A, the determination in step S1800 is “YES”, that is, the process proceeds to step S1900.

  In step S1900, the generation management table 130 is checked to see if there is a client that satisfies the client ID = A and the committed flag = “No”. If it exists, the process proceeds to “YES”. At this time, as shown in FIG. 7A, the generation management table 130 is empty, so that it is determined to be “NO” in the determination in step S1900, that is, The process proceeds to step S2100.

  In step S2100, the transaction management unit 126 issues an operation for acquiring the latest generation number at that time to the database 140. In step S2200, the generation management number acquired from the database 140 is registered in the generation management table 130 (FIG. 7B: (1)). At the time of step S2200, “null” is set in the post-processing generation number field of the generation management table 130. Similarly, since there is no change information in the case of a search operation, “null” is also set in the change information field. The series of processes in steps S2100 and S2200 has a meaning of recording the latest generation number of the database 140 at the start of the transaction corresponding to the client ID = A.

  Thereafter, in step S2300, the query conversion unit 124 converts the query received from the client interface unit 122. At this time, processing is performed so that the generation number acquired in step S2100 is given to the converted query. This makes it possible to appropriately perform a search for the current generation number on the database 140 for the search operation at time T01.

  Next, at time T02 in FIG. 5, since the change operation is performed from the client ID = B, the process proceeds to “NO” in the determination of step S1800, that is, immediately proceeds to step S2100. In the case of a change operation, the latest generation number is acquired and registered in the generation management table 130 in steps S2100 and S2200 (FIG. 7B: (2)). In this case, a series of processes in step S2100 and step S2200 has a meaning of recording the latest generation number on the database 140 in the stage before the change operation is performed. In addition, since the client ID = B does not yet exist in the generation management table 130 at time T02, it means that the latest generation number on the database 130 at the start of the transaction corresponding to the client ID (= B) is recorded. Doubles as

  Thereafter, in step S2300, as in the case of the search operation at time T01, the generation number acquired in step S2100 is assigned to the change operation query, and a post-conversion query is issued to the database 140.

  In step S2400, since the operation is a change operation, the process proceeds to “YES”, that is, to step S2500. In step S2500, as in step S2100, the latest generation number at that time is acquired from the database 140. In step S2600, the generation number acquired in step S2500 is recorded in the generation number field after processing of the record registered in step S2200 (FIG. 7B: (2)).

  Next, also at time T03 in FIG. 5, the same processing as at time T02 is performed for client ID = C and registered in the generation management table 130 (FIG. 7B: (3)).

  Next, at time T04 in FIG. 5, since the search operation is performed from the client ID = B, the determination in step S1800 is “YES”, that is, the process proceeds to step S1900. Further, since the client ID = B already exists in the generation management table 130 and the committed flag = “No” (FIG. 7B: (2)), “YES” is determined in step S1900. In other words, the process proceeds to step S2000.

  In step S2000, in step S2300, the query conversion unit 124 calculates a generation operation expression for search operation to be given to the converted query. As the first step, the generation number registered most recently for the client ID = B is acquired from the generation management table 130. For example, at time T04, the record (2) in FIG. 7B is the record registered most recently. At this time, if the post-process generation number is not “null” among the pre-process generation number and post-process generation number fields of the record, the post-process generation number is acquired. On the other hand, if the post-processing generation number is “null”, the pre-processing generation number of the record is acquired. Therefore, in step S2000, first, the post-processing generation number = 02 in (2) of FIG. 7B is acquired. Next, as a second step, a record other than the transaction is obtained between the records registered immediately after the start of the transaction. In the previous example, the transaction start record from client ID = B is (2) in FIG. 7, and the most recently registered record is also (2) in FIG. 7B. There is no record other than B. Then, in the final third stage, the contents of the generation number (02) database obtained in the first stage are subtracted from the change information (null) of each record obtained in the second stage, and this is searched. It is a generation expression for operation. In the previous example, the generation operation expression for the search operation is the generation number 02. In step S2300, this generation number 02 is given to the query.

If the client ID = B performs a search operation instead of the change operation of B3 at time T08 in FIG. 5, the record obtained in the first stage is the record (4) in FIG. 7C. The most recently registered generation number is 04. Next, the transaction start record from client ID = B obtained in the second stage is (2) in FIG. 7C, and the most recently registered record is (4) in FIG. 7C. The other records in the meantime are (3) in FIG. Since (3) in FIG. 7C is a change operation using the change information C1, the generation operation expression for the search operation is
Generation formula = 04-C1
= 04- (03-02)
It becomes.

  It can be understood by referring to the transaction management table 128 which isolation level is set for the transaction corresponding to each client ID. From FIG. 4, it can be seen that the isolation level of the transaction with client ID = B is “SERIALIZABLE”. At time T04, the change of C1 made by client ID = C should not be seen from client ID = B, but by performing the above processing, a search query assigned generation 02 is issued. Thus, it is possible to perform a search in consideration of the isolation level. Similarly, when a search operation is performed at time T08, a generation operation formula that does not show the change of C2 made by client ID = C is obtained from the generation 04 immediately after the update of B2, and therefore an appropriate search is performed. Is possible.

  Next, when the commit operation is performed as at time T06 and time T07 in FIG. 5, the determination in step S1500 proceeds to “YES”. Thereafter, the query conversion unit 124 passes the client ID that has performed the commit operation to the transaction management unit 126 (S1600). The transaction management unit 126 sets the values of all committed flag fields of the record having the passed client ID from the generation management table 130 to “Yes”, so that the operation related to the record is committed. (S1700). In step S1700, setting the value of the committed flag field to “Yes” means that the transaction of the client ID is terminated.

  Next, when a rollback operation is performed at the client ID = B as at time T09 in FIG. 5, the determination in step S1100 proceeds to “YES”, that is, the process proceeds to step S1200. In step S1200, the transaction management unit 126 issues an operation for acquiring the latest generation number at that time to the database 140.

In step S 1300, the transaction management unit 126 records from the generation management table 130 that has the passed client ID, the change information field is not “null”, and the committed flag is “No”. Search for. Since the contents of the generation management table 130 are as shown in FIG. 7C at time T09, the corresponding records are (2), (4), and (7) in FIG. 7C. Here, appropriate rollback is performed by excluding the change (B1, B2, B3) of client ID = B from the latest generation number (generation 07) at the rollback time (time T09) acquired in step S1200. It will be. Since the change information of each record can be calculated as “B1 = 0-02-01”, “B2 = 04-03”, “B3 = 07-06”, the generation equation for rollback obtained in step S1300 is ,
Generation formula = (Latest generation number)-(Uncommitted for local transaction)
= 07- (B1 + B2 + B3)
= 07 + 01-02 + 03-04 + 06-07
It becomes.

  In step S1400, a query to which the rollback generation formula is added is issued to the database 140. The database 140 creates the latest generation number = 08 from which a series of change operations (B1, B2, B3) by the transaction with the client ID = B is removed. Since a series of transactions is terminated by the rollback operation, the contents of the generation management table 130 are changed by performing steps S1600 and S1700 so that the transaction of the client ID is completed. Specifically, the corresponding client ID record is deleted (S1700).

  By configuring as described above, it is possible to generate a query with a generation number assigned to the generation management database with an isolation level of “SERIALIZABLE (serializable)”. It becomes possible to access the generation management database using a query language or the like that does not have a rule for specifying a generation number, such as SQL or JDBC. In addition, it is possible to provide a database access method and an access device having a transaction mechanism for a generation management database. In addition, the lock mechanism (exclusive control) that is used when “SERIALIZABLE (serializable)” is realized in a conventional non-generation management database becomes unnecessary.

  Next, a case where the isolation level is “READ UNCOMMITED (uncommitted read)” will be described. About the part which is common in the description so far, the same symbol and reference number are used, and the description is omitted.

  When the isolation level is “READ UNCOMMITED (uncommitted read)”, data in a state in which another transaction is not committed is read. For example, at time T04 in FIG. 5, the change of C1 made by client ID = C from client ID = B is visible.

  For this purpose, the query conversion unit 124 only needs to operate so that a generation number indicating the latest state is always given to a query during a search operation.

  FIG. 8 is a flowchart showing another example of the operations of the query conversion unit 124 and the transaction management unit 126 (in the case of “READ UNCOMMITED”) in the present embodiment. Here, in step S2010 in FIG. 8, the latest generation number is acquired from the database 140, and the search operation is performed by the query converted in step S2300 using this generation number. More specifically, by acquiring the “post-processing generation number” in the lowest column of the generation management table 130 at that time, it becomes possible to search the latest generation database.

  With the configuration as described above, it is possible to generate a query with a generation number assigned to the generation management database so that the isolation level is “READ UNCOMMITED (uncommitted read)”.

  Next, a case where the isolation level is “READ COMMITED” will be described. About the part which is common in the description so far, the same symbol and reference number are used, and the description is omitted.

  When the isolation level is “READ COMMITED”, reading is performed if it has been committed in another transaction. For example, assume that at time T07 in FIG. 5, the client ID = A performs a search operation instead of performing the change operation and the commit operation of A1. In this case, since the change of C1 and C2 performed by the client ID = C from the client ID = A has been committed, it can be seen from the client ID = A, but the change by the client ID = B. B1 and B2 are not visible because the commit operation has not yet been performed.

  For this purpose, at the time of the search operation, the search processing is performed for the state in which the uncommitted changes other than the own transaction are subtracted from the latest state (latest generation number) of the database 140 at that time. It suffices for the query conversion unit 124 to operate so as to give such a generation formula to the query.

  FIG. 9 is a diagram showing another example of the generation management table 130 in the present embodiment. FIG. 9 shows the contents of the generation management table 130 at time T07. As shown in FIG. 9, the change information field of the generation management table 130 is not “null”, but other than the own transaction (client ID = A), and the value of the committed flag field is “No”. Is indicated by records (2) and (4) in FIG. That is, in the previous example, the change information that is an uncommitted change other than the own transaction is B1 in FIG. 9 (2) and B2 in FIG. 9 (4). The latest generation number at time T07 is generation 05.

Therefore, when the client ID = A whose isolation level is set as “READ COMMITED (committed read)” at time T07 performs a search operation, the generation formula that the query conversion unit 124 should give to the converted query is ,
Generation formula = (Latest generation number)-(Uncommitted for other transactions)
= 05- (B1 + B2)
= 05 + 01-02 + 03-04
It becomes.

  FIG. 10 is a flowchart showing still another example (in the case of “READ COMMITED”) of the operations of the query conversion unit 124 and the transaction management unit 126 in the present embodiment. Here, in step S2020 in FIG. 10, the transaction management unit 126 is configured to calculate the generation arithmetic expression as described above, and to perform a search operation using the query converted in step S2300 with this generation arithmetic expression. ing.

  By configuring as described above, it is possible to generate a query with a generation number assigned to the generation management database so that the isolation level becomes “READ COMMITED (committed read)”.

  Next, the case where the isolation level is “REPEATABLE READ” is described. About the part which is common in the description so far, the same symbol and reference number are used, and the description is omitted.

When the isolation level is “REPEATABLE READ”, “PHANTOM READ” is performed for the case where the isolation level is “SERIALIZABLE”. Here, “PHANTOM READ” means that the local transaction is read again (second time) when it is read by the local transaction (first time) and another transaction inserts data that matches the search conditions and commits. ) When the committed data is reflected and read.

  FIG. 11 is a flowchart showing yet another example (in the case of “REPEATABLE READ”) of the operations of the query conversion unit 124 and the transaction management unit 126 in the present embodiment. Here, in step S2030 of FIG. 11, the transaction management unit 126 is configured to calculate the generation arithmetic expression as described above, and to perform a search operation using the query converted in step S2300 with this generation arithmetic expression. ing.

  The processing in step S2030 at this time will be described by taking as an example the case where the client ID = B performs a search operation instead of the change operation of B3 at time T08 in FIG.

First, as a first step, the generation number registered most recently for the client ID = B is acquired from the generation management table 130. That is, the generation 04 is obtained from the record (4) in FIG. Next, as a second stage, among the records after the most recently registered record ((4) in FIG. 7C), the value of the transaction field is other than the client ID, and the committed flag field The record whose value is “Yes” is obtained. In the previous example, (5) and (6) in FIG. In the final third stage, the generation number obtained in the first stage plus the change information of each record obtained in the second stage is calculated, and this is used as the generation operation formula for the search operation. In the previous example, the generation operation expression for the search operation is the generation operation expression = 04 + (A1 + C2)
= 04 + (06-05 + 05-04)
= 06
It becomes.

  By configuring as described above, it is possible to generate a query with a generation number assigned to the generation management database so that the isolation level is “REPEATABLE READ”.

(Embodiment 2)
In the second embodiment, a case where the generation management database does not perform the difference process between generations will be described. In addition, about the part which is common in Embodiment 1, the same symbol and reference number are used and the description is abbreviate | omitted.

  In addition to the generation management database, a database generally manages data using a table having a table structure. Each row in the table is called a record, and this is a piece of data. The table has a field set with a primary key, and each record can be uniquely identified by this primary key. The table also has a storage address of each record as a pseudo field for the database to access each record, and each record can be uniquely identified by this pseudo field. In the present embodiment, a field in which each record can be uniquely identified by a primary key or a pseudo field is referred to as a “record identification field”.

  FIG. 12 is a diagram showing an example of a table (table name: T_DOC) on the generation management database according to Embodiment 2 of the present invention. In FIG. 12, the record is composed of four fields COL0 to COL3, and five records are registered in the table T_DOC as data. Also, COL0 is a record identification field, and COL0 values val01 to val05 of each record are different values.

In the present embodiment, the case where the operation shown in FIG. 5 is performed will be described as an example in the case of client ID = B. However, here, in FIG.
B1 at time T02 is a change operation for updating,
B2 at time T05 is a change operation for deleting,
It is assumed that B3 at time T08 is a change operation for performing an insert.

  FIGS. 13A to 13C are diagrams illustrating an example of a change operation in the present embodiment performed by the client ID = B at each time. Here, the update operation B1 is defined by SQL expressions shown in FIG. 13A, the delete operation B2 is shown in FIG. 13B, and the add operation B3 is shown in FIG. 13C.

  FIG. 14 shows the difference processing between generations in the present embodiment with respect to the operation flow in the case of “SERIALIZABLE” of the query conversion unit 124 and the transaction management unit 126 in the first embodiment shown in FIG. It is a figure which shows the operation | movement flow for implement | achieving "SERIALIZABLE (serializable)" when not performing. Here, the operation flow of FIG. 14 differs from the operation flow of FIG. 6 in that if the query conversion unit 124 determines that the query received from the client interface unit 122 in step S1800 is a change operation, an additional step is performed. The change information is processed in S1850.

  FIG. 15 is a flowchart showing the contents of the change information processing shown in FIG.

  First, in the change operation B1 at time T02, since B1 is an update operation (Update), the determination in step S1852 proceeds to “YES”, that is, the process proceeds to step S1858. In step S1858, the state before the update of the record affected by the update operation by B1 is acquired. This state before update is used to restore the contents of the database when the update operation of B1 is canceled by rollback. Therefore, step S1858 can be omitted if the database 140 has a function capable of restoring the state of the specific operation.

  FIGS. 16A to 16C are diagrams illustrating an example of the pre-update state acquisition in step S1858 of FIG. Here, FIG. 16A is a general expression for acquiring the state before update. <Table> is a table specified by an update statement, and <condition> is as necessary. Then, the “Where” clause specified by the update statement is described. FIG. 16B is an example of the case of B1, and as a result, all fields of the record updated by B1 can be acquired. Moreover, only the minimum state is acquired by acquiring only the fields (COL2, COL3) and the record identification field (COL0) updated by B1, as in the case of FIG. It is possible to reduce server resources and communication volume.

  FIG. 17 is a diagram illustrating the state of the table before being updated by B1. FIG. 17 lists two records that are changed by the update operation B1. It can also be seen that the record identification field list of the record affected by the update operation is {val02, val04}.

  If the update operation is rolled back, a change operation for updating the state before update may be performed on the database 140. FIG. 18 shows an example of a query when the update operation by B1 is rolled back.

  As described above, the query conversion unit 124 processes the change information included in the query received from the client interface unit 122. The change information newly generated by processing and passed to the transaction management unit 126 includes a record identification field list, a table state before update shown in FIG. 17, and a query for restoring when rolled back shown in FIG. Either one or both.

  Next, in the change operation B2 at time T05, since B2 is a delete operation, the determination in step S1854 in FIG. 15 proceeds to “YES”, that is, the process proceeds to step S1858. In step S1858, the state before the deletion of the record affected by the deletion operation by B2 is acquired as in the case of the update operation. In the case of a delete operation, all the fields of records that meet the conditions are deleted. Therefore, as shown in FIG. 16B, the acquisition field of the “SELECT” statement includes a corresponding record such as “*”. It is desirable to issue a query that retrieves the state of all fields in

  When the delete operation is rolled back, a change operation for adding the state before deletion (Insert) may be performed on the database 140.

  In the case of B2 in the present embodiment, the same state as in FIG. 17 can be acquired as the state before deletion by the above query (FIG. 16B). An example of a query for restoring the state of the database when the delete operation is rolled back is shown in FIG. FIG. 19 is a diagram illustrating an example of a query when the deletion operation by B2 in the present embodiment is rolled back. Similarly, the record identification field list is {val02, val04}.

  As described above, the query conversion unit 124 passes the change information newly generated by processing to the transaction management unit 126.

  Next, in the change operation B3 at time T08, since B3 is an addition operation, the process proceeds to “YES” in the determination of step S1856 of FIG. 15, that is, to step S1860. In step S1860, a list of record identification fields of records added by the addition operation by B3 is acquired. The list of record identification fields of the record to be added is obtained from the value of the additional data being added. For example, in the present embodiment, B3 is indicated by the query shown in FIG. 13C, and the record identification field is COL0. Therefore, the record identification field list includes three items {10, 11, 12}. I understand that.

  When the add operation is rolled back, a delete operation for deleting a record having the record identification field list may be performed on the database 140. FIG. 20 is a diagram illustrating an example of a query when the addition operation by B3 in the present embodiment is rolled back.

  As described above, the query conversion unit 124 passes the change information newly generated by processing to the transaction management unit 126.

  When the generation management formula is calculated by the transaction management unit 126, the query having the change information before being processed once for the change information that is a positive (addition) term in the generation calculation formula shown in the first embodiment. Is issued to the database 140, and regarding the change information that is a negative (subtraction) term, the query for the rollback included in the change information corresponding to the relevant term is issued once. And after issuing the said query about all the terms which comprise the generation formula shown in Embodiment 1, let the newest generation number at that time be a generation formula. This generation number indicates the same state as the database state after applying the generation operation formula described in the first embodiment.

  With the configuration described above, even when the generation management database does not perform inter-generational difference processing, the same as the database state after application of the generation formula described in Embodiment 1 inside the transaction management unit 126 A generation number indicating a state can be generated, and the same effect as in the first embodiment can be obtained.

  Note that the database access device in each of the above embodiments can be used as driver software installed on the client as described above. It can also be used as a servlet engine library that operates on a Web server, an application server, or the like. Furthermore, it can be used as a daemon process on the database. Further, not only software but also a hardware board that implements the database access method can be used mounted on a client, server, database, or the like.

  The generation management database to which the present invention is applied may be any database that manages data by generation number, for example, a version management system for a program source code, or a plurality of programs such as a drama. It can also be applied to a content management / distribution system composed of stories.

  The database access apparatus according to the present invention accesses a generation management database using a query that does not specify a generation number, such as SQL or JDBC, and consequently, for a generation management database. This is useful as a database access device having a transaction mechanism that can be referred to and changed multiple times within one transaction.

1 is a block diagram showing a configuration of a database system including a database access apparatus according to Embodiment 1 of the present invention. (A) The figure which shows an example of the apparatus structure of a client, a database access apparatus, and a database, (B) The figure which shows the other example of the apparatus structure of a client, a database access apparatus, and a database, (C) A client, a database access apparatus , And a diagram showing still another example of the database device configuration The figure which shows the example of setting description of the isolation level by SQL and JDBC The figure which shows the detailed structure of the transaction management table in this Embodiment, and the registration method to a transaction management table The figure which shows the 1st example of a database access operation in this Embodiment. A flowchart showing an example of the operation of the query conversion unit and the transaction management unit (in the case of “SERIALIZABLE”) in this embodiment (A) A diagram showing an example of the generation management table in the present embodiment, showing the contents of the generation management table at time T00 in FIG. 5, and (B) the generation management table at time T04 in FIG. FIG. 5C shows the contents of the generation management table at time T09 in FIG. 5C. A flowchart showing another example of operation of the query conversion unit and the transaction management unit (in the case of “READ UNCOMMITED”) in the present embodiment It is a figure which shows the other example of the generation management table in this Embodiment, Comprising: The figure which shows the content of the generation management table at the time T07 of FIG. Flowchart showing still another example (in the case of “READ COMMITED”) of the operations of the query conversion unit and the transaction management unit in the present embodiment Flowchart showing still another example (in the case of “REPEATABLE READ”) of the operations of the query conversion unit and the transaction management unit in the present embodiment The figure which shows an example of the table on the generation management type database in the database access apparatus which concerns on Embodiment 2 of this invention. (A) It is a figure which shows an example of change operation in embodiment, Comprising: The figure which shows the case of update operation, (B) The figure which shows the case of deletion operation, The figure which shows the case of (C) addition operation A flowchart showing an example of operation of the query conversion unit and the transaction management unit (in the case of “SERIALIZABLE”) when the difference process between generations in this embodiment is not performed The flowchart which shows the content of the change information processing of FIG. (A) A diagram showing an example of the pre-update state acquisition in step S1858 of FIG. 15, showing a general formula for acquiring the pre-update state, (B) a diagram showing an example of the change operation B1 (C) The figure which shows the other example in the case of change operation B1 The figure which shows the state of the table before the update by change operation B1 in this Embodiment. The figure which shows an example of the query when the update operation by change operation B1 in this Embodiment is rolled back The figure which shows an example of the query when the deletion operation by change operation B2 in this Embodiment is rolled back The figure which shows an example of the query when the addition operation by change operation B3 in this Embodiment is rolled back (A) It is a figure for demonstrating the concept of database access, Comprising: The figure which shows the case where a reference query is issued, (B) The figure which shows the case where the query of a change is issued

Explanation of symbols

DESCRIPTION OF SYMBOLS 100 Database system 110 Client 120 Database access apparatus 122 Client interface part 124 Query conversion part 126 Transaction management part 128 Transaction management table 130 Generation management table 140 Database 142 Search processing part

Claims (11)

A client interface unit that accepts a query from the client and provides an interface for performing database access to the client;
A query conversion unit that converts a query obtained from the client interface unit into a query in a native format of a generation management database;
A database access device comprising: A transaction management table for managing a series of queries issued for each client as one transaction;
A generation number to be associated with each query constituting each transaction, change information adapted by the query, and a generation management table for managing the commit state of the transaction;
A transaction management unit having
Configure transactions for each client,
The database access apparatus according to claim 1. When the query obtained from the client interface unit is a search operation and the transaction associated with the client is set to be serializable at that time,
The transaction management unit
Registered between the record most recently registered for the client in the generation management table and the record registered most recently from the record after the start of the transaction in the generation management table record. A record set consisting of the records, and calculating a generation formula that subtracts update information indicated by the record set from a generation number indicated by the most recently registered record
The query conversion unit
Giving the generation formula calculated by the transaction management unit to the converted query,
The database access apparatus according to claim 2. When the query obtained from the client interface unit is a search operation, and the transaction associated with the client is set to uncommitted read at that time,
The query conversion unit
Assign the latest generation number to the converted query on the generation management database at the time of the search operation.
The database access apparatus according to claim 2. When the query obtained from the client interface unit is a search operation and the transaction associated with the client is set to read commit at that time,
The transaction management unit
The latest generation number is acquired on the generation management database at the time of the search operation, and the record set other than the transaction and uncommitted is obtained from the records of the generation management table. Calculate the generation formula that subtracts the update information indicated by the record set,
The query conversion unit
Giving the generation formula calculated by the transaction management unit to the converted query,
The database access apparatus according to claim 2. When the query obtained from the client interface unit is a search operation, and the transaction associated with the client is set to be readable and readable,
The transaction management unit
Finding the record most recently registered for the client in the generation management table and the record set in a committed state other than the transaction after the most recently registered record in the generation management table, Calculating a generation formula that adds the generation number indicated by the most recently registered record and the update information indicated by the record set;
The query conversion unit
Giving the generation formula calculated by the transaction management unit to the converted query,
The database access apparatus according to claim 2. When the query obtained from the client interface unit is a rollback operation,
The transaction management unit
The latest generation number is acquired on the generation management database at the time of the search operation, and a record set in an uncommitted state in the transaction is obtained from the records of the generation management table, and the record set is acquired from the latest generation number. Calculate the generation formula that subtracts the update information indicated by
The query conversion unit
Issuing a query with a generation formula calculated by the transaction management unit,
The database access apparatus according to claim 2.   If the generation management database does not perform inter-generational difference processing, and the query obtained from the client interface unit is an update operation among the change operations, the pre-update state of the record affected by the update operation is displayed. 8. The acquisition management database according to claim 2, further comprising: performing a change operation for updating the contents of the pre-update state on the generation management database when the update operation is rolled back. The database access device according to any one of the above.   If the generation management database does not perform inter-generational difference processing, and the query obtained from the client interface unit is a delete operation among the change operations, the state before deletion of the record affected by the delete operation is displayed. 8. The generation management database according to claim 2, further comprising: performing a change operation for adding the content of the state before the deletion to the generation management database when the previous deletion operation is rolled back. The database access device according to any one of the above.   If the generation management database does not perform inter-generational difference processing, and the query obtained from the client interface unit is an addition operation among the change operations, it is added from the change information included in the addition operation. The record identification field list that is a set of record identification fields of the new record is acquired, and when the previous addition operation is rolled back, the record having the record identification field included in the record identification field list is deleted. The database access apparatus according to claim 2, wherein a change operation to be performed is performed on the generation management database. The transaction management unit
When the generation management database does not perform inter-generational difference processing, change information that is a positive (addition) term for all terms in the generation arithmetic expression is changed information before processing. For change information that is a negative (subtraction) term after issuing a query that has a, after issuing the query used when the rollback included in the change information corresponding to the relevant term , Calculate the latest generation number at that time as a new generation formula,
8. The database access device according to claim 2, wherein the database access device is a database access device.

Images