Locking mechanism for materialized views in a database system

• US 7,321,898 B1
• Filed: 05/13/2004
• Issued: 01/22/2008
• Est. Priority Date: 04/04/2002
• Status: Expired due to Term

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation
• Inventors: Luo, Gang, Ellmann, Curt J., Watzke, Michael W., Naughton, Jeffrey F.
• Primary Examiner(s): LOVEL, KIMBERLY M
• Application Number:
  US10/844,680
• Time in Patent Office: 1,349 Days
• Field of Search: 707 1- 10, 707/100.S, 707/102
• 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/2393 Updating materialised views
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A database system receives a transaction that causes a tuple to be integrated into a join view. In response to receiving the transaction, a predefined first lock is placed on at least a portion of the join view, the predefined first lock conflicting with either a shared lock or an exclusive lock placed on the join view, but not conflicting with another predefined first lock placed on the join view. Also, a latch or semaphore associated with a value of a predetermined attribute of the tuple is obtained before integrating the tuple into the join view.

Claims

1. A method for use in a database system, comprising:

receiving a transaction that causes a tuple to be integrated into a join view;

in response to receiving the transaction, placing a predefined first lock on at least a portion of the join view, the predefined first lock conflicting with either a shared lock or an exclusive lock placed on the join view, but not conflicting with another predefined first lock placed on the join view; and

obtaining a latch associated with a value of a predetermined attribute of the tuple before integrating the tuple into the join view.

2. The method of claim 1, further comprising releasing the latch associated with the predetermined attribute value of the tuple after integrating the tuple into the join view.

3. The method of claim 1, further comprising storing a pool of latches,

wherein obtaining the latch associated with the predetermined attribute value of the tuple comprises obtaining one of the latches in the pool.

4. The method of claim 3, wherein storing the pool comprises storing the pool in volatile memory.

5. The method of claim 3, further comprising applying a hash function on the predetermined attribute value of the tuple to derive a pointer to a location in the pool corresponding to the obtained latch.

6. The method of claim 3, further comprising:

during a period that the transaction holds the latch associated with a value of the predetermined attribute of the tuple, the database system not requesting another latch from the pool.

7. The method of claim 3, further comprising:

prior to obtaining the latch associated with a value of the predetermined attribute of the tuple, the database system releasing all other latches held by the transaction.

8. The method of claim 3, further comprising:

during a period that the transaction holds the latch associated with a value of the predetermined attribute of the tuple, the database system not requesting another lock for the transaction.

9. The method of claim 1, wherein receiving the transaction comprises receiving a transaction to cause the tuple to be integrated into an aggregate join view,

wherein placing the predefined first lock comprises placing the predefined first lock on at least a portion of the aggregate join view, and

wherein obtaining the latch associated with the predetermined attribute value of the tuple comprises obtaining the latch associated with a group-by attribute value of the tuple.

10. The method of claim 1, wherein the join view is a materialized join view containing the results of a join of plural base tables, and

wherein the predefined first lock is placed in at least the portion of the materialized join view that contains results of the join of the plural base tables.

11. An article comprising at least one storage medium containing instructions that when executed cause a database system to:

receive a transaction that causes a tuple to be integrated into a join view;

in response to receiving the transaction, place a predefined first lock on at least a portion of the join view, the predefined first lock conflicting with either a shared lock or an exclusive lock placed on the join view, but not conflicting with another predefined first lock placed on the join view; and

obtain a latch associated with a value of a predetermined attribute of the tuple before integrating the tuple into the join view.

12. The article of claim 11, wherein the instructions when executed cause the database system to further release the latch associated with the predetermined attribute value of the tuple after integrating the tuple into the aggregate join view.

13. The article of claim 11, wherein the instructions when executed cause the database system to further store a pool of latches,

wherein obtaining the latch associated with the predetermined attribute value of the tuple comprises obtaining one of the latches in the pool.

14. The article of claim 13, wherein the instructions when executed cause the database system to further apply a hash function on the predetermined attribute value of the tuple to derive a pointer to a location in the pool corresponding to the obtained latch.

15. The article of claim 13, wherein the instructions when executed cause the database system to further:

during a period that the transaction holds the latch associated with a value of the predetermined attribute of the tuple, not request another latch from the pool.

16. The article of claim 13, wherein the instructions when executed cause the database system to further:

prior to obtaining the latch associated with a value of the predetermined attribute of the tuple, release all other latches held by the transaction.

17. The article of claim 13, wherein the instructions when executed cause the database system to further:

during a period that the transaction holds the latch associated with a value of the predetermined attribute of the tuple, not request another lock for the transaction.

18. The article of claim 11, wherein receiving the transaction comprises receiving a transaction to cause the tuple to be integrated into an aggregate join view,

wherein placing the predefined first lock comprises placing the predefined first lock on at least a portion of the aggregate join view, and

wherein obtaining the latch associated with the predetermined attribute value of the tuple comprises obtaining the latch associated with a group-by attribute value of the tuple.

19. The article of claim 11, wherein the database system comprises plural storage modules and plural data server modules to manage access of corresponding plural storage modules,

wherein the instructions when executed cause the database system to further store the join view by distributing the join view across the plural storage modules.

20. The article of claim 19, wherein placing the predefined first lock and obtaining the latch is performed by a first one of the data server modules, wherein the instructions when executed cause the database system to further:

cause a second one of the data server modules to, in response to a second transaction to integrate a second tuple into the join view, place another predefined first lock on at least a portion of the join view; and

cause the second data server module to obtain another latch associated with another value of the predetermined attribute of the second tuple before integrating the second tuple into the join view.

21. A database system, comprising:

a storage to store an aggregate join view; and

a controller to;

receive a transaction that causes a tuple to be integrated into the aggregate join view;

in response to receiving the transaction, place a predefined first lock on at least a portion of the aggregate join view, the predefined first lock conflicting with either a shared lock or an exclusive lock placed on the aggregate join view, but not conflicting with another predefined first lock placed on the aggregate join view; and

obtain a latch associated with a value of a group-by attribute of the tuple before integrating the tuple into the aggregate join view.

22. The database system of claim 21, further comprising a volatile memory to store a pool of latches, wherein the latch associated with the value of the group-by attribute of the tuple is part of the pool.

23. The database system of claim 21, wherein the storage comprises plural storage modules, and wherein the controller comprises plural data server modules to manage access of corresponding plural storage modules,

the data server modules to store the aggregate join view in the storage modules by distributing the aggregate join view across the plural storage modules.

24. The database system of claim 23, a first one of the data server modules to place the predefined first lock and obtain the latch, and a second one of the data server modules to, in response to a second transaction to integrate a second tuple into the join view:

place another predefined first lock on at least a portion of the join view; and

obtain another latch associated with another value of the predetermined attribute of the second tuple before integrating the second tuple into the join view.