Method and apparatus for insuring database data integrity without data recovery logging

• US 6,651,073 B1
• Filed: 05/23/2000
• Issued: 11/18/2003
• Est. Priority Date: 05/23/2000
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Lyle, Robert W., Yothers, Jay A., Teng, James Z.
• Primary Examiner(s): Coby, Frantz
• Application Number:
  US09/576,667
• Time in Patent Office: 1,274 Days
• Field of Search: 707/1, 707/10, 707/200, 707/202, 707/8
• US Class Current: 1/1
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06F 2201/82 Solving problems relating to consistency ensuring consistency in mirrored systems G06F11/2064
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

Database tables which are to be loaded with large data objects are defined with a special logging attribute that causes the associated database management system to suspend recovery logging for the databases so defined, but to continue with logging control information, such as space management changes. During a two-phase commit operation for a database transaction in a distributed system, at each database all modified data pages are written to disk before the end of the phase 1 commit, if the pages have not been previously written. This and other write operations are monitored for errors. If any of the changed data pages cannot be written to disk successfully before the end the phase 1 commit operation, then the transaction is rolled back at all databases.

Claims

1. A method for use in a computer system having a non-volatile storage and capable of processing a transaction which modifies one or more database pages, comprising:

(a) writing all modified, unwritten database pages to non-volatile storage before committing the transaction;

(b) monitoring for errors all write operations which write modified pages to the non-volatile storage; and

(c) committing the transaction if no errors are detected in step (b) and rolling back the transaction if errors are detected in step (b).

2. The method according to claim 1 further comprising:

(d) defining the database so that recovery logging will be performed only for control information changes, including space allocation, and not for data changes.

3. The method according to claim 1 further comprising

(e) backing up the database when the transaction has been completed.

4. The method according to claim 1 wherein the transaction is performed using conventional insert delete and update commands.

5. The method according to claim 1 wherein the computer system comprises a page buffer pool and wherein step (a) comprises:

(a1) writing all modified, unwritten database pages in the page buffer pool to non-volatile storage before committing the transaction.

6. The method according to claim 5 wherein step (b) comprises:

(b1) monitoring the write operations performed in step (a) for errors; and

(b2) monitoring for errors write operations which write modified pages to the non-volatile storage when the page buffer pool contains a predetermined number of modified pages.

7. Apparatus for use in a computer system having a non-volatile storage and capable of processing a transaction which modifies one or more database pages comprising:

a flushing mechanism which writes all modified, unwritten database pages to non-volatile storage before committing the transaction;

a monitor which checks for errors all write operations which write modified pages to the non-volatile storage; and

an updater which commits the transaction if no errors are detected by the monitor and rolls back the transaction if errors are detected by the monitor.

8. The apparatus according to claim 7 further comprising an attribute which defines the database so that recovery logging will be performed only for space allocation purposes.

9. The apparatus according to claim 7 further comprising a backup mechanism which backs up the database when the transaction has been completed.

10. The apparatus according to claim 7 wherein the transaction is performed using conventional insert, delete and update commands.

11. The apparatus according to claim 7 wherein the computer system comprises a page buffer pool and wherein the flushing mechanism comprises a first storage mechanism which writes all modified, unwritten database pages in the page buffer pool to non-volatile storage before committing the transaction.

12. The apparatus according to claim 11 wherein the monitor comprises a first monitoring mechanism which monitors the write operations performed by the flushing mechanism and a second monitoring mechanism which monitors for errors write operations which write modified pages to the non-volatile storage when the page buffer pool contains a predetermined number of modified pages.

13. A computer program product for use in a computer system having a non-volatile storage and capable of processing a transaction which modifies one or more database pages, the computer program product comprising a computer usable medium having computer readable program code thereon, including:

program code for writing all modified, unwritten database pages to non-volatile storage before committing the transaction;

program code for monitoring for errors all write operations which write modified pages to the non-volatile storage; and

program code for committing the transaction if no errors are detected by the monitoring program code and rolling back the transaction if errors are detected by the monitoring program code.

14. The computer program product according to claim 13 further comprising an attribute defining the database so that recovery logging will be performed only for control information changes, including space allocation, and not for data changes.

15. The computer program product according to claim 13 further comprising

(e) backing up the database when the transaction has been completed.

16. The computer program product according to claim 13 wherein the transaction is performed using conventional insert, delete and update commands.

17. The computer program product according to claim 13 wherein the computer system comprises a page buffer pool and wherein the writing program code comprises program code for writing all modified, unwritten database pages in the page buffer pool to non-volatile storage before committing the transaction.

18. The computer program product according to claim 17 wherein the monitoring program code comprises program code for monitoring the write operations performed by the writing program code for errors and program code for monitoring for errors write operations which write modified pages to the non-volatile storage when the page buffer pool contains a predetermined number of modified pages.

19. A method for use in a computer system having a non-volatile storage and capable of processing a transaction which modifies one or more database pages comprising:

(a) disabling data recovery logging for the database;

(b) inserting the large object into the database and logging resulting changes to the space allocation map in a recovery log;

(c) writing all modified, unwritten database pages to non-volatile storage before committing the large object insertion;

(d) monitoring for errors all write operations which write modified pages to the non-volatile storage; and

(e) committing the large object insertion if no errors are detected in step (d) and rolling back the large object insertion if errors are detected in step (d).

20. The method according to claim 19 further comprising:

(f) using the changes to the space allocation map in the recovery log to delete the large object from the database on a rollback when the large object insertion was uncommitted.

21. The method according to claim 19 wherein the large object insertion is performed using a conventional insert command.

22. The method according to claim 19 wherein the computer system comprises a page buffer pool and wherein step (c) comprises:

(c1) writing all modified, unwritten database pages in the page buffer pool to non-volatile storage before committing the large object insertion.

23. The method according to claim 22 wherein step (d) comprises:

(d1) monitoring the write operations performed in step (c) for errors; and

(d2) monitoring for errors write operations which write modified pages to the non-volatile storage when the page buffer pool contains a predetermined number of modified pages.

24. A method for use in a computer system having a non-volatile storage and capable of processing a transaction which modifies one or more database pages comprising:

(a) disabling data recovery logging for the database;

(b) deleting the large object from the database and logging resulting changes to the space allocation map in a recovery log;

(c) writing all modified, unwritten database pages to non-volatile storage before committing the large object deletion;

(d) monitoring for errors all write operations which write modified pages to the non-volatile storage; and

(e) committing the large object deletion if no errors are detected in step (d) and rolling back the large object deletion if errors are detected in step (d).

25. The method according to claim 24 further comprising:

(f) using the changes to the space allocation map in the recovery log to reallocate space allocated to the large object from the database on a rollback when the large object deletion was uncommitted.

26. The method according to claim 24 wherein the large object deletion is performed using a conventional delete command.

27. The method according to claim 24 wherein the computer system comprises a page buffer pool and wherein step (c) comprises:

(c1) writing all modified, unwritten database pages in the page buffer pool to non-volatile storage before committing the large object deletion.

28. The method according to claim 27 wherein step (d) comprises:

(d1) monitoring the write operations performed in step (c) for errors; and

(d2) monitoring for errors write operations which write modified pages to the non-volatile storage when the page buffer pool contains a predetermined number of modified pages.