Transactions and Data Locks
Context
This is why you need to learn about MySQL transactions and data locks, which are closely related:
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.
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
orROLLBACK
- Four common problems beset transactions: large transactions (modify too many rows); long-running transactions (slow response time from
BEGIN
toCOMMIT
); 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
- Mining the MySQL Performance Schema for Transactions
- MySQL Transaction Reporting
- MySQL Data Locks: Mapping 8.0 to 5.7
Additional Resources
Resource | Type | About |
---|---|---|
InnoDB Locking and Transaction Model | MySQL manual | Foundational knowledge. Must read. |
MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs by Jesper Wisborg Krogh | Book | Locking 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 series | One of the most comprehensive, technical explanation of InnoDB data locking. Written by a MySQL server developer. |
Copyright 2025 Daniel Nichter