sql - 使用 Read Committed 隔离级别，SELECT 能否在一个事务中得到不同的结果？

Question

这里是来自postgresql 9.6 docs的完整段落关于读取提交的隔离级别:

Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run. However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed. Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts.

---

所以基本上:

SELECT query sees only data committed before the query began and never sees changes committed during query execution by concurrent transactions.

但在最后一句话中指出:

Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts.

对我来说，这看起来很矛盾。有人可以详细说明吗？两个 SELECT 查询究竟如何在一个事务中看到不同的数据？事务不是隔离的吗？

Answer 1

是的，这是真的。为了避免这种情况，您需要使用更高的隔离级别:“可重复读取”。如果您需要完全隔离事务，甚至可以“序列化”。请记住，更高的隔离度会通过性能支付更高的成本。

在这里你可以找到详细的解释:https://www.postgresql.org/docs/9.1/static/transaction-iso.html

所以这是一个例子，它是如何发生的:

1. Connection1 打开事务并从 Table1 中读取一行“选择 *”

2. Connection2 更新同一行并提交

3. Connection1 再次读取同一行并获取更新的数据

隔离级别是“读取已提交”，因此从字面上看，所有已提交的内容对其他连接都是可见的。

如果由于某种原因你不能使用更高的隔离级别，有一种方法可以防止这种“意外”更新的发生:你的 Connection1 可以使用“select ... for update”来代替。这将有效地锁定该行，直到 Connection1 的事务提交或回滚。因此 Connection2 将等待此提交或回滚能够更新该行。