MySQL-2
🐬 外鍵、交易與隔離等級的那些意外行為
資料庫平常安安靜靜地把資料存好，但只要牽涉到外鍵、交易或並行讀寫，MySQL 就會出現一些跟直覺不一樣的行為，甚至讓其他主流資料庫的工程師都嚇一跳。這篇整理四個真實會踩到的情境，從外鍵鎖定的意外連坐、交易遇到 DDL 直接失效，到兩個隔離等級案例揭露的資料錯亂風險。
外鍵的本意：維持兩張表的資料一致性
外鍵（foreign key）的用意是讓使用系統的人知道兩張表是相關的，更重要的是讓資料庫本身也「意識到」這層關係。當其中一張表的資料被改動時，資料庫要負責維持兩者之間該有的一致性，而不是各改各的。
下圖是一個常見的例子，買家表與訂單表，訂單表裡的買家欄位是指向買家表的外鍵。
外鍵設定了 ON UPDATE CASCADE，代表系統要保證所謂的參照完整性（referential integrity）。也就是說，一旦外鍵被更改，資料的完整性就必須被守住。實務上的效果是，如果訂單表裡正在修改買家欄位，買家表對應的那筆資料就會被鎖住，不能同時被修改。
意外的連坐：改訂單狀態也會鎖到買家表
如果修改的其實是訂單的狀態欄位，理論上跟買家表完全無關，但實際測試會發現買家表依然被鎖住。
問題出在 INDEX：訂單表裡有一個「買家欄位加訂單狀態」的複合索引，而 MySQL 把訂單狀態也一併當作外鍵的一部分來管制，導致修改訂單狀態時，買家表也被牽連鎖住，這個行為相當令人意外。
已經有人把這個現象提交成官方 Bug 回報：Bug #94148。有網友找到的解法是，額外加一個「純買家欄位」的索引，結果就意外地不再被鎖住。索引與外鍵原本是兩個不相干的概念，卻在 MySQL 的實作裡被混在一起，變成解法也帶著一點意外的味道。
當交易遇上 DDL：一個會悄悄失效的 Rollback
SQL 語法大致分成兩種類型，一種是定義資料結構的 DDL，例如 CREATE TABLE、DROP TABLE；另一種是大家熟悉的 DML，例如 SELECT、UPDATE。在 MySQL 裡，交易（transaction）機制對這兩種語法的保護程度並不一樣，以下用一個常見的資料搬遷情境來說明。
old_data 備份到 new_data這一步是單純的資料搬移，屬於 DML 操作。
old_data（這一步是 DDL）因為是結構層級的操作，凌駕於交易之上，不受交易的 rollback 保護。
如果這一步不小心失敗，理論上整包交易都應該回滾。
實際結果：如果 audit table 那一步寫入失敗，整個交易理應回滾到最初的狀態，但因為第二步刪除 old_data 是 DDL，它凌駕於交易之上，不會被回滾撤銷。最後的結果是舊資料真的不見了，但整個搬遷操作卻沒有真正完整執行到，資料等於是憑空消失的。
案例一：明明查詢是空表，修改卻成功了
資料庫裡目前有一張空的使用者資料表，小左和小右兩位工程師幾乎同時開啟了各自的交易（transaction）。以下是實際發生的順序。
小右在自己的交易裡，往這張表寫入了 3 個新使用者，隨後執行 COMMIT。此時資料庫裡實際上已經存在這 3 筆資料。
小左在自己尚未提交的交易裡，執行 SELECT 查詢這張表，結果看到的使用者數量是 0 個。原因是 MySQL 預設的隔離等級是可重複讀（Repeatable Read），小左的交易一開始就建立了一份資料快照，這份快照停留在交易剛開始時的狀態，因此即使小右已經提交了新資料，小左依然只能看到空表。
雖然查出來是空表，小左還是被交辦「幫第一個使用者改名字」的任務，於是直接執行 UPDATE users SET name='新名字' WHERE id=1;，結果系統提示修改成功。原因是純查詢用的是快照讀，讀的是歷史資料，但 UPDATE、DELETE、INSERT 這類寫入操作用的是當前讀，會直接讀取資料庫裡最新、最真實的資料。因為小右已經提交了資料，最新資料裡確實存在 id=1 的使用者，小左的修改指令自然就找到了這筆紀錄並完成改名。
小左不放心，再次查詢整張表，這次看到了 1 個使用者，正是剛剛被自己改過名字的那一筆。根據 MySQL 的多版本併發控制（MVCC）規則，一個交易永遠可以看見自己做出的修改，該筆資料被小左修改後，最新版本的交易編號就變成小左的交易編號，因此小左的快照讀在此時被允許看見這筆被修改的資料。至於另外兩筆使用者，因為從未被小左修改過，對小左的歷史快照而言依然被隔離在外，所以看不到。
最終結局：直到下班，小左都以為這張表裡只有 1 個使用者，也就是自己改過名字的那一個，完全不知道實際上已經有 3 個使用者存在。這個現象的根源，是 MySQL 在可重複讀等級下，把快照讀與當前讀的機制混用，進而打破了原本應該嚴格的隔離性。
案例二：查出來是乾淨資料，寫進備份表卻變髒了
這天剛好是愚人節，小左和小右同樣在差不多時間開啟了各自的交易。小右的任務是整活，要把所有使用者的名字都改成「哈基米」；小左的任務是備份，要把目前的使用者資料備份到另一張表，等愚人節活動結束後用來恢復原始資料。
小右很快地在交易中把所有使用者名字都改成「哈基米」，執行 COMMIT 後就下班了。此時資料庫裡最新、最真實的使用者名稱已經全部變成「哈基米」。
小左在動手備份前，先執行單純的查詢 SELECT * FROM users;，看到的依然是整活前的原始資料。原因跟案例一相同，小左的交易在小右提交前就已經開啟，MySQL 的可重複讀利用快照擋住了小右的修改。小左因此鬆了一口氣，心想查出來的是乾淨的原始資料，備份應該不會有問題。
小左寫下了開發中很常見的一行指令：INSERT INTO backup_table SELECT * FROM users;，隨後安心下班。
第二天愚人節活動結束，小左準備用備份表恢復資料，打開一看卻發現備份表裡存的全部都是「哈基米」，原始資料徹底消失，根本無法恢復。
MySQL 的雙重標準：小左明明在備份前用 SELECT 親眼查出原始資料，為什麼用 INSERT ... SELECT 寫進去就變成了「哈基米」。原因在於 MySQL 一個很反直覺的底層設計。純查詢語句（如 SELECT）會乖乖遵守可重複讀規則，讀取歷史快照，所以小左看到了原始資料。但查詢加寫入的語句（如 INSERT ... SELECT）規則會突然切換，只要指令裡同時包含查詢與寫入，查詢的部分就會自動降級成讀已提交（Read Committed）規則執行。因為小左的備份指令是 INSERT ... SELECT，裡面的 SELECT 部分不再看歷史快照，而是直接執行當前讀，讀到了小右已經提交的最新資料，也就是「哈基米」，導致真正寫入備份表的全部是整活後的髒資料。
為什麼這很危險：其他主流資料庫如 PostgreSQL、Oracle、SQL Server，都不會因為多寫了一個 INSERT 就偷偷改變 SELECT 的讀取規則，機制的一致性是這些資料庫的基本要求。更麻煩的是，這種因為隔離性混用導致的資料錯亂永遠不會報錯，只會靜悄悄地在背後寫入與預期不符的資料，代表整個業務系統可能已經在用錯誤的資料下錯誤的結論，卻完全沒有察覺。整體來說，小左會被坑，是因為相信了 MySQL 聲稱的可重複讀隔離等級，卻不知道 MySQL 在處理 INSERT ... SELECT 這類語句時會悄悄切換規則。
換成 PostgreSQL 或 MS SQL Server，結果會不一樣嗎
在 PostgreSQL 和 MS SQL Server 這類主流資料庫上，機制的一致性是設計上的底線，不會像 MySQL 一樣在同一個隔離等級下，因為指令種類不同就偷偷切換底層的讀取規則。以下拆解如果換成這兩個資料庫，前面兩個案例會如何處理。
先了解預設隔離等級的差異
有一個根本性的差別，MySQL 預設使用可重複讀（Repeatable Read），而 PostgreSQL 和 MS SQL Server 預設使用的都是讀已提交（Read Committed）。如果用預設的讀已提交執行，小左在做任何操作前，只要小右已經提交，小左的 SELECT 就會直接看到最新提交的資料。也就是說，小左一開始就會看到那 3 個使用者（案例一）或「哈基米」（案例二），根本不會產生資訊落差，邏輯上非常直觀，不會出現 MySQL 那種先看到舊資料、後來又冒出新資料的落差。
如果都手動切換到可重複讀，公平比較會怎樣
假設把 PostgreSQL 和 MS SQL Server 也手動切換到可重複讀（快照隔離）等級，兩者的底層機制會表現得相當嚴謹，完美避開 MySQL 的陷阱。
案例一：盲改空表
PostgreSQL 嚴格遵守「只能修改快照裡看得見的資料」，執行 UPDATE ... WHERE id=1 時，因為快照裡是空表，不存在 id=1，這個 UPDATE 會直接回傳影響 0 行。如果交易試圖修改一筆在交易開始後被其他交易修改並提交的資料，PostgreSQL 甚至會直接拋出併發更新序列化錯誤，強制回滾。MS SQL Server 在傳統悲觀鎖等級下，小左查詢空表時就已經鎖定該範圍，小右的 INSERT 會被阻塞直到小左交易結束；若使用樂觀的快照隔離，行為則與 PostgreSQL 相同，UPDATE 會因為快照中找不到資料而影響 0 行，不會出現 MySQL 那種當前讀的意外行為。
案例二：備份表變髒
PostgreSQL 在可重複讀等級下，所有讀取操作都強制對齊同一份快照，不會因為多寫了 INSERT 就把裡面的 SELECT 偷偷降級成讀已提交。執行 INSERT INTO backup_table SELECT * FROM users; 時，裡面的 SELECT 依然讀取小左一開始建立的歷史快照，寫入備份表的會是百分之百的原始資料，備份成功。MS SQL Server 同樣地，在可重複讀或快照隔離下，INSERT ... SELECT 中的 SELECT 部分會嚴格受到交易歷史快照的保護，寫入備份表的同樣會是原始資料，不受小右後來提交的「哈基米」影響。
機制一致性的差異
MySQL 的機制不一致，純查詢用可重複讀走快照讀，混合寫入時 SELECT 卻悄悄降級成讀已提交走當前讀，導致資料在不知不覺中錯亂。PostgreSQL 與 MS SQL Server 的機制高度一致，是可重複讀就是可重複讀，所有讀取一律看快照，不允許「看著一份資料，卻寫入另一份資料」的情況發生。
