MySQL 事務提交過程

開發老大要求通過binlog查詢一條被修改的資料，資料被查出後問我，有沒有可能binlog中不會記錄，回答不會，因為資料被修改，若失敗直接回滾，不會在binlog中記錄，此刻一個朋友用了洪荒之力告訴我，失敗的話也會記錄，坐地無語，因為他sqlserver dba，用sqlserver的思維考慮mysql，哈哈哈哈哈，用實驗讓他閉嘴！

簡單測試步驟如下：
root(yoon)> flush logs;
Query OK, 0 rows affected (0.01 sec)

root((none))> show binlog events in 'mysql-bin.000041';  
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000041 |   4 | Format_desc |    232242 |         120 | Server ver: 5.6.26-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+


root(yoon)> begin;
Query OK, 0 rows affected (0.00 sec)


root(yoon)> update yoon set id=7 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0


在沒有commit情況下，二進位制日誌的位置偏移量未發生變化：
root(yoon)> show binlog events in 'mysql-bin.000041';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000041 |   4 | Format_desc |    232242 |         120 | Server ver: 5.6.26-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+




root(yoon)> commit;
Query OK, 0 rows affected (1.01 sec)

提交後再次檢視日誌偏移量，發生變化，並記錄在binlog中
root(yoon)> show binlog events in 'mysql-bin.000041';
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                        |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| mysql-bin.000041 |   4 | Format_desc |    232242 |         120 | Server ver: 5.6.16-log, Binlog ver: 4       |
| mysql-bin.000041 | 120 | Query       |    232242 |         199 | BEGIN                                       |
| mysql-bin.000041 | 199 | Query       |    232242 |         304 | use `yoon`; update yoon set id=7 where id=1 |
| mysql-bin.000041 | 304 | Xid         |    232242 |         335 | COMMIT /* xid=18 */                         |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------+



MySQL事務提交過程
開啟binlog後事務提交流程會變成兩階段提交，這裡的兩階段提交併不涉及分散式事務，當然mysql把它稱之為內部xa事務（Distributed Transactions），與之對應的還有一個外部xa事務。


這裡所謂的兩階段提交分別是prepare階段和commit階段。


內部xa事務主要是mysql內部為了保證binlog與redo log之間資料的一致性而存在的，這也是由其架構決定的(binlog在mysql層，而redo log 在儲存引擎層)；


外部xa事務則是指支援多例項分散式事務，這個才算是真正的分散式事務。


既然是xa事務，必然涉及到兩階段提交，對於內部xa而言，同樣存在著提交的兩個階段。


下文會結合原始碼詳細解讀內部xa的兩階段提交過程，以及各種情況下，mysqld crash後，mysql如何恢復來保證事務的一致性。




資料庫版本：5.6.16


作業系統版本：CentOS 6.5


配置檔案引數：
log-bin=/my/log/mysql-bin


binlog_format=ROW


set autocommit=0


innodb_support_xa=1


sync_binlog=1


innodb_flush_log_at_trx_commit=1


【innodb_flush_log_at_trx_commit=1，sync_binlog=1


不同的模式區別在於，寫檔案呼叫write和落盤fsync呼叫的頻率不同，所導致的後果是mysqld 或 os crash後，不嚴格的設定可能會丟失事務的更新。


雙一模式是最嚴格的模式，這種設定情況下，單機在任何情況下不會丟失事務更新。】




測試條件：
set autocommit=0;


DROP TABLE IF EXISTS `user`;


CREATE TABLE `user` (


`id` int(20) NOT NULL,


`account` varchar(20) NOT NULL,


`name` varchar(20) NOT NULL,


PRIMARY KEY (`id`),


KEY `id` (`id`) USING BTREE,


KEY `name` (`name`) USING BTREE


) ENGINE=InnoDB DEFAULT CHARSET=utf8;




測試語句：
insert into user values(1, 'sanzhang', '張三');


commit;


prepare階段：


    1.設定undo state=TRX_UNDO_PREPARED； //trx_undo_set_state_at_prepare呼叫


    2.刷事務更新產生的redo日誌；【步驟1產生的redo日誌也會刷入】
    
    
MYSQL_BIN_LOG::prepare


ha_prepare_low


    {


engine:


binlog_prepare


innobase_xa_prepare


mysql:


trx_prepare_for_mysql


{


                1.trx_undo_set_state_at_prepare    //設定undo段的標記為TRX_UNDO_PREPARED


                2.設定事務狀態為TRX_STATE_PREPARED


                3.trx_flush_log_if_needed  //將產生的redolog刷入磁碟


            }


     }
     
     


commit階段：


   1.將事務產生的binlog寫入檔案，刷入磁碟；


   2.設定undo頁的狀態,置為TRX_UNDO_TO_FREE或TRX_UNDO_TO_PURGE;  // trx_undo_set_state_at_finish呼叫


   3.記錄事務對應的binlog偏移，寫入系統表空間; //trx_sys_update_mysql_binlog_offset呼叫
   
MYSQL_BIN_LOG::commit


    ordered_commit


   {


1.FLUSH_STAGE


        flush_cache_to_file  //  刷binlog


2.SYNC_STAGE


        sync_binlog_file    //Call fsync() to sync the file to disk.


3.COMMIT_STAGE


        ha_commit_low


        {


            binlog_commit


            innobase_commit   


                trx_commit(trx) 


                {


                    trx_write_serialisation_history(trx, mtr);  //更新binlog位點，設定undo狀態


                    trx_commit_in_memory(trx, lsn); //釋放鎖資源，清理儲存點列表，清理回滾段


                }        


        } 


    }
    
在任何情況下（機器掉電）mysqld crash或者os crash，MySQL仍然能保證資料庫的一致性。資料的一致性是如何做到的哪？正是二階段提交。


我們結合幾種場景來分析下二階段提交是如何做到的：


1.prepare階段，redo log落盤前，mysqld crash


2.prepare階段，redo log落盤後，binlog落盤前，mysqld crash


3.commit階段，binlog落盤後，mysqld crash


對於第一種情況，由於redo沒有落盤，毫無疑問，事務的更新肯定沒有寫入磁碟，資料庫的一致性受影響；


對於第二種情況，這時候redo log寫入完成，但binlog還未寫入，事務處於TRX_STATE_PREPARED狀態，這是提交還是回滾呢？


對於第三種情況，此時，redo log和binlog都已經落盤，只是undo狀態沒有更新，雖然redo log和binlog已經一致了，事務是否應該提交？


   


我們結合mysqld異常重啟後的執行邏輯以及關鍵的原始碼。


對於第三種情況，我們可以蒐集到未提交事務的binlog event，所以需要提交；


對於第二種情況，由於binlog未寫入，需要通過執行回滾操作來保證資料庫的一致性。


   


異常重啟後，如何判斷事務該提交還是回滾


1.讀binlog日誌，獲取崩潰時沒有提交的event；  //info->commit_list中含有該元素


2.若存在，則對應的事務要提交；否則需要回滾。

判斷事務提交或回滾原始碼如下：



上面討論了兩階段提交的基本流程，以及伺服器異常crash後，mysql如何重啟恢復保證binlog和資料的一致性。


簡而言之，對於異常的xa事務，若binlog已落盤，則事務應該提交；binlog未落盤，則事務就應該回滾。


//異常重啟後，回滾流程

innobase_rollback_by_xid

rollback_by_xid

trx_rollback_resurrected

    trx_rollback_active

        row_undo

        { //從回滾頁獲取undo記錄 //分析undo記錄型別 if (insert)

                row_undo_ins else row_undo_mod

        }

//異常重啟後，提交流程

commit_by_xid

trx_commit_for_mysql

//寫binlog介面

handler.cc:binlog_log_row

sql/binlog.cc:commit

mysys/my_sync:my_sync

sql/binlog.cc:sync_binlog_file

handler/ha_innodb.cc:innobase_xa_prepare

binlog日誌檔案是為了解決MySQL主從複製功能而引入的一份新日誌檔案，它包含了引發資料變更的事件日誌集合。

從庫請求主庫傳送 binlog 並通過日誌事件還原資料寫入從庫，所以從庫的資料來源為 binlog。

這樣 MySQL 主庫只需做到 binlog 與本地資料一致就可以保證主從庫資料一致（暫且忽略網路傳輸引發的主從不一致）。

  參考：http://www.cnblogs.com/exceptioneye/p/5451976.html
    
    

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28939273/viewspace-2123605/，如需轉載，請註明出處，否則將追究法律責任。