Multi-phase locking for partition maintenance operations

• US 6,105,026 A
• Filed: 07/03/1997
• Issued: 08/15/2000
• Est. Priority Date: 07/03/1997
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Davis, Elizabeth, Kruglikov, Andre, Putzolu, Gianfranco
• Primary Examiner(s): VON BUHR, MARIA N
• Application Number:
  US08/887,963
• Time in Patent Office: 1,139 Days
• Field of Search: 707/8, 707/10, 707/100, 707/103, 707/200-204
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/2343 Locking methods, e.g. distributed locking or locking implementation details
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database

Abstract

A method and apparatus for multi-phase locking for partition maintenance operations. In the first phase, a shared data dictionary lock is acquired on a body of metadata in a data dictionary. Next, the data dictionary is read and a list of affected partitions is generated. In the second phase, an intent exclusive data lock is acquired on the affected table. Next, an exclusive data lock is acquired on the affected partitions of the affected table. The shared data dictionary lock on the data dictionary is released and a physical attribute of the data of the affected partitions is changed. In the third phase, an exclusive data dictionary lock is acquired on the data dictionary. The metadata associated with the affected partitions in the data dictionary is updated and the exclusive data locks on the affected partitions and the intent exclusive data lock on the affected table are released. Finally, the exclusive data dictionary lock on the data dictionary is released. As a result of performing multi-phase locking for partition maintenance operations, the scope of resources locked is reduced and the efficiency of the partition maintenance operations, from a concurrency point of view, is increased.

Claims

1. A method for performing an operation on a partitioned object, wherein said operation changes a physical attribute of one or more partitions of said partitioned object, the method comprising the steps of:

acquiring a lock on a body of metadata associated with said one or more partitions;

acquiring a data lock on said one or more partitions;

while holding said data lock on said one or more partitions, releasing said lock on said body of metadata;

changing said physical attribute of said one or more partitions; and

releasing said data lock on said one or more partitions.

2. The method of claim 1, further comprising the steps of:

acquiring a second lock on said body of metadata associated with said one or more partitions;

updating said body of metadata; and

releasing said second lock on said body of metadata.

3. The method of claim 2, wherein:

said step of acquiring said second lock on said body of metadata comprises the step of acquiring an exclusive data dictionary lock on a subset of said body of metadata; and

said step of releasing said second lock on said body of metadata comprises the step of releasing said exclusive data dictionary lock on said subset of said body of metadata.

4. The method of claim 2, wherein said step of acquiring said second lock on said body of metadata comprises the step of acquiring an exclusive data dictionary lock on said body of metadata.

5. The method of claim 1, wherein said method further comprises the steps of:

reading said body of metadata; and

generating a list of affected partitions, wherein said affected partitions are partitions that will be affected by said operation.

6. The method of claim 5 wherein:

said step of acquiring said data lock comprises the step of acquiring an exclusive data lock on said one or more partitions that belong to said list of affected partitions; and

said step of releasing said data lock comprises the step of releasing said exclusive data lock on said one or more partitions that belong to said list of affected partitions.

7. The method of claim 1, wherein:

said step of acquiring said data lock on said one or more partitions comprises the step of acquiring an exclusive data lock on said one or more partitions; and

said step releasing said data lock on said one or more partitions comprises the step of releasing said exclusive data lock on said one or more partitions.

8. The method of claim 1, wherein said step of acquiring said lock on said body of metadata comprises the step of acquiring a shared data dictionary lock on said body of metadata.

9. The method of claim 1, further comprising the step of determining whether said operation should be performed using a single-phase locking technique or a multi-phase locking technique based upon a type of said operation.

10. The method of claim 1, further comprising the steps of:

determining a cost associated with said operation; and

determining whether said operation should be performed using a single phase-locking technique or a multi-phase locking technique based upon said cost.

11. A method for performing an operation on a partitioned object, wherein said operation changes a physical attribute of one or more partitions of said partitioned object, the method comprising the steps of:

acquiring a first lock on a body of metadata associated with said one or more partitions;

acquiring a data lock on said one or more partitions;

while holding said data lock on said one or more partitions, releasing said first lock on said body of metadata;

changing said one or more partitions;

acquiring a second lock on said body of metadata;

updating said body of metadata;

releasing said data lock on said one or more partitions; and

releasing said second lock on said body of metadata.

12. The method of claim 11, wherein said method further comprises the steps of:

reading said body of metadata; and

generating a list of affected partitions, wherein said affected partitions are partitions that will be affected by said operation.

13. The method of claim 12, wherein:

said step of acquiring said data lock on said one or more partitions comprises the step of acquiring an exclusive data lock on said one or more partitions that belongs to said list of affected partitions; and

said step of releasing said data lock on said one or more partitions comprises the step of releasing said exclusive data lock on said one or more partitions.

14. The method of claim 11, wherein:

said step of acquiring said data lock on said one or more partitions comprises the step of acquiring an exclusive data lock on said one or more partitions; and

said step of releasing said data lock on said one or more partitions comprises the step of releasing said exclusive data lock on said one or more partitions.

15. The method of claim 11 wherein:

said step of acquiring said second lock on said body of metadata comprises acquiring an exclusive data dictionary lock on a subset of said body of metadata; and

said step of releasing said second lock on said body of metadata comprises releasing said exclusive data dictionary lock on said subset of said body of metadata.

16. The method of claim 11, wherein said step of acquiring said first lock on said body of metadata comprises the step of acquiring a shared data dictionary lock on said body of metadata.

17. The method of claim 11, wherein said step of acquiring said second lock on said body of metadata comprises the step of acquiring an exclusive data dictionary lock on said body of metadata.

18. The method of claim 11, further comprising the step of determining whether said operation should be performed using a single-phase locking technique or a multi-phase locking technique based upon a type of said operation.

19. The method of claim 11, further comprising the steps of:

determining a cost associated with said operation; and

determining whether said operation should be performed using a single phase-locking technique or a multi-phase locking technique based upon said cost.

20. A computer readable medium having stored thereon sequences of instructions for performing an operation on a partitioned object, wherein said operation changes a physical attribute of one or more partitions of said partitioned object, said sequences of instructions including instructions for performing the steps of:

acquiring a lock on a body of metadata associated with said one or more partitions;

acquiring a data lock on said one or more partitions;

while holding said data lock on said one or more partitions, releasing said lock on said body of metadata;

changing said physical attribute of said one or more partitions; and

releasing said data lock on said one or more partitions.

21. The computer readable medium of claim 20, said sequences of instructions further including instructions for performing the steps of:

acquiring a second lock on said body of metadata associated with said one or more partitions;

updating said body of metadata; and

releasing said second lock on said body of metadata.

22. The computer readable medium of claim 21, wherein:

said step of acquiring said second lock on said body of metadata comprises the step of acquiring an exclusive data dictionary lock on a subset of said body of metadata; and

said step of releasing said second lock on said body of metadata comprises the step of releasing said exclusive data dictionary lock on said subset of said body of metadata.

23. The computer readable medium of claim 21, wherein said step of acquiring said second lock on said body of metadata comprises the step of acquiring an exclusive data dictionary lock on said body of metadata.

24. The computer readable medium of claim 20, said sequences of instructions further including instructions for performing the steps of:

reading said body of metadata; and

generating a list of affected partitions, wherein said affected partitions are partitions that will be affected by said operation.

25. The computer readable medium of claim 24 wherein:

said step of acquiring said data lock comprises the step of acquiring an exclusive data lock on said one or more partitions that belong to said list of affected partitions; and

said step of releasing said data lock comprises the step of releasing said exclusive data lock on said one or more partitions that belong to said list of affected partitions.

26. The computer readable medium of claim 20, wherein:

said step of acquiring said data lock on said one or more partitions comprises the step of acquiring an exclusive data lock on said one or more partitions; and

said step releasing said data lock on said one or more partitions comprises the step of releasing said exclusive data lock on said one or more partitions.

27. The computer readable medium of claim 20, wherein said step of acquiring said lock on said body of metadata comprises the step of acquiring a shared data dictionary lock on said body of metadata.

28. The computer readable medium of claim 20, said sequences of instructions further including instructions for performing the step of determining whether said operation should be performed using a single-phase locking technique or a multi-phase locking technique based upon a type of said operation.

29. The computer readable medium of claim 20, said sequences of instructions further including instructions for performing the steps of:

determining a cost associated with said operation; and

determining whether said operation should be performed using a single phase-locking technique or a multi-phase locking technique based upon said cost.

30. A computer readable medium having stored thereon sequences of instructions for performing an operation on a partitioned object, wherein said operation changes a physical attribute of one or more partitions of said partitioned object, said sequences of instructions including instructions for performing the steps of:

acquiring a first lock on a body of metadata associated with said one or more partitions;

acquiring a data lock on said one or more partitions;

while holding said data lock on said one or more partitions, releasing said first lock on said body of metadata;

changing said one or more partitions;

acquiring a second lock on said body of metadata;

updating said body of metadata;

releasing said data lock on said one or more partitions; and

releasing said second lock on said body of metadata.

31. The computer readable medium of claim 30, said sequences of instructions further including instructions for performing the steps of:

reading said body of metadata; and

generating a list of affected partitions, wherein said affected partitions are partitions that will be affected by said operation.

32. The computer readable medium of claim 31, wherein:

said step of acquiring said data lock on said one or more partitions comprises the step of acquiring an exclusive data lock on said one or more partitions that belongs to said list of affected partitions; and

said step of releasing said data lock on said one or more partitions comprises the step of releasing said exclusive data lock on said one or more partitions.

33. The computer readable medium of claim 30, wherein:

said step of acquiring said data lock on said one or more partitions comprises the step of acquiring an exclusive data lock on said one or more partitions; and

said step of releasing said data lock on said one or more partitions comprises the step of releasing said exclusive data lock on said one or more partitions.

34. The computer readable medium of claim 30 wherein:

said step of acquiring said second lock on said body of metadata comprises acquiring an exclusive data dictionary lock on a subset of said body of metadata; and

said step of releasing said second lock on said body of metadata comprises releasing said exclusive data dictionary lock on said subset of said body of metadata.

35. The computer readable medium of claim 30, wherein said step of acquiring said first lock on said body of metadata comprises the step of acquiring a shared data dictionary lock on said body of metadata.

36. The computer readable medium of claim 30, wherein said step of acquiring said second lock on said body of metadata comprises the step of acquiring an exclusive data dictionary lock on said body of metadata.

37. The computer readable medium of claim 30, said sequences of instructions further including instructions for performing the step of determining whether said operation should be performed using a single-phase locking technique or a multi-phase locking technique based upon a type of said operation.

38. The computer readable medium of claim 30, said sequences of instructions further including instructions for performing the steps of:

determining a cost associated with said operation; and

determining whether said operation should be performed using a single phase-locking technique or a multi-phase locking technique based upon said cost.