NEIGHBORING LOCKING TECHNIQUE FOR INCREASING CONCURRENCY AMONG TRANSACTIONS

• US 20070233684A1
• Filed: 06/04/2007
• Published: 10/04/2007
• Est. Priority Date: 09/24/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Listgarten, Sherry, Haldar, Sibsankar, Verma, Sanjay, Hoang, Chi-Kim
• Granted Patent: US 7,680,794 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: Y10S 707/99938 Concurrency, e.g. lock management in shared database

Abstract

New lock modes are based on the concept of neighborhoods and are applied to spaces in indexes. The new lock modes include a Read, or shared, neighborhood (Snei) lock mode and a Write neighborhood (Xnei) that enhance concurrency among non-serializable transactions.

Claims

1. (canceled)

2. A computer-readable storage medium having stored thereon instructions that, if executed, provide a locking scheme comprising:

granting a first transaction an exclusive lock on a first content item and a weak lock on a neighborhood for the first content item; and

granting a second concurrent transaction an exclusive lock on a second content item located in the neighborhood.

4. The computer-readable storage medium of claim 2 wherein the neighborhood is free space adjacent to and associated with the first tuple item and extending to an adjacent tuple.

5. The computer-readable storage medium of claim 2 wherein the locking scheme determines an associated set of predetermined access restrictions for the first content item and determines an associated different set of predetermined access restrictions for the neighborhood associated with the first content item.

3. The computer-readable storage medium further having stored thereon instructions that, if executed, grant the second transaction an exclusive non-serilizable lock (Xn) on the second content item.

6. A database management system, comprising:

a processor configured to support a neighborhood locking scheme for a neighborhood of free space adjacent to and associated with a data item and extending to an adjacent data item, the neighborhood locking scheme concurrently creating both a first locking mode for the data item, while at the same time creating a second locking mode for the neighborhood associated with the data item; and

wherein the neighborhood locking scheme includes a neighborhood lock (Xnei) mode that enables a first transaction to lock the neighborhood for inserting a new tuple but prevents the first transaction from locking a tuple associated with the neighborhood.

7. A database management system according to claim 6 wherein the Xnei mode enables a second concurrent transaction to modify the tuple while preventing the second concurrent transaction from having exclusive rights on the neighborhood.

8. The database management system according to claim 6 wherein:

the first locking mode when first held on the data item determines an associated set of predetermined access restrictions for the data item and determines an associated different set of predetermined access restrictions for the neighborhood associated with the data item; and

the second locking mode when first held on the neighborhood determines the associated set of predetermined access restrictions for the neighborhood and determines the associated different set of predetermined access restrictions for the data item

9. A database management system, comprising:

a processor configured to provide a neighborhood locking scheme for a neighborhood of free space adjacent to and associated with a data item;

wherein the neighborhood locking scheme includes a non-serializable end of scan (Snei) lock mode that allows a first transaction to only read the neighborhood while preventing the first transaction from reading or writing a tuple associated with the neighborhood.

10. A database management system according to claim 9 wherein the Snei lock mode enables a second concurrent transaction to read and write the tuple and modify the data neighborhood.

11. A database management system according to claim 9 wherein:

the neighborhood locking scheme concurrently creates both a first locking mode for the data item, while at the same time creating a second locking mode for the neighborhood associated with the data item;

where the first locking mode when first held on the data item determines an associated set of predetermined access restrictions for the data item and determines an associated different set of predetermined access restrictions for the neighborhood associated with the data item;

where the second locking mode when first held on the neighborhood determines the associated set of predetermined access restrictions for the neighborhood and determines the associated different set of predetermined access restrictions for the data item.

12. A method for controlling access to data items in a database, comprising:

identifying a neighborhood of free space associated with a data item in the database;

granting a non-serializable shared lock for reading the data item with a first set of access privileges; and

granting a weak exclusive neighborhood lock on the data item with a second set of access privileges so that a first transaction can read the data item while a second transaction concurrently performs an insert operation into the neighborhood associated with the data item.

13. The method according to claim 12 including:

using a lock mode first held on the data item for determining the first set of access privileges for the data item and the second set of access privileges for the neighborhood; and

using the lock mode first held on the neighborhood for determining the first set of access privileges for the data item and the second set of access privileges for the neighborhood.

14. The method according to claim 13 including preventing a first transaction from locking the data item when a second transaction already holds a lock on the neighborhood.

15. The method according to claim 12 including:

providing the first set of access privileges to a first transaction accessing the data item and holding a lock mode on the data item corresponding with the first transaction; and

providing the second set of access privileges to a second transaction operating independently of the first transaction and accessing the neighborhood associated with the data item, the second set of access privileges determined by the lock mode already held on the data item by the first transaction;

16. An apparatus for controlling access to data items in a database, comprising:

a processor configured to identify a neighborhood of free space associated with a data item in the database and gaining access for modifying the neighborhood by asserting a neighborhood lock (Xnei) on the data item and then restricting to the data item according to a predetermined set of lock modes associated with the Xnei.

17. The apparatus according to claim 16 wherein the neighborhood lock (Xnei) enables a first transaction to lock the neighborhood for inserting a new tuple but prevents the first transaction from locking a tuple associated with the neighborhood.

18. The apparatus according to claim 16 wherein the processor is further configured to prevent a first transaction from locking the data item when a second independent transaction already holds a lock on the neighborhood.

19. The apparatus according to claim 16 wherein one of the neighborhood locking modes includes a non-serializable end of scan (Snei) lock mode that allows a first transaction to only read the neighborhood while preventing the first transaction from reading or writing a tuple associated with the neighborhood.

20. The apparatus according to claim 16 wherein the neighborhood corresponds to free space between tuples in a table.