關于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/

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈