MySQL之InnoDB存儲(chǔ)引擎:事務(wù)之基本實(shí)踐

事務(wù)作為數(shù)據(jù)庫(kù)的一項(xiàng)重要特性，這里我們簡(jiǎn)單介紹下什么是事務(wù)，以及該如何使用

abstract.png概述

定義

MySQL的操作使用相信大家都不陌生，即是通過(guò)一條條的SQL語(yǔ)句去完成的。而如果我們將一條或多條對(duì)數(shù)據(jù)庫(kù)操作的SQL語(yǔ)句視作一個(gè)整體來(lái)看，即是所謂的Transaction事務(wù)。對(duì)于事務(wù)而言，其需要滿足四個(gè)特性——ACID

Note

• 對(duì)于MySQL數(shù)據(jù)庫(kù)而言，并不是所有的存儲(chǔ)引擎都支持事務(wù)。具體地，對(duì)于InnoDB、NDB存儲(chǔ)引擎而言，其是支持事務(wù)的；但對(duì)于MyISAM存儲(chǔ)引擎而言，是不支持事務(wù)的

ACID

Atomicity 原子性

事務(wù)的Atomicity原子性指的是，對(duì)于一個(gè)事務(wù)而言其是執(zhí)行的最小單位，不可以被分割。具體地，對(duì)于一個(gè)事務(wù)中的若干條SQL語(yǔ)句而言，要么這些SQL語(yǔ)句全部不執(zhí)行，要么這些SQL語(yǔ)句全部執(zhí)行。而不允許在一個(gè)事務(wù)中出現(xiàn)只執(zhí)行一部分SQL語(yǔ)句而另一部分SQL語(yǔ)句未執(zhí)行的情況

這里我們以A給B的賬戶轉(zhuǎn)賬10元為例進(jìn)行說(shuō)明。對(duì)于這個(gè)轉(zhuǎn)賬操作來(lái)說(shuō)，我們可以大致分為兩個(gè)步驟

1. 將A賬戶余額減去10
2. 將B賬戶余額增加10

顯然上述兩個(gè)步驟要么全部執(zhí)行了，要么全部不執(zhí)行了。而其它任何的執(zhí)行情況來(lái)說(shuō)，即只執(zhí)行步驟1或只執(zhí)行步驟2，顯然都是錯(cuò)誤的

Consistency 一致性

數(shù)據(jù)庫(kù)始終是對(duì)真實(shí)的現(xiàn)實(shí)世界進(jìn)行描述的。故如果數(shù)據(jù)庫(kù)中的數(shù)據(jù)能夠滿足現(xiàn)實(shí)世界的約束，即認(rèn)為是符合Consistency一致性的。這里關(guān)于約束、規(guī)則的定義是主觀的，比如上面轉(zhuǎn)賬的例子中，如果我們可以將 A、B兩人賬戶的總額加在一起是不變 作為約束。還可以根據(jù)實(shí)際業(yè)務(wù)需要，比如根據(jù)賬戶類(lèi)型借記卡還是信用卡，來(lái)考慮是否將 賬戶余額不允許為負(fù)值 作為約束。事實(shí)上對(duì)于MySQL而言，其語(yǔ)法也在一定程度上支持約束。比如NOT NULL非空約束

Isolation 隔離性

Isolation隔離性指的是多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)不應(yīng)相互影響。同樣還是以A給B轉(zhuǎn)賬為例。不同的是，這里假設(shè)兩次轉(zhuǎn)賬操作是并發(fā)的?？梢钥吹疆?dāng)兩個(gè)轉(zhuǎn)賬事務(wù)按如下方式交錯(cuò)執(zhí)行時(shí)，即會(huì)出現(xiàn)一些意外的情況

figure 1.png

A給B轉(zhuǎn)了兩次賬，共計(jì)20元。結(jié)果B的賬戶上是多了20元，可是A的賬戶上卻只少了10元。這種局面顯然是不應(yīng)該出現(xiàn)的

Durability 持久性

所謂Durability持久性是指當(dāng)事務(wù)完成后對(duì)數(shù)據(jù)的修改應(yīng)該落到磁盤(pán)中，即被持久化存儲(chǔ)

狀態(tài)

對(duì)于事務(wù)一個(gè)而言，其通常存在下面的幾種狀態(tài)

• 活動(dòng)狀態(tài)

正在執(zhí)行該事務(wù)中的數(shù)據(jù)庫(kù)操作

• 部分提交狀態(tài)

該事務(wù)中的數(shù)據(jù)庫(kù)操作雖然全部執(zhí)行完了。但此時(shí)均只是發(fā)生在內(nèi)存中，還未刷新同步到硬盤(pán)

• 提交狀態(tài)

處于部分提交狀態(tài)的事務(wù)將數(shù)據(jù)同步刷新到硬盤(pán)

• 失敗狀態(tài)

對(duì)處于活動(dòng)狀態(tài)、部分提交狀態(tài)的事務(wù)而言，如果發(fā)生意外(數(shù)據(jù)庫(kù)出現(xiàn)錯(cuò)誤、斷電等)或主動(dòng)停止當(dāng)前事務(wù)，則該事務(wù)處于失敗狀態(tài)

• 中止?fàn)顟B(tài)

根據(jù)事務(wù)的原子性可知，當(dāng)事務(wù)處于失敗狀態(tài)時(shí)需要通過(guò)Rollback回滾將數(shù)據(jù)庫(kù)數(shù)據(jù)恢復(fù)到執(zhí)行事務(wù)之前的狀態(tài)。當(dāng)Rollback回滾操作完成后，該事務(wù)即為中止?fàn)顟B(tài)

上述各狀態(tài)之間的轉(zhuǎn)換關(guān)系，如下圖所示

figure 2.png基本使用

開(kāi)啟事務(wù)

下面語(yǔ)句均可開(kāi)啟一個(gè)事務(wù)。其中對(duì)于start transaction語(yǔ)句而言，其后面還可使用修飾符，若同時(shí)使用多個(gè)修飾符需通過(guò)逗號(hào)進(jìn)行分隔。具體地，修飾符有以下幾種：

• READ ONLY：只讀事務(wù)
• READ WRITE：讀寫(xiě)事務(wù)
• WITH CONSISTENT SNAPSHOT：一致性讀

如果不使用修飾符則默認(rèn)為讀寫(xiě)事務(wù)。值得一提的是，不可同時(shí)使用READ ONLY、READ WRITE修飾符

--?開(kāi)啟事務(wù)
begin;
--?開(kāi)啟事務(wù)
start?transaction?[修飾符];

提交事務(wù)

通過(guò)下面的SQL語(yǔ)句即可提交事務(wù)

--?提交事務(wù)
commit

下圖即為一個(gè)事務(wù)操作的基本流程，可以看到該事務(wù)中間只含有一條數(shù)據(jù)庫(kù)操作。事實(shí)上，在事務(wù)中可以包含若干條數(shù)據(jù)庫(kù)操作語(yǔ)句

figure 3.png

中止事務(wù)

在事務(wù)中，如果發(fā)現(xiàn)某個(gè)SQL語(yǔ)句輸入錯(cuò)誤了?？梢酝ㄟ^(guò)下面的語(yǔ)句進(jìn)行回滾來(lái)中止事務(wù)

rollback;

下圖即為一個(gè)事務(wù)中途中止的示例?？梢钥吹酵ㄟ^(guò)rollback回滾操作，即可將數(shù)據(jù)庫(kù)數(shù)據(jù)恢復(fù)到該事務(wù)開(kāi)始之前的狀態(tài)。這里我們演示的是主動(dòng)中止事務(wù)的情況。事實(shí)上，當(dāng)事務(wù)在執(zhí)行過(guò)程中發(fā)生了錯(cuò)誤而無(wú)法繼續(xù)執(zhí)行時(shí)，事務(wù)會(huì)自動(dòng)進(jìn)行回滾

figure 4.png

保存點(diǎn)

在上面的例子中，我們?cè)谑聞?wù)執(zhí)行時(shí)由于手抖了把SQL語(yǔ)句中的 "電氣工程" 寫(xiě)成 "電器工程"，就不得不進(jìn)行全部回滾。然后又要從頭執(zhí)行事務(wù)中所有數(shù)據(jù)庫(kù)操作，此舉顯然很麻煩。為此savepoint保存點(diǎn)應(yīng)運(yùn)而生，我們可以在事務(wù)執(zhí)行過(guò)程中適當(dāng)建立保存點(diǎn)。這樣后續(xù)如果需要回滾就不必回滾到事務(wù)開(kāi)啟前最初的狀態(tài)，而只需回滾到指定保存點(diǎn)所在的狀態(tài)即可

保存點(diǎn)相關(guān)的SQL語(yǔ)句如下所示

--?建立保存點(diǎn)
savepoint?<保存點(diǎn)名稱>;
--?回滾到指定保存點(diǎn)，其中savepoint關(guān)鍵字可省略
rollback?to?[savepoint]?<保存點(diǎn)名稱>;
--?刪除指定保存點(diǎn)
release?savepoint?<保存點(diǎn)名稱>;

下圖即生動(dòng)形象地反應(yīng)了關(guān)于保存點(diǎn)的使用姿勢(shì)

figure 5.png

自動(dòng)提交

所謂自動(dòng)提交指的是，如果我們不顯式地通過(guò) start transaction 或 begin 開(kāi)啟事務(wù)的話，則對(duì)于每條SQL語(yǔ)句而言均視為一個(gè)事務(wù)進(jìn)行執(zhí)行。該特性可通過(guò)系統(tǒng)變量autocommit進(jìn)行使能控制，其默認(rèn)為on。具體可通過(guò)下面的SQL語(yǔ)句查看、修改該系統(tǒng)變量

--?查看系統(tǒng)變量?autocommit
show?variables?like?'autocommit';
--?修改系統(tǒng)變量?autocommit
set?autocommit?=?{on|off};

如果我們不想使用自動(dòng)提交功能的話，具體有以下兩種途徑實(shí)現(xiàn)

• 關(guān)閉autocommit自動(dòng)提交

將系統(tǒng)變量autocommit修改為off以關(guān)閉自動(dòng)提交功能。這樣數(shù)據(jù)庫(kù)會(huì)將多條SQL語(yǔ)句視為在同一個(gè)事務(wù)當(dāng)中，直到我們顯式地使用 commit提交事務(wù) 或 rollback回滾事務(wù)

• 顯式地通過(guò)start transaction 或 begin開(kāi)啟事務(wù)

當(dāng)我們通過(guò)顯式地通過(guò)start transaction 或 begin開(kāi)啟事務(wù)時(shí)，即使系統(tǒng)變量autocommit為on。自動(dòng)提交的功能也會(huì)暫時(shí)被關(guān)閉。直到該事務(wù)被commit提交 或 rollback回滾

隱式提交

所謂隱式提交指的是，當(dāng)我們通過(guò)顯式地通過(guò)start transaction、begin開(kāi)啟事務(wù) 或 關(guān)閉了自動(dòng)提交功能 后，如果遇到特殊類(lèi)型的語(yǔ)句時(shí)既會(huì)導(dǎo)致當(dāng)前事務(wù)被提交，效果等同于commit語(yǔ)句。這里就會(huì)常見(jiàn)的導(dǎo)致隱式提交的操作類(lèi)型介紹下：

• DDL類(lèi)型的SQL語(yǔ)句。例如create、alter、drop等語(yǔ)句
• 使用鎖控制相關(guān)的語(yǔ)句。例如lock tables、unlock tables等語(yǔ)句
• MySQL復(fù)制的語(yǔ)句。例如{start|stop|reset} slave、change master to等語(yǔ)句
• 使用、修改mysql庫(kù)中的表。例如{alter|create|drop|rename} user、grant、set password等語(yǔ)句
• 當(dāng)前事務(wù)還未提交或回滾，就又通過(guò)start transaction、begin開(kāi)啟一個(gè)新的事務(wù)。則上一個(gè)事務(wù)會(huì)被提交
• 加載數(shù)據(jù)的操作。例如批量導(dǎo)入load data語(yǔ)句

參考文獻(xiàn)

1. MySQL是怎樣運(yùn)行的