US6606626B1
Expired / Lapsed
US
⏱️ 0.0 years remaining
Database system with lock manager enhancement for improving concurrency
Assignee: Sybase Inc
•
Filing: 2000-02-11
•
Granted: 2003-08-12
•
Expires: 2020-02-11
Abstract
Abstract A Client/Server Database System with an enhanced Lock Manager for improving concurrency is described. The system tracks information about database columns that are updated in the Lock Manager, in addition to the exclusive lock on the data row (in case of data row locking) or data page (in case of data page locking). In particular, a new field, lrcolumns, is added to the system's record lock data structure to track which columns have been modified. When an exclusive lock is requested on a row of the table being updated in the update statement, the Lock Manager sets the value of lrcolumns. In the context of an exclusive lock that was acquired to update one or more columns of a data row, if an exclusive lock was used only to insert or delete (but not update) the data row, the lrcolumns field would be set to 0. Similarly, the lrcolumns field is 0 for locks that are not exclusive locks (e.g., shared locks). With the Lock Manager enhancement of storing information about updated columns, scan (i.e., database scan operation) can skip a row (i.e., does not block for a lock on the row) if at least one of the sargs that the row does not qualify is on a column that was not updated by the updater. The approach avoids a lot of unnecessary blocking, thereby improving concurrency significantly.
Inventors
- 👤 Nagavamsi Ponnekanti
Bibliographic Details
Application Number
—
Jurisdiction
US
Data Source
google_patents
Last Synchronized
2026-11-03 21:17
CPC Classifications
G; G06; G06F; G06F16/00; G06F16/20; G06F16/23; G06F16/2308; G06F16/2336; G06F16/2343; Y; Y10; Y10S; Y10S707/00; Y10S707/99931; Y10S707/99938; Y10S707/99951; Y10S707/99952
Claims Analysis
Total Claims: 32
Claim 1
Independent Claim
215 words
In a database system having a database storing a database table comprising a plurality of data rows, said rows storing information organized into particular database fields, an improved method for scanning a table for particular rows that meet a specified scan criterion, the method comprising: providing each row of the database table with delete and update status flags for indicating which rows may be skipped during the table scan, wherein: (i) the update status flag for a row is set when a transaction updates the row and is cleared when the transaction commits, (ii) the delete status flag for a row is set when a transaction deletes a row and is cleared when the transaction rolls back, and (iii) both the update and delete status flags for a row are restored to their respective prior states when a transaction rolls back that had flagged the row as updated; tracking column status information for indicating columns that have been modified for a table being updated; based on said status flags and said column status information, determining those rows of the database table that may be skipped during the table scan; and scanning the database table for rows that meet said specified scan criterion without blocking rows that are determined to be rows that may be skipped.
Claim 2
Dependent Claim
215 words
In a database system having a database storing a database table comprising a plurality of data rows, said rows storing information organized into particular database fields, an improved method for scanning a table for particular rows that meet a specified scan criterion, the method comprising: providing each row of the database table with delete and update status flags for indicating which rows may be skipped during the table scan, wherein: (i) the update status flag for a row is set when a transaction updates the row and is cleared when the transaction commits, (ii) the delete status flag for a row is set when a transaction deletes a row and is cleared when the transaction rolls back, and (iii) both the update and delete status flags for a row are restored to their respective prior states when a transaction rolls back that had flagged the row as updated; tracking column status information for indicating columns that have been modified for a table being updated; based on said status flags and said column status information, determining those rows of the database table that may be skipped during the table scan; and scanning the database table for rows that meet said specified scan criterion without blocking rows that are determined to be rows that may be skipped.
Claim 3
Dependent Claim
57 words
The method of claim 1 , wherein during the table scan: the scan includes at least one scan argument specifying which rows qualify the scan, and wherein the scan skips a row if at least one scan argument that the row does not qualify is on a column that is not being updated by a concurrently-executing transaction.
Claim 4
Dependent Claim
35 words
The method of claim 1 , wherein said database system includes a lock data structure for tracking locks used to block access to rows, and wherein said lock data structure includes said column status information.
Claim 5
Dependent Claim
31 words
The method of claim 3 , wherein said column status information comprises a multi-bit field, with each bit representing a particular column that has been modified for a table being updated.
Claim 6
Dependent Claim
14 words
The method of claim 4 , wherein said multi-bit field comprises a 32-bit field.
Claim 7
Dependent Claim
21 words
The method of claim 1 , wherein said method is restricted for use in read committed and repeatable read isolation levels.
Claim 8
Dependent Claim
28 words
The method of claim 1 , wherein said update status flag allows a scan to skip uncommitted inserts and uncommitted deletes that do not qualify the scan criterion.
Claim 9
Dependent Claim
25 words
The method of claim 7 , wherein said column status information allows a scan to skip uncommitted updates that do not qualify the scan criterion.
Claim 10
Dependent Claim
51 words
The method of claim 1 , wherein said scan criterion comprises one or more scan arguments, and wherein a row may be skipped if at least one of the scan arguments that the row does not qualify is on a column that has not been modified for a table being updated.
Claim 11
Dependent Claim
40 words
The method of claim 1 , wherein said scanning step includes: if neither one of the status flags for a particular row have been set, skipping the particular row if the particular row fails to qualify the specified scan criterion.
Claim 12
Dependent Claim
28 words
The method of claim 10 , wherein the particular row is skipped regardless of whether there exists another concurrently pending transaction in the system that might roll back.
Claim 13
Dependent Claim
37 words
The method of claim 1 , wherein said scanning step includes: if the delete status flag for a particular row has been set, skipping the particular row if the row is not blocked by another concurrent transaction.
Claim 14
Dependent Claim
38 words
The method of claim 1 , wherein said scanning step includes: if the delete status flag for a particular row has been set, skipping the particular row if the particular row fails to qualify the specified scan criterion.
Claim 15
Dependent Claim
24 words
The method of claim 13 , wherein the particular row is skipped regardless of whether the particular row is blocked by another concurrent transaction.
Claim 16
Dependent Claim
49 words
The method of claim 1 , wherein said scanning step includes: if the update status flag for a particular row has been set, skipping the particular row if the particular row fails to qualify the specified scan criterion and the particular row is not blocked by another concurrent transaction.
Claim 17
Dependent Claim
39 words
The method of claim 1 , wherein said scanning step includes: if both of the status flags for a particular row have been set, skipping the particular row if the particular row is not blocked by another concurrent transaction.
Claim 18
Dependent Claim
51 words
The method of claim 1 , wherein said scanning step includes: if the delete status flag has been set for a particular row that does not meet the specified scan criterion but the particular row is blocked by another concurrent transaction, waiting for the concurrent transaction to commit or roll back.
Claim 19
Dependent Claim
19 words
The method of claim 17 , further comprising: if the concurrent transaction rolls back, clearing the delete status flag.
Claim 20
Dependent Claim
17 words
The method of claim 17 , further comprising: if the concurrent transaction commits, skipping the particular row.
Claim 21
Dependent Claim
17 words
The method of claim 1 , wherein the delete status flag comprises a “row delete” status bit.
Claim 22
Dependent Claim
17 words
The method of claim 1 , wherein the update status flag comprises a “row update” status bit.
Claim 23
Dependent Claim
29 words
The method of claim 1 , wherein the update status flag of a particular row is set by a transaction that inserted the particular row into the database table.
Claim 24
Dependent Claim
31 words
The method of claim 22 , wherein the delete status flag of the particular row is set if the transaction that inserted the particular row into the database table rolls back.
Claim 25
Dependent Claim
21 words
The method of claim 1 , wherein said specified scan criterion is based, at least in part, on a database query.
Claim 26
Dependent Claim
17 words
The method of claim 24 , wherein said database query comprises a data manipulation language (DML) statement.
Claim 27
Dependent Claim
173 words
A database system having a database storing a database table comprising a plurality of data records, said data records storing information organized into particular database fields, comprising: a server connect to a client; computer-implemented program logic for transmitting from the client to the server a query specifying a particular database transaction affecting data records of the database table, each data record being associated with delete and update status flags for indicating which records are to be deleted or updated, respectively, by another transaction that is concurrently executing; computer-implemented program logic for tracking column status information for indicating columns that have been modified for a table being updated; computer-implemented program logic for determining, based on said status flags, said column status information, and conformance to said query, which records of the database table need to be blocked from access by other transactions, during execution of the particular database transaction; and computer-implemented program logic for blocking only those particular records of the database table which been determined to be records that need to the blocked.
Claim 28
Dependent Claim
173 words
A database system having a database storing a database table comprising a plurality of data records, said data records storing information organized into particular database fields, comprising: a server connect to a client; computer-implemented program logic for transmitting from the client to the server a query specifying a particular database transaction affecting data records of the database table, each data record being associated with delete and update status flags for indicating which records are to be deleted or updated, respectively, by another transaction that is concurrently executing; computer-implemented program logic for tracking column status information for indicating columns that have been modified for a table being updated; computer-implemented program logic for determining, based on said status flags, said column status information, and conformance to said query, which records of the database table need to be blocked from access by other transactions, during execution of the particular database transaction; and computer-implemented program logic for blocking only those particular records of the database table which been determined to be records that need to the blocked.
Claim 29
Dependent Claim
85 words
The system of claim 26 , wherein: (i) the update status flag for a record is set when a transaction updates the record and is cleared when the transaction commits, (ii) the delete status flag for a record is set when a transaction deletes a record and is cleared when the transaction rolls back, and (iii) both the update and delete status flags for a record are restored to their respective prior states when a transaction rolls back that had flagged the record as updated.
Claim 30
Dependent Claim
42 words
The system of claim 26 , wherein said computer-implemented program logic for blocking access provides access to a particular record when the particular record conforms to the query and neither one of the status flags for the particular record have been set.
Claim 31
Dependent Claim
44 words
The system of claim 26 , wherein said computer-implemented program logic for blocking access does not block a particular record when neither one of the status flags for the particular record have been set and the particular record fails to conform to the query.
Claim 32
Dependent Claim
65 words
The system of claim 26 , wherein said query comprises one or more scan arguments, and wherein said computer-implemented program logic for blocking access does not block a particular record that may be skipped as a result of at least one of the scan arguments that the record does not qualify is on a column that has not been modified for a table being updated.
References Cited (Backward Citations)
US4606002A
Cited
US4677550A
Cited
US5265244A
Cited
US4967341A
Cited
US5115392A
Cited
US4791561A
Cited
US4805099A
Cited
US5123104A
Cited
US4947320A
Cited
US5089952A
Cited
US5163148A
Cited
US5386557A
Cited
US5430869A
Cited
US5204958A
Cited
US5504888A
Cited
US5493728A
Cited
US5555389A
Cited
US5630124A
Cited
US5721909A
Cited
US5835757A
Cited
US5758356A
Cited
US6052695A
Cited
US5842196A
Cited
US5794241A
Cited
US5884327A
Cited
US5983225A
Cited
US6112209A
Cited
USPTO Annuity & Maintenance Windows
| Annuity Stage | Window Opens | Payment Deadline | Surcharge Period Until | Estimated Fee | Status |
|---|---|---|---|---|---|
| 3.5 Years (Year 4) | 2006-08-11 | 2007-02-10 | 2007-08-12 | $2000.00 | Window Closed / Lapsed |
| 7.5 Years (Year 8) | 2010-08-11 | 2011-02-10 | 2011-08-12 | $3760.00 | Window Closed / Lapsed |
| 11.5 Years (Year 12) | 2014-08-11 | 2015-02-10 | 2015-08-12 | $7700.00 | Window Closed / Lapsed |