背景
首先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
- 僅僅Read 操作讀的是歷史版本
不遵守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 的實現更符合直觀感受.
