《高效能MySQL》讀書筆記－－多版本並發控制演算法，高效能mysql

《高效能MySQL》讀書筆記－－多版本並發控制演算法，高效能mysql
1.AUTOCOMMIT

Mysql採用預設自動認可，可以通過如下命令查看和修改：

mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT = 1;

2.隱式鎖顯式鎖

InnoDB在開啟事務時，擷取隱式鎖，在事務提交或者復原時釋放鎖，InnoDB根據隔離等級在需要的時候自動加鎖。

但InnoDB也支援顯式鎖：

SELECT ... FOR UPDATE

SELECT ... LOCK IN SHARE MODE

這是在伺服器層實現的，和儲存引擎無關。本書建議除了禁用了AUTOCOMMIT，可以使用LOCK_TABLES之外，其他任何時候都不要顯示地執行LOCK TABLES，不管使用的是什麼儲存引擎。

3.多版本並發控制（Multiversion Concurrency Controll MVCC）

第一點：

MVCC並不是MySql專屬的，Oracle，PostgreSQL等都在使用。

MVCC並沒有簡單地使用行鎖，而是使用“行層級鎖”（row-level locking）。

MVCC的基本原理是：

MVCC的實現，通過儲存資料在某個時間點的快照來實現的。這意味著一個事務無論運行多長時間，在同一個事務裡能夠看到資料一致的視圖。根據事務開始的時間不同，同時也意味著在同一個時刻不同事務看到的相同表裡的資料可能是不同的。

MVCC的基本特徵：

• 每行資料都存在一個版本，每次資料更新時都更新該版本。
• 修改時Copy出目前的版本隨意修改，各個事務之間無幹擾。
• 儲存時比較版本號碼，如果成功（commit），則覆蓋原記錄；失敗則放棄copy（rollback）

InnoDB儲存引擎MVCC的實現策略：

在每一行資料中額外儲存兩個隱藏的列：當前行建立時的版本號碼和刪除時的版本號碼（可能為空白）。這裡的版本號碼並不是實際的時間值，而是系統版本號碼。每開始 個新的事務，系統版本號碼都會自動遞增。事務開始時刻的系統版本號碼會作為事務的版本號碼，用來和查詢每行記錄的版本號碼進行比較。

每個事務又有自己的版本號碼，這樣事務內執行CRUD操作時，就通過版本號碼的比較來達到資料版本控制的目的。具體做法見下面的。

　　

 MVCC具體的操作如下：

SELECT:InnoDB會根據以下兩個條件檢查每行記錄：

1）InnoDB只尋找版本早於當前事務版本的資料行（也就是，行的系統版本號碼小於或等於事務的系統版本號碼），這樣可以確保事務讀取的行，只麼是在事務開始前已經存在的，要麼是事務自身插入或者修改過的。

2）行的刪除版本要麼未定義，要麼大於當前事務版本號碼。這可以確保事務讀取到的行，在事務開始之前未被刪除。

INSERT:InnoDB為新插入的每一行儲存當前系統版本號碼作為行版本號碼。

DELETE:InnoDB為刪除的每一行儲存當前系統版本號碼作為行刪除標識。

UPDATE:InnoDB為插入一行新記錄，儲存當前系統版本號碼作為行版本號碼，同時儲存當系統的版本號碼為原來的行作為刪除標識。

儲存這兩個額外系統版本號碼，使大多數操作都可以不用加鎖。這樣設計使得計資料操作很簡單，效能很好，並且也能保證只會讀取到符合標準的行。不足之處是每行記錄都需要額外的儲存空間，需要做更多的行檢查工作，以及一些額外的維護工作。

MVCC只在REPEATABLE READ和READ COMMITED兩個隔離等級下工作，其它兩個隔離等級和MVCC不相容。

Innodb的實現算不上MVCC，因為並沒有實現核心的多版本共存，undo log中的內容只是序列化的結果，記錄了多個事務的過程，不屬於多版本共存。但理想的MVCC是難以實現的，當事務僅修改一行記錄使用理想的MVCC模式是沒有問題的，可以通過比較版本號碼進行復原；但當事務影響到多行資料時，理想的MVCC據無能為力了。
比如，如果Transaciton1執行理想的MVCC，修改Row1成功，而修改Row2失敗，此時需要復原Row1，但因為Row1沒有被鎖定，其資料可能又被Transaction2所修改，如果此時復原Row1的內容，則會破壞Transaction2的修改結果，導致Transaction2違反ACID。 理想MVCC難以實現的根本原因在於企圖通過樂觀鎖代替二段提交。修改兩行資料，但為了保證其一致性，與修改兩個分布式系統中的資料並無區別，而二段提交是目前這種情境保證一致性的唯一手段。二段提交的本質是鎖定，樂觀鎖的本質是消除鎖定，二者矛盾，故理想的MVCC難以真正在實際中被應用，Innodb只是借了MVCC這個名字，提供了讀的非阻塞而已。