System and method for performing database operations and for skipping over tuples locked in an incompatible mode

• US 6,304,873 B1
• Filed: 07/06/1999
• Issued: 10/16/2001
• Est. Priority Date: 07/06/1999
• Status: Expired due to Term

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Compaq Computer Corporation (HP Inc.)
• Inventors: Klein, Johannes, Van der Linden, Robbert C., Rathee, Raj K.
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): NGUYEN, TAM V
• Application Number:
  US09/347,995
• Time in Patent Office: 833 Days
• Field of Search: 707/3, 707/1, 707/201, 707/202, 707/100, 707/8, 707/2
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2343 Locking methods, e.g. distributed locking or locking implementation details
• CPC Class Codes: G06F 16/2448 for particular applications; for extensibility, e.g. user defined types
• CPC Class Codes: G06F 16/24568 Data stream processing; Continuous queries
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

A database management system is extended to process SQL statements so as to skip over rows that are locked in conflicting modes. Normally, a table access operator is blocked when it attempts to access a row that is locked in a conflicting mode, such as a write lock mode, that is inconsistent with the read or write access required by the operator. Generally, the lock in the conflicting mode will be held by a transaction other than the transaction associated with the operator in question. When the SQL statement being executed uses the “skip conflict” syntax provided by the present invention, the operator skips over rows that are locked in conflicting modes, which would otherwise cause the operator to suspend operation. Furthermore, if the operator is operating in streaming mode, a key that identifies such skipped rows is added to the operator'"'"'s list of rows to be processed at a later time.

Claims

1. A method of processing data in a database management system in accordance with a specified query, comprising:

receiving from a calling application a request for rows from a database table; and

responding to the request for rows in accordance with the specified query by accessing a defined range of rows in a database table and returning to the calling application qualifying rows, if any, from the database table;

the responding step including skipping over rows, if any, locked in conflicting modes, the conflicting modes preventing access to the locked rows.

2. The method of claim 1, wherein the responding step includes deleting the qualifying rows returned to the calling application.

3. The method of claim 1, further including:

establishing a delta scan list of rows of the database table;

adding to the delta scan list the rows, if any, skipped over by the responding step; and

the responding step including, when predefined status criteria are satisfied, accessing rows in the delta scan list that are in the defined range or rows and returning to the calling application qualifying rows, if any, from among the accessed rows.

4. A method of processing a stream of data in a database management system, in accordance with a query, the query specifying a defined range of rows in a database table to be accessed, the method comprising:

performing an initial scan to access the defined range of rows in the database table;

while performing the initial scan,

building a list of rows that are added to the database table during the initial scan and that are to be accessed by the scan operator after the initial scan;

skipping over rows, if any, locked in conflicting modes, the conflicting modes preventing access to the locked rows; and

adding to the list of rows the locked rows skipped during the initial scan;

performing a secondary scan to access rows in the list, including accessing the rows in the list that are not locked in the conflicting modes; and

while performing the secondary scan, adding to the list of rows additional rows added to the database table after the initial scan.

5. The method of claim 4,

during the secondary scan,

removing from the list each unlocked row accessed during the secondary scan; and

suspending operation of the secondary scan when all rows in the list not locked in the conflicting modes have been accessed.

6. The method of claim 5, further including

when a row locked in at least one of the conflicting modes is unlocked by a terminating transaction, scheduling the secondary scan to resume operation.

7. The method of claim 4,

when a transaction that has added rows to the database table aborts, removing from the list the rows that were added to the table by the transaction.

8. The method of claim 4, wherein when the query specifies a data dequeuing operation the method includes automatically deleting each unlocked row accessed while performing the first and secondary scans.

9. A database management system, comprising:

a query execution module for executing a specified query, the query execution module including a table access operator for accessing a defined range of rows in a specified one of the database tables;

the table access operator including instructions for responding to a request, received from a calling application by returning to the calling application qualifying rows, if any, from the database table;

the table access operator further including instructions for skipping over rows, if any, locked in conflicting modes, the conflicting modes preventing access to the locked rows.

10. The database management system of claim 9, wherein the specified query includes an embedded delete operation, and the table access operator includes instructions for deleting the qualifying rows returned to the calling application.

11. The database management system of claim 9, the table access operator further including instructions for:

establishing a delta scan list of rows of the database table;

adding to the delta scan list the rows, if any, skipped over,

when predefined status criteria are satisfied, accessing rows in the delta scan list that are in the defined range or rows and returning to the calling application qualifying rows, if any, from among the accessed rows.

12. A database management system, comprising:

a query execution module for executing a specified query, the query execution module including a table access operator for accessing a defined range of rows in a specified one of the database tables;

the table access operator including executable instructions for;

performing an initial scan to access the defined range of rows in the database table;

while performing the initial scan,

building a list of rows that are added to the database table during the initial scan and that are to be accessed by the scan operator after the initial scan;

skipping over rows, if any, locked in conflicting modes, the conflicting modes preventing access to the locked rows; and

adding to the list of rows the locked rows skipped during the initial scan;

performing a secondary scan to access rows in the list, including accessing the rows in the list that are not locked in the conflicting modes; and

while performing the secondary scan, adding to the list of rows additional rows added to the database table after the initial scan.

13. The database management system of claim 12,

the table access operation further including instructions for:

removing from the list each unlocked row accessed during the secondary scan; and

suspending operation of the secondary scan when all rows in the list not locked in the conflicting modes have been accessed.

14. The database management system of claim 13, further including

instructions for scheduling the secondary scan to resume operation when a row locked in at least one of the conflicting modes is unlocked by a terminating transaction.

15. The database management system of claim 12, further including instructions for removing from the list rows added to the table by a transaction when that transaction aborts.

16. The database management system of claim 12, wherein when the query specifies a data dequeuing operation the system includes instructions for automatically deleting each unlocked row accessed while performing the first and secondary scans.

17. A computer program product for use in conjunction with a computer system, the computer system including a database, including a plurality of tables, each table storing data in one or more rows;

the computer program product comprising a computer readable storage medium and a computer program mechanism embedded therein, the computer program mechanism comprising;

a query execution module for executing a specified query, the query execution module including a table access operator for accessing a defined range of row s in a specified one of the database tables;

the table access operator including instructions for responding to a request, received from a calling application by returning to the calling application qualifying rows, if any, from the database table;

the table access operator further including instructions for skipping over rows, if any, locked in conflicting modes, the conflicting modes preventing access to the locked rows.

18. The computer program product of claim 17, wherein the specified query includes an embedded delete operation, and the table access operator includes instructions for deleting the qualifying rows returned to the calling application.

19. The computer program product of claim 17, the table access operator further including instructions for:

establishing a delta scan list of rows of the database table;

adding to the delta scan list the rows, if any, skipped over,

when predefined status criteria are satisfied, accessing rows in the delta scan list that are in the defined range or rows and returning to the calling application qualifying rows, if any, from among the accessed rows.

20. A computer program product for use in conjunction with a computer system, the computer system including a database, including a plurality of tables, each table storing data in one or more rows;

the computer program product comprising a computer readable storage medium and a computer program mechanism embedded therein, the computer program mechanism comprising;

a query execution module for executing a specified query, the query execution module including a table access operator for accessing a defined range of rows in a specified one of the database tables;

the table access operator including executable instructions for;

performing an initial scan to access the defined range of rows in the database table;

while performing the initial scan,

building a list of rows that are added to the database table during the initial scan and that are to be accessed by the scan operator after the initial scan;

skipping over rows, if any, locked in conflicting modes, the conflicting modes preventing access to the locked rows; and

adding to the list of rows the locked rows skipped during the initial scan;

performing a secondary scan to access rows in the list, including accessing the rows in the list that are not locked in the conflicting modes; and

while performing the secondary scan, adding to the list of rows additional rows added to the database table after the initial scan.

21. The computer program product of claim 20,

the table access operation further including instructions for:

removing from the list each unlocked row accessed during the secondary scan; and

suspending operation of the secondary scan when all rows in the list not locked in the conflicting modes have been accessed.

22. The computer program product of claim 21, further including

instructions for scheduling the secondary scan to resume operation when a row locked in at least one of the conflicting modes is unlocked by a terminating transaction.

23. The computer program product of claim 20, further including instructions for removing from the list rows added to the table by a transaction when that transaction aborts.

24. The database management system of claim 20, wherein when the query specifies a data dequeuing operation the system includes instructions for automatically deleting each unlocked row accessed while performing the first and secondary scans.