目前在閱讀《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/