JPH01263745A - Recovery of data base - Google Patents

Abstract

PURPOSE: To avoid the defect of a shadow method and realize the simultaneity of a sub-page and restoration from a plural-page transaction by quickly and efficiently executing an operation at an overhead point at the time of execution and also unnecessitating an analysis step at the time of restoring. CONSTITUTION: Functions MINBUFLSN and LOWTRANLSN realized in a computerized routine are defined and add the first and second components of a check point. MINBUFLSN functionally executes first updating as against a first 'soiling' data page inside a RAM buffer. LOWTRANLSN is functionally executes the first updating of sequence in a table corresponding to the transaction where respective kinds of updating are not functionally comitted. The two components are periodically taken-out during advance writing and stored in a log header as the function of logging action. At the time of recovering, the check point is retrieved and the functions between the components are compared by a recovery algorithm.

Description

DETAILED DESCRIPTION OF THE INVENTION A. INDUSTRIAL APPLICATION This invention relates to computerized databases and, more particularly, to techniques for recovering data after a system crash.

B. Prior Art] It is well known that computerized database systems have found wide acceptance in numerous applications. The data accumulated in this database system represents a huge amount of expense and effort, and is extremely valuable, if not essential, to users, making data loss very serious and costly. There is a risk that it will happen.

Therefore, in addition to the more general functions of database systems in data storage and manipulation, computerized database systems also provide important data recovery capabilities in the event of a system crash that halts normal processing. must be provided. One of the difficulties in providing recovery functionality has been the need to restore the database to a consistent state. A common example of the problem of inconsistency can be found, for example, in the case of financial business applications. A bank database system crash may occur after a customer's account record on disk is debited but before the correlated account is credited. Although the credit activity may be complete in the sense of being entered into main memory or RAM buffer memory, it has not actually been written to disk yet. Therefore, the image of the database on the external permanent storage disk is said to be in an inconsistent state.

To solve this problem, separate sets of database activity boundaries are created such that the database is always in a consistent state when all activities between transaction boundaries are completed, such as debit and credit activities in this example. The concept of transaxillon boundaries has been developed in the art. In other words, transaction control in the database associated with a series of updates to the database
All or none of these updates will be completed. If a problem occurs and the database needs to be recovered after a crash before the transaction completes, operations on the database can be redirected to these transaction boundaries.

In addition to the consistency issues addressed by the notion of transaction boundaries and commits, database recovery is accompanied by the need to restore the previous data image if the database must be restored to the previous data image during recovery. There was another difficulty related to the problem of remembering. In the simple example above, that would be to preserve the original state of the two accounts if a partial transaction or operating transaction with only debit activity occurs. In this case, the transaction can be undone so that the database is in its original consistent state before being altered by the incomplete transaction.

One technique developed in the art to retain previous image data for recovery is called shadow methods or shadow basing. Early database systems using this technology included System R, developed by IBM, and the commercial database product SQL.
There is a DS.

With this technique, copies of recorded data pages were maintained. When a transaction commits, a new copy is created that contains the page changes from the recording copy, becomes the new committed copy, and the previous copy is deleted.

Each time additional database changes are made, the current committed copy is replicated with those changes to a new committed copy of the data page, and the previous correlated data
Page has been deleted. The throughput benefit of this technique occurred at commit time because the required change from old to new data page was simply a matter of changing a pointer. There was no need to rerun the database activity, as is the case with newer write-ahead logging techniques described below.

Nevertheless, many of the well-known shortcomings of shadow technology have been exposed, necessitating the development of algorithms that support write-ahead logging.

Its main drawbacks were the extra RAM and disk space and associated overhead for maintaining shadow copies of data. This is because a second copy is required when updating all pages in the database. Other drawbacks include expensive checkpoints, fragmented data that prevents physical aggregation of data, inefficient maps, and
Extra I/O was sometimes required for map blocks.

In short, the disadvantages of shadow paging were found to be too great in use, so write-ahead logging was developed and considered to be a better solution to the recovery problem. An early example of this technique is seen in the SQL/DS database system mentioned above. Instead of maintaining an entire second copy of the data, this technique simply maintains a linear record or journal of what happened before and after database activity. Activities represented in RAM buffers are not necessarily written to disk, even for completed transactions. Therefore, such work is lost in the event of a system crash and must be re-executed when performing recovery from the recovery log, unlike in the case of the shadow paging techniques described above.

Under write-ahead logging protocols, typically
Before correlated data containing changes can be written to a data file, log records corresponding to the changes must first be written to disk. Write-ahead logging 1
One important aspect concerns checkpointing, in which a point in the recovery log from which to begin recovery in case of a system crash is periodically determined and written out. One factor that determines the efficiency of checkpointing is the internal structure of the database, and more specifically, the granularity of locking. A well-known problem occurs in multi-user databases when more than two users attempt to access the same data. Returning to the previous example, while the first user is reading the debit account above,
A second user may change the credit account. A first user may access the same credit account while a second user's playing card is active, resulting in a mismatch.

One solution to this problem has been to limit access to portions of the database to one user. Such restrictions were called locks, and the size of the restricted portion of the database was directly related to the "granularity" of the lock. For example, the aforementioned S Q L/
In DS databases, the granularity of locking was at the physical data page level, which encompasses a large number of records.

The importance of such large granularity for database recovery was that it simplifies the recovery problem in checkpointing methods.

Enabling recovery to begin at an optimal checkpoint has been a simpler problem than, for example, in subpage granularity systems where locking is done at the record level.

To explain why checkpointing simplifies the recovery problem with high granularity, record locking requires multiple physical pages to move data in from disk and out from RAM buffers. may contain updates from transaxilons. Furthermore, at the time of a system crash, some of these transactions are committed, some are aborted, and some are stopped. At that point, in database systems such as DB2 that have large granularity locking, the page will be affected by at most one transaction. Therefore, handling recovery for that page becomes relatively easy. However, such subpage locking facilitates concurrent updates, since many transactions can be executed on the same page, and all related database changes must be made in the proper order. , recovery becomes more difficult.

For example, locking granularity and associated recovery algorithms are simpler at the data page level, where it is relatively easy to record whether a data page has been paged out and requires an update. Nevertheless, there has been increased development and interest in ways to implement subpage granularity locking and thus recovery for multiple transactions within a data page.

One reason why such simultaneity is required is that page
The level of locking was related to the physical size of the page. However, since databases operate in terms of logical objects or entities rather than arbitrary physical constraints, locking at the table or record level for logical data objects is not possible, i.e., at the sub-record level, etc. Page-level granularity was highly desired. Although data pages may be entered into the database for input and output, the locks themselves are preferably not functionally restrictive. Therefore, in situations where write-ahead techniques are used for recovery, it has been desirable to have multiple transactions with locks on records within a single page for concurrency.

Therefore, techniques for subpage granularity locking and recovery techniques for multiple transactions within a data page have been developed. One technique provides write-ahead logging with locking granularity that is finer than the data page size (ie, the unit used to bring data into or write data out). This technique writes out, or "logs", detailed information about the state of the buffer pool during normal database operations. in particular,
Status of pages in the RAM buffer pool (i.e.
Which pages were "dirty", the activities that soiled those pages, their associated log sequence numbers (LSNs), etc.) are periodically written to the recovery log and included in a record containing this checkpoint information. It will be done. In addition to this data needed for recovery, log records of changes to the actual data in the database are also written to the recovery log.

Therefore, the entire log was a series of records consisting of checkpoint information and actual data updates to the database.

Upon recovery, an analysis pass is performed. At this stage, a forward pass is made to the log and all log records containing checkpoint information previously written to the log are read and parsed. The optimal checkpoint from which to begin recovery during the analysis pass is then computed from this information.

″More specifically, two LSNs are determined from a number of checkpoint records: the LSN for the first update to the first “dirty” page in the buffer, and the LSN for the first update to the first “dirty” page in the buffer, and The LSN corresponding to the first update for the first transaction that is "uncommitted." The optimal recovery point is these L
It corresponds to the smaller value of SN. These LSNs calculated during the analysis pass will be referred to below as our MINB
It is understood that this corresponds to UFLSN and LOWTRANLSH. These are determined and stored periodically during normal forward processing according to the present invention.

One problem with such prior art techniques is that they read every log record just to calculate these two LSN values (to determine the optimal recovery point), so the buffer pool's entire log (i.e., a parsing pass is required). This is in contrast to the present invention, in which the MINBU
The values of FLSN and LOWTRANLSN are determined periodically and written to the log record, with no need for a parse pass.

In the prior art, MINBUFLSN and LOWTRAN
Although the actual instantaneous value of LSN is newer than the last value written to the log according to the present invention, thus resulting in a more optimal recovery point, the present invention does not require an analysis bus and available for immediate use.

Problems That the C1 Invention Attempts to Solve Thus, with the above in mind, it is readily apparent that a new method for flexible checkpointing was desired. It would be desirable to have a technique that avoids the drawbacks of the shadow method described above while simultaneously providing subpage concurrency and recovery from multi-page transactions on data pages. Furthermore, it would be desirable to have a system and method for database recovery that is particularly quick and efficient in terms of the overhead required at runtime to support this technique, and that does not require an analysis step during recovery.

5 Measures to solve D1 problem] Function MINBUF realized by computerized routine
LSN and LOWTRANLSN are defined and include the first and second components of the checkpoint. MINBUFLS
N is functionally related to the first update to the first "dirty" data page in the RAM buffer. L.O.
WTRANLSN pertains to the first update in the sequence in the transaction table such that each update functionally corresponds to an uncommitted transaction. These two components are periodically retrieved during write ahead and stored in the log header as a function of logging activity. During recovery, checkpoints are searched and the recovery algorithm uses functional comparisons between its components. A traditional recovery log parsing bus is no longer required, reducing overhead during logging and increasing recovery efficiency.

E. EXAMPLE A brief overview of some of the terms and concepts used and the systems and methods used is provided below to describe the invention. Thereafter, the operation of the present invention will be explained in more detail with reference to the drawings.

In database technology that uses write-ahead logging protocols, the following terms are commonly used:

Data Page: This term refers to a block of storage of fixed size that contains user data. Such data
Pages can be paged into some form of secondary storage, such as a RAM buffer (those data pages are lost in the event of a system crash). Alternatively, these pages can be paged out to a conventional form of primary storage, such as a hard disk file (where they are retained even in the event of a system crash). Although a data page in the buffer contains the most recent updates to it, an older copy of the data page on the hard disk may not contain the most recent updates to that page.

Log record: The term log record refers to a record that contains both the before and after images of a single data change. This log record is typically used to allow redoing and undoing of data changes using the information contained in the log record.

Recovery log: This refers to a set of log records (described immediately above) that records all changes made to the data pages of a database in the order in which they were performed. By referring to the recovery log, the recovery process can restore the database to a consistent state.

Log Sequence Number Each log record described above in the recovery log is identified using a unique log sequence number (LSN).

The LSN of a log record is the first byte of the log record, starting from the logical start of the log.
It is an offset.

Write-ahead logging: When a data page is updated or "tainted", the update generates a log record with a corresponding identifying LSN. The data page is also updated with this LSN. Every "dirty" data page contains the LSN of the log record that recorded the last update made to that page. In this way, a data page is associated with a particular point in the log identified by its corresponding LSH, and all log records after this LSN do not refer to that particular corresponding page. Become. When a "dirty" data page is written to disk, the write-ahead logging protocol specifies that the recovery log must have been previously written to disk up to the log record identified by the LSN on the page to be written. do. This procedure ensures that a data page is not written to disk before all log records recording changes to that page have already been written to disk. Therefore, in case of recovery, it allows the database to be restored to a consistent state.

MINBUFLSN: The database RAM buffer contains zero or more "dirty" pages at any time.

If a buffer contains one or more "dirty" pages, there is one page that is the oldest, ie, one that was updated before any other "dirty" pages. The LSN of the first update to this page is herein MINB
Define as UFLSN. MINBUFLSN is not necessarily the same as t, S N written to that page, but is the L S of the first update to that page.
Note that N. (It remains the same until the second update.) The MINBUFLSN parameter is important because it identifies the first point in the log at which the logged operation needs to be re-executed. This is because the data page associated with the previous log record has already been written to disk and is no longer in Buffer1. When there are no "dirty" pages in the buffer, the next available or free LSN is used as the MINBUF'LSN. This is because this LSN is the first location where writes to the buffer page can occur.

LOWTRANLSN: This is the LSN of the first log record written by the oldest active transaction. In other words, this is the lowest LSN of any log record written by a transaction that is still active. LOWTRANLSN identifies a point in the log before which no active transaction appears.

With the above in mind, the general concept of the present invention will now be outlined. Above MINBUFLSN and LOWT
The RANLSN is determined periodically and written to the RAM virgin of the log file header. The LSN of the last log record written to disk is also updated. The log file header is then written to disk. These activities constitute the normal runtime overhead of the checkpointing system and method of the present invention. M.I.
Maintaining NBUFLSN and LOWTRANLSN requires relatively little overhead and requires a checkpoint (MINBUFLSN.

Note that the occasional disk I/O in writing log file headers, including LOWTRANLSN), requires little overhead as well.

When a database needs to be recovered, the starting point in the log, S″T A R S N, is LOW T
It is determined as the minimum value of RA NLSN and MINBUFLSH. If the 5TART LSN is less than the last LSN written to disk, recovery is complete, otherwise no recovery is required. However, in addition, LOW
If TRANLSN is less than MINBUFLSN, the logged updates have been applied, so LOWTRAN
Data loss during recovery between LSN and MINBUFLSH
No need to load the page. This step of the invention is hereinafter referred to as "mini-analysis." After reaching MINBUFLSN,
Normal recovery re-execution processing resumes.

Referring first to FIG. 1, according to the present invention, block 1
MINBUFLSN and LO as shown at 0 and 12
A checkpoint consisting of WTRANLSN is created periodically and then the hard
Recall that log file headers are also periodically written to persistent storage such as files or fixed disks. Line 16 is between the process of retrieving and storing checkpoints above this line and the process of using such checkpoints to facilitate database recovery after a system crash below this line. This is to conceptually show the functional distinction between These latter recovery methods, in which previously retrieved and stored checkpoints are retrieved and used in the recovery process described in more detail below, are indicated generically at block 18.

Referring now to FIG. 2, the MINBUFLSN concept is conceptually illustrated. Secondary storage in the form of RAM buffer 20 of the data processing system includes a plurality of data pages 22 and 24. Some of these pages 22 are "clean" and others are "dirty" pages 24 where updates have been made to those pages with correlated log records showing before and after images of these changes. It is written. Each "dirty" page is associated with a unique LSN 28 of log writes that changes the status of the respective page from "clean" to "dirty." As mentioned above, MINBUFLSN is the LS of the oldest page.
Nl, the LSN of the page that was updated before any other "dirty" page updates. When there are no "dirty" pages in buffer 20, the next available or free LSN is the first place a write can be made to a buffer page, so the LSN
SN is used as MINBUFLSN.

Thus, in the example of FIG. 2, "dirty" page 24 has associated LSNs of 5.7.10.11 and 16 in ascending order. Since LSNs are assigned in ascending order in time, the lowest LSN indicates the first relevant page.

According to the definition of MINBUFLSN, indicated by reference number 28, in this example, MINBUFLSN is the lowest sequence number, namely 5, indicated by reference number 30. 5LS
If a "dirty" page 24 associated with N is written to disk, that page disappears from RAM buffer 20 accordingly. According to the process shown in block 10 of FIG. 1, when periodically retrieving a new MINBUFLSN (shown in FIG.
- Before writing a "dirty" page point to disk, determine the smallest LSN associated with a "dirty" page in buffer 20, i.e. MINBUFLSN28, and
The list of LSNs corresponding to pages within (ie, 5.7.10.11 and 16 in this example) is periodically scanned. Therefore, if this "dirty" page associated with an LSN of 5 is written out and disappears from buffer 20, then based on this scan, the LSN of 7 becomes the new MIN
Replaces LSN of 5 as BUFLSN28 and stores "dirty" page 24 associated with LSH of 7 in buffer 2
Mark as the newly specified oldest "dirty" page in 0.

Referring now to FIG. 3, a schematic diagram of transaction table 32 is shown. This diagram is intended to conceptually illustrate that the transaction management function of a database system typically records log records written in connection with transactions that are still in progress. Transaxilon Table 32
One of the values typically held in is LSN34. When a transaction writes a log record for the first time, the LSN of that log record is stored in transaction table 3.
4 and stored. (Log records are only written by transactions to record updates to the database.

Some transactions never update, only read. Transaxilon will be displayed as "until you update it.
called a "read" transaction. The read transaction is not writing a log record, so the LSN
is not associated. The example of FIG. 3 shows that the first transaction write field 34 is occupied by an "R" or read (reference numeral 36). This is because there are no LSNs associated with these transactions. ) Continuing to refer to the illustration of FIG. 3, a particular transaction writes its first log record, and LSN 38 having a value of 14 in table 34 is associated with it. Another transaction similarly writes its first log record and has associated with it an LSN 38 having a value of 18, as shown in table 34. LOW
TRANLSN is the LSN of the first log record written by the oldest active transaction, i.e.
Recall from above that since LSNs are assigned in ascending order in time, it is the lowest LSN of any log record written by that transaction. Therefore, with respect to Figure 3, by definition LOWTRA
NLSN 40 is the minimum value of all LSNs in transaction table 34, ie, the minimum value of the sequence number columns 10, 14 and 18, respectively, associated with the write transaction that wrote the log record. LOWTRANLSN40 is therefore the reference number 42
10, which is the first LS written for each transaction recorded in table 34.
This is the minimum value of all LSNs in column 34 containing N.

LSN in buffer 20 or table 34 at a particular time
does not exist, LOWTRANLSN and MIN
BUFLSN is assigned a value that corresponds to the value of the next available log record. This value is, of course, MINBUFLSN or L which is determined later according to the procedure of FIGS.
Equal to or greater than OWTRANLSN.

In summary, with reference to FIGS. 2 and 3, the buffer
A log record is created when a page (a unit of input/output page written to or written from disk) is written to in the database. Associated with this log record is an LSN that uniquely identifies the record to the database system and uniquely identifies that particular I/O write to a data page. LSN was then “dirty”
Used to "tag" a page as "dirty"
A list of such LSHs associated with the page is created. When a page is first "dirty" (i.e., the first I/O write is made to it), L
An SN is created. (LSNs are of course created whenever a write is made, but there is always the first such write.) This first SN associated with the first write is the "dirty" page in buffer 20. LSN
be included in the list. Therefore, for every page currently in the buffer pool, the list has an identifier indicating where in the log the first write was made to the "dirty" page in question; Or an LSN is associated with each "dirty" page.

Since LSNs are assigned sequence numbers in ascending order of time, any already existing LSN must by definition be smaller than any newly allocated LSN. Therefore, the value of MINBUFLSH is equal to or less than the newly allocated LSH. When a buffer page is written to disk, its associated LSN is the LSN of the "dirty" page in buffer 20.
Removed from the SN list. Similarly, if a "clean" page that can be newly read into buffer 20 has made its first write to disk, then the page will be assigned an LSN accordingly. Therefore, the list of LSHs associated with "dirty" pages is periodically modified as there are changes or additions to the buffer pool accordingly.

Referring now to FIG. 4, the derivation and maintenance of the checkpoint LOWTRANLSN component during normal database operation will now be described in more detail.

Referring to Figure 4, LOW during normal database operation.
The method for retrieving and maintaining the value of TRANLSH is detailed below. First, at 44, LOWTRANLS
N is initialized to the next available LSN in the log.

Note that at database startup, the LSH's list of active write transactions is empty.

Therefore, LOWTRANLSN is initialized to the next available LSH in the log. When a transaction appears and writes a log record, an LSN is created that identifies the corresponding log record. Therefore, at 46, the subroutine waits for such a transaction to write the corresponding first log record. When this event occurs, the new LSN defined by it is
At 48, the write transaction is added to the list of LSNs as shown in the example of FIG. Newly added S
N must of course be equal to or greater than LOWTRANLSN. Because, L.S.
N is allocated sequentially in ascending order. therefore,
No need to recalculate.

When the list of LSNs as shown in Figure 3 is no longer empty,
The next change to the list is either the removal of the LSH when its corresponding transaction (which resulted in the LSN) ends, or the addition of a new LSN when the transaction writes its first log record.

Therefore, at 50, the process waits for the next addition of an LSH to the list or deletion of an LSH. Then at 52, Ls
A check is made to see if the change to the N list is the addition of an LSH, and if so, the process loops at 54 and returns to 48 to add the additional LSN to the list. On the other hand, if the change to the list at 52 is a deletion, then such deletion empties the LSN list again, as determined at block 58. If the list is empty, processing loops back at 60 and returns to LOWTRA at 44.
Reinitialize the NLSN to the next available LSN.

If, on the other hand, the list has not been emptied, processing exits decision block 58 at 62 and proceeds to block 64. At block 64, the LSN list still includes the deleted member, but the LOWTRANLS is added by determining the lowest LSN of all LSNs currently in the list.
The value of H is updated and this minimum LSN becomes the next new one,
It becomes 0WTRANLSN. Processing then returns at 65 and 5
0 to wait for the next addition to or deletion from the list.

Next, referring to FIG. 5, during normal database operation M
The method for maintaining the INBUFLSN value will be described in more detail. When the database is started, a "dirty" state as shown in the RAM buffer 20 in the explanatory diagram of FIG.
The list of LSHs corresponding to the buffer page becomes empty. Therefore, as shown at 66, MINBUFLSN
must be initialized to the next available LSH in the log. When a buffer page is written to, or "sullied", for the first time, a corresponding LSN is created that identifies the log record corresponding to the write to the page that first "sullied" the page. Therefore, 68
, the routine writes that the transaction is the first buffer.
Wait to "smear" the page and generate the corresponding LSN. Newly added LSN to the list as shown in Figure 2
must be greater than or equal to any previous MINBUFLSN. This is because LSNs are allocated sequentially in ascending order, so there is no need for recalculation. 70, the LSN of the "dirty" buffer page
The new LSN is added to the list.

When the LSH list is no longer empty, the corresponding "dirty"
Removal of LSN when buffer page is written out,
The next change to the list results from either a new addition of LSNs that occurs when a page in a buffer page is "sullied". Thus, at 72, the process waits for the next such addition to the LSN list, or the next such deletion, corresponding to the "dirty" page. At 74, a check is made to determine whether the changes to the list are additions or deletions. If so, the routine returns to 70 at 76 to add the new LSN to the list. On the other hand, if the change to the list is a deletion, processing continues at 78 from the test at 74;
Proceed to the next test 80. As with the processing for LOWTRANLSN, deletions from the list may result in the list being empty again, which is checked at 80. If the list is empty, exit the routine at 84 and
Return to the initial state of 6. On the other hand, if the list is not empty,
Processing exits test 80 at 82 and proceeds to block 86 . After deletion, if the LSN list still contains members,
It may be necessary to update the value of INBUFLSN. Therefore, at 86, the minimum value of LSH in the list corresponding to the "dirty" page is determined and assigned as the new value to MINBUFLSH, and processing loops back at 88 and waits for the next change to the list at 72. .

The values of MINBUFLSN and LOWTRANLSN are
If maintained at 10 and 12 in FIG. 1 by the correlation process detailed in connection with FIGS. 5 and 4, these components of the checkpoint during normal operation, as shown at 14 in FIG. It is desirable to periodically store . Next, writing a checkpoint to disk will be described in more detail with reference to FIG.

LOW at 90 when the database is first started
The values of T, RANLSN and MINBUFLSN are initialized to the next available LSH in the log. MINB
It is a feature of the invention that checkpoints consisting of the UFLSN and LOWTRANLSN are stored from time to time and made available for recovery, and such storage typically takes the form of periodic writes to disk. At 92, the subroutine waits for each n log write to occur for one log record before this storage step. However, n is chosen such that checkpoint efficiency is optimal. For example, for a data page update where a log record is written once, n=1.

If another transaction scene occurs and a corresponding log record is written, then n=2. During normal operation of the database, the process simply counts the number of such log writes, or the corresponding number of LSHs, until the value n is reached. 94, LOWTRANLSN and M
The value of INBUFLSN is written to the log file header. Processing then loops at 96 and returns to 92 to check for the next newly retrieved checkpoint (i.e., MI
The n count of log writes begins to accumulate again before the next current value of NBUFLSN and LOWTRANLSN) is written to the log file header at 94.

Therefore, the idea is to take a checkpoint from time to time as the log continues to grow, and write this checkpoint to disk. As the log grows, the checkpoint value in the log file header becomes stale and non-optimal. But clearly,
If additional writes are made to the log, the checkpoint value maintains the same optimality.

Regarding the choice of n, it is desirable to use some means of log file write activity to determine the interval at which checkpoints are taken. In the examples described herein,
This is done by counting the number of log writes, n, which is the factor that determines the interval between determining subsequent checkpoints and writing checkpoints to the log file header. However, it should be understood that the present invention is not limited to any particular metric of log file activity, and other determinants of n may be used instead. For example, such a log activity metric could alternatively simply be a count of the number of bytes written to the log.

In this case, the interval will be more accurate, but the cost and overhead of maintaining this value will increase.

Therefore, there is a need to measure log file activity using the number of inputs and outputs, number of bytes written to the log, etc., although the present invention is not limited to any particular log activity metric, and the present invention is not limited to any particular log activity metric; It is contemplated that several such techniques, which are well known, may be used. Furthermore, it is assumed that in some applications it may be desirable to externally select the functionality that causes periodic storage of checkpoints. In one such case, a user can arbitrarily change configuration parameters such as the value of n, or precisely what metric n is, at the user interface. The basic idea is to take checkpoints periodically and store them as a function of some metric of log activity.

Referring again to FIG. 1, MINBUFLSN and LO
WTRAN LSN is determined according to reference numerals 10 and 12 and the corresponding procedures of FIGS. 5 and 4; and 1. These MINBUFLSN and LOWTRAN
After a checkpoint containing the LSN value is written out in step 14 according to the procedure detailed in connection with Figure 6, such a logged checkpoint is useful if it is used for system recovery after a crash. for later use. Accordingly, such use of the checkpoint designated by reference numeral 18 in FIG. 1 will now be described in more detail with respect to the process of FIG.

Follow the procedure for writing out checkpoints (Figure 6),
Recall that the corresponding values for MINBUFLSN and LOWTRANLSN were written to the log file database. In the event of a system crash, LOWTRAN
These values for LSN and MINBUFLSH are retrieved from the log file header as shown at 98. In accordance with the present invention, a significant advantage of being able to extract checkpoint parameters from such read log file headers is that it eliminates the need for log file parsing passes associated with normal log recovery techniques. It disappears due to

Continuing to refer to FIG. 7, starting LSN1 or L
The minimum values of OWTRANLSN and MINBUFLSN are determined upon recovery. At 102, a check is made whether this starting LSN is less than the last LSN written to disk.

If so, the routine exits at 104 and proceeds to 118 to end the forward recovery procedure. If the starting LSN is not less than the last LS'N for the disk, the procedure follows path 106 to 108 where a check is made to determine if LOWTRANLSN is less than MINBUFLSN. LOWTRANLSN is MI
LOWTRANL if less than NBUFLSN
We can see that all the updates recorded in the log records starting from SN up to the last update before MINBUFLSH were actually made to the database's disk persilon, and therefore for inspection There is no need to load data pages. It is only necessary to record the transaxilons starting at this interval. Therefore, processing exits at 110 and MINBUFLS
A "mini-analysis" is performed only on log records starting from N and ending with the log record immediately before LOWTRANLSN. This step will be explained in more detail later. The log record is MINBUFL
SN and the associated LSN (that is, block 10
8), it is necessary to read the data page from disk to know whether the update has actually been applied, and this step is a complete forward step in MINBUFLSN. It is called recovery 116. Processing then proceeds to 118 and forward recovery ends.

Returning to the steps related to the "mini-analysis" shown in 114, from the above, LOWTRANLSN and MINB
It can be seen that there is a series of log records equal to or larger than the log records between UFLSN. Furthermore, starting from LOWTRANLSN, MI
It can be seen that all updates logged to log records ending before the log record identified by NBUFLSN have been written to disk. Minimum LSN (i.e. MIN determined at the same time as LOWTRANLSN)
BUFLSN) is greater than LOWTRANLSN, then the LSN that identifies the first data change not written to disk is MINBUFLSN. L.O.
All logged operations starting from WTRANLSN to MINBUFLSN were written to disk. If not, their behavior is MINBU″FLS
are present in the list of LSNs of "dirty" log records used to calculate N, and therefore MINB
UFLSN will be less than or equal to LOWTRANLSN. In that case, the process of FIG. 7 can perform a "mini-parse" phase in which no data pages need to be read, unlike traditional write-ahead log checkpointing methods. this is,
This is because those updates referenced by the log records are known to be on external disk.

When MINBUFLSN is less than LOWTRANLSN, this fact is not known and the process is forced to fetch data pages.

A data page contains the LSN of the last update made to that data page. Log record LS
If N is greater than the LSN on the data page, we know that the update log has not been applied to external disk and must be reapplied. The LSN on the data page is the LSN of the log record that is currently being processed.
If it is equal to or greater than , we know that the update was actually made. This is referred to in the art as a full redo, which means that the forward recovery or redo phase
It is expensive because page I/O is required each time a record is processed.

In contrast, the present technique uses MINBUFLS
Until N is reached, there is no need for such input/output.

Often LOWTRANLSN and MINBUFLS
There are many log records between N. Therefore, if LOWTRANLSN is less than MINBUFLSN, page I/O can be eliminated when examining those log records during rerun. There is no need to perform I/O, only to look for the start of a new transaction. The analysis phase of traditional checkpointing techniques examines state information to determine the starting point for a new transaction.

In the present invention, MINBUFLSN and LOWTRANLS
By memorizing N, the analysis phase becomes unnecessary,
Some of the benefits of the analysis phase are largely realized without the need to actually perform the analysis phase (hence the term "mini-analysis"). Under this method, logs are processed.

Also, in certain cases where MINBUFLSN is equal to or less than LOWTRANLSN, a full re-execution with accompanying page I/O is required. However, the present invention allows for the possibility that no expensive input/output is required for a particular log interval.

For clarity, LOWTRANLSN and MI
The routine calling program of FIGS. 4 and 5 for maintaining NBUFLSN has been omitted. However, it will be appreciated that in order to maintain these subroutines as part of the transaction management functions in adding, deleting and updating records in transactional state, as part of the database management mechanism, well known in the art Appropriate transaction management mechanisms are provided.

Similarly, the calling routines for adding, deleting, and maintaining the LSN list of "dirty" pages and the list of LSNs in the transaction table shown in FIGS. 2 and 3 have also been omitted. But when the buffer is brought in and paged out, these "dirty"
Conventional buffer pool management functions, well known in the art, are also provided to perform the functions of adding, deleting, and maintaining LSN IJ lists and LSN values for pages. Thus, for example, when writing a new checkpoint according to block 94 of FIG.
The N and LOWTRANLSN values can be retrieved from storage locations maintained by the buffer pool manager.

Finally, referring to FIG. 8, the database system 12 of the present invention comprises a microprocessor with associated software for performing the processes described herein.
0 is shown. The system is preferably constructed in the manner of conventional personal computer architecture, such as that associated with the IBM Personal System/2 family of computers. Such computer systems include Intel 80386
A microprocessor 132, such as a microprocessor, is provided. microprocessor 132 and memory 1
A bus 134 is provided to connect between 26, ROM 128, and several related functions implemented by various input/output devices 130. Although the bus is functionally shown as a single line, it will be appreciated that it includes conventional address, data, and control lines for purposes well known in the art.

For simplicity, devices 126, 128, and 130 are shown without an adapter interface to microprocessor 132; however, they are, for well-known purposes, included as part of Personal System/2. Or it is provided as an optional plug-in item.

Read-only memo! J (ROM) 128 contains a basic human output operating system (BIO8). It is executed by microprocessor 132 and controls the basic operation of system 120. An operating system 124, such as O8/2, is shown functionally and R
Although operating in a conventional manner with BIO 8 within OM 128 , software for operating system 124 is typically stored in other memory 126 . Input/output device 1
30 is a keyboard, mouse, etc. for operator input, and an IB for providing visual output of the system to the user.
A display device such as an MPS/2 color display device 8514 may be included. Memory 126 may take the form of a variety of media, such as a disk and associated disk drive, one or more disk files, and the like.

More detailed information regarding suitable hardware and operating system software suitable for implementing the teachings of the present invention can be found in the following documents:

OS/2 Programmer's Guide (OS/2 Programmer's G), edited by Iacobucci.
uide) J, McGraw-Hill, 1988; ``Technical Reference Manual Personal System/2 (50 type, 60 type system) (Technical Reference
Manual, Personal System/2
(Mode150, 60 Systems)) J,
IBM, part number 68X2224, document number 588X
2224; and rIBM operating system/
2 Fision 1. Standard rate version technical manual ('IBM
Operating System/2 Version
1.05standard EditionTechn
ical Reference) J, IBM Corporation, Part No. 6280201, Document No. 5871-AA, each of which is incorporated herein by reference.

In addition to the operating system 124, in accordance with the present invention, an application program 122, such as the database program shown, is also loaded into the memory 126. Database program 122 includes a number of relational database routines, such as those well known in the art, as well as for implementing the processes described above in connection with FIGS. 5, 4, 7, and 6. computer program routines 136, 138, 140 and 142. Database program 122 generally provides instructions to microprocessor 132 to cause system 120 to perform relational database functions. Accordingly, a user can interact with the system through various input/output devices 130 in response to output produced on display input/output devices 130 by execution of program 122 .

In connection with computerized functions 136-142,
The values of MINBUFLSN and LOWTRANLSH are determined periodically by routines 136 and 138 during forward processing. These values are periodically written to memory 126 in response to checkpoint write subroutine 142.

In addition to these LSN values being logged in memory 126, changes and updates to the database in memory 126 of system 120 (entered via input/output device 130) are also logged in the recovery log stored in memory 126. It should be clear that it is logged. During recovery, system 120 uses the checkpoint routine 140 portion of database program 122 to
The values of MINBUFLSN and LOWTRANLSN are retrieved in the manner described above in connection with FIG.
Functionally responsive to the ANLSH value, recovery of the database at the appropriate location in memory 126 is controlled.

[Brief explanation of the drawing]

FIG. 1 is a block diagram illustrating the major functional components of the system and method of the present invention. Figure 2 shows the MI associated with the data pages in the buffer.
FIG. 2 is a conceptual diagram of a database RAM buffer showing the relationship between NBUFLSN and LSH. Figure 3 shows the LOWTRANLSN and LSN associated with the transaction indicated in the transaction table.
FIG. 2 is a conceptual diagram of a transaction table associated with a database, showing the relationship between the two. FIG. 4 is a flow diagram illustrating a method for retrieving and maintaining LOWTRANLSN during normal database operations, such as shown in block 12 of FIG. FIG. 5 is a flow diagram illustrating a method for retrieving and maintaining MINBUFLSN during normal database operations, such as shown in block 10 of FIG. FIG. 6 shows checkpoints created by the method shown in FIGS. 4 and 5, as shown in block 14 of FIG. 1, corresponding to blocks 12 and 10 of FIG. 1, respectively).
3 is a flowchart illustrating a method for storing (MINBUFLSN, LOWTRANLSN). FIG. 7 is created by blocks 10 and 12 of FIG.
and is a flowchart illustrating in further detail the initiation of database recovery using the checkpoint stored in block 14 of FIG. FIG. 8 is a schematic diagram of a computerized database system equipped with the recovery function of the present invention. 120...Database system, 122...
- Database program, 124... Operating system, 126... Memory, 130...
...input/output device, 132...microprocessor. RA Ba Izu 77 Toe 艷 LSN III Law II A@=JeI Shige IF, Bi x-7. Ding! I;L(r41N
BuFL5N, LOWTRApJ (JN) L saving%6 [

Claims (1)

[Scope of Claims] Data corresponding to a plurality of updated data pages is stored, and the first indicator is functionally related to the update time of a corresponding one of the updated pages. retrieving from the data a log record corresponding to each of the operational database transactions, and retrieving a second indicator functionally related to the time of one of the operational transactions from the stored log record; A method for restoring a database, comprising: retrieving a checkpoint as a function of the first and second indicators, storing the checkpoint, and restoring the database in response to the checkpoint.