Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-12-28
2003-12-16
Coby, Frantz (Department: 2171)
Data processing: database and file management or data structures
Database design
Data structure types
C008S101000, C008S102000, C008S094240
Reexamination Certificate
active
06665678
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention
The present invention relates to a method, system, and program for providing optimistic concurrency with scrollable cursors in a database.
2. Description of the Related Art
Prior art database programs include a feature referred to as cursors. A cursor is a named control structure used by an application program to point to a row of interest within some set of rows and to retrieve rows from the set, possibly making updates and deletions. A cursor points to rows from a database table that satisfy a structured query language (SQL) query against the table. The rows in the table that satisfy the SQL query comprise a result table of data. The SQL query includes an SQL SELECT statement and a WHERE clause to qualify rows according to a predicate. An application can then access data on a row-by-row basis from the result table.
If the result table is static and not updateable, then the result table may be materialized in a workfile. Alternatively, the cursor may point directly to the rows in the base table. In such case, the result table is not materialized in a workfile and the cursor is updateable when the base table is updated.
When a cursor is opened or initialized, the current row position of the cursor is the first record in the result table. The application program may then issue fetch commands to move the current row position and fetch forward or backward by one or more rows or from the first or last row by one or more rows. In current implementations, if the cursor is static, then the result table cannot be updated so that once it is created no rows will be added or removed, and no values in any rows will change. Further, a static cursor is not affected by other applications accessing or updating the data records in the underlying database table from which the result set was generated. Still further, in prior art static cursor implementations, the cursor is also read-only so that it is not possible for the application to change any values in the cursor result table.
In the prior art, after the cursor is opened and a FETCH request issued to access a row, the cursor obtains a lock on the page including the requested row or the row in the base table that is being fetched. This lock on the page or row of the base table is maintained until the next fetch request or when the cursor is closed. The purpose of this lock is to ensure that the row that was fetched and returned to the application program is not changed. One problem with this prior art approach is that maintaining the lock on the fetched page or row in the base table provides low concurrency because other applications cannot modify the database page or row while the lock is held. Thus, in the prior art, the best concurrency that can be obtained is by only locking the row, not the page including the row. However, even with this approach, there is still low concurrency for the row.
Thus, there is a need in the art for a technique for improving concurrency when cursors are used.
SUMMARY OF THE PREFERRED EMBODIMENTS
Preferred embodiments disclose 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 as part of performing the operation specified in the received command. 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.
In further embodiments, the command comprises a fetch command. In such case, performing the operation further comprises updating the subject row in the result table with the locked row in the base table if the locked row in the base table satisfies the query predicate clause. The updated row in the result table is then returned in response to the fetch command.
In still further embodiments, the command may comprise a delete or update command to delete or update a subject row in the result table. In such case, if the locked row in the base table satisfies the query predicate clause, then the subject row in the result table is deleted or updated.
With preferred embodiments, a lock is obtained on a row in the base table for the duration of the cursor operation, e.g., fetch, update, delete, etc. Once the FETCH operation has completed, the lock is released to provide high concurrency for the row in the base table. Data integrity is maintained because a requested operation following a release of the lock is not performed until various checks on the row in the result table are made to ensure that the base table row has not been modified in a manner that would preclude the fetch, delete or update operation from occurring in order to maintain data integrity.
REFERENCES:
patent: 4190835 (1980-02-01), Buynak
patent: 4221003 (1980-09-01), Chang et al.
patent: 4434475 (1984-02-01), McCaskill et al.
patent: 5097408 (1992-03-01), Huber
patent: 5241648 (1993-08-01), Cheng et al.
patent: 5276870 (1994-01-01), Shan et al.
patent: 5396623 (1995-03-01), McCall et al.
patent: 5410693 (1995-04-01), Yu et al.
patent: 5412805 (1995-05-01), Jordan, II et al.
patent: 5446858 (1995-08-01), Copeland et al.
patent: 5450581 (1995-09-01), Bergen et al.
patent: 5455944 (1995-10-01), Haderle et al.
patent: 5546576 (1996-08-01), Cochrane et al.
patent: 5579515 (1996-11-01), Hintz et al.
patent: 5581275 (1996-12-01), Glei et al.
patent: 5590319 (1996-12-01), Cohen et al.
patent: 5724575 (1998-03-01), Hoover et al.
patent: 5739817 (1998-04-01), Glei et al.
patent: 5742806 (1998-04-01), Reiner et al.
patent: 5758357 (1998-05-01), Barry et al.
patent: 5819268 (1998-10-01), Hackett
patent: 5835904 (1998-11-01), Vicik et al.
patent: 5887274 (1999-03-01), Barry et al.
patent: 5930795 (1999-07-01), Chen et al.
patent: 5963936 (1999-10-01), Cochrane et al.
patent: 5974407 (1999-10-01), Sacks
patent: 5987472 (1999-11-01), Serafin
patent: 6092062 (2000-07-01), Lohman et al.
patent: 6212514 (2001-04-01), Eberhard et al.
patent: 6223420 (2001-05-01), Lee et al.
patent: 63071741 (1988-04-01), None
patent: 1009546 (1989-01-01), None
patent: 5225250 (1993-09-01), None
patent: WO9857279 (1998-12-01), None
patent: WO9927679 (1999-06-01), None
U.S. patent application Ser. No. 09/656,558, filed on Sep. 7, 2000, entitled, “Method, System And Program For Implementing Scrollable Cursors In A Database”, invented by MA Bernal, et al.
Preliminary Amendment submitted in application Ser. No. 09/915,869, 09/915,866, 09/915,868, 09/915,783, 09/915,867, filed on Jul. 26, 2001.
U.S. patent application Ser. No. 09/365,953, filed on Aug. 8, 1999, entitled, “Automatic Pruning For Log-Based Replication”, invented by IT leong and PL Shimer.
U.S. patent application Ser. No. 09/325,661, filed on Jun. 2, 1999, Prov filed Oct. 8, 1998, entitled, “Database Extender For Storing, Querying, and Retrieving Structured Documents”, invented by DT Chang; JH Chow; J Xu; and JM Cheng.
U.S. patent application Ser. No. 09/324,499, filed on Jun. 2, 1999, Prov filed Oct. 8, 1998, entitled, “Method and Apparatus For Indexing Structured Documents With Rich Data Types”, invented by YC Fuh; JH Chow; J Xu; and JM Cheng.
U.S. patent application Ser. No. 09/324,827, filed on Jun. 2, 1999 , Prov filed Oct. 8, 1998, entitled, “Method and Apparatus For Querying Structured Documents Using A Database Extender”, invented by JH Chow; J Xu; and JM Cheng.
U.S. patent application Ser. No. 09/466,862, filed on Dec. 17, 1999, entitled, “Support For Summary Tables In A Heterogeneous Database Environment”, invented by RJ Cochrane; JC Kleewein; TY Leung; MH Pirahesh; N. Subramanian; and S. Venkataraman.
IBM, Corp., “New Options for FETCH in SQL”, Technical Disclosure Bulletin, vol. 36, No. 06A, Jun. 1993, pp. 559-560.
IBM, Corp., “Dynamic Ordering o
Chen Stephen Yao Ching
Goldsmith Jerry Wayne
Shyam Kalpana
Watts Julie Ann
Coby Frantz
Konrad Raynes & Victor & Mann LLP
LandOfFree
Method, system, and program for optimistic concurrency... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Method, system, and program for optimistic concurrency..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method, system, and program for optimistic concurrency... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3148549