Database system with lock manager enhancement for improving concurrency
First Claim
1. 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.
1 Assignment
0 Petitions
Accused Products
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.
170 Citations
30 Claims
-
1. 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. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25)
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.
-
-
3. 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.
-
4. 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.
-
5. The method of claim 4, wherein said multi-bit field comprises a 32-bit field.
-
6. The method of claim 1, wherein said method is restricted for use in read committed and repeatable read isolation levels.
-
7. 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.
-
8. The method of claim 7, wherein said column status information allows a scan to skip uncommitted updates that do not qualify the scan criterion.
-
9. 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.
-
10. 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.
-
11. 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.
-
12. 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.
-
13. 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.
-
14. The method of claim 13, wherein the particular row is skipped regardless of whether the particular row is blocked by another concurrent transaction.
-
15. 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.
-
16. 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.
-
17. 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.
-
18. The method of claim 17, further comprising:
if the concurrent transaction rolls back, clearing the delete status flag.
-
19. The method of claim 17, further comprising:
if the concurrent transaction commits, skipping the particular row.
-
20. The method of claim 1, wherein the delete status flag comprises a “
- row delete”
status bit.
- row delete”
-
21. The method of claim 1, wherein the update status flag comprises a “
- row update”
status bit.
- row update”
-
22. 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.
-
23. 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.
-
24. The method of claim 1, wherein said specified scan criterion is based, at least in part, on a database query.
-
25. The method of claim 24, wherein said database query comprises a data manipulation language (DML) statement.
-
26. 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. - View Dependent Claims (27, 28, 29, 30)
(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.
-
-
28. 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.
-
29. 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.
-
30. 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.
Specification