Concurrent Queries and Updates in Summary Views and Their Indexes | SpringerLink
Skip to main content
Springer Nature Link
Log in

Concurrent Queries and Updates in Summary Views and Their Indexes

  • Chapter
On Transactional Concurrency Control

Part of the book series: Synthesis Lectures on Data Management ((SLDM))

  • 110 Accesses

Abstract

Materialized views have become a standard technique in decision support databases and for a variety of monitoring purposes.1 In order to avoid inconsistencies and thus unpredictable query results, materialized views and their indexes should be maintained immediately within user transactions just like ordinary tables and their indexes. Unfortunately, the smaller and thus the more effective 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 snapshot transactions, multi-version concurrency control, multi-granularity (hierarchical) locking, key range locking, and system transactions, i.e., with 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; it enables maximal concurrency of mixed read-write transactions with other transactions; it supports bulk operations such as data import and online index creation; it provides recovery for transaction, media, and system failures; and it can participate in coordinated commit processing, e.g., in two-phase commit.

This is a preview of subscription content, log in via an institution to check access.

Access this chapter

Log in via an institution

Subscribe and save

Springer+ Basic
¥17,985 /Month
  • Get 10 units per month
  • Download Article/Chapter or eBook
  • 1 Unit = 1 Article or 1 Chapter
  • Cancel anytime
Subscribe now

Buy Now

Chapter
JPY 3498
Price includes VAT (Japan)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
JPY 7435
Price includes VAT (Japan)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
JPY 9294
Price includes VAT (Japan)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Institutional subscriptions

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

Similar content being viewed by others

References

  • Arun, G. and Joshi, A. (1998). KODA—The architecture and interface of a data model independent kernel. VLDB Conference, pp. 671-674. 85

    Google Scholar 

  • Berenson, H., Bernstein, P. A., Gray, J., Melton, J., O’Neil, E. J., and O’Neil, P. E. (1995). A critique of ANSI SQL isolation levels. ACM SIGMOD Conference, pp. 1-10. 10.1145/223784.223785. 84

    Google Scholar 

  • Bernstein, P. A., Hadzilacos, V., and Goodman, N. (1987). Concurrency Control and Recovery in Database Systems, Addison-Wesley Longman Publishing Co., Inc., Boston, MA. 84

    Google Scholar 

  • Blakeley, J. A., Coburn, N., and Larson, P. (1986). Updating derived relations: Detecting irrelevant and autonomously computable updates. VLDB Conference, pp. 457-466. 10.1145/68012.68015. 80, 94

    Google Scholar 

  • Blakeley, J. A., Larson, P., and Tompa, F. W. (1986). Efficiently updating materialized views. ACM SIGMOD Conference, pp. 61-71. 10.1145/16856.16861. 94

    Google Scholar 

  • Blasgen, M. (2003). Personal communication. 90

    Google Scholar 

  • Bridge, W., Joshi, A., Keihl, M., Lahiri, T., Loaiza, J., MacNaughton, N. (1997). The oracle universal server buffer manager. VLDB Conference, pp. 590-594. 84

    Google Scholar 

  • Chang, J.-W., Lee, Y.-K., and Whang, K.-Y. (2002). Global lock escalation in database management systems. Information Processing Letters, 82(4), pp. 179-186. 10.1016/s0020-0190(01)00261-7. 86

    Article  MathSciNet  Google Scholar 

  • Chang, J.-W., Whang, K.-Y., Lee, Y.-K., Yang, J.-H., and Oh, Y.-C. (2005). A formal approach to lock escalation. Information Systems, 30(2), pp. 151-166. 10.1016/j.is.2003.10.009. 86

    Article  Google Scholar 

  • DeWitt, D. J., Katz, R. H., Olken, F., Shapiro, L. D., Stonebraker, M., and Wood, D. A. (1984). Implementation techniques for main memory database systems. ACM SIGMOD Conference, pp. 1-8. 10.1145/602260.602261. 89

    Google Scholar 

  • Gärtner, A., Kemper, A., Kossmann, D., and Zeller, B. (2001). Efficient bulk deletes in relational databases. IEEE ICDE, pp. 183-192. 10.1109/icde.2001.914827. 103

    Google Scholar 

  • Gawlick, D. (2003). Personal communication. 83

    Google Scholar 

  • Gawlick, D. and Kinkade, D. (1985). Varieties of concurrency control in IMS/VS fast path. IEEE Data Engineering Bulletin, 8(2), pp. 3-10. 82, 89, 101

    Google Scholar 

  • Gottemukkala, V. and Lehman, T. J. (1992). Locking and latching in a memory-resident database system. VLDB Conference, pp. 533-544. 86

    Google Scholar 

  • Graefe, G. (2003). Sorting and indexing with partitioned B-tree. Conference on Innovative Data Systems Research, Asilomar, CA, January. http://www-db.cs.wisc.edu/cidr 121

  • Gray, J. and Reuter, A. (1993). Transaction Processing: Concepts and Techniques, Morgan Kaufmann, San Mateo, CA. 85, 90, 111, 115

    MATH  Google Scholar 

  • Gupta, A. and Mumick, I. S. Eds. (1999). Materialized Views: Techniques, Implementations, and Applications, The MIT Press, Cambridge, MA. 10.7551/mitpress/4472.001.0001. 80

    Google Scholar 

  • Halici, U. and Dogac, A. (1991). An optimistic locking technique for concurrency control in distributed databases. IEEE Transactions on Software Engineering, 17(7), pp. 712-724. 10.1109/32.83907. 110, 111

    Article  Google Scholar 

  • Härder, T. and Reuter, A. (1983). Principles of transaction-oriented database recovery. ACM Computing Surveys, 15(4), pp. 287-317. 10.1145/289.291. 89

    Article  MathSciNet  Google Scholar 

  • Härder. T. (1984). Observations on optimistic concurrency control schemes. Information Systems, 9(2), pp. 111-120. 10.1016/0306-4379(84)90020-6. 112

    Article  Google Scholar 

  • Johnson, T. and Shasha, D. (1989). Utilization of B-trees with inserts, deletes and modifies. PODS Conference, pp. 235-246. 10.1145/73721.73745. 90

    Google Scholar 

  • Joshi, A. M. (1991). Adaptive locking strategies in a multi-node data sharing environment. VLDB Conference, pp. 181-191. 86

    Google Scholar 

  • Kawaguchi, A., Lieuwen, D. F., Mumick, I. S., Quass, D., and Ross, K. A. (1997). Concurrency control theory for deferred materialized views. ICDT, pp. 306-320. 10.1007/3-540-62222-5_53. 80

    Google Scholar 

  • Kim, S. H., Jung, M. S., Park, J. H. and Park, Y. C. (1999). A design and implementation of savepoints and partial rollbacks considering transaction isolation levels of SQL2. DASFAA Conference, pp. 303-312. 10.1109/dasfaa.1999.765764. 87

    Google Scholar 

  • Korth, H. F. (1983). Locking primitives in a database system. JACM, 30(1), pp. 55-79. 10.1145/322358.322363. 85, 112, 121

    Article  MathSciNet  Google Scholar 

  • Kung, H. T. and Robinson, J. T. (1979). On optimistic methods for concurrency control. VLDB Conference, p. 351. 10.1145/319566.319567. 110

    Google Scholar 

  • Kung, H. T. and Robinson, J. T. (1981). On optimistic methods for concurrency control. ACM TODS, 6(2), pp. 213-226. 10.1145/319566.319567. 110

    Article  Google Scholar 

  • Lausen, G. (1982). Concurrency control in database systems: A step towards the integration of optimistic methods andlocking. ACM Conference, pp. 64-68. 10.1145/800174.809759. 111

    Google Scholar 

  • Lehman, T. J. and Carey, M. J. (1989). A concurrency control algorithm for memory-resident database systems. FODO, pp. 490-504. 10.1007/3-540-51295-0_150. 86

    Google Scholar 

  • Liskov, B. and Scheifler, R. (1982). Guardians and actions: Linguistic support for robust, distributed programs. POPL Conference, pp. 7-19. 10.1145/582153.582155. 82

    Google Scholar 

  • Lomet, D. B. (1992). MLR: A recovery method for multi-level systems. ACM SIGMOD Conference, pp. 185-194. 10.1145/130283.130314. 81, 90

    Google Scholar 

  • Lomet, D. B. (1993). Key range locking strategies for improved concurrency. VLDB Conference, pp. 655-664. 117

    Google Scholar 

  • Luo, G., Naughton, J. F., Ellmann, C., and Watzke, M. (2003). Locking protocols for materialized aggregate join views. VLDB Conference, pp. 596-607. 10.1016/b978-012722442-8/50059-8. 80, 81, 122

    Google Scholar 

  • Luo, G., Naughton, J. F., Ellmann, C. J., and Watzke, M. (2005). Locking protocols for materialized aggregate join views. IEEE Transactions on Knowledge Data Engineering, 17(6), pp. 796-807. 10.1109/tkde.2005.96. 80, 81

    Article  Google Scholar 

  • Mohan, C. (1990). ARIES/KVL: A key-value locking method for concurrency control of mul-tiaction transactions operating on B-tree indexes. VLDB Conference, pp. 392-405. 90

    Google Scholar 

  • Mohan, C. (1990). Commit_LSN: A novel and simple method for reducing locking and latching in transaction processing systems. VLDB Conference, pp. 406-418. 85, 90

    Google Scholar 

  • Mohan, C. (1992). Less optimism about optimistic concurrency control. RIDE-TQP, pp. 199204. 10.1109/ride.1992.227405. 112

    Google Scholar 

  • Mohan, C., Haderle, D. J., Lindsay, B. G., Pirahesh, H., and Schwarz, P. M. (1992). ARIES: A transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging. ACM TODS, 17(1), pp. 94-162. 10.1145/128765.128770. 81, 82, 108

    Article  Google Scholar 

  • Mohan, C. and Narang, I. (1992). Algorithms for creating indexes for very large tables without quiescing updates. ACM SIGMOD Conference, pp. 361-370. 10.1145/141484.130337. 91, 119

    Google Scholar 

  • O’Neil, P. E. (1986). The escrow transactional method. ACMTODS, 11(4), pp. 405-430. 10.1145/7239.7265. 82, 100

    Google Scholar 

  • Palpanas, T., Sidle, R., Cochrane, R., and Pirahesh, H. (2003). Incremental maintenance for non-distributive aggregate functions. VLDB Conference. DOI: 10.1016/b978-155860869-6/50076-7.

    Google Scholar 

  • Quass, D. (1996). Maintenance expressions for views with aggregation. Workshop on Materialized Views: Techniques and Applications (VIEW), pp. 110-118, Montreal, Canada, June 7. 94

    Google Scholar 

  • Reuter, A. (1982). Concurrency on high-traffic data elements. ACM PODS Conference, pp. 8392. 10.1145/588111.588126. 83

    Google Scholar 

  • Samaras, G., Britton, K., Citron, A., and Mohan, C. (1993). Two-phase commit optimizations and tradeoffs in the commercial environment. IEEE ICDE, pp. 520-529. 10.1109/icde.1993.344028. 89, 109

    Google Scholar 

  • Shasha, D. (1985). What good are concurrent search structure algorithms for databases anyway? IEEE Database Engineering Bulletin, 8(2), pp. 84-90. 82

    Google Scholar 

  • Skeen, D. (1981). Nonblocking commit protocols. ACM SIGMOD Conference, pp. 133-142. 10.1145/582338.582339. 90

    Google Scholar 

  • Transaction Processing Performance Council. http://www.tpc.org/tpch 10.1007/978-3-319-77525-8_100350. 76

    Google Scholar 

  • Weikum, G., Hasse, C., Brössler, P., and Muth, P. (1990). Multi-level recovery. ACM PODS Conference, pp. 109-123. 10.1145/298514.298548. 81

    Google Scholar 

  • Weikum, G. and Schek, H.-J. (1984). Architectural issues of transaction management in multi-layered systems. VLDB Conference, pp. 454-465. 81

    Google Scholar 

Download references

Authors
  1. Goetz Graefe
    View author publications

    You can also search for this author in PubMed Google Scholar

Rights and permissions

Reprints and permissions

Copyright information

© 2019 Springer Nature Switzerland AG

About this chapter

Cite this chapter

Graefe, G. (2019). Concurrent Queries and Updates in Summary Views and Their Indexes. In: On Transactional Concurrency Control. Synthesis Lectures on Data Management. Springer, Cham. https://doi.org/10.1007/978-3-031-01873-2_4

Download citation

Publish with us

Policies and ethics