MySQL之InnoDB存儲引擎:事務(wù)之基本實踐
事務(wù)作為數(shù)據(jù)庫的一項重要特性，這里我們簡單介紹下什么是事務(wù)，以及該如何使用
定義
MySQL的操作使用相信大家都不陌生，即是通過一條條的SQL語句去完成的。而如果我們將一條或多條對數(shù)據(jù)庫操作的SQL語句視作一個整體來看，即是所謂的Transaction事務(wù)。對于事務(wù)而言，其需要滿足四個特性——ACID
Note
- 對于MySQL數(shù)據(jù)庫而言，并不是所有的存儲引擎都支持事務(wù)。具體地，對于InnoDB、NDB存儲引擎而言，其是支持事務(wù)的；但對于MyISAM存儲引擎而言，是不支持事務(wù)的
ACID
Atomicity 原子性
事務(wù)的Atomicity原子性指的是，對于一個事務(wù)而言其是執(zhí)行的最小單位，不可以被分割。具體地，對于一個事務(wù)中的若干條SQL語句而言，要么這些SQL語句全部不執(zhí)行，要么這些SQL語句全部執(zhí)行。而不允許在一個事務(wù)中出現(xiàn)只執(zhí)行一部分SQL語句而另一部分SQL語句未執(zhí)行的情況
這里我們以A給B的賬戶轉(zhuǎn)賬10元為例進行說明。對于這個轉(zhuǎn)賬操作來說，我們可以大致分為兩個步驟
- 將A賬戶余額減去10
- 將B賬戶余額增加10
顯然上述兩個步驟要么全部執(zhí)行了，要么全部不執(zhí)行了。而其它任何的執(zhí)行情況來說，即只執(zhí)行步驟1或只執(zhí)行步驟2，顯然都是錯誤的
Consistency 一致性
數(shù)據(jù)庫始終是對真實的現(xiàn)實世界進行描述的。故如果數(shù)據(jù)庫中的數(shù)據(jù)能夠滿足現(xiàn)實世界的約束，即認為是符合Consistency一致性的。這里關(guān)于約束、規(guī)則的定義是主觀的，比如上面轉(zhuǎn)賬的例子中，如果我們可以將 A、B兩人賬戶的總額加在一起是不變 作為約束。還可以根據(jù)實際業(yè)務(wù)需要，比如根據(jù)賬戶類型借記卡還是信用卡，來考慮是否將 賬戶余額不允許為負值 作為約束。事實上對于MySQL而言，其語法也在一定程度上支持約束。比如NOT NULL非空約束
Isolation 隔離性
Isolation隔離性指的是多個事務(wù)并發(fā)執(zhí)行時不應(yīng)相互影響。同樣還是以A給B轉(zhuǎn)賬為例。不同的是，這里假設(shè)兩次轉(zhuǎn)賬操作是并發(fā)的。可以看到當兩個轉(zhuǎn)賬事務(wù)按如下方式交錯執(zhí)行時，即會出現(xiàn)一些意外的情況
A給B轉(zhuǎn)了兩次賬，共計20元。結(jié)果B的賬戶上是多了20元，可是A的賬戶上卻只少了10元。這種局面顯然是不應(yīng)該出現(xiàn)的
Durability 持久性
所謂Durability持久性是指當事務(wù)完成后對數(shù)據(jù)的修改應(yīng)該落到磁盤中，即被持久化存儲
狀態(tài)
對于事務(wù)一個而言，其通常存在下面的幾種狀態(tài)
- 活動狀態(tài)
正在執(zhí)行該事務(wù)中的數(shù)據(jù)庫操作
- 部分提交狀態(tài)
該事務(wù)中的數(shù)據(jù)庫操作雖然全部執(zhí)行完了。但此時均只是發(fā)生在內(nèi)存中，還未刷新同步到硬盤
- 提交狀態(tài)
處于部分提交狀態(tài)的事務(wù)將數(shù)據(jù)同步刷新到硬盤
- 失敗狀態(tài)
對處于活動狀態(tài)、部分提交狀態(tài)的事務(wù)而言，如果發(fā)生意外(數(shù)據(jù)庫出現(xiàn)錯誤、斷電等)或主動停止當前事務(wù)，則該事務(wù)處于失敗狀態(tài)
- 中止狀態(tài)
根據(jù)事務(wù)的原子性可知，當事務(wù)處于失敗狀態(tài)時需要通過Rollback回滾將數(shù)據(jù)庫數(shù)據(jù)恢復到執(zhí)行事務(wù)之前的狀態(tài)。當Rollback回滾操作完成后，該事務(wù)即為中止狀態(tài)
上述各狀態(tài)之間的轉(zhuǎn)換關(guān)系，如下圖所示
開啟事務(wù)
下面語句均可開啟一個事務(wù)。其中對于start transaction語句而言，其后面還可使用修飾符，若同時使用多個修飾符需通過逗號進行分隔。具體地，修飾符有以下幾種：
- READ ONLY：只讀事務(wù)
- READ WRITE：讀寫事務(wù)
- WITH CONSISTENT SNAPSHOT：一致性讀
如果不使用修飾符則默認為讀寫事務(wù)。值得一提的是，不可同時使用READ ONLY、READ WRITE修飾符
--?開啟事務(wù)
begin;
--?開啟事務(wù)
start?transaction?[修飾符];
提交事務(wù)
通過下面的SQL語句即可提交事務(wù)
--?提交事務(wù)
commit
下圖即為一個事務(wù)操作的基本流程，可以看到該事務(wù)中間只含有一條數(shù)據(jù)庫操作。事實上，在事務(wù)中可以包含若干條數(shù)據(jù)庫操作語句
中止事務(wù)
在事務(wù)中，如果發(fā)現(xiàn)某個SQL語句輸入錯誤了。可以通過下面的語句進行回滾來中止事務(wù)
rollback;
下圖即為一個事務(wù)中途中止的示例?？梢钥吹酵ㄟ^rollback回滾操作，即可將數(shù)據(jù)庫數(shù)據(jù)恢復到該事務(wù)開始之前的狀態(tài)。這里我們演示的是主動中止事務(wù)的情況。事實上，當事務(wù)在執(zhí)行過程中發(fā)生了錯誤而無法繼續(xù)執(zhí)行時，事務(wù)會自動進行回滾
保存點
在上面的例子中，我們在事務(wù)執(zhí)行時由于手抖了把SQL語句中的 "電氣工程" 寫成 "電器工程"，就不得不進行全部回滾。然后又要從頭執(zhí)行事務(wù)中所有數(shù)據(jù)庫操作，此舉顯然很麻煩。為此savepoint保存點應(yīng)運而生，我們可以在事務(wù)執(zhí)行過程中適當建立保存點。這樣后續(xù)如果需要回滾就不必回滾到事務(wù)開啟前最初的狀態(tài)，而只需回滾到指定保存點所在的狀態(tài)即可
保存點相關(guān)的SQL語句如下所示
--?建立保存點
savepoint?<保存點名稱>;
--?回滾到指定保存點，其中savepoint關(guān)鍵字可省略
rollback?to?[savepoint]?<保存點名稱>;
--?刪除指定保存點
release?savepoint?<保存點名稱>;
下圖即生動形象地反應(yīng)了關(guān)于保存點的使用姿勢
自動提交
所謂自動提交指的是，如果我們不顯式地通過 start transaction 或 begin 開啟事務(wù)的話，則對于每條SQL語句而言均視為一個事務(wù)進行執(zhí)行。該特性可通過系統(tǒng)變量autocommit進行使能控制，其默認為on。具體可通過下面的SQL語句查看、修改該系統(tǒng)變量
--?查看系統(tǒng)變量?autocommit
show?variables?like?'autocommit';
--?修改系統(tǒng)變量?autocommit
set?autocommit?=?{on|off};
如果我們不想使用自動提交功能的話，具體有以下兩種途徑實現(xiàn)
- 關(guān)閉autocommit自動提交
將系統(tǒng)變量autocommit修改為off以關(guān)閉自動提交功能。這樣數(shù)據(jù)庫會將多條SQL語句視為在同一個事務(wù)當中，直到我們顯式地使用 commit提交事務(wù) 或 rollback回滾事務(wù)
- 顯式地通過start transaction 或 begin開啟事務(wù)
當我們通過顯式地通過start transaction 或 begin開啟事務(wù)時，即使系統(tǒng)變量autocommit為on。自動提交的功能也會暫時被關(guān)閉。直到該事務(wù)被commit提交 或 rollback回滾
隱式提交
所謂隱式提交指的是，當我們通過顯式地通過start transaction、begin開啟事務(wù) 或 關(guān)閉了自動提交功能 后，如果遇到特殊類型的語句時既會導致當前事務(wù)被提交，效果等同于commit語句。這里就會常見的導致隱式提交的操作類型介紹下：
- DDL類型的SQL語句。例如create、alter、drop等語句
- 使用鎖控制相關(guān)的語句。例如lock tables、unlock tables等語句
- MySQL復制的語句。例如{start|stop|reset} slave、change master to等語句
- 使用、修改mysql庫中的表。例如{alter|create|drop|rename} user、grant、set password等語句
- 當前事務(wù)還未提交或回滾，就又通過start transaction、begin開啟一個新的事務(wù)。則上一個事務(wù)會被提交
- 加載數(shù)據(jù)的操作。例如批量導入load data語句
- MySQL是怎樣運行的