Learn MySQL Performance

1. Query Response Time
2. Indexes and Indexing
3. Data and Access Patterns
4. Sharding
5. Server Metrics and InnoDB
6. Replication
7. Transactions and Data Locks
8. Common Challenges
9. Cloud Performance
Efficient MySQL Performance bird

Efficient MySQL Performance
20 years of MySQL performance, written for software engineers

O'Reilly ⟫
Amazon  ⟫
Goodreads ⟫

Transactions and Data Locks

Context

This is why you need to learn about MySQL transactions and data locks, which are closely related:

MySQL has non-transactional storage engines, but InnoDB is the default and the presumptive norm. Therefore, practically speaking, every MySQL query executes in a transaction by default, even a single SELECT statement.

From our point of view as programmers, transactions appear conceptual: BEGIN, execute queries, and COMMIT. Then we trust MySQL (and InnoDB) to uphold the ACID properties: atomicity, consistency, isolation, and durability. When the application workload—queries, indexes, data, and access patterns—is well optimized, transactions are a nonissue with respect to performance. (Most database topics are a nonissue when the workload is well optimized.) But behind the scenes, transactions invoke a whole new world of considerations because upholding ACID properties while maintaining performance is not an easy feat. Fortunately, MySQL shines at executing transactions.

As with replication lag, the inner workings of transactions are beyond the scope of this book, but understanding a few basic concepts is pivotal to avoiding common problems that hoist transactions from the lowest levels of MySQL to the tops of programmers’ minds. A little understanding avoids a lot of problems.

Excerpt from Efficient MySQL Performance, Chapter 8
Copyright 2021 Daniel Nichter
No reproduction of this excerpt without permission

Key Points

  • Transaction isolation levels affect row locking (data locks)
  • The fundamental InnoDB data locks are: record lock, next-key lock, gap lock, and insert intention lock
  • Record lock: lock a single index record
  • Next-key lock: lock a single index record plus the record gap before it
  • Gap lock: lock the range (gap) between two records
  • Insert intention lock: allow INSERT into a gap; more like a wait condition than a lock
  • The default transaction isolation level, REPEATABLE READ, uses gap locking to isolate the range of rows accessed
  • The READ COMMITTED transaction isolation level disables gap locking
  • InnoDB uses consistent snapshots in REPEATABLE READ transactions to make reads (SELECT) return the same rows despite changes to those rows by other transactions
  • Consistent snapshots require InnoDB to save row changes in undo logs to reconstruct old row versions
  • History list length (HLL) gauges the amount of old row versions not purged or flushed
  • History list length is a harbinger of doom: always monitor and alert on HLL greater than 100,000
  • Data locks and undo logs are released when a transaction ends: COMMIT or ROLLBACK
  • Four common problems beset transactions: large transactions (modify too many rows); long-running transactions (slow response time from BEGIN to COMMIT); stalled transactions (superfluous waits between queries); abandoned transactions (client connection vanished during active transaction)
  • The MySQL Performance Schema makes detailed transaction reporting possible

Pitfalls

  • Not having a basic understanding of InnoDB row/data locks
  • Using FOR UPDATE without knowing exactly why and what locks it takes
  • Abandoned transactions
  • Slow (to commit) transactions
  • Doing application working during an open transactions
  • InnoDB locking the supremum pseudo record

Hack MySQL Articles

Additional Resources

ResourceTypeAbout
InnoDB Locking and Transaction ModelMySQL manualFoundational knowledge. Must read.
MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs by Jesper Wisborg KroghBookLocking and transaction are such deep topics they could fill a book, and they do.
• InnoDB Data Locking - Part 1 “Introduction”
• InnoDB Data Locking - Part 2 “Locks”
• InnoDB Data Locking - Part 2.5 “Locks” (Deeper Dive)
• InnoDB Data Locking - Part 3 “Deadlocks”
• InnoDB Data Locking - Part 4 “Scheduling”
• InnoDB Data Locking - Part 5 “Concurrent Queues”
by Jakub Łopuszański @ Oracle		Article seriesOne of the most comprehensive, technical explanation of InnoDB data locking. Written by a MySQL server developer.
Source: https://hackmysql.com/learn/transactions-and-data-locks/
Copyright 2025 Daniel Nichter