US 6,606,626 B1 · Database system with lock manager enhancement for improving concurrency

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.

Claims30

Dependent claims

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.

5 more elements

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.

3 more elements

Dependencies are inferred from the claim text.

Database system with lock manager enhancement for improving concurrency

Life of this patent

Database system with lock manager enhancement for improving concurrency

Life of this patent

Abstract

Claims30