关于MySQL中的InnoDB引擎的MVCC机制的理解

目前在阅读《High Performance MySQL Second Edition》，读到Multiversion Concurrency Control章节时对InnoDB实现MVCC的解释总感觉有点简单，不容易理解(当然也许是自己比较笨看不懂)，所以自己总结了一点自己的理解。(查看翻译>>>)

我摘取了本章节中个人认为比较重要的一段：

<!--[endif]-->

SELECT

InnoDB must examine each row to ensure that it meets two criteria:

• InnoDB must find a version of the row that is at least as old as the transac-

tion (i.e., its version must be less than or equal to the transaction’s version).

This ensures that either the row existed before the transaction began, or the

transaction created or altered the row.

• The row’s deletion version must be undefined or greater than the transac-

tion’s version. This ensures that the row wasn’t deleted before the transac-

tion began.

Rows that pass both tests may be returned as the query’s result.

INSERT

InnoDB records the current system version number with the new row.

DELETE

InnoDB records the current system version number as the row’s deletion ID.

UPDATE

InnoDB writes a new copy of the row, using the system version number for the

new row’s version. It also writes the system version number as the old row’s

deletion version.

<!--[endif]-->
对于以上的内容我有一些理解是这样(主要讨论需要满足的第二点，红色标记)：检查发生在事务递交时。由于插入操作如果和更新删除发生在不同行是并发的，发生在同一行时当然不可能并发，所以不多做讨论。

由于更新操作是分为插入和删除两部分，插入又是插入新的行(任何时候都能并发的)，所以在这里可以将更新看作和删除一样。

用横轴表示时间的话我们得到这样的结果：

Update/Delete ------------------------------ 成功递交

Update/Delete --------------------------- 失败(因为deletion version已定义)

Update/Delete ------------------------------ 失败(因为deletion version已定义)

Update/Delete -------- 成功递交

deletion version应该是单独的一个版本号。

转自：http://www.cnblogs.com/isql/

生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠