A Comprehensive Guide to InnoDB Locking

A Comprehensive (and Animated) Guide to InnoDB Locking | Jahfer's BlogRecently, 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 Locking behaves very differently under each of these, and I’ll touch on just the first two for now. To help explain these concepts, let’s make a table for tracking our book collection: CREATE TABLE `books` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `author_id` bigint(20) NOT NULL, `title` varchar(255) NOT NULL, `borrowed` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_books_on_author_id` (`author_id`) );

INSERT INTO `books` (`author_id`, `title`) VALUES (101, "The Pragmatic Programmer"), (102, "Clean Code"), (102, "The Clean Coder"), (104, "Ruby Under a Microscope"); idauthor_idtitleborrowed1101The Pragmatic ProgrammerFALSE2102Clean CodeFALSE3102The Clean CoderFALSE4104Ruby Under a MicroscopeFALSEInnoDB Lock Types Before diving too deeply into the details, we should start with an important semantic that applies to all of the locks we’ll be looking at in this article. InnoDB locks can either be “shared” or “exclusive”: A shared (S) lock permits the transaction that holds the lock to read a row. An exclusive (X) lock permits the transaction that holds the lock to update or delete a row. ~ 14.7.1 InnoDB Locking

Like their names suggest, a shared lock can be held by multiple concurrent transactions since they may only read the row. In order to perform a write, an exclusive lock must be obtained, and can only be held by one transaction at a time. An exclusive lock cannot be taken if some other transaction holds a shared lock on the record; this is how InnoDB can guarantee the results of a read performed under a shared lock won’t change while being read. Locks that are shared will only lock rows in the index used to perform the read. This is because the read needs to be reproducible, but only in the context of how the records were found. On the opposite end, exclusive locks are taken against the primary key of the record(s) which may have a larger impact since all secondary indexes are affected. Next-Key Locks This blog post was intended to be primarily about a single type of InnoDB lock: the next-key lock. In an effort to thwart my abbreviated commentary, a next-key lock is actually a combination of two other kinds of locks: a record lock and a gap lock. Record Locks Record locks are one of the simplest in InnoDB. It’s a lock taken on a specific row inside of an index. If we want to make sure no one updates our row while we’re inside of our transaction, we can acquire a shared read lock: -- open transaction BEGIN; -- perform read SELECT * FROM `books` -- obtain a shared (S) lock WHERE `id` = 2 LOCK IN SHARE MODE; -- ... FOR SHARE; in MySQL 8.0 If we take a look at active locks via performance_schema.metadata_locks, we’ll see that we have a SHARED_READ lock on the books table: mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS -> FROM performance_schema.metadata_locks -> WHERE OBJECT_NAME="books"; +-------------+-------------+---------------+-------------+ | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | +-------------+-------------+---------------+-------------+ | books | SHARED_READ | TRANSACTION | GRANTED | +-------------+-------------+---------------+-------------+ 1 row in set (0.00 sec) Multiple transactions are able to hold this lock, and they prevent any connection from updating the book where id = 2 as long as the lock is held. This lock will be released once the transaction ends, either via COMMIT or ROLLBACK. Now that we’ve seen how to acquire a lock for reading, let’s take a look at how we can acquire a lock for writing a record: -- open transaction BEGIN; -- perform read SELECT * FROM `books` -- obtain an exclusive (X) lock WHERE `id` = 2 FOR UPDATE; This query is slightly different in that we’ve acquired an exclusive lock for the row, meaning that anyone else who requests a lock for this row will have to wait for us to be done. This is because we’ve signaled to InnoDB that we’re going to change some of the values (via FOR UPDATE) so other updates would be operating on potentially-stale data. mysql> SELECT OBJECT_NAME, LOCK_TYPE, LOCK_DURATION,...

A Comprehensive Guide to InnoDB Locking

Related Articles

(no title)

Scientists reverse brain aging, with a nasal spray

NHS apologises and admits Palantir have access to identifiable patient data

AI has torched the market for junior programmers

Al Vigier: Canada's AI strategy shouldn't include secret Palantir bills