Method, system, and program for optimistic concurrency control for scrollable cursors in a database

• US 6,665,678 B2
• Filed: 12/28/2000
• Issued: 12/16/2003
• Est. Priority Date: 09/07/2000
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Watts, Julie Ann, Goldsmith, Jerry Wayne, Ching Chen, Stephen Yao, Shyam, Kalpana
• Primary Examiner(s): Coby, Frantz
• Application Number:
  US09/750,543
• Publication Number:
  US 20020087561A1
• Time in Patent Office: 1,083 Days
• Field of Search: 707/8, 707/203, 707/100, 707/101, 707/102
• US Class Current: 707/704
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

Provided is a method, system, and program for performing a cursor operation. A result table is generated including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause. A command is received to perform a cursor operation with respect to a subject row in the result table. A lock is obtained on a row in the base table corresponding to the subject row in the result table. An operation specified in the received command is performed with respect to the subject row after obtaining the lock. The lock on the row in the base table is released after performing the operation.

Claims

1. A method for performing a cursor operation, comprising:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

receiving a command to perform a cursor operation with respect to a subject row in the result table;

obtaining a lock on a row in the base table corresponding to the subject row in the result table as part of performing the operation specified in the received command;

performing the operation specified in the received command with respect to the subject row after obtaining the lock; and

releasing the lock on the row in the base table after performing the operation.

2. The method of claim 1, wherein the command comprises a fetch command, and wherein performing the operation further comprises:

if the locked row in the base table satisfies the query predicate clause, then updating the subject row in the result table with the locked row in the base table; and

returning the updated row in the result table in response to the fetch command.

3. The method of claim 2, wherein performing the operation further comprises:

determining whether the locked row in the base table has been deleted or has data that no longer satisfies the query predicate clause; and

returning a code indicating that fetch operation not completed if the locked row in the base table was deleted or no longer satisfies the query predicate clause.

4. The method of claim 1, wherein the command comprises a delete command to delete a subject row from the result table, wherein the lock is released after the delete operation is committed, and wherein performing the operation further comprises:

if the locked row in the base table satisfies the query predicate clause, then deleting the row in the result table.

5. The method of claim 4, wherein performing the operation further comprises:

determining whether the locked row in the base table has been deleted; and

returning a code indicating that the delete operation was not completed if the locked row in the base table has been deleted or no longer satisfies the query predicate clause.

6. The method of claim 5, further comprising:

determining whether data in the subject row in the result table matches data in the locked row in the base table, wherein the subject row is not deleted from the result table if a data mismatch is determined.

7. The method of claim 5, wherein a flag indicates whether a row in the base table has been deleted, and wherein the flag for the locked row in the base table is checked to determine whether the locked row has been deleted.

8. The method of claim 1, wherein the command comprises an update command to update a subject row in the result table, wherein the lock is released after the delete operation is committed, and wherein performing the operation further comprises:

if the locked row in the base table satisfies the query predicate clause, then updating the row in the result table.

9. The method of claim 8, wherein performing the operation further comprises:

determining whether the locked row in the base table has been deleted; and

returning code indicating that update operation was not completed if the locked row in the base table has been deleted or no longer satisfies the query predicate clause.

10. The method of claim 8, further comprising:

determining whether data in the subject row in the result table matches data in the locked row in the base table, wherein the subject row in the result table is not updated if a data mismatch is determined.

11. The method of claim 9, wherein a flag indicates whether a row in the base table has been deleted, and wherein the flag for the locked row in the base table is checked to determine whether the locked row has been deleted.

12. A system for performing a cursor operation, comprising:

means for generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

means for receiving a command to perform a cursor operation with respect to a subject row in the result table;

means for obtaining a lock on a row in the base table corresponding to the subject row in the result table as part of performing the operation specified in the received command;

means for performing the operation specified in the received command with respect to the subject row after obtaining the lock; and

means for releasing the lock on the row in the base table after performing the operation.

13. The system of claim 12, wherein the command comprises a fetch command, and wherein the means for performing the operation further performs:

if the locked row in the base table satisfies the query predicate clause, then updating the subject row in the result table with the locked row in the base table; and

returning the updated row in the result table in response to the fetch command.

14. The system of claim 13, wherein the means for performing the operation further performs:

determining whether the locked row in the base table has been deleted or has data that no longer satisfies the query predicate clause; and

returning a code indicating that fetch operation not completed if the locked row in the base table was deleted or no longer satisfies the query predicate clause.

15. The system of claim 12, wherein the command comprises a delete command to delete a subject row from the result table, wherein the lock is released after the delete operation is committed, and wherein the means for performing the operation further performs:

if the locked row in the base table satisfies the query predicate clause, then deleting the row in the result table.

16. The system of claim 15, wherein the means for performing the operation further performs:

determining whether the locked row in the base table has been deleted; and

returning a code indicating that the delete operation was not completed if the locked row in the base table has been deleted or no longer satisfies the query predicate clause.

17. The system of claim 16, further comprising:

means for determining whether data in the subject row in the result table matches data in the locked row in the base table, wherein the subject row is not deleted from the result table if a data mismatch is determined.

18. The system of claim 16, wherein a flag indicates whether a row in the base table has been deleted, further comprising means for checking the flag for the locked row in the base table to determine whether the locked row has been deleted.

19. The system of claim 12, wherein the command comprises an update command to update a subject row in the result table, wherein the lock is released after the delete operation is committed, and wherein the means for performing the operation further performs:

if the locked row in the base table satisfies the query predicate clause, then updating the row in the result table.

20. The system of claim 19, wherein the means for performing the operation further performs:

determining whether the locked row in the base table has been deleted; and

returning code indicating that update operation was not completed if the locked row in the base table has been deleted or no longer satisfies the query predicate clause.

21. The system of claim 19, further comprising:

means for determining whether data in the subject row in the result table matches data in the locked row in the base table, wherein the subject row in the result table is not updated if a data mismatch is determined.

22. The system of claim 20, wherein a flag indicates whether a row in the base table has been deleted, further comprising means for checking the flag for the locked row in the base table to determine whether the locked row has been deleted.

23. A program for performing a cursor operation, wherein the program comprises code implemented in a computer readable medium that is capable of causing a processor to perform:

generating a result table including rows corresponding to a subset of rows in a base table having data in one or more columns that satisfy a query predicate clause;

receiving a command to perform a cursor operation with respect to a subject row in the result table;

obtaining a lock on a row in the base table corresponding to the subject row in the result table as part of performing the operation specified in the received command;

performing the operation specified in the received command with respect to the subject row after obtaining the lock; and

releasing the lock on the row in the base table after performing the operation.

24. The program of claim 23, wherein the command comprises a fetch command, and wherein performing the operation further comprises:

if the locked row in the base table satisfies the query predicate clause, then updating the subject row in the result table with the locked row in the base table; and

returning the updated row in the result table in response to the fetch command.

25. The program of claim 24, wherein performing the operation further comprises:

determining whether the locked row in the base table has been deleted or has data that no longer satisfies the query predicate clause; and

returning a code indicating that fetch operation not completed if the locked row in the base table was deleted or no longer satisfies the query predicate clause.

26. The program of claim 23, wherein the command comprises a delete command to delete a subject row from the result table, wherein the lock is released after the delete operation is committed, and wherein performing the operation further comprises:

if the locked row in the base table satisfies the query predicate clause, then deleting the row in the result table.

27. The program of claim 26, wherein performing the operation further comprises:

determining whether the locked row in the base table has been deleted; and

returning a code indicating that the delete operation was not completed if the locked row in the base table has been deleted or no longer satisfies the query predicate clause.

28. The program of claim 27, wherein the program code is further capable of causing the processor to perform:

determining whether data in the subject row in the result table matches data in the locked row in the base table, wherein the subject row is not deleted from the result table if a data mismatch is determined.

29. The program of claim 27, wherein a flag indicates whether a row in the base table has been deleted, and wherein the flag for the locked row in the base table is checked to determine whether the locked row has been deleted.

30. The program of claim 23, wherein the command comprises an update command to update a subject row in the result table, wherein the lock is released after the delete operation is committed, and wherein performing the operation further comprises:

if the locked row in the base table satisfies the query predicate clause, then updating the row in the result table.

31. The program of claim 30, wherein performing the operation further comprises:

determining whether the locked row in the base table has been deleted; and

returning code indicating that update operation was not completed if the locked row in the base table has been deleted or no longer satisfies the query predicate clause.

32. The program of claim 30, wherein the program code is further capable of causing the processor to perform:

determining whether data in the subject row in the result table matches data in the locked row in the base table, wherein the subject row in the result table is not updated if a data mismatch is determined.

33. The program of claim 31, wherein a flag indicates whether a row in the base table has been deleted, and wherein the flag for the locked row in the base table is checked to determine whether the locked row has been deleted.