System and method for quiescing select data modification operations against an object of a database during one or more structural operations

• US 7,805,423 B1
• Filed: 11/15/2000
• Issued: 09/28/2010
• Est. Priority Date: 11/15/1999
• Status: Active Grant

• Current Assignee: Quest Software, Inc.
• Original Assignee: Quest Software, Inc.
• Inventors: Romine, William, Doering, Ross
• Primary Examiner(s): LE, DEBBIE M
• Application Number:
  US09/713,479
• Time in Patent Office: 3,604 Days
• Field of Search: 707200-206, 707100-102, 707/8
• US Class Current: 707/704
• CPC Class Codes: G06F 16/288 Entity relationship models
• CPC Class Codes: G06F 16/289 Object oriented databases

Abstract

The invention is a reorganization system having a reorganization application, a database management system, and one or more database files. The reorganization application communicates with the database management system and the one or more database files to reorganize an object, such as, for example, a table or index, while providing one or more clients substantially continuous access to the object. The reorganization application employs one or more trigger locks to synchronize the data in an original table and a reorganized table. The one or more trigger locks block select data modification operations while allowing one or more read-only operations and structural modification operations.

Claims

1. A method of reorganizing a table in a database file while providing clients of the database file continuous access to data stored in the table, the method comprising:

reorganizing data of an original table by copying the data to a reorganized table;

during the copying, allowing modifications to the data of the original table while collecting records of the modifications;

when the copying completes, applying the modifications from the collected records against the reorganized table;

applying a first partial lock to the original table, the first partial lock blocking select data modification operations against the original table while allowing other operations against the original table;

applying any remaining modifications from the collected records against the reorganized table;

applying a second partial lock to the reorganized table, the second partial lock blocking select data modification operations against the reorganized table while allowing other operations against the reorganized table during the reorganization such that the reorganized table remains continuously accessible during the reorganization;

substituting the reorganized table for the original table such that the reorganized table remains accessible while substituting the reorganized table for the original table; and

removing the second partial lock, wherein additional more-restrictive locks to the original table are not needed during the method of reorganizing the original table, thereby providing clients of the original table continuous access to the data during the reorganization through at least the other operations allowed by the first partial lock.

2. The method of claim 1, wherein the other operations allowed by at least one of the first and second partial locks comprises one or more structural modification operations.

3. The method of claim 1, wherein the other operations allowed by at least one of the first and second partial locks comprises one or more read-only operations.

4. The method of claim 1, further comprising:

during the application of the modifications from the collected records against the reorganized table, allowing additional modifications to the data of the original table while collecting additional records of the additional modifications; and

when the modifications and at least portions of the additional modifications have been applied against the reorganized table, applying the first partial lock to the original table;

wherein the step of applying any remaining modifications includes applying any remaining modifications or additional modifications against the reorganized table.

5. The method of claim 1, wherein when the original table included one or more relational constraints, the method further comprises applying at least one of the one or more relational constraints to the reorganized table.

6. The method of claim 5, wherein the application of the at least one relational constraint to the reorganized table includes applying a trigger procedure to the reorganized table.

7. The method of claim 5, wherein the application of the at least one relational constraint to the reorganized table includes applying a partial lock to another table.

8. The method of claim 1, wherein the original table includes a table name, and wherein the step of substituting the reorganized table for the original table further comprises renaming the original table another name and naming the reorganized table the table name.

9. The method of claim 1, further comprising archiving the original table.

10. The method of claim 1, wherein the copying of the data of the original table to the reorganized table further comprises creating an original synchronization point, after which the records of modifications are collected.

11. The method of claim 1, wherein before the application of the second partial lock, the original table and the reorganized table are in synchronization with one another.

12. A method of reorganizing an object in a database, the method comprising:

reorganizing an original object by copying data from the original object to a reorganized object;

applying a first partial lock to the original object, the first partial lock blocking data modification operations from modifying the original object while allowing other operations to access the original object, wherein additional more-restrictive locks to the original object are not needed during the method of reorganizing the original object, thereby providing clients of the original object continuous access to the data during the reorganization through at least the other operations allowed by the partial lock;

applying a second partial lock to the reorganized table, the second partial lock blocking select data modification operations against the reorganized table while allowing other operations against the reorganized table during the reorganization; and

substituting the reorganized object with the original object such that the reorganized object remains readable while substituting the reorganized object for the original object.

13. The method of claim 12, wherein the other operations include one or more read-only operations.

14. The method of claim 12, wherein the other operations include one or more structural modification operations.

15. A computer-implemented method of reorganizing an object in a database file, the method comprising:

reorganizing an original object by copying data from the original object to a reorganized object;

applying a first partial lock to the original table, the first partial lock blocking select data modification operations against the original table while allowing other operations against the original table;

applying a second partial lock to the reorganized object, the second partial lock blocking data modification operations from modifying the reorganized object, while allowing other operations to access the reorganized object, wherein the reorganized object remains accessible during reorganization; and

substituting the reorganized object with the original object such that the reorganized object remains readable while substituting the reorganized object for the original object.

16. The method of claim 15, wherein the other operations include one or more read-only operations.

17. The method of claim 15, wherein the other operations include one or more structural modification operations.

18. The method of claim 15, wherein the one or more structural modification operations include consecutive data definition language operations.

19. A reorganization system, comprising:

at least one database file having a table of data and a log file;

a database management system communicating with the at least one database file, thereby governing the modification of the data in the table; and

a reorganization application communicating with the database management system to access the table and communicating with the database file to access the log file, wherein the reorganization application is configured to copy the data of the table to a reorganized table, to apply modifications from the log file corresponding to modifications to the table during the copying of the data, and to substitute the reorganized table for the table, thereby reorganizing the data of the table, wherein the reorganization application is further configured to apply a first partial lock to the table, thereby blocking select data modification language operations while allowing at least read-only operations, wherein additional more-restrictive locks to the table are not needed during reorganization of the table, thereby providing clients of the table access to the data during the reorganization through at least the other operations allowed by the first partial lock, and wherein the reorganization application is further configured to apply a second partial lock to the reorganized table, thereby blocking select data modification language operations while allowing at least read-only operations and wherein the reorganized table remains readable when the reorganization application substitutes the reorganized table for the table.

20. The reorganization system of claim 19, wherein the second partial lock blocks select data modification language operations while allowing one of one or more read-only operations and one or more data definition language operations.

21. A reorganization application for reorganizing an object in a database, the reorganization application comprising an execution thread which reorganizes an original object by copying data of the original object to a reorganized object, and which applies a first partial lock to the original object, wherein the first partial lock blocks data modification operations from modifying the original object while allowing other operations to access the original object thereby providing clients of the original object access to the data during the reorganization through at least the other operations allowed by the first partial lock, and wherein a second partial lock is applied to the reorganized object such that the reorganized object remains readable when substituting the reorganized object for the original object.

22. The reorganization application of claim 21, wherein the other operations include one or more read-only operations.

23. The reorganization application of claim 21, wherein the other operations include one or more structural modification operations.

24. A computer-implemented reorganization application for reorganizing an object in a database, the reorganization application comprising an execution thread which reorganizes an original object by copying data of the original object to a reorganized object, and which applies a first partial lock to the original object and a second partial lock to the reorganized object, wherein the second partial lock blocks data modification operations from modifying the reorganized object while allowing other operations to access the reorganized object, and wherein the reorganized object remains readable when substituting the reorganized object for the original object.

25. The reorganization application of claim 24, wherein the other operations include one or more read-only operations.

26. The reorganization application of claim 24, wherein the other operations include one or more structural modification operations.

27. A computer-implemented reorganization application for reorganizing an object in a database, the reorganization application comprising an execution thread which reorganizes an original object by copying data of the original object to a reorganized object and which substitutes the reorganized object for the original object, wherein the execution thread applies a first partial lock to the original object and a second partial lock to the reorganized object that allows read-only access to the data during the substitution of the reorganized object for the original object while blocking other access to the data and wherein the reorganized object remains continuously accessible during reorganization.

28. The reorganization application of claim 27, wherein the read-only access to the data includes read-only access during multiple data definition language operations.