InnoDB的行锁定与MVCC非阻塞读取相同吗？
MVCC Non-Blocking Reads 是 InnoDB 行锁定的正式名称吗？我在 中遇到过这个词汇表InnoDB和NDB的比较表；我不确定它们是相同的东西还是完全不同的东西。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
MVCC 非阻塞读取在某种程度上是锁定的缺失。 MVCC 允许一个或多个读取器获得对数据的可重复读取访问，即使写入器正在更新相同的行。在这种情况下不需要锁定。
例如，如果我更改某些行，InnoDB 会立即创建该行旧版本的副本。读取该数据的并发事务可以继续读取副本。只要您的交易持续，旧版本就会保留在数据库中。
如果您启动一个新事务，您将看到该行的最近提交的版本，并且旧版本最终可以被垃圾收集，从而回收一些空间。
锁定适用于多个写入者尝试更新相同行的情况。一次只有一个写入者可以更新一行，第一个更新该行的写入者会锁定该行，直到他们提交更改。其他编写者必须等到第一个编写者提交。但至少对于行级锁定，它们只有在更新同一行时才会发生争用。
要了解有关 InnoDB 并发和锁定的更多信息，高性能 MySQL，第 3 版是一个很好的资源。
重新评论@AlexYakunin：
任意数量的并发线程都可以获取同一行上的共享锁。但是独占锁要求任何一种类型的锁都不存在——一次只有一个线程可以获取独占锁。
UPDATE 总是请求独占锁，这是更常见的情况。共享锁用于 InnoDB 中的一些更特殊的情况：
我更新具有父表外键的子行。我在子行上获得了 X 锁，在父行上获得了 S 锁。基本上，当我更新依赖于父表的行时，没有人可以更新父行。
我在阅读时明确使用 SELECT ... LOCK IN SHARE MODE 来阻止对某些行的更新。这通常不是必需的。
当我的事务隔离级别为
SERIALIZABLE（这并不常见）时，我执行任何SELECT。我发出一个导致重复键错误的插入，我的线程请求该行的共享锁。
请参阅http://dev.mysql.com/doc/ refman/5.6/en/innodb-locks-set.html 了解更多详细信息和示例。
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.
是：http://en.wikipedia.org/wiki/Multiversion_concurrency_control
Yes: http://en.wikipedia.org/wiki/Multiversion_concurrency_control