Is MVCC Non-Blocking Reads the formal name for InnoDB's row locking? I've come across this vocabulary in a comparison table for InnoDB and NDB; I'm not sure whether they're the same thing or something completely different.
Asked
Active
Viewed 2,914 times
2 Answers
5
MVCC Non-Blocking Reads is, in a way, the absence of locking. MVCC enables one or more readers to get repeatable-read access to data even while writers are updating the same rows. No locking is required in this case.
For example, if I change some row, InnoDB immediately creates a copy of the old version of that row. Your concurrent transaction reading that data can continue reading the copy. That old version is preserved in the database as long as your transaction lasts.
If you start a new transaction, you'll see the most recently committed version of the row, and the old version can eventually be garbage-collected, which reclaims some space.
Locking is for when multiple writers are trying to update the same rows. Only one writer can update a row at a time, and the first one to update the row locks it until they commit the change. Other writers have to wait until the first writer commits. But at least with row-level locking, they only have contention if they're updating the same row.
A good resource for learning more about InnoDB concurrency and locking is High Performance MySQL, 3rd ed.
Re comment from @AlexYakunin:
Any number of concurrent threads can acquire a shared lock on the same row. But an exclusive lock requires that no locks of either type exist -- only one thread at a time can acquire an exclusive lock.
UPDATE always requests an exclusive lock, and this is the more common case. Shared locks are used for some more exotic cases in InnoDB:
I update a child row that has a foreign key to a parent table. I get an X lock on the child row, and an S lock on the parent row. Basically, no one can update the parent row while I'm updating a row that depends on that parent.
I explicitly use
SELECT ... LOCK IN SHARE MODEto block updates to some rows while I'm reading. This is not usually necessary.I execute any
SELECTwhile my transaction isolation level isSERIALIZABLE(this is not common).I issue an INSERT that results in a duplicate-key error, my thread requests a shared lock on the row.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html for more details and examples.
Bill Karwin
- 538,548
- 86
- 673
- 828
-
So what's the purpose of shared locks in InnoDB? Is this a correct part of documentation: "If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r." - this implies that writers block readers. – Alex Yakunin Jul 11 '13 at 19:47
-
The page I'm referring to: http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html – Alex Yakunin Jul 11 '13 at 19:49
-
Thanks for the clarification - I see the point now. Shared locks are actually applied explicitly, or on SERIALIZABLE, or in cases they're necessary to properly provide isolation for DML statements updating rows w/FKs. So thus writers almost never block readers. – Alex Yakunin Jul 11 '13 at 20:10
-
Deleting my answer and upvoting yours. – Alex Yakunin Jul 11 '13 at 20:11
-
Yep, and readers mostly don't block writers (unless the read creates a shared lock). Not locking in as many cases as possible is the real benefit of MVCC. – Bill Karwin Jul 11 '13 at 21:10
