Database system providing methodology for enhancing concurrency using row update bit and deferred locking

Claim 2

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 granting access during a table scan to particular rows that meet a specified scan criterion, said table scan occurring during execution of multiple concurrent transactions, 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 delete a 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; based on said status flags, determining those rows of the database table that may be skipped during the table scan; and granting access to particular rows of the database table that meet said specified scan criterion and have not been determined to be rows that may be skipped.

Claim 3

The method of claim 1 , wherein said granting step includes: if neither one of the status flags for a particular row have been set, granting access to the particular row if the particular row qualifies the specified scan criterion.

Claim 4

The method of claim 1 , wherein said granting 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 5

The method of claim 3 , wherein the particular row is skipped regardless of whether there exists another concurrently pending transaction in the system that might roll back.

Claim 6

The method of claim 1 , wherein said granting 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 7

The method of claim 1 , wherein said granting 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 8

The method of claim 6 , wherein the particular row is skipped regardless of whether the particular row is blocked by another concurrent transaction.

Claim 9

The method of claim 1 , wherein said granting step includes: if the update status flag for a particular row has been set, granting access to the particular row if the particular row qualifies the specified scan criterion.

Claim 10

The method of claim 1 , wherein said granting 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 11

The method of claim 1 , wherein said granting 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 12

The method of claim 1 , wherein said granting 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 13

The method of claim 11 , further comprising: if the concurrent transaction rolls back, clearing the delete status flag.

Claim 14

The method of claim 11 , further comprising: if the concurrent transaction commits, skipping the particular row.

Claim 15

The method of claim 1 , wherein said granting step includes: if the update 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 16

The method of claim 14 , further comprising: if the concurrent transaction rolls back, clearing the update status flag.

Claim 17

The method of claim 14 , further comprising: if the concurrent transaction commits, skipping the particular row.

Claim 18

The method of claim 1 , wherein said granting step includes: if both of the status flags for a particular row have been set but the particular row is blocked by another concurrent transaction, waiting for the concurrent transaction to commit or roll back.

Claim 19

The method of claim 17 , further comprising: if the concurrent transaction rolls back, clearing at least the delete status flag.

Claim 20

The method of claim 17 , further comprising: if the concurrent transaction commits, skipping the particular row.

Claim 21

The method of claim 1 , wherein the delete status flag comprises a “row delete” status bit.

Claim 22

The method of claim 1 , wherein the update status flag comprises a “row update” status bit.

Claim 23

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

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

The method of claim 1 , wherein said specified scan criterion is based, at least in part, on a database query.

Claim 26

The method of claim 24 , wherein said database query comprises a data manipulation language (DML) statement.

Claim 27

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, said database table having an index for logically ordering said plurality of rows, an improved method for granting access to particular rows that meet a specified query criterion during execution of multiple concurrent transactions, the method comprising: providing each index row of the index with a delete status flag for indicating that an index key of the index is marked for deletion; providing each row of the database table with delete and update status flags for indicating particular rows that 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; based on said index and row status flags, determining those rows of the database table that may be skipped as not meeting specified query criterion; and granting access to particular rows of the database table that meet said specified query criterion and have not been determined to be rows that may be skipped.

Claim 28

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, said database table having an index for logically ordering said plurality of rows, an improved method for granting access to particular rows that meet a specified query criterion during execution of multiple concurrent transactions, the method comprising: providing each index row of the index with a delete status flag for indicating that an index key of the index is marked for deletion; providing each row of the database table with delete and update status flags for indicating particular rows that 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; based on said index and row status flags, determining those rows of the database table that may be skipped as not meeting specified query criterion; and granting access to particular rows of the database table that meet said specified query criterion and have not been determined to be rows that may be skipped.

Claim 29

The method of claim 26 , wherein said granting step includes: if neither one of the status flags for a particular row have been set and the particular row qualifies the specified query criterion, waiting for access to the particular row if the particular row is blocked by another concurrent transaction.

Claim 30

The method of claim 26 , wherein said granting step includes: if neither one of the status flags for a particular row have been set and the particular row fails to qualify the specified query criterion, skipping the particular row regardless of whether the particular row is blocked by another concurrent transaction.

Claim 31

The method of claim 28 , wherein the particular row is skipped regardless of whether there exists another concurrently pending transaction in the system that might rolls back.

Claim 32

The method of claim 26 , wherein said granting step includes: if the delete status flag for a particular row has been set and the particular row qualifies the specified query criterion, skipping the particular row if the particular row is not blocked by another concurrent transaction.

Claim 33

The method of claim 26 , wherein said granting step includes: if the delete status flag for a particular row has been set and the particular row fails to qualify the specified query criterion, skipping the particular row regardless of whether another concurrent transaction is blocking the particular row.

Claim 34

The method of claim 31 , wherein the particular row is skipped regardless of whether there exists another concurrently pending transaction in the system that might roll back.

Claim 35

The method of claim 26 , wherein said granting step includes: if the update status flag for a particular row has been set, skipping the particular row if the particular row is not blocked by another concurrent transaction.

Claim 36

The method of claim 33 , wherein occurrence of said skipping step is without regard to whether the particular row qualifies the specified query criterion.

Claim 37

The method of claim 26 , wherein said granting step includes: if the update status flag has been set for a particular row that is blocked by another concurrent transaction, waiting for the concurrent transaction to commit or roll back.

Claim 38

The method of claim 35 , wherein the particular row is skipped if the concurrent transaction commits.

Claim 39

The method of claim 26 , wherein said granting 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 40

The method of claim 26 , wherein said granting step includes: if both of the status flags have been set for a particular row that is blocked by another concurrent transaction, waiting for the concurrent transaction to commit or roll back.

Claim 41

The method of claim 38 , wherein the particular row is skipped if the concurrent transaction commits.

Claim 42

The method of claim 39 , wherein occurrence of said skipping step is without regard to whether the particular row qualifies the specified query criterion.

Claim 43

A client/server 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 determining, based on said status flags 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 44

A client/server 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 determining, based on said status flags 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 45

The system of claim 41 , 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 46

The system of claim 41 , 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 47

The system of claim 41 , 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 48

The system of claim 41 , wherein said computer-implemented program logic for blocking access does not block a particular record when the delete status flag for the particular record has been set and the record is not blocked by another concurrent transaction.