Concurrency control for transactions that update base tables of a materialized view using different types of locks

• US 6,353,828 B1
• Filed: 05/14/1999
• Issued: 03/05/2002
• Est. Priority Date: 05/14/1999
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Witkowski, Andrew, Ganesh, Amit
• Primary Examiner(s): Choules, Jack
• Assistant Examiner(s): CHANNAVAJJALA, SRIRAMA T
• Application Number:
  US09/311,273
• Time in Patent Office: 1,026 Days
• Field of Search: 707/1-3, 707/8, 707/9, 707/10, 707/100-104, 707/200-203, 711/148, 710/1, 710/200, 710/244, 710/240, 710/108, 709/213, 709/220
• 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: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

Concurrency control for maintenance of materialized view defined as a join on a plurality of base tables is provided by obtaining different types of locks. The base table being updated is locked with one type of lock, and the other base tables of the materialized view is locked with a different type of lock. These lock types are defined so that another process attempting to update another base table simultaneously is blocked until the update on the base table is committed. On the other hand, another process attempting to update the same base table is allowed to perform that update concurrently.

Claims

1. A method of maintaining a materialized view, defined by a join of a first table and a second table, during a transaction that performs a operation to modify the first table, said method comprising the computer-implemented steps of:

obtaining a first-type lock on the first table;

obtaining a second-type lock on the second table, wherein the first-type lock and the second-type lock are different types of locks;

after obtaining the first-type lock and obtaining the second-type lock, performing the operation to modify the first table; and

maintaining the materialized view based on results of performing the operation to modify the first table.

2. The method of claim 1, further comprising the step of releasing the first-type lock on the first table and the second-type lock on the second table after said maintaining the materialized view based on the results.

3. The method of claim 1, wherein:

the step of obtaining the first-type lock on the first table includes obtaining a first-type lock on the first table when a lock of the second-type is not currently granted on the first table; and

the step of obtaining the second-type lock on the second table includes obtaining a second-type lock on the second table only when a lock of the first-type is not currently granted on the second table.

4. The method of claim 3, wherein:

the step of includes obtaining a first-type lock on the first table when a lock of the second-type is not currently granted on the first table includes waiting for the second-type on the first table to be released; and

the step of includes obtaining a second-type lock on the first table when a lock of the first-type is not currently granted on the first table includes waiting for the first-type on the first table to be released.

5. The method of claim 1, wherein the step of obtaining the first-type lock on the first table includes obtaining permission to modify rows of the first table.

6. The method of claim 5, wherein the step of obtaining the second-type lock on the first table includes denying permission to modify rows of the second table during another concurrent transaction.

7. The method of claim 1, wherein the first-type lock is a row exclusive table lock and the second-type lock is a share table lock.

8. The method of claim 1, wherein the first-type lock is a write lock on a subset of the first table and the second-type lock is a read lock on all of the second table.

9. The method of claim 1, wherein the first table is a fact table and the second table is a dimension table.

19. The computer-readable medium of claim 3, wherein:

the step of includes obtaining a first-type lock on the first table when a lock of the second-type is not currently granted on the first table includes waiting for the second-type on the first table to be released; and

the step of includes obtaining a second-type lock on the first table when a lock of the first-type is not currently granted on the first table includes waiting for the first-type on the first table to be released.

10. A method of maintaining a materialized view, defined by a join of a plurality of tables, said method comprising the computer-implemented steps of:

if a first transaction and a second transaction modify different tables of the plurality of tables and incrementally maintain the materialized view, then serially executing the first transaction and the second transaction; and

if the first transaction and the second transaction modify a same table of the plurality of tables and incrementally maintain the materialized view, then concurrently executing the first transaction and the second transaction.

11. A computer-readable medium bearing instructions for maintaining a materialized view, defined by a join of a first table and a second table, during a transaction that performs a operation to modify the first table, said instructions arranged, when executed by one or more processors, to cause the one or more processors to perform the steps of:

obtaining a first-type lock on the first table;

obtaining a second-type lock on the second table, wherein the first-type lock and the second-type lock are different types of locks;

after said obtaining the first-type lock and said obtaining the second-type lock, performing the operation to modify the first table; and

maintaining the materialized view based on results of said performing the operation to modify the first table.

12. The computer-readable medium of claim 11, wherein said instructions are further arranged to cause the one or more processors to perform the step of releasing the first-type lock on the first table and the second-type lock on the second table after said maintaining the materialized view based on the results.

13. The computer-readable medium of claim 11, wherein:

the step of obtaining the first-type lock on the first table includes obtaining a first-type lock on the first table when a lock of the second-type is not currently granted on the first table; and

the step of obtaining the second-type lock on the second table includes obtaining a second-type lock on the second table when a lock of the first-type is not currently granted on the second table.

14. The computer-readable medium of claim 11, wherein the step of obtaining the first-type lock on the first table includes obtaining permission to modify rows of the first table.

15. The computer-readable medium of claim 14, wherein the step of obtaining the second-type lock on the first table includes denying permission to modify rows of the second table during another concurrent transaction.

16. The computer-readable medium of claim 11, wherein the first-type lock is a row exclusive table lock and the second-type lock is a share table lock.

17. The computer-readable medium of claim 11, wherein the first-type lock is a write lock on a subset of the first table and the second-type lock is a read lock on all of the second table.

18. The computer-readable medium of claim 11, wherein the first table is a fact table and the second table is a dimension table.

20. A computer-readable medium bearing instructions for maintaining a materialized view, defined by a join of a plurality of tables, said instructions arranged, when executed by one or more processors, to cause the one or more processors to perform the steps of:

if a first transaction and a second transaction modify different tables of the plurality of tables and incrementally maintain the materialized view, then serially executing the first transaction and the second transaction; and

if the first transaction and the second transaction modify a same table of the plurality of tables and incrementally maintain the materialized view, then concurrently executing the first transaction and the second transaction.