MySQL Repeatable-Read 的一些誤解

背景

首先1992 年發表的SQL Standard 對隔離級别進行的定義是根據幾個異象(Dirty Read, Non-Repeatable Read, Phantom Read) , 當然這個定義非常模糊, 後面Jim Grey 也有文章說這個不合理, 然而此時MVCC, snapshot isolation 還沒被發明. 等有snapshot isolation 以後發現snapshot isolation 能夠規避Dirty Read, Non-Repeatable Read, 是以認為snapshot isolation 和 Repeatable-read 很像, 是以MySQL, Pg 把他們實作的snapshot isolation 就稱為了Repeatable-read isolation.

另外snapshot isolation 其實也沒有準确的定義, 是以MySQL 和 PG, Oracle 等等的實作也是有很大的差別的.

關于snapshot isolation 的定義:

A transaction running in Snapshot Isolation is never blocked attempting a read as long as the snapshot data from its Start-Timestamp can be maintained.The transaction's writes (updates, inserts, and deletes) will also be reflected in this snapshot, to be read again if the transaction accesses (i.e., reads or updates) the data a second time.

這裡對于snapshot isolation 的定義不論對于讀操作和寫操作都是讀取snapshot 的版本, 這也是pg, oracle 等等版本實作的, 但是InnoDB 不是這樣的. InnoDB 隻有讀操作讀取到的是snapshot 的版本, 但是DML 操作是讀取目前已送出的最新版本.

When the transaction T1 is ready to commit, it gets a Commit-Timestamp, which is larger than any existing Start-Timestamp or Commit-Timestamp. The transaction successfully commits only if no other transaction T2 with a Commit-Timestamp in T1’s execution interval [Start- Timestamp, Commit-Timestamp] wrote data that T1 also wrote. Otherwise, T1 will abort. This feature, called First- committer-wins prevents lost updates (phenomenon P4).

對于 first-committer-wins 的定義, 在si 模式下, 如果在Start-Timestamp -> Commit-Timestamp 這之間如果有其他的trx2 修改了目前trx1 修改過的内容, 并且在trx1 送出的時候, trx2 已經送出了. 那麼trx1 就會abort, 這個叫first-committer-wins.

但是InnoDB 也不是這樣的. InnoDB 并不遵守這個規則, 在repeatable read 模式下, 如果trx1, trx2 都修改了同一行, trx2 是先送出的, 那麼trx1 的送出會直接把trx2 覆寫. 而在類似PG, Oracle 實作的snapshot isolation 裡面, 則是遵守first-committer-wins 的規則.

是以InnoDB 的snapshot isolation

1. 僅僅Read 操作讀的是曆史版本
2. 1. 不遵守first-committer-wins 規則

官方把這種實作叫做Write committed Repeatable Read.

MySQL 開發者對于InnoDB repeatable-read 實作的介紹:

But when InnoDB Repeatable Read transactions modify the database, it is possible to get phantom reads added into the static view of the database, just as the ANSI description allows. Moreover, InnoDB relaxes the ANSI description for Repeatable Read isolation in that it will also allow non-repeatable reads during an UPDATE or DELETE. Specifically, it will write to newly committed records within its read view. And because of gap locking, it will actually wait on other transactions that have pending records that may become committed within its read view. So not only is an UPDATE or DELETE affected by pending or newly committed records that satisfy the predicate, but also 'SELECT … LOCK IN SHARE MODE' and 'SELECT … FOR UPDATE'.

This WRITE COMMITTED implementation of REPEATABLE READ is not typical of any other database that I am aware of. But it has some real advantages over a standard 'Snapshot' isolation. When an update conflict would occur in other database engines that implement a snapshot isolation for Repeatable Read, an error message would typically say that you need to restart your transaction in order to see the current data. So the normal activity would be to restart the entire transaction and do the same changes over again. But InnoDB allows you to just keep going with the current transaction by waiting on other records which might join your view of the data and including them on the fly when the UPDATE or DELETE is done. This WRITE COMMITTED implementation combined with implicit record and gap locking actually adds a serializable component to Repeatable Read isolation.

PG 社群對于repeatable-read 實作的介紹:

UPDATE

,

DELETE

SELECT FOR UPDATE

, and

SELECT FOR SHARE

commands behave the same as

SELECT

in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message https://www.postgresql.org/docs/13/transaction-iso.html#XACT-READ-COMMITTED

是以這裡我們看一下MySQL repeatable-read 的具體行為, 也了解MySQL社群為什麼要做這樣的實作.

mysql> create table checking (name char(20) key, balance int) engine InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into checking values ("Tom", 1000), ("Dick", 2000), ("John", 1500);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Client #1                               Client #2
=====================================   =====================================
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from checking;
+------+---------+
| name | balance |
+------+---------+
| Dick |    2000 |
| John |    1500 |
| Tom  |    1000 |
+------+---------+
3 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update checking
   set balance = balance - 250
   where name = "Dick";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update checking
   set balance = balance + 250
   where name = "Tom";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from checking;
+------+---------+
| name | balance |
+------+---------+
| Dick |    1750 |
| John |    1500 |
| Tom  |    1250 |
+------+---------+
3 rows in set (0.02 sec)
                                        mysql> begin;
                                        Query OK, 0 rows affected (0.00 sec)

                                        mysql> select * from checking;
                                        +------+---------+
                                        | name | balance |
                                        +------+---------+
                                        | Dick |    2000 |
                                        | John |    1500 |
                                        | Tom  |    1000 |
                                        +------+---------+
                                        3 rows in set (0.00 sec)
                                                                                
                                        mysql> update checking
                                           set balance = balance - 200
                                           where name = "John";
                                        Query OK, 1 row affected (0.00 sec)
                                        Rows matched: 1  Changed: 1  Warnings: 0
                                                                                
                                        mysql> update checking
                                           set balance = balance + 200
                                           where name = "Tom";

                                        ### Client 2 waits on the locked record
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
                                        Query OK, 1 row affected (19.34 sec)
                                        Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from checking;
+------+---------+
| name | balance |
+------+---------+
| Dick |    1750 |
| John |    1500 |
| Tom  |    1250 |
+------+---------+
3 rows in set (0.00 sec)
                                        mysql> select * from checking;
                                        +------+---------+
                                        | name | balance |
                                        +------+---------+
                                        | Dick |    2000 |
                                        | John |    1300 | 
                                        | Tom  |    1450 |
                                        +------+---------+
                                        3 rows in set (0.00 sec)

                                      # 這裡可以看到Tom = 1450, 而不是從上面 1000 + 200 = 1200, 
                                      # 因為update 的時候, InnoDB 實作的是write-committed repeatable, 
                                      # 不是基于場景的snapshot isolation的實作, 
                                      # write 操作是直接讀取的已送出的最新版本的資料1250, 
                                      # 而不是snapshot 中的資料1000.
                                                                                
                                        mysql> commit;
                                        Query OK, 0 rows affected (0.00 sec)

mysql> select * from checking;
+------+---------+
| name | balance |
+------+---------+
| Dick |    1750 |
| John |    1300 |
| Tom  |    1450 |
+------+---------+
3 rows in set (0.02 sec)           

這裡可以看到Tom = 1450, 而不是從上面 1000 + 200 = 1200, 因為update 的時候, InnoDB 實作的是write-committed repeatable, 不是基于場景的snapshot isolation的實作, write 操作是直接讀取的已送出的最新版本的資料1250, 而不是snapshot 中的資料1000.

對比在PG裡面, 由于PG是使用常見的 snapshot isolation 實作repeatable-read, 那麼trx2 在修改Tom 的時候, 同樣必須等待trx1 commit or rollback, 因為PG 讀取和修改基于trx 開始時候的snapshot 的record. 是以如果trx1 rollback, 那麼trx2 則會基于開始snapshot 時候的值進行修改, 也就是Tom = 1200, 如果trx1 commit, 那麼trx2 隻能rollback, 并且會傳回

ERROR: could not serialize access due to concurrent update

也就是在上面的場景下 trx2 是會rollback.

那麼MySQL 為什麼要這麼做呢?

MySQL 社群的觀點是在常見的通過snapshot isolation 來實作repeatable Read 的方案裡面, 經常會出現如果兩個事務修改了同一個record, 那麼就需要後送出的事務重試這個流程. 這種在小事務場景是可以接受的, 但是如果後送出的事務是大事務, 比如trx1 修改了1個record rec1并先送出了, 但是trx2 修改了100 行, 正好包含了rec1, 那麼常見的snapshot isolation 的實作就需要trx2 傳回錯誤, 然後重新執行這個事務. 這樣對沖突多的場景是特别不友好的.

但是Innodb 的實作則在修改rec1 的時候, 如果trx1 已經送出了, 那麼直接讀取trx1 committed 的結果, 這樣就可以避免了讓trx2 重試的過程了. 也可以達到幾乎一樣的效果.

當然這個僅僅MySQL InnoDB 是這樣的實作, 其他的資料庫都不會這樣.

兩種方案都有優缺點吧, 基于常見SI(snapshot isolation) 實作會存在更多的事務復原, 一旦兩個事務修改了同一個row, 那麼必然有一個事務需要復原, 但是InnoDB 的行為可以允許和其他trx 修改同一個record, 并且可以在其他trx 修改後的結果上進行更新, 不需要進行事務復原, 效率會更高一些, 但是基于常見的snapshot isolation 的實作更符合直覺感受.