Investigating Locks in MySQL

https://emmer.dev/blog/investigating-locks-in-mysql/

A crash course on locks

MySQL (and most relational databases) have a few different types of locks to limit concurrent access from different sessions, protecting schema and data integrity. In MySQL, specifically:

• Table locks on either base tables or views limit what sessions can read from or write to the table.

• Row locks on individual rows limit what sessions can read or update those rows. If a row has a read or a "shared" lock then no session can modify the row until the lock is released, but any session can read the row. If a row has a write or an "exclusive" lock then only the session holding the lock can modify the row.

  Standard SELECT ... FROM statements do not need to obtain row read locks unless the transaction isolation level is set to SERIALIZABLE. That means a row write lock won't prevent row reads from other sessions.

  If a statement that modifies rows (e.g. UPDATE or DELETE) has no suitable index, then InnoDB will obtain a write lock on every row in the table.

• Metadata locks on objects (schemas, tables, triggers, etc.) limit what sessions can alter the metadata of the database object.

This is not an exhaustive list, but it gives us enough information for the sections below.

https://emmer.dev/blog/investigating-locks-in-mysql/

A Comprehensive (and Animated) Guide to InnoDB Locking

1/1/2021

Recently, I had a chance to go deep on InnoDB’s locking mechanisms while attempting to debug some contention in MySQL that only appeared during high-throughput. This post is a culmination of my learnings on how InnoDB locking behaves under common scenarios.

Introduction

InnoDB only has a handful of locking concepts, but their use and behaviour depend greatly on the transaction isolation level that is active for the connection.

…the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time. ~ 14.7.2.1 Transaction Isolation Levels

There are four transaction isolation levels for InnoDB (in order of most-to-least strict):

• SERIALIZABLE
• REPEATABLE READ (default)
• READ COMMITTED
• READ UNCOMMITTED