Tag: galera

在「測試 MariaDB 上 Galera Cluster 的 Isolation」這篇提到了 Stripe 的 Kyle Kingsbury 寫了「Call me Maybe: MariaDB Galera Cluster」這篇文章，在討論 Galera Cluster 的 Isolation 問題。

Percona 的 CTO Vadim Tkachenko 寫了回應的文章：「Clarification on “Call me Maybe: MariaDB Galera Cluster”」。

看完後再跑去翻了一些資料，首先是 SQL 92 對 Isolation 的原始定義，尤其是對 REPEATABLE-READ 的定義。

REPEATABLE-READ 是透過 P2 ("Non-repeatable read") 來定義的，只要保證不會發生 P2 就是 REPEATABLE-READ 了：

P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

也就是說，T1 讀過的 row 只要在 T1 transaction 的期間內都保持一樣就可以。由於沒有多餘其他保證，所以有機會產生 P3 ("Phantom") 問題：

P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some

. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.

但 SERIALIZABLE 與 REPEATABLE-READ 的定義方式不同，並不是避免 P3 就可以叫做 SERIALIZABLE。SQL 92 定義說明必須與序列執行的結果一樣才能叫做 SERIALIZABLE：

The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

所以依照定義，SERIALIZABLE 一定可以避免 P3 發生，但避免了 P3 不代表做到 SERIALIZABLE。

另外一份資料是「MySQL :: MySQL 5.6 Reference Manual :: 13.3.6 SET TRANSACTION Syntax」對於 MySQL 5.6 中 InnoDB 實作 REPEATABLE-READ 的細節：

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention 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. See Section 14.2.2.2, “Consistent Nonlocking Reads”.

InnoDB 的 REPEATABLE-READ 會在第一次讀取時建立一份 snapshot，所以 MySQL 的 REPEATABLE-READ 有達到不產生 P2 的要求，但也因為 snapshot 的關係而不會產生 P3，不過也就僅此而已，寫入的部份還是沒有達到 SERIALIZABLE 的要求。

回到原來的文章所整理出來的測試資料 (我稍微排版過)，在這兩個不同的 transaction 同時發生時：(不同人都轉帳給 id = 8)

BEGIN; -- T1
SELECT balance FROM accounts WHERE account_id = 5;
SELECT balance FROM accounts WHERE account_id = 8;
UPDATE account SET balance = 75 WHERE account_id = 5;
UPDATE account SET balance = 125 WHERE account_id = 8;
COMMIT;

BEGIN; -- T2
SELECT balance FROM accounts WHERE account_id = 6;
SELECT balance FROM accounts WHERE account_id = 8;
UPDATE account SET balance = 80 WHERE account_id = 6;
UPDATE account SET balance = 120 WHERE account_id = 8;
COMMIT;

SERIALIZABLE 保證執行結果會與某種循序的結果相符 (可能是 T1 -> T2，也可能是 T2 -> T1)。但 REPEATABLE-READ 只保證在 transaction 裡面看到的 row 不會變化。

在 MySQL 裡，如果你想要跟 MySQL 告知「變更的結果會收到某些欄位的值的影響」，你至少要用 LOCK IN SHARE MODE 來做 (或是用 FOR UPDATE 會更清楚表示出意思)，也就是：

BEGIN; -- T1
SELECT balance FROM accounts WHERE account_id = 5 LOCK IN SHARE MODE;
SELECT balance FROM accounts WHERE account_id = 8 LOCK IN SHARE MODE;
UPDATE account SET balance = 75 WHERE account_id = 5;
UPDATE account SET balance = 125 WHERE account_id = 8;
COMMIT;

BEGIN; -- T2
SELECT balance FROM accounts WHERE account_id = 6 LOCK IN SHARE MODE;
SELECT balance FROM accounts WHERE account_id = 8 LOCK IN SHARE MODE;
UPDATE account SET balance = 80 WHERE account_id = 6;
UPDATE account SET balance = 120 WHERE account_id = 8;
COMMIT;

這樣至少會有一個 transaction 會出現 deadlock 訊息，於是就會確保是正確的結果：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

值得一提的是，Percona 文章提到的第二種解法是有問題的：

BEGIN;
SELECT balance FROM accounts WHERE account_id = 5;
SELECT balance FROM accounts WHERE account_id = 8;
UPDATE account SET balance = balance - 25 WHERE account_id = 5;
UPDATE account SET balance = balance + 25 WHERE account_id = 8;
COMMIT;

這樣「總和」會因為保證原子性 (atomicity) 所以不會產生問題，但 balance 可能會產生出負數。

所以結論是 Kyle Kingsbury 那篇文章測試的方法本來就有問題，在 SELECT 時少了 LOCK IN SHARE MODE 或是 FOR UPDATE 的要求。