MySQL事務隔離與undo log、MVCC的親密關系（下）

5 多版本并發控制（MVCC）

使InnoDB支援一緻性讀：

• READ COMMITTED
• REPEATABLE READ、

讓查詢不被阻塞、無需等待被其他事務持有的鎖，可以增加并發度。

InnoDB保留被修改行的舊版本。查詢正在被其他事務更新的資料時，會讀取更新之前的版本。每行資料都存在一個版本号，每次更新時都更新該版本。

聚簇索引的更新=替換更新

二級索引的更新=删除+建立

6 事務啟動方式

6.1 顯式啟動事務、

begin 或 start transaction 開啟事務：

• 送出語句 commit
• 復原語句 rollback

6.2 set autocommit=1

set autocommit=0       

将該線程的自動送出關閉。如果你隻執行一個select，事務就啟動了，且不會自動送出。

該事務會持續存在，直到主動執行commit 或 rollback，或斷開連接配接。

有些用戶端連接配接架構會預設連接配接成功後先執行

set autocommit=0      

導緻接下來的查詢都在事務中，若是長連接配接，就導緻意外的長事務。

是以建議總用

set autocommit=1      

顯式啟動事務。

頻繁事務的業務，第二種方式每個事務在開始時都不需要主動執行一次 “begin”，減少了語句互動次數。如果你也有這個顧慮，建議使用commit work and chain。

autocommit為1時，用begin顯式啟動的事務，若執行commit，則送出事務。

若執行 commit work and chain，則是送出事務并自動啟動下個事務，省去執行begin語句的開銷。從程式開發的角度也能明确知道每個語句是否處于事務。

7 查詢長事務

information_schema庫的innodb_trx表中查詢長事務

• 查找持續時間超過60s的事務。

select * from information_schema.innodb_trx where
TIME_TO_SEC(timediff(now(),trx_started))>60      

8 日備 V.S 周備

好處是“最長恢複時間”更短。

• 一天一備

  最壞情況下需要應用一天的binlog。比如，你每天0點做一次全量備份，而要恢複出一個到昨天晚上23點的備份

• 一周一備

  最壞情況就要應用一周的binlog啦！

系統的對應名額是RTO（恢複目标時間）。

當然這個是有成本的，因為更頻繁全量備份需要消耗更多存儲空間，是以這個RTO是成本換來的，需要根據業務評估。

9 避免長事務對業務的影響

長事務意味着系統裡面會存在很老的事務視圖。由于這些事務随時可能通路db裡的任何資料，是以該事務送出之前，db裡它可能用到的復原記錄都必須保留，導緻大量占存儲。

在MySQL 5.5及以前，undo log是跟資料字典一起放在ibdata檔案，即使長事務最終送出，復原段被清理，檔案也不會變小。

除了對復原段影響，長事務還占用鎖資源，可能拖慢全庫。

9.1 應用開發端

确認是否使用

set autocommit=0      

确認可在測試環境中，把MySQL的general_log開啟，随便跑個業務邏輯，通過general_log确認。

一般架構如果設定該值，也會提供參數來控制，目标就是把它改成1。

确認是否有不必要的隻讀事務。有些架構不管什麼語句先begin/commit框。有些是業務并沒有這需要，但也把好幾個select語句放到事務。這種隻讀事務可以去掉。

業務連接配接資料庫時，根據業務預估，通過SET MAX_EXECUTION_TIME指令，控制每個語句執行最長時間，避免單語句意外執行太長時間。

9.2 資料庫端

監控 information_schema.Innodb_trx表，設定長事務門檻值，超過就報警/或者kill。

Percona的pt-kill這個工具不錯，推薦。

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

使用的MySQL 5.6或更新版本，把innodb_undo_tablespaces設定成2或更大值。如果真的出現大事務導緻復原段過大，這樣設定後清理起來更友善。