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未落盤,則事務就應該回滾。
//異常重啟後,回滾流程
//異常重啟後,提交流程
//寫binlog介面
參考:http://www.cnblogs.com/exceptioneye/p/5451976.html
簡單測試步驟如下:
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL事務兩段式提交MySql
- PostgreSQL 原始碼解讀(123)- MVCC#8(提交事務-實際提交過程)SQL原始碼MVCC#
- MySQL innodb引擎的事務執行過程MySql
- MySQl事務建立,開始以及提交MySql
- spark原始碼之任務提交過程Spark原始碼
- 【MySQL】MySQL(三)儲存過程和函式、觸發器、事務MySql儲存過程函式觸發器
- MySQL兩階段提交過程原理簡述MySql
- MySQL:begin後事務為什麼不提交MySql
- MySQL實現事務的提交和回滾MySql
- MySQL事務提交的三個階段介紹MySql
- mysql隱式提交事務transaction一點筆記MySql筆記
- 從一個線上問題分析binlog與內部XA事務提交過程
- 程式碼提交過程
- 十五:MySQL層事務提交流程簡析(筆記)MySql筆記
- 第15節:MySQL層事務提交流程簡析MySql
- 事務單獨提交和
- 一文帶你深度解析MySQL 8.0事務提交原理MySql
- MySQL事務還沒提交,Canal就能讀到訊息了?MySql
- Spring中的事務提交事件Spring事件
- vitess兩階段提交事務Vite
- mysql事務MySql
- MySQL 事務MySql
- ORACLE事務和例項恢復過程梳理Oracle
- MySQL事務(一)認識事務MySql
- MySQL 核心模組揭秘 | 06 期 | 事務提交之前,binlog 寫到哪裡?MySql
- MySQL 8.0.20 MGR資料遷移過程以及注意事項MySql
- MySQL 事務操作MySql
- MySQL--->事務MySql
- MySQL 三 事務MySql
- MySQL--事務MySql
- MySQL-事務MySql
- MySQL索引事務MySql索引
- MySQL 8.0 —— CATS事務排程演算法的效能提升MySql演算法
- @Transactional註解管理事務和手動提交事務
- 分散式:分散式事務(CAP、兩階段提交、三階段提交)分散式
- 分散式事務(二)之兩階段提交分散式
- 分散式事務(二)之三階段提交分散式
- mysql 儲存過程MySql儲存過程
- MySQL的session過程MySqlSession