问题描述
MVCC是否为非锁定读取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.
推荐答案
MVCC非阻塞读取在某种程度上是不存在的锁定. MVCC使一个或多个读取器即使在写入器更新同一行时也能获得对数据的可重复读取访问.在这种情况下，无需锁定.
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.
例如，如果我更改某些行，InnoDB会立即创建该行的旧版本的副本.您的并发事务读取该数据可以继续读取副本.只要您的交易持续进行，该旧版本就会保留在数据库中.
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.
@AlexYakunin的评论:
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总是请求排他锁，这是更常见的情况.共享锁用于InnoDB中一些更特殊的情况:
UPDATE always requests an exclusive lock, and this is the more common case. Shared locks are used for some more exotic cases in InnoDB:
-
我更新了具有父表外键的子行.我在子行上得到了X锁，在父行上得到了S锁.基本上，当我更新依赖于该父行的行时，没有人可以更新父行.
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.
我在阅读时明确使用SELECT ... LOCK IN SHARE MODE阻止对某些行的更新.通常这不是必需的.
I explicitly use SELECT ... LOCK IN SHARE MODE to block updates to some rows while I'm reading. This is not usually necessary.
我的事务隔离级别为SERIALIZABLE时执行任何SELECT(这是不常见的).
I execute any SELECT while my transaction isolation level is SERIALIZABLE (this is not common).
我发出的INSERT导致重复键错误，我的线程请求该行的共享锁.
I issue an INSERT that results in a duplicate-key error, my thread requests a shared lock on the row.
请参见 http://dev.mysql.com /doc/refman/5.6/en/innodb-locks-set.html 了解更多详细信息和示例.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html for more details and examples.
这篇关于InnoDB的行锁定与MVCC非阻塞读取相同吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！