JP2610926B2 - Transaction control method - Google Patents

Description

Description: BACKGROUND OF THE INVENTION The present invention relates to a transaction control system for a computer.

[Conventional technology]

The commit process is described in, for example, “§§ 172 to 177” of Iwanami Koza Information Science-8 “Information Structure and Database” (Iwanami Shoten, published June 10, 1983) (hereinafter referred to as Reference 1). This is a general concept, as described in the section on Disaster Recovery. In the following, the text described on page 173, line 4 to page 174, line 14 of Reference 1 is cited.

"If a failure interrupts the execution of a logical unit of work, the database will be in an inconsistent state. After the execution has been completed, we want to be able to reproduce the result even if the failure destroys the result. Thus, the concept of a logical unit of work plays a significant role in disaster recovery.

(1) A logical unit of work must be either completely executed or not executed at all.

(2) The logical work unit should be able to return to the initial state.

(3) The execution of a logical unit of work should be reproducible.

(2) 'In order to return a logical work unit independently of the others, all objects to be changed must be locked until the end.

(3) 'In order for a logical unit of work to be reproducible independently of the others, all objects to be referenced must also be locked to the end.

In the following, the locking rule of (2) ′ (3) ′ is a major premise. Locking and unlocking is the responsibility of the system in principle. Unlocking is performed when the user explicitly declares the end of the logical work unit, or when the logical work unit is implicitly ended by the normal end of terminal use. At this time, the data of the work area is written out to the database, and the change is reflected in the database. It is said that this unlocking and writing operation is committed or reflected. That is, the commit process is performed in a logical unit of work (job).
Is a process for releasing the data that has been referenced / updated, and making it available to other jobs. Specifically, the following processing is performed.

(A) Updating the database file In the database system, in order to be able to handle a large amount of data at high speed, the necessary data is expanded from the magnetic disk device that requires input / output time to the main storage device that has fast data access. To use. Usually, this area on the main storage is called a buffer. The buffer has a size corresponding to a block for holding data on the magnetic disk device. These buffers constitute a buffer group composed of a plurality of buffers. When the commit processing is executed, and when the data is updated, the information in the buffer is written on the magnetic disk device.

(B) Outputting a checkpoint to a journal file It is a general method to output a journal in order to prevent data on a database system from being destroyed and use it when a failure occurs. Journal output usually takes two things.

-Rollback journal (journal before update) This is used to prevent the database from becoming incomplete if the processing ends abnormally during update. The rollback journal retains the state before the data is updated, and covers this information in the event of abnormal termination. Therefore, when the update process is completed (that is, in the commit process), a checkpoint indicating that the process is completed is output.

-History journal (updated journal) If the magnetic disk device or the like that stores data has been destroyed, it is used to restore it to the state immediately before it was destroyed. The history journal holds the data after the update. The operation of a database system generally lasts for a long time, but the contents of the database are taken up on a magnetic tape or the like at certain intervals (weekly or daily) for troubleshooting. If the magnetic disk drive is destroyed,
The contents of the magnetic tape evacuated to another magnetic disk device are returned, and restored using a history journal until just before destruction. Therefore, checkpoint information indicating that the update has been completed is also output to the history journal.

The present invention is directed to a rollback journal.

(C) Releasing the lock list The information lock list for specifying that the data is being used is released in response to the data that has been referenced or updated. The lock list will be described later.

In this type of transaction control method, the update is invalidated by a rollback (ROLLBACK) instruction, and the commit (CO
(MMIT) instruction is used to enable updating.

Conventionally, the interval at which a COMMIT instruction is issued for a batch job is left to the user program.

Next, the prior art will be described in detail with reference to the drawings.

FIG. 4 is a diagram for explaining a conventional transaction control method.

In FIG. 4, the conventional transaction control method comprises an operation number setting means 41, a transaction operation means 42,
It has an operation number counting unit 43, an operation number determination unit 44, and a COMMIT operation unit 45.

The number-of-operations setting means 41 sets how many times the transaction processing is executed before executing the COMMIT instruction or sets the number of transaction processings.

The transaction operation means 42 performs transaction processing. The operation count totaling means 43 counts the number of times the transaction has been executed since the stationary point was established. The number-of-operations determination means 44 is the number of transaction processes counted by the number-of-operations counting means 43 and the number-of-operations setting means 41
Is compared with a preset transaction processing count. When the number of transaction processes counted by the operation count totaling unit 43 exceeds the transaction processing circuit set by the operation count setting unit 41, the operation count determining unit 44
Notify. The COMMIT operation means 45 executes a COMMIT instruction.

The number of times of transaction processing is changed in various ways by the operation number setting means 41, and the number of times of transaction processing is considered to be optimal by repeatedly trying until the optimum execution interval of the COMMIT instruction is found.

[Problems to be solved by the invention]

In the case of a communication job in which the concept of the transaction is clear, the point at which the COMMIT instruction is executed from the user program can be the end point of the transaction, and can be easily determined from the user program or the system. However, in the case of a batch job, the end time of the transaction is not always the time of the optimal COMMIT instruction, and the COMMIT instruction is executed after the transaction is repeatedly executed several times. In this method, COMMI
The interval at which the T instruction is executed must be found by trial and error. In addition, there is no guarantee that the found interval is the optimum interval, and the execution of the COMMIT instruction causes an exclusive wait, an exclusive error, or an error in the program due to lock list overflow, journal file overflow, etc. It may end. Conversely, if the execution interval of the COMMIT instruction is short, the file I / O increases due to the actual update to the file for each COMMIT instruction,
An increase in the number of CPUs associated with the lock list release processing causes a decrease in system throughput.

An object of the present invention is to make it possible to execute a COMMIT instruction at an appropriate timing in the operation of a database system, thereby preventing queuing due to contention for a lock of a business program, an exclusive error, an overflow of a journal, and the like. Another object of the present invention is to improve the throughput of a database system.

[Means for solving the problem]

The present invention inquires whether execution of a COMMIT instruction is necessary in a database system that performs transaction control in which update is invalidated by a ROLLBACK instruction and update is enabled by a COMMIT instruction, and the COMMIT processing is performed based on the result. The test COMMIT means to be performed, the journal output amount judgment means for judging whether the execution of the COMMIT instruction is necessary based on the journal output amount, and the lock list holding amount for judging whether the execution of the COMMIT instruction is necessary based on the lock list holding amount This is a transaction control method that includes a determination unit and an update lock holding amount determination unit that determines whether a COMMIT instruction is required based on the amount held by the update lock list.

〔Example〕

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

Referring to FIG. 1, the transaction control system according to one embodiment of the present invention comprises a test COMMIT unit 1, a journal output amount judgment unit 2, a lock list holding amount judgment unit 3, and an update lock holding amount judgment unit 4. I have.

The test COMMIT means 1 inquires whether a COMMIT instruction should be executed, and if the query indicates that the COMMIT instruction should be executed, executes the COMMIT instruction. The journal output amount determination means 2 determines whether or not to execute a COMMIT instruction based on the journal output amount. The lock list holding amount determination means 3 calculates the CO from the lock list holding amount
Determines whether to execute the MMIT instruction. The update lock holding amount determination unit 4 determines whether or not to execute the COMMIT instruction based on the holding amount of the update lock list.

FIG. 2 is a diagram showing a journal control table. The journal control table 21 has journal allocation information 23 and journal output information 24 of a journal file 22. The journal allocation information 23 has a journal file capacity 25 and a journal type 26. The journal type is an identifier indicating whether the journal is a shared journal or a private journal. The journal output information 24 has a journal output amount 27 from the stationary point.

FIG. 3 is a diagram showing a lock list table. The job management list 31 is a table created for each job, and holds a lock list holding number 32, an update lock list holding number 33, and an update buffer number 34 held by a job. The lock list pool table 35 is for storing the lock list 37, and is commonly used in the system.

 Next, the operation of the present invention will be described with reference to the drawings.

At the end of the transaction, the test COMMIT unit 1 inquires whether a COMMIT instruction should be executed.
As a result of the inquiry, when an instruction to execute the COMMIT instruction is received, the test COMMIT means 1 executes the COMMIT instruction. If it is indicated that execution of the COMMIT instruction is not necessary,
The test COMMIT means 1 does not execute the COMMIT instruction.

Next, the journal will be described. Reference 1
On page 174, lines 21 to 25, the following is described.

"The main means of restoring and reproducing a logical work unit is a copy (also called log, audit trail, or journal). In the copy, each logical work unit changes its target one by one. To restore the original state, the original state can be restored by tracing the value (before image) immediately before the change of the object changed by the change in the reverse order of execution, and this is called a rollback. As can be inferred from this description, the journal needs to be output immediately before changes in the work area are reflected in the database in the commit process. Further, when the commit process is completed, the journal for the purpose of the rollback process becomes unnecessary, the journal area can be reused as another journal area, and the overflow of the journal file can be prevented.

The journal output amount determination means 2 compares the journal file capacity 25 of the journal control table 21 with the journal output amount 27 from the quiesce point to determine whether to execute the COMMIT instruction. The judgment criteria differ depending on the journal type 26. In the case of a shared journal, if the batch job is a batch job, the journal output 27 from the quiesce point exceeds 20% of the journal file capacity 25, and if it is a communication job, the quiesce When the journal output amount 27 from the point exceeds 10% of the journal file capacity 25, the journal output amount determination means 2 instructs execution of a COMMIT instruction. In the case of private journals, the amount of journal output 27 from the quiesce point is not limited to batch-type and communication-type jobs.
When it exceeds 80%, the journal output amount determination means 2 instructs execution of a COMMIT instruction.

On page 174, line 5 to line 8 of the above document 1, "(2) 'In order to restore the logical work unit independently of the others, lock all the objects to be changed with the exclusive lock until the end. (3) 'In order for a logical unit of work to be reproducible independently of the rest, all objects to be referenced must also be locked to the end. "
As described above, a locking / unlocking operation for exclusive use or sharing is required. This locking / unlocking process normally leaves information in the form of a list on a work space that can be shared and referenced by the system. This locking information is called a “lock list”. Similarly, the exclusive lock is called "update lock list".
That. The work space for managing the lock list is called a “lock list pool”. For example, ACOS software
ACOS-2 / FF RIQS Manual (NEC, March 1987 7th Edition)
The “LOCK table” described in the section “2.2.2 Determining the resident table size” on page 61 of this document (hereinafter referred to as Document 2) corresponds to this.

The lock list holding amount determination means 3 compares the number of lock lists 36 of the lock list pool in the lock list pool table 35 of the lock list table with the number of held lock lists 32 to determine whether or not to execute the COMMIT instruction. For a batch job, if the number of lock lists held 32 exceeds 20% of the number of lock lists 36 in the lock list pool,
In the case of a communication job, when the number 32 of lock lists retained from the quiesce point exceeds 10% of the number 36 of lock lists in the lock list pool, the lock list retained amount determination means 3 instructs execution of a COMMIT instruction.

The third to ninth lines of page 61 of the above-mentioned document 2 describe the “update buffer” as follows.

"When an update instruction is executed, if the block containing the specified record does not exist in the system buffer, the block is read into the system buffer. If the block to be updated by the update instruction already exists in the system buffer as a reference buffer, If it exists, it updates using that buffer, which is called the update buffer.

The update buffer is occupied by the activity that has executed the update instruction and cannot be used by other activities. However, from the occupying activity COMM
When the IT / ROLLBACK instruction is issued, it can be shared by multiple activities. The "update buffer" in the present invention is based on the above description. A job that occupies and uses the update buffer is referred to as a “unique job”. The “unique job” will be described in some detail. The data read from the magnetic disk device onto the main storage device, that is, the buffer area,
By sharing and using a jab that uses the database system, the number of inputs to the magnetic disk device can be reduced. However, since data being updated cannot be referenced or updated by another job, the buffer in such a state is occupied and used by a unique job. This job is called a unique job.

The update lock holding amount determination means 4 compares the update lock list holding number 33 with the update buffer number 34 to determine whether or not to execute the COMMIT instruction. As a determination method, when the update lock list holding number 33 exceeds 1.5 times the update buffer number 34, the update lock holding amount determining unit 4 instructs execution of a COMMIT instruction. The number of update buffers 34 is the number of buffers used by private jobs occupying the buffer area for I / O shared by the database system. If the number of update lock lists 33 exceeds 1.5 times the number 34 of update buffers, The hit rate of the COMMIT instruction is reduced, and it is not necessary to delay the execution of the COMMIT instruction.

Hereinafter, the present invention will be described in detail using a sales management operation as an example.

Sales management operations are roughly divided into order processing operations and inventory allocation operations. The database system used in the sales management business includes an order number table, a customer table, an order slip table, an inventory master table, and an index key system associated therewith.

Each of the order number table, customer table, order slip table, and inventory master table is a polymer of a record including a plurality of items.

The order number table is composed of order numbers that have been paid out and order numbers that have been allocated to stock. At first, both the paid-out order number and the stock-ordered order number are 0.

The customer table has items for managing customer information such as customer number, customer name, current month sales amount, last month sales amount, and other customer addresses. There are index keys for customer numbers and customer names to speed up reading into a particular customer record. The index key is for taking out the value of the item of the customer number or the customer name and arranging the values in descending order of value, and holds the storage address of the customer record on the magnetic disk described later as a pointer value. By referring to the value of the item of the customer number or the customer name using the index key, the corresponding record can be accessed at high speed. In particular, the customer number can be uniquely determined by a serial number determined for each customer.

The order receipt slip table includes items such as an order number, a sales representative, a customer number, a product name, an order quantity, a reception date, and a delivery date. The order number has an index key in order to read the order slip record at high speed.

The stock master table includes items such as a product, a product code, a stock quantity, an order point, an average delivery date, an order quantity, and an order destination. In order to speed up access to a specific record in the inventory master table, an index key similar to the customer table is attached to the product name and product code.

The records of the order number table, customer table, order slip table, and inventory master table are collectively stored for each table and stored in the magnetic disk device.

 FIG. 5 is a diagram illustrating a magnetic disk drive.

The magnetic disk device has a storage area in which a plurality of record disks are stacked. It has a storage area on a closed circle on a disk equivalent to a record disk, and this is called a track. A set of tracks located on concentric vertical circles on a plurality of record disks is called a cylinder. There are a plurality of cylinders from the outside on the record disk toward the center.

FIG. 6 is a diagram showing an image in which a database system for sales management business is stored in the magnetic disk device.

To simplify the problem, if the block size managed by the database is fixed at 4096 bytes, four blocks can be stored in one track. The surplus area is not available. The block is a unit for reading a record from the magnetic disk device to the hand storage device or writing a record from the main storage device to the magnetic disk device. 40
Records of the order number table, customer table, and order slip table are stored in a 96-byte block. However, it is assumed that the database system occupies 96 bytes in the block as an area for managing the block, and records of each table are stored in the remaining 4000 bytes.

FIG. 7 is a flowchart for explaining the processing of the order receiving business.

First, an order number record is read from the order number table (101), the value of the issued order number in the record is incremented by 1 (102), and the record is written back to the record (103).

Next, a corresponding customer record is read from the customer table using the customer name as a key (104).

Next, in addition to the order number and the customer number of the acquired customer record, the sales person, product name, quantity, delivery date, etc. are input (10
5) Write in order receipt record (106).

After completion of steps 101 to 106, the test COMMIT means 1 is executed (107).

Thereafter, it is determined whether or not there is further order processing (108). If there is, processing returns to 101, and if not, COMMIT is performed (10
9).

The order processing task is a communication job that is executed simultaneously by a plurality of sales personnel from a terminal connected to the database system. Up to 10 sales representatives at a time from the terminal screen
Data can be entered at the same time. Also,
Order processing is performed between 9:00 and 17:00 during business hours. The application programs that perform the order processing are represented as order 1, order 2,..., Order n.

FIG. 8 is a flowchart for explaining the processing of the inventory allocation business.

First, the order number record is read from the order number table (111), and the order number with inventory allocation is used as a key to position the order number record at the top of the order form table for which inventory allocation has not been completed (112).

Next, the order receipt record is read from the order receipt table (113), and the corresponding stock master record is read from the inventory master table using the product name as a key (114).

Next, the order quantity of the order slip record is subtracted from the stock quantity of the stock master record (115). If the stock quantity after subtraction becomes equal to or less than the order point (116), order processing is performed (1).
17).

Next, when the stock quantity is minus (118) and the delivery time of the order receipt record is earlier than the average delivery time of the stock master record (119), an alarm is output (120) and the stock record is written back (121). ).

After the completion of 111 to 121, the test COMMIT means 1 is executed (122).

After that, it is determined whether there is any outstanding order slip (123),
If there is an unprocessed order slip, the order slips are read out in order of the order number and the process returns to 113. If not, the order number that has been processed last is substituted for the stock-allocated order number in the order number record (124 After that, COMMIT is performed (125).

The stock allocation business is a batch job performed at one time, three times a day at 12:00, 15:00, and 17:00. There is one application program that performs the inventory allocation business, and is represented as inventory 1.

The record is referred to on a buffer secured by the data system in advance. This buffer is prepared in plurals,
The block containing the record to be referenced is read from the magnetic disk device to the buffer at the time of the first input request. Of the records read into the buffer, only the records requested by the business program are copied onto the main storage that can be used by the business program. Lock the reference before referring to the buffer. If the reference lock is not locked, the buffer cannot be referenced. Operations related to locking will be described later.

When a business program issues a record update request, the image of the updated record is written back to the area previously occupied by the record on the buffer. At this time, the image before update on the buffer is output as a journal record. The journal operation will be described later. Prior to updating the buffer, an update lock is obtained. If the update lock is not held, the buffer cannot be updated. The update lock will also be described later.

Further, when a record registration is requested from the business program, and when the registration request is made for the first time, a new unused buffer is secured and an image of the record requested to be registered is transferred. Prior to record registration, an update lock is applied. If a buffer for record registration has already been acquired, the image of the record is transferred to an unused area on the buffer. If there is not enough space to store the record, allocate a new unused buffer,
Transfer the image of the record requested to be registered. An update lock is required before allocating a buffer.

When a new buffer is reserved, unused blocks on the magnetic disk device for storing the database file are reserved in a one-to-one relationship. The image of the secured block is output as a journal record. Regarding the blocks on the database file, whether each block is an unused block or a used block is managed by free management block information. When allocating a new block, the database system refers to the free block information to obtain an unused block, and changes the free space management information for the obtained block to used.

The buffer managed by the database system is shared or used exclusively by a plurality of business programs operating simultaneously. The referring buffer can be referred to by other business programs that operate simultaneously. The updated buffer rejects reference and update from other business programs and uses it exclusively. The latter updating buffer is called an update buffer.

The update image is reflected in the database file unless the buffer runs out during the execution of the transaction.
This is performed when the COMMIT instruction is executed. When the rollback processing is executed, the contents updated on the buffer are cancelled, and the transaction ends in failure. The reference lock and update lock are released when the COMMIT instruction or ROLLBACK processing is completed.

When a business program newly requests a record included in another block or newly writes a record, the buffer secured in advance may be insufficient. In this case, the used reference buffer is used, and is used as a buffer of an output block for reading another block or registering a new record. However, when a certain business program performs a large amount of update processing in one transaction, for example, in the inventory allocation business, there are a large number of order receipt records for which inventory allocation has not been completed, and operations 111 to 123 were repeatedly performed in one transaction. In this case, when the buffers are insufficient, only the reference buffers are sequentially released and reused, and the buffers managed by the database system eventually become only the update buffers for the inventory allocation business. To prevent this,
The number of available update buffers 34 is determined for each business program. If the number of update buffers is exceeded, the contents of the buffers can be written back to the database file on the magnetic disk device to release the buffers.

Prior to writing back the contents of the buffer to the magnetic disk device, the pre-update image is output as a journal. If the rollback processing is executed, the image of the block on the magnetic disk device is restored to the time before the execution of the transaction by using the journal of the image before update.

A conflict occurs between the order processing operations or the inventory allocation operation in the order number table and the order slip table that are updated. This means that you can browse, update,
It is necessary to lock at the time of registration and exclude reference and update from other business.

There are reference locks and update locks as lock types.
The reference lock is applied when referring to a record, allows reference from another business, and waits for update from another. The update lock is used when updating a record, and waits for reference and update from another business. The reference lock and update lock are released when each task executes a COMMIT command or a ROLLBACK command.

The lock is applied to the referenced or updated block. Therefore, when a plurality of records are stored in one block, the lock is performed when the first record included in the block is accessed. Locks are managed in a lock list 37. The lock list 37 is list data including a lock type, a business program identifier, a storage address of a block in which a record to be managed is stored on a magnetic disk (this is called a lock key), and a synonym chain. is there. The lock type is
Reference lock is 1, update lock is 2, if wait,
The reference lock is indicated by 3, and the update lock is indicated by 4. The identification of the business program is order 1, order 2,..., Order n, inventory 1.
It is. The storage address on the magnetic disk is composed of the serial number in the table and the block number in the table.

The serial number of the database table used for sales management work is as follows. The order number table is 1, the customer table is 2, the order slip table is 3, and the stock master table is 4. For example, when the order processing (101 to 106) is executed, (1, order 1,1,1,1, null), (1, order 1,2,1000, null)
l), (3, orders 1, 2, 100, null), (2, orders 1,
A lock list 37 such as (1, 1, null) is generated.

The lock list 37 is managed in the lock list pool table 35 by hashing using the lock key as a hash key. The hash key is a general method for searching a large amount of list data on a table at high speed. Dedicated hardware instructions for hashing are executed by the computer CPU.
It is provided as one of the functions. The hashing instruction hashes an arbitrary area on the list data extracted as a hash key, and stores the list data in a distributed manner in a table. The list data is stored in the table based on the hashed value.

If the list data is already stored when attempting to store the list data based on the hashing result, the list data is stored in the overflow area, and the stored list data is pointed to.

The lock means operates as an extension of operations such as record reference, update, and addition. For example, referring to the order number table of the order processing business (101) or reading the customer record (1
04) In the process, the reference lock is registered and the order number record of the order processing is written back (103).
In the process of writing the order slip record (106), the update lock is registered. The lock means is a lock list
By using 37, waiting or deadlock is detected. Here, registration of the lock list 37 will be described.

FIG. 9 is a diagram for explaining the registration status of the lock list.

Hereinafter, the operation of the lock list holding amount determination unit 3 and the update lock holding amount determination unit 4 will be described using an order receiving business and a stock allocation business.

At the start of the order receiving operation, the job management list 31 is initialized with the lock list holding number 32 and the updated lock list holding number 33 is initialized with 0. The number of update buffers 34 is determined by designating the operation environment of the business program, and is set to 20, for example. This 20
Means that the total number of buffers available in the database system is 200, and
It is assumed that there is no operation.

The reference lock is set by the order receiving operation referring to the order number table (101). At this time, one lock list 37 is generated, and 1 is added to the lock list holding number 32 (to 1). One is added to the value of the issued order number of the order number record (102), and when the record is rewritten (103), 1 is added to the updated lock list holding number 33 (to 1). When a corresponding customer record is read from the customer table using the customer name as a key (104), one more lock list 37 is created and the number of lock list holdings 32 is set to two. In addition to the order number and the customer number of the acquired customer record, the sales representative, the product name, the quantity, the delivery date, etc. are input (105), and when the information is written to the order slip record (106), the updated lock list 37 is set to 1
In this case, the number of locks held is 32, and the number of held lock lists 33 is 2.

That is, when the order receiving service executes the first test COMMIT means 1 (107), the number of held lock lists 32 is 3, and the number of held updated lock lists 33 is 2.

The lock list holding amount determination means 3 started by the test COMMIT means 1 determines that the lock list holding number 32 is 10% or less of 2000 of the lock list pool lock list number 36, so that the execution of the commit instruction is Judge as unnecessary. The reason why 10% is used as the criterion is that the maximum number of communication jobs that operate simultaneously is assumed to be 10. This index may change depending on the conditions of the operating environment. Similarly, the update lock holding amount determination unit 3 also determines that the update lock list holding number 33 is 1.5 times or less the update buffer number 34,
It is determined that execution of the OMMIT instruction is unnecessary. The reason for using 1.5 times as the criterion is to reduce the occurrence of frequent writing of the update buffer when the update lock list is held beyond the value of the update buffer number of 34.

When the order receiving service refers to the order number table for the second time (101), a new lock list is not generated because it is already locked (actually, update lock). When writing back the order number record (103), a new lock list is not generated. Because customer records are distributed and stored in 100 blocks, when reading customer records (104),
It is likely to read customer records stored in another block. Here, the lock list 37 is created on the assumption that a customer record of another block is newly read. The number of lock list holdings 33 is counted up to 4. Since 40 order slip records can be stored in one block, a new update lock does not occur when the order slip record is written (105).

When the second test COMMIT means 1 is executed (107), the lock list holding number 32 is 4 and the updated lock list holding number 33 is 2. Therefore, the lock list holding amount determination unit 3 and the update lock holding amount determination unit 4 determine that execution of the COMMIT instruction is unnecessary.

FIG. 10A shows the state of the lock list holding number 32 and the updated lock list holding number 33 when the test COMMIT means 1 is executed from the third time to the tenth time (107). According to this, since the lock list held amount determination unit 3 and the updated lock held amount determination unit 4 determine that execution of COMMIT is unnecessary, the test COMMIT unit 1 never executes COMMIT. After processing all sales orders, only once at 108 C
Execute the OMMIT instruction. Execution of COMMIT instruction in 108, 32 lock lists held, updated lock list held
33 is cleared to 0.

In this example, the order processing business does not execute the COMMIT instruction until the order entry processing on one screen is completed.
When all order data entered on the screen has been processed
The COMMIT instruction will be executed. The reason why the judgment criteria of the lock list holding amount 3 is set to 20% is that, unlike the communication job, the patch job does not perform communication processing with the terminal, uses the same CPU, and can perform a lot of work on the database. This is because Therefore, the communication job was doubled.

On the other hand, the lock list holding number 32 and the updated lock list holding number 33 of the inventory allocation business will be considered. Assume that the number of update buffers for the inventory allocation business is 20.

The reference lock is set when the stock allocation business refers to the order number (111). Further, when the corresponding stock master record is read from the stock master table using the product name as a key (114), a reference lock is applied, and when the stock record is written back (121), an update lock is applied.

When the test COMMIT means 1 is executed (122), the lock list holding number 32 and the update lock list holding number 33 are:
The result is as shown in FIG. Until the 24th execution of the test COMMIT unit 1, the lock list holding number 32 is 20% or less of the lock list pool 36, so the lock list holding amount determination unit 3 determines that the execution of the COMMIT instruction is unnecessary, and the update lock list Since the number 33 of holdings does not exceed 1.5 times the number 34 of update buffers, the update lock list holding amount determination means 4 also determines that execution of the COMMIT instruction is unnecessary, and the number 33 of update locks is 1.5 times the number 34 of update buffers at the 25th time. , The update lock holding amount determination means 4 determines that execution of the COMMIT instruction is necessary, and
The MMIT instruction is executed. As a result, the lock list holding number 32 and the updated lock list holding number 33 are cleared to zero.

For example, it is assumed that the inventory allocation business is not performed in parallel with the order processing, but is started after 17:00 when the order processing is completed. At this time, the update buffer number 34 of the inventory allocation business is reset to 160 for the 200 buffers available in the database system. Assuming that the customer table and inventory master table are randomly accessed to enter data in the customer information and inventory information of the order receipt record, at the stage when 100 order receipt records have been processed, all of the customer table and inventory master table A block is entered once, and a lock list 37 for blocks in the customer table and a lock list 37 for all blocks in the inventory master are generated. The lock list of the stock master is an update lock list. On the other hand, three lock lists 37 are generated because three blocks are input for the order receipt slip, and one lock list 37 is generated because one block is input for the order slip number table. At this point, the number of held lock lists 32 is 204, and the number of updated lock lists is 3
3 is 100 pieces. The update lock hold amount determination unit 4 does not execute COMMIT until the number of update lock holds exceeds 240 = 160 × 1.5. Since the update is only for the stock master record, all the stock master records have already been updated, and this condition is not satisfied. Therefore, when the test COMMIT means 1 is executed (122), the determination by the lock list holding amount means 2 becomes valid.

The lock list 37 is newly generated when a block including an order slip record is newly read, and one lock list is added to 40 blocks. Therefore, when 7961 order receipt records are processed, the lock list holds 32 for the first time.
When the number exceeds 400, the lock list holding amount determination unit 3 determines that commit is necessary. As described above, when a specific job can occupy the specifications of the database in the single operation, the costly COMMIT processing can be reduced as much as possible.

The above proceeded without consideration of the journal.
In a database system environment where the entire magnetic disk unit with a megabyte storage capacity is occupied by the journal file, it is not necessary to consider the journal usage to determine whether to execute COMMIT processing. It is.

In other words, the order receipt and inventory master table, which are the tables to be updated, have a total capacity of 2600 blocks and 10.4 megabytes,
Even if the control part is added to the journal record, there is no possibility that the journal file overflows.

In the following, the operations of the order receiving service and the inventory allocation service when a small size is assigned to the journal file in a magnetic disk having a capacity of 635 megabytes will be considered.

FIG. 11 is a diagram showing the contents of a journal file.

Looking at this, variable length records are sequentially stored on the track. By updating the record and registering the record, the image of the record on the update buffer before the update is output as a journal record. The journal record is a variable length record including a header part and an image before update. The header part is the journal record output date and time, business program identification, table name, block relative number,
It consists of a relative position within the block and an image length of 48 bytes.

The journal record created in 103 of the order processing business has a header part of 48 bytes, a pre-update image of 20 bytes,
The total is 68 bytes. When a new block is secured, the journal record created in 106 has a header part of 48 bytes and a pre-update image of 4096 bytes, for a total of 4144 bytes. These journal output amounts are added to the journal output amount 27 from the stationary point. When the COMMIT instruction is executed, 20 bytes are output as a COMMIT journal.

Similarly, the journal record created in 121 of the inventory allocation business has a header part of 48 bytes and a pre-update image of 20 bytes.
0 bytes, for a total of 248 bytes.

The maximum amount of journal output when the order processing and inventory allocation tasks are executed simultaneously is about 41 kilobytes = 41
It is 44 x 10 + 248, so the journal file is very small, and there is no journal overflow if it is not allocated. Is done. If the judgment criterion of the batch job journal amount judging means 2 is set to 20%, the journal file judging means 2 must be allocated to the journal file by 100 MB or more before processing the 7961 items.
Determines that a COMMIT instruction is required. If you use a private journal, you need to allocate more than 25 megabytes.

〔The invention's effect〕

As described above, the transaction control method according to the present invention can easily execute the COMMIT instruction at an appropriate interval without any consideration from the user program.

If the COMMIT instruction is executed at an appropriate interval, the execution of the COMMIT instruction will be delayed, causing the program to terminate abnormally due to waiting for exclusion, reducing the frequency of exclusion errors, overflowing the lock list, or overflowing the journal file. Can be prevented. Conversely, if the execution interval of the COMMIT instruction is short,
Although the system throughput decreases due to an increase in file I / O due to the actual update to the file and an increase in the CPU accompanying the release processing of the lock list, the transaction control method according to the present invention prevents this and improves the system throughput. be able to.

[Brief description of the drawings]

FIG. 1 is a block diagram showing the configuration of a transaction control system according to an embodiment of the present invention, FIG. 2 is a diagram showing a journal control table, FIG. 3 is a diagram showing a lock list table, and FIG. FIG. 5 is a block diagram showing the configuration of the control system, FIG. 5 is a diagram for explaining the structure of the magnetic disk device, FIG.
FIG. 7 is a flow chart for explaining the processing of the order receiving business, FIG. 8 is a flow chart for explaining the processing of the inventory allocation business, FIG. 9 is a view for explaining the registration status of the lock list, and FIG. And FIG. 11 is a diagram showing the contents of a journal file. 1 ... Test COMMIT means, 2 ... Journal output amount judgment means, 3 ... Lock list holding amount judgment means, 4 ... Update lock holding amount judgment means, 21 ... Journal control table, 22 ...
Journal file, 23 ... journal allocation information, 24 ... journal output information, 25 ... journal file capacity, 26 ... journal type, 27 ... journal output amount from stationary point, 31 ... job management list, 32 …… Lock list holding number, 33 …… Updated lock list holding number, 34…
… Number of update buffers, 35 …… Lock list pool table,
36: number of lock lists in a lock list pool, 37: lock list, 41: operation number setting means, 42: transaction operation means, 43 ... operation number totaling means, 44 ... operation number determination means, 45 ... … COMMIT operation means.

Claims (1)

(57) [Claims] In a transaction control system of an electronic computer in which an update is invalidated by a rollback instruction and an update is validated by a commit instruction, test commit means (1)
And a journal output amount determination unit (j) which determines that execution of a commit command is necessary when the output amount (27) of the journal from the quiesce point has reached a predetermined amount with respect to the capacity (25) of the journal file (22). 2) and when the number of lock list holdings (32) reaches a predetermined number with respect to the number of lock lists (36) of the lock list pool in the lock list pool table (35), it is determined that the execution of the comic instruction is necessary. A lock list holding amount judging means (3), and an update lock list holding judging that a commit instruction is required when the number of held update lock lists (33) reaches a predetermined number with respect to the number of update buffers (34). The test committing means (1) makes an inquiry to the determining means (2) to (4) as to whether the execution of the commit instruction is necessary, and the determining means (2) Stone (4) transaction control method, characterized by instructing the execution of the commit instruction to test commit unit (1) by the determination output.