MySQL中事務並發問題的解決方案

程序猿集錦

事務並發問題的驗證

為解決事務並發帶來的這些問題，在SQL92標准中提出了四個隔離級別來修複這些問題。各個數據庫廠商根據此標准，在各自的數據庫產品中做了不同方式的實現，以此來實現數據庫中事務的隔離特性。

事務的隔離級別和事務並發出現的各種問題之間有一個對應的表格，如下所示：

如何解決事務並發問題

MVCC的認識

MVCC：multi version concurrency control，多版本並發控制。只在讀已提交和可重複讀兩個事務隔離級別下才有MVCC的實現，在讀未提交和串行化中不存在MVCC。

下面通過這個圖解，我們一起分析一些MySQL是如果做到MVCC的。

MySQL中事務ID是從1開始連續自增的，並且全局唯一，不同的事務他們的ID不同。按照發生的時間先後持續自增。也就是後發生的事務的ID一定比先發生的事務ID的值要大。如上圖所示，事務是從1持續增長。

每個事務在啟動的時候會分配一個唯一的ID，確切的說是在事務執行第一個非select語句的時候，才分配的事務ID。只讀事務可以認為不分配事務ID，會給分配一個很大的隨機數作為當前只讀事務的ID。

數據庫中的每行數據，在被DML事務語句操作並提交之後，都會用操作它的事務ID作為該行數據最新的版本號。所以我們可以理解為：事務ID=數據的版本號。

當某個事務啟動的瞬間，MySQL會基於此時數據庫中的事務分布情況，構建一個一致性視圖(也可以理解為快照)，在這個事務運行期間，始終使用這一個視圖，來保證這個事務運行的整個過程中，所讀取到的數據都是一致的，不管其他事務怎樣修改提交數據，這些操作對這個當前的事務而言都是透明的，當前事務所能讀到的數據自始至終都是一樣的，不受其他事務的影響，以此達到事務的隔離性。這就是MySQL的MVCC技術核心思想和效果。而這個構建一致性視圖的基礎就是用運行中的事務的ID組成一個運行中的事務ID數組，然後基於每行數據的版本號和這個數組進行比較來判斷是否可以讀到某一行數據。

假設某個事情啟動的瞬間，當前數據庫的事務隊列情況如上所示。

其中正在運行中的事務的ID有85,88,89,94,96共5個事務，MySQL會用這些運行中的事務的ID組成一個運行中的事務ID數組。此時的數據為：<85,88,89,94,96>。

其中最小的事務ID為：min(ID)=85，最大的事務ID為：max(ID)=96。

綠色表示已經結束的事務。

黃色表示運行中的事務。

橘色表示未來要啟動的並且結束事務，橘色的事務在當前這個將要啟動的事務啟動之後才啟動的。

在剛啟動當前事務的時候，這些橘色的事務是不存在的。但是在當前事務啟動後，並且運行的過程中，MySQL中是可以啟動新的事務的，而這些新事務的ID由於是後啟動的，所以它們的ID肯定比當前事務的max(ID)要大，所以他們會排在當前活動的最大的事務ID的後面。

在當前事務運行過程中去判斷某行數據是否可見的時候，是有可能讀取到後啟動的事務修改並提交的數據的。為了便於我們分析，所以把它們都用橘色的事務給標出來放在最後面了。這樣才便於我們理解和判斷在後啟動的事務所修改的行數據是否對當前事務可見。

那麼在這個事務運行過程中，判斷某行數據是否可見的時候，判斷的原則就是拿到某個數據行的版本號，我們暫時把某行數據得到的版本號記作：x，拿x和當前的活動的事務ID數組進行比較。判斷原則和結果如下：

如果 x < min(ID)，例如 x=83 < min(ID)，那麼表示這個數據行的數據內容是在當前事務啟動之前，就已經完成修改且提交的內容，可見，即：當前事務可以讀到這樣的修改。

如果x剛好等於某個正在運行的某個事務的ID，例如 x = 88 這個事務ID，那麼表示這行數據是由88這個事務修改的內容。由於當前事務啟動的時候，這個88編號的事務正在運行還沒有提交，所以對於當前事務而言，不可見，即：不能讀取這行數據的修改內容。

如果 min(ID) < x < max(ID)，並且不等於活躍事務ID數組中的任何一個值。例 x=91 這個事務ID，那麼表示這個數據行的數據是在當前事務啟動之前，就已經運行結束並且提交的修改。此時對於當前運行的事務而言，可見，即：可以讀取這行數據的修改內容。

如果 max(id) < x，例如x=99，表示在當前事務啟動的時候，修改這行數據的編號為99的事務還沒有啟動，是在當前事務啟動之後才啟動的，但是99號事務比當前事務運行的還要快，它已經運行結束並提交了它的修改。此時，對於當前事務來說，不可見，即：是不能讀取到這行數據被修改的內容。要知道在當前事務啟動之後運行的過程中，是可以有其他事務陸續啟動的，所以在當前事務運行的過程中，晚於當前事務啟動的那些事務也是有可能早於當前事務結束的，所以才出現了在判斷當前事務中數據可見性的時候，讀取到了比max(ID)還要大的數據版本號。

當我們發現一條數據的當前版本對當前事務不可見的時候，MySQL這個時候是不能把這個最新版本的數據返回給客戶端的。那麼它會怎麼做呢？總不能不返會任何數據吧。

innodb會通過undo-log，通過數據行的版本號，向上找一個版本。拿到這個版本的ID之後，再次判斷這個版本號的數據內容對當前事務是否可見，如果可見這通過undo-log計算出上一個版本的數據內容，然後將數據結果返回。

如果上一個版本的數據仍然不滿足對當前事務可見的要求，那麼繼續查找上一個版本的數據內容。直到找到符合要求的、對當前事務可見的數據版本。然後通過undo-log根據版本號的順序依次計算出應該返回的數據內容，然後再返回給客戶端。

一致性視圖創建的時間點

針對不同的事務隔離級別，一致性視圖創建時間點也是各不相同的。在各個事務隔離級別下，MySQL中MVCC的數據一致性視圖創建的時候遵循如下規則

讀未提交：讀未提交隔離級別下直接返回行記錄上的最新值，沒有視圖概念。

讀已提交：這個一致性視圖是在每個 SQL 語句開始執行的時候創建的。也就是說，如果一個事務中包含多條SQL語句，那麼在每一個SQL語句執行前都會創建一個一致性視圖，所以在這個事務中，會有多個一致性視圖存在。如果同樣的SQL語句，在同一個事務的不同的時間點執行，那麼他們的一致性視圖就可能會不一樣，這也是導致在讀已提交事務隔離級別下出現不可重複讀和幻讀的原因。

可重複讀：在執開啟事務之後就創建了。這個視圖是在事務啟動時創建的，整個事務存在期間都用這個視圖。但是在開啟事務的方式不同的情況下，創建一致性視圖的時間又有點區別，詳細區別如下：

序列化：對於“串行化”隔離級別下，直接用加鎖的方式來避免並行訪問。沒有一致性視圖的概念。

事務的啟動方式和區別

MySQL默認是自己開維護每一個SQL語句事務的開啟和提交的。它通過參數

autocommit

來控制，當參數為1的時候表示自動開啟和提交事務；當為0的時候表示需要手動開啟和提交事務。

當然了，如果MySQL的控制數據是否自動提交的參數設置為1，也是支持我們自己去開啟和提交事務的時候，我們可以使用如下的命令去手動的開啟和提交事務。

begin：開啟事務，它和start transaction的功能是等效的。此時其實不會馬上創建一致性視圖，會在後面第一個操作innodb表的SQL語句執行的時候創建一致性事務視圖，而這個操作語句不管是什麼類型的操作語句，隨便select,insert,update,delete都可以。與此同事事務的ID的分配也是在執行這第一個操作innodb表的SQL語句時分配的，需要注意的是：只讀事務的事務ID是一個隨機數，非只讀事務的ID是一個比較正常數。

start transaction with consistent snapshot：開啟事務，並且馬上開始創建一個一致性視圖，供當前啟動的事務期間使用。此時事務的ID也分配好了。這是該語句和begin/start transaction語句不同的地方。

autocommit=0：設置數據庫自動提交事務的功能關閉，執行這個命令後，此時只要執行任何一個操作innodb表的SQL語句，事務開啟了，需要在SQL語句結束後，手動執行commit命令才會提交事務。否則不提交前面的SQL語句。

commit：提交事務的語句。

commit work with chain：提交事務，並開啟下一個事務。這個命令等效於執行完成commit之後，馬上又執行了一個begin命令。所以，它的效果等效於

commit+begin

rollback：回歸事務的語句。

長事務

什麼是長事務

前面我們提到MySQL在可重複讀隔離級別下，一個事務在開啟之後一直到這個事務結束之前，它所能看到的數據內容和這個事務啟動的時候看到的數據內容是相同的，這個事務不會受到其他事務修改數據的影響。其實這個時候，是有其他事務修改數據並提交操作的。MySQL之所以能夠實現可重複讀的隔離級別，是通過數據行的版本號來依次判斷後才實現的。

那麼試想一下，如果一個事務從早上就開啟了，但是它一直沒有提交，直到晚上還沒有提交，此時為了滿足這個事務的可重複讀的要求，MySQL就需要一直保留著這數據庫中的很多數據行的版本信息和undo-log，以便這個事務在晚上某個時刻想要查看某一行數據的時候，可以通過這些版本號和undo-log追溯到這個事務啟動時刻的數據版本。如果這一天中，數據庫中有很多事務發生。那麼此時就會存在很多的版本號和undo-log。這就導致了回滾日志的暴漲，並且增大了MySQL數據庫發生鎖沖突的可能性。

我們上面說的這樣的事務就是一個長事務。在 MySQL 5.5 及以前的版本，回滾日志是跟數據字典一起放在 ibdata 文件裏的，即使長事務最終提交，回滾段被清理，文件也不會變小。
這個“清理”的意思是 “邏輯上這些文件位置可以複用”，但是並沒有刪除文件，也沒有把文件變小。

所以我要避免使用長事務。那麼我們該如何避免使用長事務呢？

如何避免長事務

從應用開發端方面

確認是否使用了 set autocommit=0。這個確認工作可以在測試環境中開展，把 MySQL 的 general_log 開起來，然後隨便跑一個業務邏輯，通過 general_log 的日志來確認。

確認是否有不必要的只讀事務。有些框架會習慣不管什麼語句先用 begin/commit 框起來。有些是業務並沒有這個需要，但是也把好幾個 select 語句放到了事務中。這種只讀事務可以去掉。

業務連接數據庫的時候，根據業務本身的預估，通過 SET MAX_EXECUTION_TIME 命令，來控制每個語句執行的最長時間，避免單個語句意外執行太長時間。

從數據庫端方面

監控 information_schema.Innodb_trx 表，設置長事務閾值，超過就報警 / 或者 kill，Percona 的 pt-kill 這個工具不錯，推薦使用。

在業務功能測試階段要求輸出所有的 general_log，分析日志行為提前發現問題。

如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 設置成 2（或更大的值）。如果真的出現大事務導致回滾段過大，這樣設置後清理起來更方便。

總結

四種隔離級別怎麼理解？

讀未提交：別人改數據的事務尚未提交，我在我的事務中也能讀到。

讀已提交：別人改數據的事務已經提交，我在我的事務中才能讀到。

可重複讀：別人改數據的事務已經提交，我在我的事務中也不去讀。

串行化：我的事務尚未提交，別人就別想讀數據。

什麼是髒讀、不可重複讀、幻讀？

髒讀：一個事務讀取到其它事務update、delete、insert後未提交的數據。

不可重複讀：一個事務讀取其它事務update或delete後且已提交的數據。

幻讀：一個事務讀取到其他事務insert且已提交的數據。