Skip to main content

Avoiding Index-Navigation Deadlocks

Lock acquisition sequences in queries and updates

  • Chapter
On Transactional Concurrency Control

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

Abstract

Query execution plans in relational databases often search a secondary index and then fetch data from a table’s primary storage structure. In contrast, database updates usually modify first a table’s primary storage structure and then all affected secondary indexes. Deadlocks are likely if queries acquire locks first in the secondary index and then in the primary storage structure while updates acquire locks first in the primary storage structure and then in secondary indexes. In fact, some test teams use this scenario as a reliable means to create deadlocks in order to test deadlock resolution code. Some development teams have known of the problem for over 20 years but so far have failed to solve it, instead recommending that customers and application developers accept a weaker transaction isolation level and thus weaker correctness guarantees. A new technique uses the traditional, opposing navigation directions in queries and updates but the same locking sequence in both queries and updates. This design retains the efficiency of both queries and updates but avoids deadlocks caused by the traditional, opposing locking sequences.

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

Access this chapter

Chapter
USD 29.95
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
USD 54.99
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD 69.99
Price excludes VAT (USA)
  • 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.

References

  • Carey, M. J. and Muhanna, W. A. (1986). The performance of multi-version concurrency control algorithms. ACM TODS, 4(4), pages 338-378. DOI: 10.1145/6513.6517.

    Article  Google Scholar 

  • Chan, A., Fox, S., Lin, W.-T. K., Nori, A., and Ries, D. R. (1982). The implementation of an integrated concurrency control and recovery scheme. ACM SIGMOD Conference, pages 184191. DOI: 10.1145/582383.582386.

    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, pages 1-8. 10.1145/602260.602261. 314

    Google Scholar 

  • Graefe, G., Bunker, R., and Cooper, S. (1998). Hash joins and hash teams in Microsoft SQL server. VLDB Conference, pages 86-97. 313

    Google Scholar 

  • Graefe, G., Lillibridge, M., Kuno, H. A., Tucek, J., and Veitch, A. C. (2013). Controlled lock violation. ACM SIGMOD Conference, pages 85-96 (Chapter 4). 10.1145/2463676.2465325. 314

    Google Scholar 

  • Graefe, G. and Kimura, H. (2015). Orthogonal key-value locking. BTW Conference, pages 237256 (Chapter 5). 305, 309, 310

    Google Scholar 

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

    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, pages 392-405. 304, 307, 311, 314

    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), pages 94-162. DOI: 10.1145/128765.128770.

    Article  Google Scholar 

  • Mohan, C. and Levine, F. E. (1992). ARIES/IM: An efficient and high-concurrency index management method using write-ahead logging. ACM SIGMOD Conference, pages 371-380. 10.1145/130283.130338. 304, 307, 308, 311, 314

    Google Scholar 

Download references

Authors

Rights and permissions

Reprints and permissions

Copyright information

© 2019 Springer Nature Switzerland AG

About this chapter

Cite this chapter

Graefe, G. (2019). Avoiding Index-Navigation Deadlocks. In: On Transactional Concurrency Control. Synthesis Lectures on Data Management. Springer, Cham. https://doi.org/10.1007/978-3-031-01873-2_10

Download citation

Publish with us

Policies and ethics