MYSQL事務及儲存引擎對比

標籤：

Innodb支援事務，而myisam不支援事務。

事務的定義：

當多個使用者訪問同一份資料時，一個使用者在更改資料的過程中可能有其他使用者同時發起變更要求，為保證資料的更新從一個一致性狀態變更為另一個一致性狀態，這時有必要引入事務的概念。

Mysql提供了多種引擎支援Innodb和BDB。Innodb儲存引擎事務主要通過UNDO日誌和REDO日誌實現，Myisam和memory引擎則不支援事務。分別給出三種mysql引擎的區別和特性：

Myisam儲存引擎：由於該引擎不支援事務、也不支援外鍵，所以訪問速度很快。因此對事務完整性沒有要求並以訪問為主的應用適合使用該儲存引擎。

Innodb儲存引擎：

由於該儲存引擎在事務上具有優勢，即支援具有提交、復原和崩潰恢複能力的事務安裝，所以比myisam儲存引擎佔用更多的磁碟空間。因此需要進行頻繁的更新、刪除操作，同時對事務的完整性要求比較高，需要實現並發控制，此時適合使用該儲存引擎。

Memory儲存引擎：

該儲存引擎使用記憶體來儲存資料，因此該儲存引擎的資料訪問速度非常快，但是安全上沒有保障,(redis也是一個記憶體儲存引擎)。如果應用中涉及資料比較小，需要進行快速存取，則適合使用該儲存引擎。

本文主要從以下四個方面來介紹mysql的事務：

1. 事務概述
2. 事務控制語句
3. 交易隔離等級
4. innodb鎖機制

 

事務概述：

事務具有ACID四個性質來保證資料庫的更新從一個一致性狀態到另一個一致性狀態：

原子性（atomicity）：事務中所有的操作視為一個原子單元，即對事務所進行的資料修改等操作只能是完全提交或者完全復原。

一致性（consistency）：事務在完成時，必須使所有的資料從一種一致性狀態變更為另外一種一致性狀態，所有的變更都必須應用於事務的修改，以確保資料的完整性。

隔離性（insolation）：一個事物中的動作陳述式所做的修改必須與其他事物所做的修改相隔離。在進行事務查看資料時資料所處的狀態，要麼是被另一併發事務修改之前的狀態，要麼是被另一併發事務修改之後的狀態，即當前事務不會查看由另外一個並發事務正在修改的資料。這種特性主要由鎖機制實現。

持久性（durability）：事務完成之後，所做的修改對資料的影響是永久的，即使系統重啟或者系統出現故障，資料仍可恢複。

 

REDO日誌和UNDO日誌：

REDO日誌：

事務執行時需要將執行的交易記錄寫入到記錄檔裡，對應的檔案為redo日誌。當每條sql語句進行資料庫更新操作時，首先將redo日誌寫入到日誌緩衝區。當用戶端執行commit命令提交時，日誌緩衝區的內容將被重新整理到磁碟，日誌緩衝區的重新整理方式或者時間間隔可以通過參數innodb_flush_log_at_trx_commit控制。

REDO日誌對應磁碟上的ib_logfileN(ib_logfile0,ib_logfile1)檔案，該檔案預設為5MB，建議設定為512MB以便容納較大的事務。在mysql崩潰恢複時會重新執行redo日誌中的記錄。

UNDO日誌：

與redo日誌相反，undo日誌主要用於事務異常時的資料復原，具體內容就是複製事務前的資料庫內容到undo緩衝區，然後在合適的時間將內容重新整理到磁碟。

與redo日誌不同的是，磁碟上不存在單獨的undo記錄檔，所有的undo記錄檔均存放在資料表空間對應的.idb資料檔案中，undo日誌又被成為復原段。

 

Mysql事務控制語句：

START TRANSACTION | BEGIN [WORK]                  //開啟一個事務COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]       //提交一個事務ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]     //復原一個事務SET AUTOCOMMIT = {0 | 1}    //自動認可事務開啟或者關閉

查看事務的隔離等級：

show variables like ‘tx_isolation‘;

 

例子：commit之後才會對錶進行插入

復原之後，表不會有變動

SET AUTOCOMMIT =1;
當自動認可被開啟以後，我們rollback也沒用，會自動認可。

 

Mysql交易隔離等級

sql標準定義了四種隔離等級，指出了哪些改變其他事務可見，哪些資料改變其他資料不可見。低層級的隔離等級可以支援更高的並發處理，同時佔用的系統資源更少。

查看事務的隔離等級：

show variables like ‘tx_isolation‘;

#未提交讀,以下是設定隔離等級語句，更改預設隔離等級，需要重新登入SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;#提交讀SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;#可重複讀SET GLOBAL TRANSACTION ISOLATION LEVEL  REPEATABLE READ;  #可序列化SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

READ-UNCOMMITTED(讀取未提交內容)：

在該隔離等級，所有事物都可以看到其他未提交事務的執行結果。因為其效能也不必其他層級高很多，因此該隔離等級很少使用。讀取未提交的資料被稱為髒讀。

兩個事物在更新前，資料一致，但是左邊的事務還未提交更改，右邊的事務也讀到其更改資料，這種情況稱為髒讀。

READ_COMMITED(讀取提交內容)：

這是大多數資料庫預設的隔離等級，但並不是mysql預設的隔離等級。其滿足了隔離的簡單定義：一個事務從開始到提交前所做的任何改變都是不可見的，事務職能看見已經提交的事務所做的改變。

該隔離等級可能導致不可重複讀取問題，因為同一事務的其他執行個體在該執行個體的處理期間可能會有新的資料提交導致資料改變，所以同一個查詢可能在不同時刻返回不同結果。

 

以下例子：A事務雖然沒有提交，但是卻看到不同資料。同一事務中，兩次看到的資料是不一致的。

REPEATABLE-READ（可重讀）：

這是mysql的預設隔離等級。

該隔離等級能保證同一事務的多個執行個體在並發讀取資料時，會看到同樣的資料行，理論上會導致另一問題：幻讀。

幻讀：例如事務A對一個表中的資料修改涉及到全部的資料行。此時，事務B對該表插入一個資料行。那麼出現的問題就是，該表中出現了事務A還沒有修改的資料行。

Innodb和falcon儲存引擎通過mvcc（多版本控制）機制解決了該問題。

innodb儲存引擎的MVCC機制：innodb通過為每個資料行增加兩個隱含值的方式來實現。這兩個隱含值記錄了行的建立時間，及到期時間。每一行儲存事件發生時的系統版本號碼，每一次開始一個新的事物，版本號碼會自動加1，每個事物都會儲存開始時的版本號碼，每個查詢根據事物的版本來查詢結果。

 

Serializable（可序列化）：

這是最高的隔離等級，通過強制事物排序，使之不可能相互衝突，從而解決幻讀問題。簡言之，是在每個讀的資料行上加上共用鎖定實現。在這個層級，可能會導致大量的逾時現象和鎖競爭，一般不推薦使用。

該隔離等級採用不同的鎖類型來實現。

 

事務的隱式提交：例如alter table會造成隱含交易提交，儘管我們沒有提交事務，但是在其他事務中卻看到了我們的資料變化。

Innodb的鎖機制：

鎖的類型：

共用鎖定：

共用鎖定又稱為S鎖，是share的縮寫，共用鎖定的鎖粒度是行或者元組（多個行）。一個事務擷取了共用鎖定之後，可以對鎖定範圍的資料執行讀操作。

排它鎖：

排它鎖的代號是X，是eXclusive的縮寫，排它鎖的鎖粒度也是行或者元組，一個事務擷取了排它鎖以後，可以對鎖定範圍內的資料執行寫操作。

意圖鎖定：

意圖鎖定是一種表鎖，鎖的粒度是整張表，分為意圖共用鎖（IS）和意向排它鎖（IX）兩類。意圖鎖定表示一個事務有意對資料上共用鎖定或者排它鎖。”有意“表示事務想執行操作，但還沒有真正執行。

 

鎖和鎖之間要麼是相容的，要麼是互斥的：

鎖粒度：

鎖的粒度主要分為行鎖和表鎖。鎖的粒度越小，其耗費的系統資源越多，但是並發性卻更好。

表鎖的開銷最小，同時允許的並發量也是最小的鎖機制。Myisam使用該鎖機制。

行鎖支援最大的並發，innodb儲存引擎使用該鎖機制，如果支援並發讀寫，建議採用innodb鎖機制。

 

 

select語句會加上一個共用鎖定，如果尋找的資料已經被加上排它鎖的話，共用鎖定會等待期結束再加，若等待時間過長就會顯示需要等待的鎖逾時。

insert、update、delete會被加上排它鎖。

 

MYSQL事務及儲存引擎對比