[數據庫] 事務已提交，數據卻丟了，趕緊檢查下這個配置！

有個星球水友提問：

沈老師，我們有一次MySQL崩潰，重啟後發現有些已經提交的事務對數據的修改丟失了，不是說事務能保證ACID特性麼，想問下什麼情況下可能導致“事務已經提交，數據卻丟失”呢？

這個問題有點複雜，且容我係統性梳理下思路，先從redo log說起吧。

畫外音：水友問的是MySQL，支持事務的是InnoDB，本文以InnoDB為例展開敘述，其他數據庫不是很瞭解，但估計原理是相同的。

為什麼要有redo log？

事務提交後，必須將事務對數據頁的修改刷(fsync)到磁盤上，才能保證事務的ACID特性。

這個刷盤，是一個隨機寫，隨機寫性能較低，如果每次事務提交都刷盤，會極大影響數據庫的性能。

隨機寫性能差，有什麼優化方法呢？

架構設計中有兩個常見的優化方法：

（1）先寫日誌(write log first)，將隨機寫

（2）將每次寫優化為批量寫；

這兩個優化，數據庫都用上了。

先說第一個優化，將對數據的修改先順序寫到日誌裡，這個日誌就是redo log。

假如某一時刻，數據庫崩潰，還沒來得及將數據頁刷盤，數據庫重啟時，會重做redo log裡的內容，以保證已提交事務對數據的影響被刷到磁盤上。

一句話，redo log是為了保證已提交事務的ACID特性，同時能夠提高數據庫性能的技術。

既然redo log能保證事務的ACID特性，那為什麼還會出現，水友提問中出現的“數據庫奔潰，丟數據”的問題呢？一起看下redo log的實現細節。

redo log的三層架構？

花了一個醜圖，簡單說明下redo log的三層架構：

• 粉色，是InnoDB的一項很重要的內存結構(In-Memory Structure)，日誌緩衝區(Log Buffer)，這一層，是MySQL應用程序用戶態
• 屎黃色，是操作系統的緩衝區(OS cache)，這一層，是OS內核態
• 藍色，是落盤的日誌文件

redo log最終落盤的步驟如何？

首先，事務提交的時候，會寫入Log Buffer，這裡調用的是MySQL自己的函數WriteRedoLog；

接著，只有當MySQL發起系統調用寫文件write時，Log Buffer裡的數據，才會寫到OS cache。注意，MySQL系統調用完write之後，就認為文件已經寫完，如果不flush，什麼時候落盤，是操作系統決定的；

畫外音：有時候打日誌，明明printf了，tail -f卻看不到，就是這個原因，這個細節在《明明打印到文件了，為啥tail -f看不到》一文裡說過，此處不再展開。

最後，由操作系統（當然，MySQL也可以主動flush）將OS cache裡的數據，最終fsync到磁盤上；

操作系統為什麼要緩衝數據到OS cache裡，而不直接刷盤呢？

這裡就是將“每次寫”優化為“批量寫”，以提高操作系統性能。

數據庫為什麼要緩衝數據到Log Buffer裡，而不是直接write呢？

這也是“每次寫”優化為“批量寫”思路的體現，以提高數據庫性能。

畫外音：這個優化思路，非常常見，高併發的MQ落盤，高併發的業務數據落盤，都可以使用。

redo log的三層架構，MySQL做了一次批量寫優化，OS做了一次批量寫優化，確實能極大提升性能，但有什麼副作用嗎？

畫外音：有優點，必有缺點。

這個副作用，就是可能丟失數據：

（1）事務提交時，將redo log寫入Log Buffer，就會認為事務提交成功；

（2）如果寫入Log Buffer的數據，write入OS cache之前，數據庫崩潰，就會出現數據丟失；

（3）如果寫入OS cache的數據，fsync入磁盤之前，操作系統奔潰，也可能出現數據丟失；

畫外音：如上文所說，應用程序系統調用完write之後（不可能每次write後都立刻flush，這樣寫日誌很蠢），就認為寫成功了，操作系統何時fsync，應用程序並不知道，如果操作系統崩潰，數據可能丟失。

任何脫離業務的技術方案都是耍流氓：

（1）有些業務允許低效，但不允許一丁點數據丟失；

（2）有些業務必須高性能高吞吐，能夠容忍少量數據丟失；

MySQL是如何折衷的呢？

MySQL有一個參數：

innodb_flush_log_at_trx_commit

能夠控制事務提交時，刷redo log的策略。

目前有三種策略：

策略一：最佳性能(innodb_flush_log_at_trx_commit=0)

每隔一秒，才將Log Buffer中的數據批量write入OS cache，同時MySQL主動fsync。

這種策略，如果數據庫奔潰，有一秒的數據丟失。

策略二：強一致(innodb_flush_log_at_trx_commit=1)

每次事務提交，都將Log Buffer中的數據write入OS cache，同時MySQL主動fsync。

這種策略，是InnoDB的默認配置，為的是保證事務ACID特性。

策略三：折衷(innodb_flush_log_at_trx_commit=2)

每次事務提交，都將Log Buffer中的數據write入OS cache；

每隔一秒，MySQL主動將OS cache中的數據批量fsync。

畫外音：磁盤IO次數不確定，因為操作系統的fsync頻率並不是MySQL能控制的。

這種策略，如果操作系統奔潰，最多有一秒的數據丟失。

畫外音：因為OS也會fsync，MySQL主動fsync的週期是一秒，所以最多丟一秒數據。

講了這麼多，回到水友的提問上來，數據庫崩潰，重啟後丟失了數據，有很大的可能，是將innodb_flush_log_at_trx_commit參數設置為0了，這位水友最好和DBA一起檢查一下InnoDB的配置。

可能有水友要問，高併發的業務，InnoDB運用哪種刷盤策略最合適？

高併發業務，行業最佳實踐，是使用第三種折衷配置（=2），這是因為：

（1）配置為2和配置為0，性能差異並不大，因為將數據從Log Buffer拷貝到OS cache，雖然跨越用戶態與內核態，但畢竟只是內存的數據拷貝，速度很快；

（2）配置為2和配置為0，安全性差異巨大，操作系統崩潰的概率相比MySQL應用程序崩潰的概率，小很多，設置為2，只要操作系統不奔潰，也絕對不會丟數據。
總結
一、為了保證事務的ACID特性，理論上每次事務提交都應該刷盤，但此時效率很低，有兩種優化方向：
（1）隨機寫優化為順序寫；
（2）每次寫優化為批量寫；
二、redo log是一種順序寫，它有三層架構：
（1）MySQL應用層：Log Buffer

（2）OS內核層：OS cache

（3）OS文件：log file

三、為了滿足不用業務對於吞吐量與一致性的需求，MySQL事務提交時刷redo log有三種策略：
（1）0：每秒write一次OS cache，同時fsync刷磁盤，性能好；
（2）1：每次都write入OS cache，同時fsync刷磁盤，一致性好；
（3）2：每次都write入OS cache，每秒fsync刷磁盤，折衷；
四、高併發業務，行業內的最佳實踐，是：innodb_flush_log_at_trx_commit=2

知其然，知其所以然，希望大家有收穫。

（轉自58沈劍）

閱讀更多 java架構筆記 的文章

關鍵字: 明明 畫外音 技術