MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

© Jesper Wisborg Krogh 2021

J. W. KroghMySQL Concurrencyhttps://doi.org/10.1007/978-1-4842-6652-6_10

10. Indexes and Foreign Keys

Jesper Wisborg Krogh¹  

(1)

Hornsby, NSW, Australia

 

In the previous chapter, you learned how indexes and foreign keys can influence locking. This is a topic that is worth diving further into as it is important to understand the effects.

The first part of this chapter investigates how primary, secondary, ascending, descending, and unique indexes affect locking. The second part covers foreign keys and how they affect locking for DML and DDL statements.

Indexes

In short indexes provide a shortcut to access a given record, so that the number of records examined is reduced. This has a positive effect on the number of locks as only ...