Method and system for reduced lock contention in SQL transactions

• US 20020091694A1
• Filed: 11/05/2001
• Published: 07/11/2002
• Est. Priority Date: 11/08/2000
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Teng, James Zu-Chia, Hrle, Namik, Watts, Julie A., Maier, Andreas R.
• Granted Patent: US 7,080,074 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2343 Locking methods, e.g. distributed locking or locking implementation details
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database

Abstract

A method, apparatus, and computer readable medium containing programming instructions for reducing lock contention of concurrent transactions is disclosed. According to the method, apparatus, and computer readable medium of the present invention, a scan is performed by accessing all rows of a table in a relational database, evaluating each row to determine whether the row satifies a set of predicates of a database query, and returning the row if it satisfies the set of predicates. According to a preferred embodiment of the present invention, all rows are evaluated regardless of current locks. The scan is continued if the row does not satisfy the set of predicates. A lock is requested if the row satisfies the set of predicates, and the scan is suspended, if the requested lock is refused. The request is repeated and the row is re-evaluated when the lock is permitted. If thereafter the row still satisfies the set of predicates, the row is returned. If the row no longer satisfies the set of predicates, the lock is released, the row is skipped, and the scan is continued.

The locking semantics according to the invention has the advantage that it completely removes the problem of lock contention on the rows that do not satisfy statement predicates.

Claims

1. A method for reducing lock contention of concurrent transactions on a plurality of rows of a table in a relational data base system in response to a database query having a set of predicates, the method comprising the steps of:

(a) scanning all rows of the table within an access range determined by the query;

(b) evaluating each scanned row to determine whether the row satisfies the set of predicates; and

(c) returning the row if it satisfies the set of predicates of the query.

2. The method of claim 1, wherein the scanning step (a) further comprising the step of:

(a1) accessing the rows of the table with uncommitted read semantics irrespective of current locks.

3. The method of claim 2, wherein the step of evaluating (b) further comprises the steps of:

(b1) evaluating each row to determine whether it satisfies the set of predicates of the query; and

(b2) continuing the scan if the row does not satisfy the set of predicates of the query.

4. The method of claim 3, wherein the returning step (c) further comprises the steps of:

(c1) requesting a lock on the row;

(c2) suspending the scan, if the requested lock is refused;

(c3) repeating the request for a lock and re-evaluating the row when the lock is permitted ; and

(c4) returning the row if the row still satisfies the set of predicates of the query.

6. The method of claim 1, wherein the returning step (c) further includes the step of :

(c1) returning the row as a result set.

7. The method of claim 1, wherein the returning step (c) further includes the step of:

(c1) returning the row if the row is a committed row.

8. The method of claim 1, wherein the database query is a SQL statement.

5. The method of claim 5, wherein the returning step (c) further comprises the step of:

(c5) releasing the lock, skipping the row, and continuing the scan if the row no longer satisfies the set of predicates of the query.

9. An apparatus for reducing lock contention of concurrent transactions on a plurality of rows of a table in a relational data base system in response to a database query having a set of predicates, comprising:

means for scanning all rows of the table within an access range determined by the query;

means for evaluating each scanned row to determine whether the row satisfies the set of predicates; and

means for returning the row if it satisfies the set of predicates of the query.

10. The apparatus of claim 9, wherein means for scanning further comprising:

means for accessing the rows of the table with uncommitted read semantics irrespective of current locks.

11. The apparatus of claim 10, wherein the means for evaluating further comprising:

means for evaluating each row to determine whether it satisfies the set of predicates of the query; and

means for continuing the scan if the row does not satisfy the set of predicates of the query.

12. The apparatus of claim 11, wherein the means for returning further comprising:

means for requesting a lock on the row;

means for suspending the scan, if the requested lock is refused;

means for repeating the request for a lock and re-evaluating the row when the lock is permitted; and

means for returning the row, if the row still satisfies the set of predicates of the query.

13. The apparatus of claim 12, wherein the means for returning further includes means for releasing the lock, skipping the row, and continuing the scan, if the row no longer satisfies the set of predicates of the query.

14. The apparatus of claim 9, wherein the row is returned as a result set.

15. The apparatus of claim 9, wherein the row returned is a committed row.

16. The apparatus of claim 9, wherein the database query is a SQL statement.

18. The computer readable medium of claim 17, wherein the scanning instruction (a) further comprising the instruction for:

(a1) accessing the rows of the table with uncommitted read semantics irrespective of current locks.

19. The computer readable medium of claim 18, wherein the instruction for evaluating (b) further comprises the instructions for:

(b

     1) evaluating each row to determine whether it satisfies the set of predicates of the query; and

(b2) continuing the scan if the row does not satisfy the set of predicates of the query.

20. The computer readable medium of claim 19, wherein the returning instruction (c) further comprises the instructions for:

(c1) requesting a lock on the row;

(c2) suspending the scan, if the requested lock is refused;

(c3) repeating the request for a lock and re-evaluating the row when the lock is permitted ; and

(c4) returning the row if the row still satisfies the set of predicates of the query.

21. The computer readable medium of claim 20, wherein the returning instruction (c) further comprises the instructions for:

(c5) releasing the lock, skipping the row, and continuing the scan if the row no longer satisfies the set of predicates of the query.

22. The computer readable medium of claim 17, wherein the returning instruction (c) further includes the instruction for:

(c1) returning the row as a result set.

23. The computer readable medium of claim 17, wherein the returning instruction (c) further includes the instruction for:

(c1) returning the row if the row is a committed row.

24. The computer readable medium of claim 17, wherein the database query is a SQL statement.

17. A computer readable medium containing programming instructions for reducing lock contention of concurrent transactions on a plurality of rows of a table in a relational data base system in response to a database query having a set of predicates, the programming instructions for:

(a) scanning all rows of the table within an access range determined by the query;

(b) evaluating each scanned row to determine whether the row satisfies the set of predicates; and

(c) returning the row if it satisfies the set of predicates of the query.