Backend 淺談 database Transaction Isolation Levels
在資料庫管理系統中有 4 個很重要的特性被稱為 ACID(Atomicity, Consistency, Isolation, Durability) ，也就是不可分割性、一致性、隔離性、持久性，這 4 個重要特性是為了確保每個 Transaction 操作結果都是正確、可靠的。
其中 Isolation 是為了防止多個 Transactions 同時執行導致資料不一致的情況，而 Isolation 中又有所謂的 Isolation Levels ，根據 SQL-92 的標準分為 4 種級別：
- Repeatable Read
- Read Committed
- Read Uncommitted
- Serializable
p.s. MySQL 的 InnoDB 預設的 Isolation Level 是 Repeatable Read
p.s. PostgreSQL 預設的 Isolation Level 是 Read Committed
接著一一介紹這 4 種 Isolation Levels 各自有什麼不同。
Repeatable Read
什麼是 Repeatable Read 呢？
Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other
根據 MySQL 文件說明，如果在同一個 Transaction 內的 nonblocking read 會是第 1 個 read 的 snapshot ，所以相同的 read query 都會取得跟第 1 個 read 一樣的結果。
光看文字其實難以想像，但做個實驗就能知道是怎麼回事。
首先 create table 新增幾筆資料準備做實驗：
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
接著 SQL 模擬 2 個 Transactions 同時正在執行（p.s. 請開 2 個不同的連線)，2 個都 select b = 3 的資料：
-- transaction 1
START TRANSACTION;
SELECT * FROM t WHERE b = 3;
-- transaction 2
START TRANSACTION;
SELECT * FROM t WHERE b = 3;
可以看到 2 個 Transaction 都正常取得 b = 3 的資料：
再來 transaction 1 新增一筆資料 a = 6, b = 3 的資料，然後 commit ：
-- transaction 1
INSERT INTO t VALUES (6, 3);
COMMIT;
SELECT * FROM t;
此時資料庫已經有 a = 6, b = 3 的資料了，但在 transaction 2 再 select 一次 b = 3 的情況的話，會發現依然只有 2 筆資料。
-- transaction 2
SELECT * FROM t WHERE b = 3;
這就是先前所提的，在同一個 Transaction 內的 nonblocking read 會是第 1 個 read 的 snapshot ，所以相同的 read query 都會取得跟第 1 個 read 一樣的結果，只要這個 transaction 尚未結束，相同的 query 就會一直重複。
那要怎麼取得最新的資料？很簡單，只要把 transaction 2 commit 之後，再 select 一次 b = 3 的情況就發現結果正確了！因為此時已經不在同一個 transaction 內了！
-- transaction 2
COMMIT;
START TRANSACTION;
SELECT * FROM t WHERE b = 3;
所以未來要是遇到這種 transaction 內的相同 select 總是拿不到最新資料的情況，就要直覺想到是不是 Repeatable Read 的影響。
Read Committed
了解 Repeatable Read 之後，就能很迅速理解 Read Committed 。因為 Read Commited 即使在同一個 Transaction 內使用相同的 select 會拿到最新的資料。
可以利用 Repeatable Read 的實驗再做一次試試，不過 transaction 2 START TRANSACTION; 之前需先執行以下 SQL ，變更 isolation level:
-- transaction 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Read Uncommitted
如其名， Read Uncommitted 可以讀取到其他 transactions 尚未 commit 時的資料，這被稱為 dirty read ，因為讀取尚未 commit 的資料會有些風險，這些資料在 commit 之前仍有可能還會變化，或者最後被 rollback ，導致此時讀取到的資料與資料庫最新狀態不一致。除了 dirty read 這問題之外，其他行為倒跟 Read Committed 一樣。
同樣可以利用 Repeatable Read 的實驗試試，不過 transaction 2 START TRANSACTION; 之前需先執行以下 SQL ，變更 isolation level，然後在 transaction 1 還沒 commit 之前，用 transaction 2 select 看看能不能讀取到 transaction 1 尚未 commit 前的資料。
-- transaction 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Serializable
此 isolation level 在 autocommit 未啟用的情況下，會將所有的 select 都視為 select ... for share 的形式處理。此時，所有的 transactions 都能讀取資料，但是如果有其中 1 個 transaction 欲更新資料，就必須等其他 transactions 都 commit 了才行。另外，如果有其中 1 個 transaction 更新資料但是尚未 commit ，其他的 transactions 如要讀取資料，就得等該 transaction commit 。
Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
可以想見此種 isolation level 是最嚴格的，在效能上也會造成影響，在使用上需多加注意。
總結
本文僅簡單介紹 4 isolation levels ，在各個 DBMS 文件中也提供更深入的介紹，包含在不同的 isolation level 中，會因為不一樣的 locking 有不同的作用，例如 MySQL 的 Transaction Isolation Levels 提到各種 isolation level 中 locking 的差異，建議可以花些時間閱讀。
References
https://en.wikipedia.org/wiki/SQL-92
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_transaction