JP2008186358A - Data base access system and data base access method, and program - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To increase efficiency of processing for database (DB) access. <P>SOLUTION: A DB session pool 22 maintains and manages a plurality of DB sessions to be connected with a database (DB) 30. An increase management thread 23 monitors unused sessions of the DB session pool 22 and increases the number of unused DB sessions when the number of unused DB sessions becomes equal to or below the predetermined number. An idle monitoring thread 24 monitors whether fixed time passes as the DB sessions are left unused and deletes the DB sessions which are not used for the fixed time or over. A business thread 21 performs an acquisition request of a DB session concerning a transaction in accordance with a transaction request to the DB session pool 22, notifies the increase management thread 23 about acquisition of the DB session and executes the transaction while referring to or updating the DB 30 connected by the acquired DB session. <P>COPYRIGHT: (C)2008,JPO&INPIT

Description

  The present invention relates to a database access system, a database access method, and a program, and more particularly to a technique for executing connection / disconnection processing with a database.

  In business processing by a computer, processing is generally performed by accessing a database (DB), and is executed by a plurality of business processing threads (hereinafter referred to as business threads). Patent Document 1 discloses a system for managing a free segment pool that enables an administrator to reduce this risk by acquiring additional storage space when the pool approaches a space shortage in database access. Yes.

  As a related technique, Patent Document 2 discloses a database tuning apparatus that performs monitoring and tuning for database access in units of services. When a processing request including a service name is input from a user terminal, this apparatus stores statistical information including a session ID for identifying a session with the user terminal and session information including the service name in the statistical information storage unit. Database access means for performing the storing process and adding the SQL statement or change SQL statement of the component, the processing time, and the component name to the statistical information every time the component constituting the service with the service name is executed. I have.

  As a related technique, Patent Document 3 discloses that pool management of session control resources is performed and a session establishment request addressed to a communication processing device from any terminal device connected to the LAN is permitted to be connected to the host computer. A dynamic terminal connection method is described in which a session control resource that is received as a session establishment request that is virtually defined as being to be selected, a pool-managed session control resource is selected, and a session is established using the selected resource.

JP 2006-302258 A JP 2006-277179 A JP-A-3-266156

  When a database is referred to or updated from a plurality of business threads existing in one process, at least the same number of DB sessions as the number of business threads that are referred to or updated simultaneously are required. In this case, (1) Prepare the same number of DB sessions as the number of business threads in advance, or (2) Prepare a certain number of DB sessions on the process in advance and refer to or update the database from the business threads. When there is a vacant DB session when the request is issued, it is necessary to pay out the DB session to the business thread, and to create a new DB session if there is no vacancy.

  In the method (1), DB sessions corresponding to the number of business threads are always held, and resources are wasted when the business threads are not referring to or updating the database. On the other hand, in the above method (2), when there is no free DB session, the DB session is created after the request is generated, and therefore the processing of the business thread is equivalent to the processing time for creating the DB session. Will cause a delay.

  A technique for solving such a waste of resources and occurrence of delay has not been known so far and has been desired as a problem to be solved. Therefore, an object of the present invention is to reduce the time required for business processes from starting to ending the connection to the database to be referenced or updated from the business thread, and to share the processes linked in the server. It is an object to provide a database access system, a database access method, and a program for improving the efficiency of resources.

  A database access system according to an aspect of the present invention includes a database session management unit that maintains and manages a plurality of database sessions for connecting to a database, and monitors unused database sessions in the database session management unit. Monitor the increase management thread that increases the number of unused database sessions when the number of database sessions is less than or equal to the predetermined number, and whether the database sessions in the database session management unit have not been used for a certain period of time, An idle monitoring thread that deletes a database session that has not been used for a certain period of time, and a database session acquisition request for a transaction corresponding to an incoming transaction request. Performed for emissions management unit, it notifies the acquisition of the database session increasing management thread comprises a work thread executing the transaction with reference or update the database to be connected by the acquired database session, the.

  In the database access system of the present invention, the idle monitoring thread may check database sessions that have not been used at predetermined time intervals.

  In the database access system of the present invention, each database session holds the last use time, and the idle monitoring thread is used for a predetermined time or more while the database session is unused by comparing the current time with the last use time. You may make it judge that there was not.

  In the database access system of the present invention, the increase management thread decreases the number of unused database sessions by 1 when notified of acquisition of a database session, increases the number of database sessions in use by 1, and is unused. When the number of database sessions becomes equal to or less than the predetermined number, unused database sessions may be increased by a predetermined number of increased connections.

  A database access method according to another aspect of the present invention includes: (a) a step of creating a predetermined number of database sessions connected to a database in a database session pool; and (b) a database for performing reference / update to the database. Requesting the session pool to issue a database session, and (c) if the number of database sessions remaining in the database session pool falls below a predetermined value as a result of the payment, a predetermined increase in the number of connections Connecting an additional database session to the database and adding the additional database session to the database session pool; (d) examining the database session usage at a predetermined time interval, and unused and at a predetermined disconnection interval; Including the step of deleting elapsed have been a database session disconnection from a database session pool from the database over.

  In the database access method of the present invention, in step (d), each database session retains the last use time and compares the current time with the last use time so that the database session remains unused and used for a certain period of time. You may make it judge that it was not done.

  In the database access method of the present invention, in step (c), as a result of the payout, the number of unused database sessions is decreased by 1, the number of database sessions in use is increased by 1, and the number of unused database sessions. May be increased by a predetermined increase in the number of database sessions.

  A program according to still another aspect of the present invention includes a database session management process for maintaining and managing a plurality of database sessions for connecting to a database in a computer constituting a database access system, and an unused database in the database session management process Monitors sessions and increases management threads to increase the number of unused database sessions when the number of unused database sessions falls below a predetermined number, and for a certain period of time while database sessions remain unused in database session management processing Idle monitoring thread that monitors whether it has passed and deletes database sessions that have not been used for a certain period of time, and data related to transactions in response to incoming transaction requests A transaction session that executes a transaction while referring to or updating the database connected by the acquired database session, and notifies the increase management thread of the acquisition of the database session. Is executed.

  According to the present invention, it is possible to create a DB session on the increase management thread so as not to delay the processing of the business thread, and to delete the DB session that has not been used for a certain period of time by the idle monitoring thread, thereby It is possible to appropriately adjust the number of DB sessions while maintaining the number of DB sessions to be used and not holding unnecessary DB sessions. Therefore, it is possible to avoid unnecessarily securing resources such as the amount of memory, the number of sockets, and the session of the DB server that are shared with other processes operating in the server without delaying the processing of the business thread. For this reason, efficiency can be achieved when the shared resource is linked with another process.

  FIG. 1 is a block diagram showing a configuration of a database access system according to an embodiment of the present invention. In FIG. 1, the database access system is realized in a business processing process 20 such as a server, and the business processing process 20 refers to or updates the database 30 by a processing request (transaction request) from a client AP (application) 10. While performing, a desired process is executed.

  The business process 20 includes one or more business threads 21, a DB (database) session pool 22, an increase management thread 23, an idle monitoring thread 24, and operation definition information 25. The DB session pool 22 includes DB sessions 40a to 40j and 40k to 40n. The last use time 50 is described in each of the DB sessions 40a to 40j and 40k to 40n. The operation definition information 25 includes information on a maximum connection number 41, an initial connection number 42, a connection increase number 43, an increase threshold value 44, an idle disconnection interval 45, and an idle monitoring interval 46. Processing in each of the business thread 21, the DB session pool 22, the idle monitoring thread 24, and the increase management thread 23 is performed by executing a program by a computer constituting the database access system.

  The client AP 10 makes a processing request to the business processing process 20. The business process 20 that has received the processing request causes the business thread 21 in the process to execute the requested processing.

  When the business process 20 is started, the number of DB sessions 40 a to 40 j determined in advance by the initial connection number 42 is connected to the database 30 to create a DB session pool 22.

  The business thread 21 receives a processing request from the client AP 10 and requests the DB session pool 22 to pay out a DB session in order to refer / update the database 30. The business thread 21 that has received the payout performs business processing by referring to the database 30 and updating the result of the business processing in accordance with the business processing programmed in advance. The number of unused (empty) DB sessions remaining in the DB session pool 22 as a result of paying out unused (empty) DB sessions from the DB session pool 22 is the value determined by the increase threshold 44. Suppose that it falls below. In this case, the business thread 21 that has issued the DB session payout request notifies the increase management thread 23 of the increase and continues the business process.

  Further, when the reference / update to the database 30 is completed, the business thread 21 returns the used DB session to the DB session pool 22 and records the last use time 50 in the returned DB session. The returned DB session is paid out again when there is a payout request from the business thread 21. In this case, the payout destination business thread 21 is not necessarily the same as the business thread used last time.

  The DB session pool 22 includes DB sessions 40a to 40j and added DB sessions 40k to 40n. Each DB session is connected to the database 30, paid out in response to a request from the business thread 21, and the last use time 50 accessed by the business thread 21 is described.

  The increase management thread 23 receives an increase notification from the business thread 21 and makes a connection request to the database 30. Also, additional DB sessions 40k to 40n corresponding to the number of connection increases 43 are created. Then, the DB sessions 40k to 40n are added to the DB session pool 22, and the added DB sessions 40k to 40n are included in the DB sessions 40a to 40j as unused (free) DB sessions. In the DB session pool 22, DB sessions 40 a to 40 j that can always be paid in response to a DB session payout request from the business thread 21 are prepared.

  The idle (IDLE) monitoring thread 24 operates at a time interval determined by the idle monitoring interval 46 and checks the usage status of the DB sessions 40 a to 40 k in the DB session pool 22. As a result of the examination, if the current time is more than the idle disconnection interval 45 or more from the last use time 50 recorded in each DB session of the DB sessions 40a to 40k, the DB session that has passed the time is retrieved from the database 30. Disconnect and delete from the DB session pool 22.

  Next, the operation of the database access system configured as described above will be described. FIG. 2 is a flowchart showing the operation of the database access system according to the embodiment of the present invention.

  In the standby state of step S10, when the business thread 21 receives a processing request from the client AP 10, it starts business processing and requests a DB session for referring / updating the database 30 to the DB session pool 22.

  The DB session pool 22 requested for the DB session pays out the DB session to the requesting business thread 21 when there is an empty DB session in the DB sessions 40a to 40k. As a result, the number of DB sessions in use increases by 1, and the number of unused DB sessions decreases by 1 (step S11).

  The business thread 21 uses the paid-out DB session to refer / update the database and complete the business process. The business thread 21 that has completed the business processing returns the DB session to the DB session pool 22. Further, when returning the DB session, the return time is recorded at the last use time 50 of the DB session. The returned DB session is paid out again when a request is generated from the business thread 21. Note that the business thread 21 to which the DB session is paid out again is not necessarily the same as the previous payout destination business thread 21. When receiving a DB session payout, the business thread 21 checks the number of empty DB sessions remaining in the DB session pool 22 (step S12). When the confirmed number is equal to or less than the increase threshold 44 (Y in step S12), the increase management thread 23 is notified that the free DB session is equal to or less than the increase threshold 44.

  The increase management thread 23 starts its operation upon receiving a notification from the business thread 21. The increase management thread 23 creates additional DB sessions 40k to 40n for referring / updating the database 30 from the business process 20 by the number determined by the connection increase number 43 one by one. The created additional DB sessions 40k to 40n are included in the DB sessions 40a to 40j of the DB session pool 22 (step S13).

  If the number of DB sessions in the business process becomes the maximum number of connections 41 as a result of creating one additional DB session, the increase management thread 23 has not created additional DB sessions for the increased number of connections. Even the operation ends. The increase management thread 23 that has finished the operation starts the operation when receiving a notification from the business thread 21 again. When the increase management thread 23 receives a notification from the business thread during the operation, the increase management thread 23 does not perform anything for the notification.

  On the other hand, in the standby state in step S 10, the idle monitoring thread 24 operates at an interval of time determined by the idle monitoring interval 46.

  When the idle monitoring interval elapses, the idle monitoring thread 24 starts investigating an empty DB session in the DB session pool 22. The idle monitoring thread 24 compares the last use time 50 of the examined DB session with the current time (step S14). As a result of the comparison, if the difference between the current time and the last use time 50 is equal to or greater than the idle disconnection interval 45 (Y in step S14), the DB session is disconnected from the database 30, and the DB session pool 22 and the business process 20 Is deleted (step S15).

  In the database access system operating as described above, the idle monitoring thread 24 is responsible for the disconnection process and the increase management thread 23 is responsible for the connection process in the database connection and disconnection process that takes time. Further, it is assumed that the connection process is triggered by the notification from the business thread 21 and the disconnection process is performed at the idle disconnect interval 45. Furthermore, the business thread 21 performs only the processing of transaction requests generated from the client AP 10 and the DB session acquisition request. By operating the database access system in this way, when the number of sessions and the flow rate of the transaction are balanced, it is difficult for the increase management thread 23 to generate a processing load, and the processing load of the disconnection processing thread that is frequently executed is also increased. It can be made difficult to occur. Therefore, it is possible to release a burden caused by time-consuming processing from the business thread 21, thereby reducing the processing burden on the entire process.

  Originally, when the DB session is full, the business thread 21 has to wait for the creation of the DB session to be completed. On the other hand, when the number of vacant DB sessions becomes less than a certain number and the DB sessions are likely to be insufficient, the increase management thread 23 different from the business thread 21 creates a new additional DB session, and on the business thread 21 Processing efficiency is improved by preventing a time delay due to waiting for DB session creation. Further, the idle monitoring thread 24 periodically monitors the presence or absence of a DB session that has not been used for a long time, and by deleting DB sessions that have not been used for a certain period of time, the number of DB sessions is maintained appropriately, and server resources are wasted. Can be prevented.

  According to the database access system as described above, the processing delay of the business thread is prevented by increasing the number of DB sessions with a background thread different from the business thread before the necessary DB sessions are exhausted. Furthermore, useless use of resources by preparing more DB sessions than necessary at the time of activation is prevented. By periodically disconnecting DB sessions that have not been used for a long time, it is possible to maintain the number of DB sessions used by business processing processes and adjust the number to an appropriate number without holding unnecessary DB sessions. It becomes. As a result, resources such as the amount of memory shared with other processes running in the server, the number of sockets, and database server sessions are not reserved more than necessary, and resources shared within the server are linked with other processes. It is possible to improve the efficiency when performing.

It is a block diagram which shows the structure of the database access system which concerns on embodiment of this invention. It is a flowchart which shows operation | movement of the database access system which concerns on embodiment of this invention.

Explanation of symbols

10 Client AP
20 business processing process 21 business thread 22 DB (database) session pool 23 increase management thread 24 idle monitoring thread 25 operation definition information 30 database 40a-40j, 40k-40n DB session 41 maximum connection number 42 initial connection number 43 connection increase number 44 Increase threshold 45 Idle disconnect interval 46 Idle monitor interval 50 Last use time

Claims (13)

A database session manager that maintains and manages multiple database sessions for connecting to the database;
An increase management thread that monitors unused database sessions in the database session management unit and increases the number of unused database sessions when the number of unused database sessions falls below a predetermined number;
An idle monitoring thread that monitors whether a database session in the database session management unit has not been used for a certain period of time and deletes a database session that has not been used for a certain period of time;
In response to an incoming transaction request, a database session acquisition request related to a transaction is made to the database session management unit, the database session acquisition is notified to the increase management thread, and the database connected by the acquired database session A business thread that executes transactions while referring to or updating
A database access system comprising:   The database access system according to claim 1, wherein the idle monitoring thread checks a database session that has not been used at predetermined time intervals. Each database session has a last used time,
The database access system according to claim 2, wherein the idle monitoring thread determines that the database session has not been used for a certain period of time without being used by comparing the current time with the last use time.   The increase management thread decreases the number of unused database sessions by 1 when notified of acquisition of a database session, increases the number of database sessions in use by 1, and sets the number of unused database sessions to a predetermined value. 2. The database access system according to claim 1, wherein when the number is less than the number, the number of unused database sessions is increased by a predetermined number of connections. (A) creating a predetermined number of database sessions connected to the database in a database session pool;
(B) requesting the database session pool to issue a database session in order to perform reference / update to the database, and receiving the payment;
(C) If the number of database sessions remaining in the database session pool falls below a predetermined value as a result of the payout, an additional database session is connected to the database for a predetermined connection increase number, and the additional database session Adding to the database session pool;
(D) examining the usage status of the database session at a predetermined time interval, disconnecting the database session that has not been used and has passed the predetermined disconnection interval time from the database, and deleting the database session from the database session pool;
A database access method comprising:   In the step (d), each database session holds the last use time, and compares the current time with the last use time to determine that the database session is unused and has not been used for a certain period of time. The database access method according to claim 5, wherein:   In the step (c), as a result of the payout, the number of unused database sessions is decreased by 1, the number of database sessions in use is increased by 1, and the number of unused database sessions is less than a predetermined number. 6. The database access method according to claim 5, wherein an unused database session is increased by a predetermined number of connection increases. On the computer that configures the database access system,
A database session management process for maintaining and managing multiple database sessions for connecting to the database;
An increase management thread that monitors unused database sessions in the database session management process, and increases the number of unused database sessions when the number of unused database sessions falls below a predetermined number;
An idle monitoring thread that monitors whether a database session has been unused for a certain period of time in the database session management process and deletes a database session that has not been used for a certain period of time;
In response to an incoming transaction request, a database session acquisition request relating to a transaction is made to the database session management process, the database session acquisition is notified to the increase management thread, and the database connected by the acquired database session A business thread that executes transactions while referring to or updating
A program that executes   9. The program according to claim 8, wherein the idle monitoring thread examines a database session that has passed unused at a predetermined time interval. Each database session has a last used time,
The program according to claim 9, wherein the idle monitoring thread determines that the database session is unused and has not been used for a certain period of time by comparing the current time with the last use time.   The increase management thread decreases the number of unused database sessions by 1 when notified of acquisition of a database session, increases the number of database sessions in use by 1, and sets the number of unused database sessions to a predetermined value. 9. The program according to claim 8, wherein when the number is less than or equal to the number, the number of unused database sessions is increased by a predetermined number of connections.   9. The program according to claim 8, wherein the database session management process, the increase management thread, the idle monitoring thread, and the business thread are executed in a business process.   The program according to claim 12, wherein the business process is executed by a request from an application program in a client.

Images