Learning Objectives
- 說明交易的用途與 ACID 四個特性
- 用 START TRANSACTION、COMMIT、ROLLBACK 把多個敘述包成一個不可分割的工作單位
- 說明 autocommit 的預設行為，使用 SAVEPOINT 回到交易中的某一點
- 辨認 ROLLBACK 收不回來的情況：DDL 的隱含提交、MyISAM 表格、已經提交的錯誤處理
- 說出四種隔離等級的差別，以及 LOCK TABLES 與 SET TRANSACTION 的作用範圍
轉帳為什麼需要交易
銀行轉帳是兩個動作：從 2 號帳戶扣 1000 元，再把 1000 元加到 1 號帳戶。
UPDATE account SET balance = balance - 1000 WHERE number = 2;
UPDATE account SET balance = balance + 1000 WHERE number = 1;
如果第一個 UPDATE 執行完、伺服器剛好當機，第二個 UPDATE 沒有執行，1000 元就憑空消失了。 交易（transaction）把多個 SQL 敘述包成一個工作單位：要嘛全部成功並提交（COMMIT），要嘛全部取消並回復（ROLLBACK），回到交易開始前的狀態。
交易的另一個用途是隔離：很多使用者同時讀寫同一個資料庫時，一個交易做到一半的修改，不應該干擾其他人。本課後半段的隔離等級談的就是這件事。
在 MySQL 裡，只有 InnoDB 表格支援交易（上一課的 SHOW ENGINES，只有 InnoDB 的 Transactions 欄是 YES）。
本課使用課程的 account 表格：
CREATE TABLE account (
number INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
balance DECIMAL(10,2)
) ENGINE=InnoDB;
INSERT INTO account (balance) VALUES (0.00), (1000.00), (2000.00);
原課程的 balance 是 float。浮點數是近似值，0.1 + 0.2 不會剛好等於 0.3，金額請一律使用 DECIMAL（定點數，DECIMAL(10,2) 是共 10 位數、小數 2 位）。
ACID：交易的四個特性
| 特性 | 意思 | 以轉帳為例 |
|---|---|---|
| 原子性（Atomicity） | 交易裡的敘述是一個整體，全部執行或全部不執行 | 不會只扣款、沒入帳 |
| 一致性（Consistency） | 交易前後，資料都符合定義好的規則（主鍵、外鍵、CHECK 等） | 兩個帳戶的總金額轉帳前後相同；餘額不會違反 CHECK |
| 隔離性（Isolation） | 同時執行的交易彼此不干擾，像是一個接一個執行 | 別人查帳時，看不到轉到一半的金額 |
| 永續性（Durability） | 提交之後的修改永久保存，即使隨後當機也不會遺失 | COMMIT 成功後停電，重新啟動時轉帳仍然存在 |
InnoDB 在 COMMIT 時先把修改寫進硬碟上的 redo log，當機後重新啟動時依 redo log 復原，這是永續性的來源。
課程投影片把交易描述成「修改先寫入緩衝區，COMMIT 時才寫進資料庫」，這是方便理解的說法。 InnoDB 實際上是直接修改資料，同時把修改前的樣子記在 undo log：ROLLBACK 時依 undo log 還原，其他連線在提交前讀到的也是 undo log 裡的舊版本。
轉帳並提交
從 2 號帳戶轉 1000 元到 1 號帳戶。交易開始之後，播放器會顯示「交易進行中（尚未 COMMIT）」的標籤，表示這些修改還可以收回；COMMIT 之後標籤消失，修改正式生效。
發現錯誤，回復交易
接續上一個範例：2 號帳戶現在只剩 0 元，卻又要再轉出 1000 元。兩個 UPDATE 都執行了，查詢後發現 2 號的餘額變成負數，於是用 ROLLBACK 取消整個交易（課程的 Lab01 第 4 題）。
autocommit：每個敘述自己就是一個交易
前面的課沒有寫 START TRANSACTION，修改照樣生效，因為 MySQL 預設 autocommit = 1：
每個敘述執行完就自動提交，自己就是一個交易。START TRANSACTION 的作用是暫時停止自動提交，直到 COMMIT 或 ROLLBACK。
也可以整個工作階段關閉自動提交：
SET autocommit = 0; -- 之後的修改都不會自動提交
UPDATE …;
COMMIT; -- 或 ROLLBACK；結束後下一個敘述又自動開始新的交易
SET autocommit = 1; -- 回到預設；如果還有未提交的交易，會先自動提交
| 寫法 | 說明 |
|---|---|
START TRANSACTION |
開始一個交易；BEGIN 是同義詞，但在預存程序裡 BEGIN 有別的意思，建議寫 START TRANSACTION |
COMMIT |
提交，修改正式生效 |
ROLLBACK |
回復，取消這個交易的所有修改 |
SET autocommit = 0 |
關閉自動提交，不需要每次都寫 START TRANSACTION |
關閉 autocommit 時要記得提交：連線中斷時，尚未提交的交易會被回復。忘了 COMMIT 就離開，前面做的修改全部不算。
關閉 autocommit 之後
關閉自動提交之後，第一個 UPDATE 會自動開始一個交易，直到 ROLLBACK 才結束。
想一想：COMMIT、ROLLBACK 與 autocommit
轉帳時「2 號帳戶已經扣款，但 1 號帳戶沒有入帳」的情況不會發生，主要是靠交易的哪一個特性？
account 的 3 號帳戶原本是 2000.00。執行下面的敘述後，最後一行的結果是什麼？
SET autocommit = 0;
UPDATE account SET balance = 5000 WHERE number = 3;
ROLLBACK;
SELECT balance FROM account WHERE number = 3;
1 號帳戶原本是 0.00。下面的交易在中間建立了一張表格，最後一行的結果是什麼？
START TRANSACTION;
UPDATE account SET balance = balance + 500 WHERE number = 1;
CREATE TABLE transfer_note (id INT);
ROLLBACK;
SELECT balance FROM account WHERE number = 1;
SAVEPOINT：回到交易中的某一點
ROLLBACK 會取消整個交易。如果只想取消後半段，可以先設一個存檔點：
START TRANSACTION;
UPDATE …; -- 第一段
SAVEPOINT bonus_done; -- 設存檔點
UPDATE …; -- 第二段
ROLLBACK TO SAVEPOINT bonus_done; -- 只取消第二段，交易還沒結束
COMMIT; -- 提交第一段
ROLLBACK TO SAVEPOINT 名稱之後交易仍在進行，最後還是要 COMMIT 或 ROLLBACKRELEASE SAVEPOINT 名稱刪除存檔點，不影響資料- 交易結束（COMMIT 或 ROLLBACK）時，所有存檔點都會消失；回到不存在的存檔點會出現
ERROR 1305 (42000): SAVEPOINT … does not exist
只取消交易的後半段
交易的前半段替 1 號帳戶加 100 元獎金，設定存檔點後，又從 3 號帳戶扣 2000 元。後來決定不扣了，回到存檔點，再提交前半段。
ROLLBACK 收不回來的情況
1. DDL 敘述會隱含提交。 CREATE TABLE、ALTER TABLE、DROP TABLE、TRUNCATE TABLE、RENAME TABLE、CREATE INDEX，
以及 CREATE USER、GRANT、LOCK TABLES 等敘述，執行前會先自動提交目前的交易，而且這些敘述本身也不能回復。
所以不要在交易裡混用資料修改與表格結構的修改。
2. MyISAM 表格不支援交易。 交易裡改到 MyISAM 表格，修改當下就寫入了。ROLLBACK 時 InnoDB 的部分會還原，
MyISAM 的部分留著，MySQL 只給一個警告：Warning 1196: Some non-transactional changed tables couldn't be rolled back。
3. 發生錯誤時，MySQL 只回復出錯的那一個敘述。 交易裡某個敘述失敗（例如違反 CHECK 或主鍵重複）， 那個敘述的修改不會生效，但交易沒有結束，之前成功的敘述也還在。這時如果照樣 COMMIT，就只提交了一半。 程式必須自己檢查每個敘述的結果，出錯時執行 ROLLBACK。
（CHECK 條件從 MySQL 8.0.16 起才真正檢查；更早的版本只接受語法、不做檢查。）
DDL 讓交易提前提交
注意交易標籤在哪一步消失：CREATE TABLE 一執行，交易就被提交了。
錯誤之後照樣 COMMIT
這次 account 表格加上了 CHECK (balance >= 0)，餘額不能是負數。交易先替 1 號帳戶加 5000 元，再從只有 1000 元的 2 號帳戶扣 5000 元，第二個 UPDATE 違反 CHECK 而失敗。程式沒有理會錯誤，直接 COMMIT。
MyISAM 表格不能回復
transfer_log 是 MyISAM 表格，用來記錄轉帳；account 是 InnoDB。交易裡兩張表格都修改了，然後 ROLLBACK。
想一想：存檔點與收不回來的情況
1 號帳戶原本是 0.00。下面的交易結束後，最後一行的結果是什麼？
START TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE number = 1;
SAVEPOINT a;
UPDATE account SET balance = balance + 10 WHERE number = 1;
ROLLBACK TO SAVEPOINT a;
UPDATE account SET balance = balance + 1 WHERE number = 1;
COMMIT;
SELECT balance FROM account WHERE number = 1;
account 有 CHECK (balance >= 0)，3 號帳戶 2000.00、2 號帳戶 1000.00。執行：
START TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE number = 3;
UPDATE account SET balance = balance - 5000 WHERE number = 2; -- 違反 CHECK
COMMIT;
3 號帳戶最後是多少？
transfer_log 是 MyISAM 表格，原本沒有資料。執行下面的敘述後，最後一行的結果是什麼？
START TRANSACTION;
INSERT INTO transfer_log (fromAccount, toAccount, amount) VALUES (2, 1, 300);
UPDATE account SET balance = balance - 300 WHERE number = 2;
ROLLBACK;
SELECT COUNT(*) FROM transfer_log;
LOCK TABLES 與 UNLOCK TABLES
課程的 Lab02 用 LOCK TABLES 鎖住整張表格，避免其他連線在你修改時讀寫：
LOCK TABLES account WRITE;
SELECT balance FROM account WHERE number = 2;
UPDATE account SET balance = 1500 WHERE number = 2;
UNLOCK TABLES;
| 鎖定方式 | 自己 | 其他連線 |
|---|---|---|
READ |
只能讀，不能寫（ERROR 1099） |
可以讀，寫入要等到解鎖 |
WRITE |
可以讀寫 | 讀寫都要等到解鎖 |
- 持有 LOCK TABLES 時，只能使用鎖住的表格，用到其他表格會出現
ERROR 1100 … was not locked with LOCK TABLES - LOCK TABLES 會先隱含提交進行中的交易；START TRANSACTION 則會釋放 LOCK TABLES 的鎖定
- 連線中斷時，鎖定自動釋放
LOCK TABLES 是為 MyISAM 這類沒有交易的引擎設計的。InnoDB 用交易就好：InnoDB 只鎖住實際修改到的資料列，
其他人可以同時修改別的資料列；需要「先讀、再依讀到的值修改」時，用 SELECT … FOR UPDATE 鎖住讀到的列，直到交易結束。
本課的直譯器只有一個連線，無法呈現其他連線等待鎖定的情形，所以這一段沒有播放範例。
隔離等級
兩個交易同時讀寫同一筆資料時，可能出現三種現象：
| 現象 | 說明 |
|---|---|
| 髒讀（dirty read） | 讀到別的交易還沒提交的修改；如果對方後來 ROLLBACK，你讀到的值從來沒有真正存在過 |
| 不可重複讀（non-repeatable read） | 同一個交易裡讀同一筆資料兩次，中間別人提交了修改，兩次讀到的值不同 |
| 幻讀（phantom read） | 同一個交易裡用同樣的條件查詢兩次，中間別人新增了符合條件的列，第二次多出新的列 |
SQL 標準定義了四種隔離等級，越往下越嚴格，但同時執行的效率也越低：
| 隔離等級 | 髒讀 | 不可重複讀 | 幻讀 |
|---|---|---|---|
READ UNCOMMITTED |
可能 | 可能 | 可能 |
READ COMMITTED |
不會 | 可能 | 可能 |
REPEATABLE READ（InnoDB 預設） |
不會 | 不會 | 標準允許；InnoDB 的一般 SELECT 讀的是快照，不會看到 |
SERIALIZABLE |
不會 | 不會 | 不會 |
InnoDB 在 REPEATABLE READ 下，交易裡第一次讀取時建立一份快照，之後的一般 SELECT 都讀這份快照，所以看不到別人後來提交的修改。 SERIALIZABLE 則讓讀取也加上鎖定，別人要修改你讀過的列，就得等你的交易結束。
設定隔離等級：
SELECT @@transaction_isolation; -- 查詢目前的等級：REPEATABLE-READ
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 只影響「下一個」交易
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 影響這個連線之後的所有交易
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 影響之後才建立的連線（需要管理權限）
- 沒有寫 SESSION 或 GLOBAL 的
SET TRANSACTION只對下一個交易有效，那個交易結束後就回到原本的等級 - 交易進行中不能改隔離等級：
ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress - 舊版的系統變數
tx_isolation在 MySQL 8.0 已經移除，改用transaction_isolation
隔離等級要兩個連線同時操作才看得出差別，本課的直譯器只有一個連線，下面用兩個終端機視窗的導覽說明。
導覽：兩個終端機視窗觀察隔離等級（示意）
開兩個終端機視窗，分別稱為終端機 A 與終端機 B，都登入同一個 bank 資料庫。1 號帳戶一開始是 1000.00。每一步的標題寫著在哪個視窗執行；$ 後面的 SQL 都是在 mysql> 提示字元下輸入的。以下輸出是依 MySQL 8.0 的行為寫成的示意，不是本課直譯器的執行結果。
-
1
兩個視窗都登入 bank 資料庫
在 Ubuntu 上用
sudo mysql bank登入（其他帳號用mysql -u 帳號 -p bank）。兩個視窗是兩條獨立的連線，各自有自己的交易與隔離等級。 -
2
終端機 B：設定 READ UNCOMMITTED，開始交易
B 把自己這條連線的隔離等級設成最寬鬆的 READ UNCOMMITTED，開始交易並讀一次 1 號帳戶。
-
3
終端機 A：修改但不提交
A 開始交易，從 1 號帳戶扣 500 元，不提交。
-
4
終端機 B：讀到尚未提交的資料（髒讀）
B 再讀一次，讀到了 A 還沒提交的 500.00。
-
5
終端機 A：ROLLBACK
A 取消了修改。B 剛才讀到的 500.00 從來沒有真正存在過，這就是髒讀的危險：如果 B 依這個值做了決定，決定就是錯的。
-
6
終端機 B：改用 READ COMMITTED，開始新的交易
B 結束原本的交易，把隔離等級改成 READ COMMITTED，再開始一個交易並讀取。
-
7
終端機 A：修改並提交
A 這次沒有寫 START TRANSACTION，autocommit 下 UPDATE 執行完就提交了。
-
8
終端機 B：同一個交易讀到不同的值（不可重複讀）
B 還在同一個交易裡，第二次讀到的是 A 已提交的 500.00，和第一次的 1000.00 不同。READ COMMITTED 不會髒讀，但會不可重複讀。
-
9
終端機 B：改回 REPEATABLE READ，開始交易
B 改回 InnoDB 的預設等級，開始交易並讀取。這一次讀取建立了交易的快照。
-
10
終端機 A：再修改並提交
A 把 1 號帳戶加回 500 元，autocommit 下立即提交。
-
11
終端機 B：交易裡讀到的值不變
B 在同一個交易裡讀到的仍是快照裡的 500.00；COMMIT 結束交易之後再讀，才看到 A 提交的 1000.00。
想一想：隔離等級
在 MySQL 8.0 剛登入時執行 SELECT @@transaction_isolation;，結果是什麼？
終端機 A 修改了資料但還沒提交。終端機 B 在哪一種隔離等級下，會讀到 A 尚未提交的值？
下面的第二行會得到什麼結果？
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
動手寫：一筆完整的轉帳
bank 資料庫的 account 表格已經準備好（1 號 0.00、2 號 1000.00、3 號 2000.00）。請用一個交易從 3 號帳戶轉 500 元到 1 號帳戶並提交，最後依 number 排序列出整張表格。
重點整理
| 指令 | 作用 |
|---|---|
START TRANSACTION（或 BEGIN） |
開始交易 |
COMMIT |
提交，修改正式生效 |
ROLLBACK |
回復，取消整個交易 |
SAVEPOINT 名稱／ROLLBACK TO SAVEPOINT 名稱 |
設存檔點／回到存檔點（交易仍在進行） |
SET autocommit = 0 |
關閉自動提交（預設是 1） |
LOCK TABLES … READ／WRITE、UNLOCK TABLES |
鎖住整張表格；InnoDB 通常改用交易 |
SET [SESSION] TRANSACTION ISOLATION LEVEL … |
設定隔離等級；沒寫 SESSION 只影響下一個交易 |
- ACID：原子性、一致性、隔離性、永續性；MySQL 只有 InnoDB 支援交易
- ROLLBACK 收不回來的情況：DDL 的隱含提交、MyISAM 表格（警告 1196）、錯誤後照樣 COMMIT（MySQL 只回復出錯的敘述）
- 隔離等級由寬到嚴：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ（InnoDB 預設）、SERIALIZABLE
- 金額欄位用
DECIMAL，不用float
下一課介紹使用者帳號與權限。