Transaction support for indexed summary views

ABSTRACT

Materialized views have become a standard technique for performance improvement in decision support databases and for a variety of monitoring purposes. In order to avoid inconsistencies and thus unpredictable query results, materialized views and their indexes should be maintained immediately within user transaction just like indexes on ordinary tables. Unfortunately, the smaller a materialized view is, the higher the concurrency contention between queries and updates as well as among concurrent updates. Therefore, we have investigated methods that reduce contention without forcing users to sacrifice serializability and thus predictable application semantics. These methods extend escrow locking with multi-granularity (hierarchical) locking, snapshot transactions, multi-version concurrency control, key range locking, and system transactions, i.e., multiple proven database implementation techniques. The complete design eliminates all contention between pure read transactions and pure update transactions as well as contention among pure update transactions as well as contention among pure update transactions; it enables maximal concurrency of mixed read-write transactions with other transactions; it supports bulk operations such as data import and online index creation; and it provides recovery for transaction, media, and system failures.

REFERENCES

• 1: {B 03} Michael Blasgen: Personal communication.
• 2: Hal Berenson , Phil Bernstein , Jim Gray , Jim Melton , Elizabeth O'Neil , Patrick O'Neil, A critique of ANSI SQL isolation levels, Proceedings of the 1995 ACM SIGMOD international conference on Management of data, p.1-10, May 22-25, 1995, San Jose, California, United States
• 3: José A. Blakeley , Neil Coburn , Per-Åke Larson, Updating Derived Relations: Detecting Irrelevant and Autonomously Computable Updates, Proceedings of the 12th International Conference on Very Large Data Bases, p.457-466, August 25-28, 1986
• 4: Philip A. Bernstein , Vassco Hadzilacos , Nathan Goodman, Concurrency control and recovery in database systems, Addison-Wesley Longman Publishing Co., Inc., Boston, MA, 1987
• 5: {G 03} Dieter Gawlick: Personal communication.
• 6: {GK 85} Dieter Gawlick, David Kinkade: Varieties of Concurrency Control in IMS/VS Fast Path. IEEE Data Eng. Bulletin 8(2):3--10 (1985).
• 7: {GM 99} Ashish Gupta, Inderpal Singh Mumick (eds): Materialized Views: Techniques, Implementations, and Applications. The MIT Press, Cambridge, MA, 1999.
• 8: Theo Haerder , Andreas Reuter, Principles of transaction-oriented database recovery, ACM Computing Surveys (CSUR), v.15 n.4, p.287-317, December 1983 [doi>10.1145/289.291]
• 9: T. Johnson , D. Shasha, Utilization of B-trees with inserts, deletes and modifies, Proceedings of the eighth ACM SIGACT-SIGMOD-SIGART symposium on Principles of database systems, p.235-246, March 1989, Philadelphia, Pennsylvania, United States [doi>10.1145/73721.73745]
• 10: Henry F. Korth, Locking Primitives in a Database System, Journal of the ACM (JACM), v.30 n.1, p.55-79, Jan. 1983 [doi>10.1145/322358.322363]
• 11: Akira Kawaguchi , Daniel F. Lieuwen , Inderpal Singh Mumick , Dallan Quass , Kenneth A. Ross, Concurrency Control Theory for Deferred Materialized Views, Proceedings of the 6th International Conference on Database Theory, p.306-320, January 08-10, 1997
• 12: David B. Lomet, MLR: a recovery method for multi-level systems, Proceedings of the 1992 ACM SIGMOD international conference on Management of data, p.185-194, June 02-05, 1992, San Diego, California, United States
• 13: David B. Lomet, Key Range Locking Strategies for Improved Concurrency, Proceedings of the 19th International Conference on Very Large Data Bases, p.655-664, August 24-27, 1993
• 14: Barbara Liskov , Robert Scheifler, Guardians and actions: linguistic support for robust, distributed programs, Proceedings of the 9th ACM SIGPLAN-SIGACT symposium on Principles of programming languages, p.7-19, January 25-27, 1982, Albuquerque, Mexico [doi>10.1145/582153.582155]
• 15: {LNE 03} Gang Luo, Jeffrey F. Naughton, Curt Ellmann, Michael Watzke: Locking Protocols for Materialized Aggregate Join Views. VLDB Conf. 2003: 596--607.
• 16: C. Mohan, Commit_LSN: A Novel and Simple Method for Reducing Locking and Latching in Transaction Processing Systems, Proceedings of the 16th International Conference on Very Large Data Bases, p.406-418, August 13-16, 1990
• 17: C. Mohan , Don Haderle , Bruce Lindsay , Hamid Pirahesh , Peter Schwarz, ARIES: a transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging, ACM Transactions on Database Systems (TODS), v.17 n.1, p.94-162, March 1992 [doi>10.1145/128765.128770]
• 18: C. Mohan , Inderpal Narang, Algorithms for creating indexes for very large tables without quiescing updates, Proceedings of the 1992 ACM SIGMOD international conference on Management of data, p.361-370, June 02-05, 1992, San Diego, California, United States
• 19: Patrick E. O'Neil, The Escrow transactional method, ACM Transactions on Database Systems (TODS), v.11 n.4, p.405-430, Dec. 1986 [doi>10.1145/7239.7265]
• 20: {PSC 02} Themistoklis Palpanas, Richard Sidle, Roberta Cochrane, Hamid Pirahesh: Incremental Maintenance for Non-Distributive Aggregate Functions. VLDB Conf. 2003.
• 21: {Q 96} Dallan Quass: Maintenance Expressions for Views with Aggregation. Workshop on Materialized Views: Techniques and Applications (VIEW 1996). Montreal, Canada, June 7, 1996: 110--118.
• 22: {S 85} Dennis Shasha: What Good are Concurrent Search Structure Algorithms for Database Anyway? IEEE Database Eng. Bull 8(2): 84--90 (1985).
• 23: {TPC} Transaction Processing Performance Council, http://www.tpc.org/tpch.
• 24: Gerhard Weikum , Hans-Jörg Schek, Architectural Issues of Transaction Management in Multi-Layered Systems, Proceedings of the 10th International Conference on Very Large Data Bases, p.454-465, August 27-31, 1984
• 25: Gerhard Weikum , Christof Hasse , Peter Broessler , Peter Muth, Multi-level recovery, Proceedings of the ninth ACM SIGACT-SIGMOD-SIGART symposium on Principles of database systems, p.109-123, April 02-04, 1990, Nashville, Tennessee, United States [doi>10.1145/298514.298548]