New Book: MySQL Concurrency

In March 2020, my book MySQL 8 Query Performance Tuning (Apress) was released. That book with its more than 900 pages in 27 chapters covered a wide range of topics from executing benchmarks with sysbench over indexes and the optimizer to query rewriting, replication, and caching. Some time later my editor Jonathan Gennick suggested to take a limited set of the topics and go into more detail. We settled on locks and transactions which together have become MySQL Concurrency.

If you have already read MySQL 8 Query Performance Tuning, you may wonder what the new book offers compared to the previous one. In some way, you can think of MySQL Concurrency to be a second and expanded edition for the content on locks and transactions. Of course, given MySQL Concurrency is limited to those two topics, it has also been possible to organise the content in a way more logical for covering just those two topics. Examples of the additional information in the new book are:

• Synchronisation waits, mutexes, and semaphores – particularly for InnoDB.
• The Contention Aware Transaction Scheduling (CATS) algorithm.
• More about configuration options affecting locking.
• How indexes affect locking.
• How foreign keys affect locking.
• Transactions and ACID.
• The group commit feature.
• The four transaction isolation levels supported by InnoDB and how they affect locking.
• Two extra case studies: one for foreign keys and one for semaphores.
• A comprehensive module for MySQL Shell for reproducing the book's lock examples and to create your own test cases. I will write a blog with more about this soon.

If you are interested, you can read more about the content and/or buy it at Apress, Amazon, and others book shops:

Shop	Paper Book	E-Book
Apress	Softcover	DRM free ePub/PDF
Amazon US	Softcover	Mobi (Kindle)

Acknowledgements

I would first of all like to say thank you to all of those from the Apress team that have made this book possible. In particular, I would like to shout out Jonathan Gennick who came up with the idea, Jill Balzano who coordinated the work, Laura Berendson for her work behind the scenes, and Creapzylene Roma for catching my linguistic slipups.

The knowledge shared in MySQL Concurrency has not materialised out of nothing. Thanks to Charles Bell for providing – as always – a thorough review with constructive feedback and suggestions for improvements. Jakub Lopuszanski has been helpful with details on InnoDB locking. I would also like to thank all my colleagues over the years as they have all been part of my journey learning how MySQL works through mentorship, teaching me, asking me good questions, and general discussions. A special thank you to Edwin Desouza and Frédéric Descamps (better known as Lefred) for their assistance.

Last but not least, thanks to my wife Ann-Margrete for her patience and support while I wrote this book. Without you, it would not have been possible to write this book.

Book Structure

The book is divided into 18 chapters and two appendixes. The chapters can be viewed as covering five parts (these are informal parts):

• Introduction:
  • Chapter 1: Introduction
• Monitoring:
  • Chapter 2: Monitoring Locks and Mutexes
  • Chapter 3: Monitoring InnoDB Transactions
  • Chapter 4: Transactions in the Performance Schema
• Locks:
  • Chapter 5: Lock Access Levels
  • Chapter 6: High-Level Lock Types
  • Chapter 7: InnoDB Locks
  • Chapter 8: Working with Lock Conflicts
  • Chapter 9: Reducing Locking Issues
  • Chapter 10: Indexes and Foreign Keys
• Transactions:
  • Chapter 11: Transactions
  • Chapter 12: Transaction Isolation Levels
• Case Studies:
  • Chapter 13: Case Study: Flush Locks
  • Chapter 14: Case Study: Metadata and Schema Locks
  • Chapter 15: Case Study: Record-Level Locks
  • Chapter 16: Case Study: Deadlocks
  • Chapter 17: Case Study: Foreign Keys
  • Chapter 18: Case Study: Semaphores

MySQL Shell Module for Reproducing Test Cases

One of the challenges when working with locks is to reproduce an issue whether it is a real world issue or one described in the book. This is also something I noticed in connection with MySQL 8 Query Performance Tuning. So, for MySQL Concurrency I decided to take advantage of MySQL Shell's support for external Python modules and code a module that allows you to recreate the examples in the book simply by invoking a method of the module. The source code for the module can be downloaded from the book's GitHub repository; installation and usage instructions can be found in Chapter 1 and Appendix B of the book. I plan to discuss this module in more detail in a later blog post.

I hope you will enjoy the book.