[玩轉MySQL之九]MySQL事務ACID詳解

一、引言

做資料庫相關工作的同學都知道事務，就是一個操作序列，這些操作要麼都執行，要麼都不執行，它是一個不可分割的工作單位， ACID又是事務的四大特性。

那麼就會有如下疑問:

ACID具體代表什麼？
MySQL InnoDB引擎又是如何實現ACID的呢？

二、 ACID詳解

ACID，是指在可靠資料庫管理系統（DBMS）中，事務(transaction)所應該具有的四個特性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）

2.1 原子性(Atomicity)

一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作。實現事務的原子性，資料庫要支持回滾操作，在事務中某個操作失敗後，回滾到事務之前的狀態。大多數資料庫實現事務時，事務操作的數據是在事務相關的數據快照上，並不是真正修改實際的數據，如果有錯不提交即可。而其他一些只支持簡單事務的資料庫系統中，事務更新數據不在快照上，而是直接操作實際數據，則系統需要先預演一遍所有要執行的操作，如果失敗，事務相關的所有操作都不會被執行。

2.2 一致性（consistency）

一致性是指事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態，也就是說一個事務執行之前和執行之後都必須處於一致性狀態。

如轉賬的栗子: 假設用戶A和用戶B兩者的錢加起來一共是5000，那麼不管A和B之間如何轉賬，轉幾次賬，事務結束後兩個用戶的錢相加起來應該還得是5000，這就是事務的一致性。

事務的一致性決定了一個系統設計和實現的複雜度，因為事務可以有不同程度的一致性：

強一致性: 無論更新操作實在哪一個數據副本執行，之後所有的讀操作都能獲得最新的數據。
弱一致性: 提交的更新操作，不一定立即會被讀操作讀到，需要一段時間，此種情況會存在一個不一致窗口。
最終一致性: 是弱一致性的特例。事務更新一份數據，最終一致性保證在沒有其他事務更新同樣的值的話，最終所有的事務都會讀到之前事務更新的最新值。如果沒有錯誤發生，不一致窗口的大小依賴於：通信延遲，系統負載等。

2.3 隔離性(Isolation)

隔離性是當多個用戶並發訪問資料庫時，比如操作同一張表，資料庫為每一個用戶開啟的事務，不能被其他事務的操作所幹擾，多個並發事務之間要相互隔離。

當前大多數資料庫都提供了不同級別的事務隔離，之後便會產生不同的問題，一般會出現的問題主要有:

臟讀

臟讀是指在一個事務處理過程裏讀取了另一個未提交的事務中的數據。當一個事務A正在多次修改某個數據，而在這個事務中這多次的修改都還未提交，這時另外一個並發的事務B來訪問該數據，將會獲取到事務A中沒有提交的數據，從而最終造成兩個事務得到的數據不一致。

如: 張三有500塊錢，現在給李四轉100塊錢，這個事務中的SQL涉及到兩個操作:1、 A賬戶減100塊錢

update account set money=money - 100 where name=』張三』;

2、 B賬戶加100塊錢

update account set money=money+100 where name=』李四』;

我們假設事務A執行轉賬，剛執行完第一條SQL語句，此時，另外一個事務B來查詢張三賬戶的錢，那麼獲取到的是400塊錢。但是事務A執行第二條語句的時候出現了問題，事務A進行了回滾，那麼事實上張三賬戶的錢應該是500塊錢，而不是400塊錢，從而出現了臟讀。

不可重複讀

不可重複讀是指在對於資料庫中的某個數據，一個事務範圍內多次查詢卻返回了不同的數據值，這是由於在查詢間隔，被另一個事務修改並提交了。

例如事務T1在讀取某一數據，而事務T2立馬修改了這個數據並且提交事務給資料庫，事務T1再次讀取該數據就得到了不同的結果，發送了不可重複讀。

不可重複讀和臟讀的區別是，臟讀是某一事務讀取了另一個事務未提交的臟數據，而不可重複讀則是讀取了前一事務提交的數據。

幻讀

幻讀，是指當事務不是獨立執行時發生的一種現象。

例如事務T1對一個表中所有的行的某個數據項做了從「1」修改為「2」的操作，這時事務T2又對這個表中插入了一行數據項，而這個數據項的數值還是為「1」並且提交給資料庫。而操作事務T1的用戶如果再查看剛剛修改的數據，會發現還有一行沒有修改，其實這行是從事務T2中添加的，就好像產生幻覺一樣，這就是發生了幻讀。

幻讀和不可重複讀都是讀取了另一條已經提交的事務（這點就臟讀不同），所不同的是不可重複讀查詢的都是同一個數據項，而幻讀針對的是一批數據整體（比如數據的個數）。

丟失更新

兩個事務同時讀取同一條記錄，A先修改記錄，B也修改記錄（B是不知道A修改過），B提交數據後B的修改結果覆蓋了A的修改結果。

2.4 持久性（Durability）

持久性是指一個事務一旦被提交了，那麼對資料庫中的數據的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。

三、MySQL實現ACID的機制

3.1 原子性(Atomicity)

MySQL InnoDB引擎通過以下幾個方面實現事務的原子性:

事務自動提交(Autocommit)配置
commit 和rollback語句
從元資料庫INFORMATION_SCHEMA的表中獲取操作數據

3.1.1 MySQL 事務自動提交(Autocommit)配置

MySQL默認操作模式就是autocommit自動提交模式。這就表示除非顯式地開始一個事務，否則每個查詢都被當做一個單獨的事務自動執行。

查看autocommit模式

變數autocommit分會話系統變數與全局系統變數，所以查詢的時候，最好區別是會話系統變數還是全局系統變數。

Value的值為ON，表示autocommit開啟。OFF表示autocommit關閉。

修改autocommit模式

方法1: 通過命令行修改

mysql> set session autocommit=0; Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> set global autocommit=0;
Query OK, 0 rows affected (0.00 sec)

上述SQL修改會話系統變數或全局系統變數，只對當前實例有效，如果MySQL服務重啟的話，這些設置就會丟失，如果要永久生效，就必須在配置文件中修改系統變數。

方法2: 修改配置文件

[mysqld] autocommit=0

autocommit與顯性事務的關係

對於顯性事務start transaction或begin，在自動提交模式關閉（關閉隱式提交）的情況下，開啟一個事務上下文。首先資料庫會隱式提交之前的還未被提交的操作，同時開啟一個新事務。具體實驗如下：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from tester.test where name=張三;
Query OK, 1 row affected (0.00 sec)

此時重新打開一個終端，登陸MySQL開啟一個新的繪畫，此時可以查詢到會話ID為15的事務信息，詳情如下:

mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 16 | +-----------------+ 1 row in set (0.00 sec)

mysql> SELECT a.trx_state,
-> b.event_name,
-> a.trx_started,
-> b.timer_wait / 1000000000000 timer_wait,
-> a.trx_mysql_thread_id blocking_trx_id,
-> b.sql_text
-> FROM information_schema.innodb_trx a,
-> performance_schema.events_statements_current b,
-> performance_schema.threads c
-> WHERE a.trx_mysql_thread_id = c.processlist_id
-> AND b.thread_id = c.thread_id;
+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+
| trx_state | event_name | trx_started | timer_wait | blocking_trx_id | sql_text |
+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+
| RUNNING | statement/sql/delete | 2019-02-25 15:45:00 | 0.0010 | 1 | delete from tester.test where name=張三 |
+-----------+----------------------+---------------------+------------+-----------------+------------------------------------------+
1 row in set (0.00 sec)

如果在會話15中開啟顯性事務，那麼之前掛起的事務會自動提交，然後，你再去會話16當中查詢，就發現之前的DELETE操作已經提交。

在會話15中開啟顯示事務:start transaction;

mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 15 | +-----------------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec)

在會話16中繼續查詢事務

mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 16 | +-----------------+ 1 row in set (0.00 sec)

使用START TRANSACTION，自動提交將保持禁用狀態，直到你使用COMMIT或ROLLBACK結束事務。自動提交模式然後恢復到之前的狀態（如果start transaction 前 autocommit = 1，則完成本次事務後 autocommit 還是 1。如果 start transaction 前 autocommit = 0，則完成本次事務後 autocommit 還是 0）

3.1.2 COMMIT

一般的MySQL語句都是直接針對資料庫表執行和編寫的。這就是所謂的隱含提交（implicit commit），即提交（寫或保存）操作是自動進行的。但是，在事務處理塊中，提交不會隱含地進行。為進行明確的提交，使用 COMMIT 語句，如下所示：

mysql> start transaction; mysql> delete from persons where id = 200; mysql> delete from students where person_id = 200; mysql> commit;

當 COMMIT 或 ROLLBACK 語句執行後，事務會自動關閉

3.1.3 ROLLBACK

ROLLBACK 只能在一個事務處理內使用，即: 在執行一條 START TRANSACTION 命令之後。

mysql> begin transaction; # 開始事務 Query OK, 0 rows affected (0.00 sec)

mysql> insert into aaaa values(7);
Query OK, 1 rows affected (0.00 sec)

mysql> rollback; # 回滾
Query OK, 0 rows affected (0.00 sec)

3.1.4 從元資料庫INFORMATION_SCHEMA獲取和操作數據

元數據就是描述數據的數據，如資料庫名，表名，表大小，欄位名，欄位類型等。而MySQL中的INFORMATION_SCHEMA就是一個元資料庫，存儲著資料庫實例中的所有元數據信息。

關於INFORMATION_SCHEMA的官方定義:

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges

information_schema資料庫是MySQL自帶的，確切說information_schema是MySQL的信息資料庫，保存著關於MySQL伺服器所維護的所有其他資料庫的信息。如資料庫名，資料庫的表，欄位的數據類型與訪問許可權等。

nformation_schema資料庫是MySQL系統自帶的資料庫，它提供了資料庫元數據的訪問方式。感覺information_schema就像是MySQL實例的一個百科全書，記錄了資料庫當中大部分我們需要了結的信息，比如字符集，許可權相關，資料庫實體對象信息，外檢約束，分區，壓縮表，表信息，索引信息，參數，優化，鎖和事物等等。通過information_schema我們可以窺透整個MySQL實例的運行情況，可以了結MySQL實例的基本信息，甚至優化調優，維護資料庫等，

下面對information_schema中的表進行簡單的分類:

1、關於字符集和排序規則相關的系統表

CHARACTER_SETS : 保存所有Mysql可用的字符集。相當於命令：SHOW CHARACTER SET
COLLATIONS: 字符集對應的排序規則
COLLATION_CHARACTER_SET_APPLICABILITY: 顯示了哪種字符集適用於哪種排序方式。這些列相當於我們從SHOW COLLATION獲得的前兩個欄位。

2、許可權相關的表

SCHEMA_PRIVILEGES:保存資料庫的許可權信息，該表是個內存表，裡面的數據從mysql.db中載入。
TABLE_PRIVILEGES: 保存表的許可權信息。該表是個內存表，裡面的數據從mysql.tables_priv中載入。
COLUMN_PRIVILEGES: 保存表中列的許可權信息。該表是個內存表，裡面的數據從mysql.columns_priv中載入。
USER_PRIVILEGES:存儲用戶的許可權。該表是個內存表，裡面的數據從mysql.user中載入。

3、存儲資料庫系統實體對象的表

COLUMNS: 保存所有資料庫中表的列信息
INNODB_SYS_COLUMNS: 存放的是INNODB的元數據，依賴於SYS_COLUMNS這個統計表而存在的。
ENGINES: 存儲Mysql支持的資料庫引擎類型，相當於命令SHOW ENGINES
EVENTS: 保存計劃事件（scheduled events）的信息，類似於定時作業
FILES: 這張表提供了有關在MySQL的表空間中的數據存儲的文件的信息，文件存儲的位置
PARAMETERS: 參數表存儲了一些存儲過程和方法的參數，以及存儲過程的返回值信息。存儲和方法在ROUTINES裡面存儲。
PLUGINS: MySQL的插件信息。其實SHOW PLUGINS本身就是通過這張表來獲取數據。
ROUTINES: 關於存儲過程和方法function的一些信息，不過這個信息是不包括用戶自定義的，只是系統的一些信息。
SCHEMATA: 供了實例下有多少個資料庫，以及資料庫默認的字符集
TABLES: 保存數據表信息。類似show tables。
TRIGGERS: 記錄的就是觸發器的信息，包括所有的相關的信息。包括系統的和自己用戶創建的觸發器。
VIEWS: 視圖的信息，包括系統的和用戶的基本視圖信息

4、約束外鍵等相關的表

REFERENTIAL_CONSTRAINTS: 這個表提供的外鍵相關的信息，而且只提供外鍵相關信息
TABLE_CONSTRAINTS: 保存表的約束信息
INNODB_SYS_FOREIGN_COLS: 這個表也是存儲的INNODB關於外鍵的元數據信息和INNODB_SYS_FOREIGN 存儲的信息是一致的
INNODB_SYS_FOREIGN: 存儲的INNODB關於外鍵的元數據信息和SYS_FOREIGN_COLS 存儲的信息是一致的，只不過是單獨對於INNODB來說的
KEY_COLUMN_USAGE: 資料庫中所有有約束的列都會存下下來，也會記錄下約束的名字和類別

5、關於資料庫管理的表

GLOBAL_STATUS: 保存MySQL的全局狀態。全局是相對於Session而言的，Session是指單個Mysql連接，全局可以理解為自從Mysql啟動以來，所有的連接，產生的狀態。
GLOBAL_VARIABLES: 保存MySQL的全局參數。

狀態（status）是隨著MySQL的運行，發生變化的。參數（variable）只有資料庫管理員主動修改，才會變化的。

SESSION_STATUS: 保存SESSION時的資料庫狀態，類似於GLOBAL_STATUS
SESSION_VARIABLES: 保存SESSION的資料庫變數，類似於GLOBAL_BARIABLES
PARTITIONS: MySQL分區表相關的信息，通過這張表我們可以查詢到分區的相關信息（資料庫中已分區的表，以及分區表的分區和每個分區的數據信息）
PROCESSLIST：show processlist其實就是從這個表拉取數據。由於是一個內存表，所以我們相當於在內存中查詢一樣，這些操作都是很快的。
INNODB_CMP_PER_INDEX，INNODB_CMP_PER_INDEX_RESET:這兩個表存儲的是關於壓縮INNODB信息表的時候的相關信息,有關整個表和索引信息都有
INNODB_CMPMEM，INNODB_CMPMEM_RESET: 這兩個表是存放關於MySQL INNODB的壓縮頁的buffer pool信息，但是要注意一點的就是,用這兩個表來收集所有信息的表的時候,是會對性能造成嚴重的影響的,所以說默認是關閉狀態的。
INNODB_BUFFER_POOL_STATS: 表提供有關INNODB 的buffer pool相關信息，和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息來源。
INNODB_BUFFER_PAGE_LRU，INNODB_BUFFER_PAGE: 維護了INNODB LRU LIST的相關信息
INNODB_SYS_DATAFILES: 這張表就是記錄的表的文件存儲的位置和表空間的一個對應關係(INNODB)
INNODB_TEMP_TABLE_INFO:
INNODB_METRICS: 提供INNODB的各種的性能指數，收集的是MySQL的系統統計信息。這些統計信息都是可以手動配置打開還是關閉的。有以下參數都是可以控制的：innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all。
INNODB_SYS_VIRTUAL:表存儲的是INNODB表的虛擬列的信息，
INNODB_CMP，INNODB_CMP_RESET: 存儲的是關於壓縮INNODB信息表的時候的相關信息。

6、關於表信息和索引信息的一些表

TABLES: 記錄的資料庫中表的信息，其中包括系統資料庫和用戶創建的資料庫。show table status like test1G的來源就是這個表；
TABLESPACES: 標註活躍表空間，這個表是不提供關於innodb的表空間信息。
INNODB_SYS_TABLES: 這張表依賴的是SYS_TABLES數據字典中拉取出來的。此表提供了有關表格的格式和存儲特性，包括行格式，壓縮頁面大小位級別的信息。提供的是關於INNODB的表空間信息，其實和SYS_TABLESPACES 中的INNODB信息是一致的。
STATISTICS: 這個表提供的是關於表的索引信息，所有索引的相關信息。
INNODB_SYS_INDEXES: 提供相關INNODB表的索引的相關信息，和SYS_INDEXES 這個表存儲的信息基本是一樣的，只不過後者提供的是所有存儲引擎的索引信息，前者只提供INNODB表的索引信息。
INNODB_SYS_TABLESTATS: 這個表非常重要，記錄的是MySQL的INNODB表信息以及MySQL優化器會預估SQL選擇合適的索引信息，其實就是MySQL資料庫的統計信息。這個表的記錄是記錄在內存當中的，是一個內存表，每次重啟後就會重新記錄，所以只能記錄從上次重啟後的資料庫統計信息。有了這個表，我們對於索引的維護就更加方便了，我們可以查詢索引的使用次數，方便清理刪除不常用的索引，提高表的更新插入等效率，節省磁碟空間。
INNODB_SYS_FIELDS: 這個表記錄的是INNODB的表索引欄位信息，以及欄位的排名
INNODB_FT_CONFIG: 這張表存的是全文索引的信息
INNODB_FT_DEFAULT_STOPWORD: 這個表存放的是stopword 的信息,是和全文索引匹配起來使用的，和innodb的 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 是相同的，這個STOPWORD必須是在創建索引之前創建，而且必須指定欄位為varchar。stopword 也就是我們所說的停止詞，全文檢索時，停止詞列表將會被讀取和檢索，在不同的字符集和排序方式下，會造成命中失敗或者找不到此數據，這取決於停止詞的不同的排序方式。我們可以使用這個功能篩選不必要欄位。
INNODB_FT_INDEX_TABLE: 這個表存儲的是關於INNODB表有全文索引的索引使用信息的，同樣這個表也是要設置innodb_ft_aux_table以後纔能夠使用的，一般情況下是空的
INNODB_FT_INDEX_CACHE: 這張表存放的是插入前的記錄信息，也是為了避免DML時候昂貴的索引重組

7、關於MySQL優化相關的表

OPTIMIZER_TRACE: 提供的是優化跟蹤功能產生的信息.
PROFILING: SHOW PROFILE可以深入的查看伺服器執行語句的工作情況。以及也能幫助你理解執行語句消耗時間的情況。一些限制是它沒有實現的功能，不能查看和剖析其他連接的語句，以及剖析時所引起的消耗。
INNODB_FT_BEING_DELETED，INNODB_FT_DELETED: INNODB_FT_BEING_DELETED 這張表是INNODB_FT_DELETED的一個快照,只在OPTIMIZE TABLE 的時候才會使用。

8、關於MySQL事物和鎖的相關的一些表

INNODB_LOCKS: 現在獲取的鎖，但是不含沒有獲取的鎖，而且只是針對INNODB的。
INNODB_LOCK_WAITS: 系統鎖等待相關信息，包含了阻塞的一行或者多行的記錄，而且還有鎖請求和被阻塞改請求的鎖信息等
INNODB_TRX: 包含了所有正在執行的的事物相關信息（INNODB），而且包含了事物是否被阻塞或者請求鎖。

3.2 一致性（consistency）

MySQL通過如下策略完成事務的一致性:

InnoDB doublewrite buffer。
InnoDB crash recovery。

3.2.1 InnoDB doublewrite buffer(雙寫緩衝)

3.2.1.1 什麼是 InnoDB doublewrite buffer？

[官方定義] InnoDB uses a file flush technique called doublewrite. Before writing pages to the data files, InnoDB first writes them to a contiguous area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer have completed, does InnoDB write the pages to their proper positions in the data file. If there is an operating system, storage subsystem, or mysqld process crash in the middle of a page write, InnoDB can later find a good copy of the page from the doublewrite buffer during crash recovery.

InnoDB使用了一種叫做doublewrite的特殊文件flush技術，在把pages寫到date files之前，InnoDB先把它們寫到一個叫doublewrite buffer的連續區域內，在寫doublewrite buffer完成後，InnoDB才會把pages寫到data file的適當的位置。如果在寫page的過程中發生意外崩潰，InnoDB在稍後的恢復過程中在doublewrite buffer中找到完好的page副本用於恢復。

3.2.1.2 InnoDB doublewrite buffe解決的問題

資料庫，操作系統和磁碟讀寫的基本單位是塊，也可以稱之為(page size)block size。資料庫的塊一般為8K，16K；而OS的塊則一般為4K；IO塊則更小，linux內核要求IO block size<=OS block size。

磁碟IO除了IO block size，還有一個概念是扇區(IO sector)，扇區是磁碟物理操作的基本單位，而IO 塊是磁碟操作的邏輯單位，一個IO塊對應一個或多個扇區，扇區大小一般為512個位元組。

各個塊大小的關係如下： DB block > OS block >= IO block > 磁碟 sector，而且他們之間保持了整數倍的關係。

下面小編系統各個塊的大小(MySQL為5.7，OS以Centos7):

MySQL block size

OS block

[root@ecs-prod-my57-fserp-ro ~]# getconf PAGESIZE 4096

IO block size

[root@ecs-prod-my57-fserp-ro ~]# blockdev --getbsz /dev/vdb 4096

sector size

root@ecs-prod-my57-fserp-ro ~]# fdisk -l | grep Sector Sector size (logical/physical): 512 bytes / 512 bytes

從上面的結果可以看到DB page=4*OS page=4*IO page=32*sector size

由於任何DB page的寫入，最終都會轉為sector的寫入，如果在寫磁碟的過程中，出現異常重啟，就可能會發生一個DB頁只寫了部分sector到磁碟，進而出現頁斷裂的情況　

InnoDB的page size一般是16KB，其數據校驗也是針對這16KB來計算的，將數據寫入到磁碟是以page為單位進行操作的。操作系統寫文件是以4KB作為單位的，那麼每寫一個InnoDB的page到磁碟上，操作系統需要寫4個塊。而計算機硬體和操作系統，在極端情況下（比如斷電）往往並不能保證這一操作的原子性，16K的數據，寫入4K時，發生了系統斷電或系統崩潰，只有一部分寫是成功的，這種情況下就是partial page write（部分頁寫入）問題。這時page數據出現不一樣的情形，從而形成一個"斷裂"的page，使數據產生混亂。這個時候InnoDB對這種塊錯誤是無能為力的.

有人會認為系統恢復後，MySQL可以根據redo log進行恢復，而MySQL在恢復的過程中是檢查page的checksum，checksum就是pgae的最後事務號，發生partial page write問題時，page已經損壞，找不到該page中的事務號，就無法恢復。

doublewrite buffer是InnoDB在tablespace上的128個頁（2個區）大小是2MB。為瞭解決 partial page write問題，當MySQL將臟數據flush到data file的時候, 先使用memcopy將臟數據複製到內存中的doublewrite buffer，之後通過doublewrite buffer再分2次，每次寫入1MB到共享表空間，然後馬上調用fsync函數，同步到磁碟上，避免緩衝帶來的問題，在這個過程中，doublewrite是順序寫，開銷並不大，在完成doublewrite寫入後，再將double write buffer寫入各表空間文件，這時是離散寫入，詳情如下: 。