mysql - InnoDB's row locking the same as MVCC Non-Blocking Reads?

Question

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.

Answer 1

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 MODE to block updates to some rows while I'm reading. This is not usually necessary.

• I execute any SELECT while my transaction isolation level is SERIALIZABLE (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.