實驗 MySQL 跟 PostgreSQL 在 repeatable read 下的差異

最近發現在 isolation 都設為 repeatable read 時，MySQL 與 PostgreSQL 在操作上有些不一樣的行為，以下會分成兩個實驗來比較差別。

先複習一下 Read Committed 與 Repeatable Read 的分別避免了什麼事情發生：

Read Committed 避免了兩個 transactions 同時執行時，後者讀取到未完成的修改 (dirty read)，所以在 transaction 未完成的情況下，彼此之間的修改不會互相影響，直到其中一個 transaction commit 成功，其他 transaction 才會獲取修改後的值。

Repeatable read 則是禁止 transaction 在自身沒有做修改的情況下，同樣的查詢取得不同結果（non-repeatable），簡單來說 read committed 不會讀取到未送出的修改，repeatable read 則是不論其他 transaction 完成送出與否，都不會改變讀取的值。

# PostgreSQL 實驗 (記得切換 isolation level)

# 建立測試資料
CREATE TABLE "public"."accounts" ("id" serial, PRIMARY KEY ("id"));

ALTER TABLE "public"."accounts" ADD COLUMN "own" varchar;
ALTER TABLE "public"."accounts" ADD COLUMN "balance" int4;

INSERT INTO "public"."accounts" ("own", "balance") VALUES ('A', '100');

/*
SELECT * FROM accounts;
 id | own | balance 
----+-----+---------
  1 | A   |     100 
*/
# 發起兩筆交易 (transaction)
-- 交易一
BEGIN;
UPDATE accounts SET balance = balance - 10;
SELECT * FROM accounts;
/*
 id | own | balance 
----+-----+---------
  1 | A   |      90 
*/


-- 交易二
SELECT * FROM accounts;
/*
 id | own | balance 
----+-----+---------
  1 | A   |     100 
*/

由上可以看出，repeatable read 避免了 dirty read 的問題，接著進行將交易一送出

-- 交易一
COMMIT;


-- 交易二
SELECT * FROM accounts;
/*
 id | own | balance 
----+-----+---------
  1 | A   |     100 
*/

可以看出在 isolation 層級為 repeatable read 下不會發生 non-repeatable read，那我們再嘗試在交易二去修改值

-- 交易二
UPDATE accounts SET balance = balance - 10;
/*
ERROR:  could not serialize access due to concurrent update
*/

-- 交易二
UPDATE accounts SET balance = 10;

/*
ERROR:  could not serialize access due to concurrent update
*/

可以發現 postgres 會禁止改變已經在其他交易做過更動的欄位（需要在交易二做過 read 的動作）

# MySQL 實驗

# 建立測試資料
CREATE TABLE `accounts` (`id` serial, PRIMARY KEY (id));
ALTER TABLE `accounts` ADD COLUMN `own` varchar(255) NULL;
ALTER TABLE `accounts` ADD COLUMN `balance` int NULL;

INSERT INTO `accounts` (`own`, `balance`) VALUES ('A', '100');

SELECT * FROM accounts;
/*
+----+------+---------+
| id | own  | balance |
+----+------+---------+
|  1 | A    |     100 |
+----+------+---------+
*/
# 發起兩筆交易 (transaction)
-- 交易一
BEGIN;
UPDATE accounts SET balance = balance - 10;
SELECT * FROM accounts;
/*
+----+------+---------+
| id | own  | balance |
+----+------+---------+
|  1 | A    |      90 |
+----+------+---------+
*/

-- 交易二
BEGIN;
SELECT * FROM accounts;
/*
+----+------+---------+
| id | own  | balance |
+----+------+---------+
|  1 | A    |     100 |
+----+------+---------+
*/

由上確認 MySQL 在 repeatable read 下理所當然也不會有 dirty read 問題

-- 交易一
COMMIT;

-- 交易二
SELECT * FROM accounts;
/*
+----+------+---------+
| id | own  | balance |
+----+------+---------+
|  1 | A    |     100 |
+----+------+---------+
*/

MySQL 在 repeatable read 一樣避免了 non-repeatable read

-- 交易二
UPDATE accounts SET balance = balance - 10;

SELECT * FROM accounts;

/*
+----+------+---------+
| id | own  | balance |
+----+------+---------+
|  1 | A    |      80 |
+----+------+---------+
*/

可以看到 MySQL 在一樣對同欄位做 atomic operation 操作是可以成功的，但是會發生上一步餘額還在 100 元，扣掉 10 元後瞬間變成 80 元的狀況。

MySQL 跟 PostgreSQL 都是藉由 ReadView(snapshot) 的建立時間點，來區分 read-committed 和 repeatable read，不過在 MVCC 的實作跟限制上看起來還是有很多不同，這次實驗可以發現 PostgreSQL 在 repeatable read 下可以偵測並阻止 Lost Update 的發生，感覺似乎更加嚴謹一些。

# references

https://www.postgresql.org/docs/current/transaction-iso.html (opens new window)

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html (opens new window)

PostgreSQL 實驗 (記得切換 isolation level)
MySQL 實驗
references