PostgreSQL中明显的事务隔离冲突 [英] apparent transaction isolation violation in postgresql

问题描述

我在CentOS Linux上使用PostgreSQL 9.3.12。

I am using PostgreSQL 9.3.12 on CentOS Linux.

我有两个连接到同一数据库的进程，默认的事务隔离级别为读取已提交。 。根据postgres文档，事务中的一个进程在提交之前不应看到另一个进程中的更改。

I have two processes connecting to the same database, using a default transaction isolation level of "read committed". According to the postgres docs, one process in a transaction should not "see" changes made by another process in a transaction until they are committed.

我看到的一个序列是：

• 进程A开始进行交易


• 进程A删除表T中的所有内容


• 进程B开始其事务


• 进程B尝试对表T中的一行进行选择更新


• 进程B来了清空（0行）并调用回滚


• 进程A从传入数据中重新填充表T


• 进程A提交其事务

• process A starts its transaction
• process A deletes everything from table T
• process B starts its transaction
• process B attempts a select for update on one row in table T
• process B comes up empty (0 rows) and calls rollback
• process A repopulates table T from incoming data
• process A commits its transaction

现在，表T应该在两个事务开始之前就已填充，并且进程B的查询应该已经出现了一行。如果这些进程不能同时运行，就可以做到这一点。

Now, table T should have been populated before both transactions began, and process B's query should have turned up one row. And it does if these processes do not run concurrently.

我的理解是，进程B应该在表T中看到所需行的旧副本，进行更改，然后这些更改应由进程A删除和重新填充表T来掩盖。我不知道为什么进程B会变成空的。

My understanding is that process B should see the old copy of the desired row in table T, makes its changes, and those changes should be clobbered by process A's deletion and repopulation of table T. I can't figure out why process B is coming up empty.

除了我自己的完全误解之外在这些前提条件中，谁能想到我会看到这种行为的另一个原因？

Beyond a complete misunderstanding by myself of these preconditions, can anyone think of another reason why I would see this behaviour?

不用担心糟糕的架构，它会消失的。我只是想了解为什么这种情况似乎违反了我所理解的读取已提交事务隔离。

Worry not about the lousy architecture, it is going away. I'm just trying to understand why this situation seems to violate the "read committed" transaction isolation as I understand it.

谢谢。

推荐答案

根据postgres文档，事务中的一个过程应该
不能看到由另一个过程在事务中所做的更改，直到提交
。

According to the postgres docs, one process in a transaction should not "see" changes made by another process in a transaction until they are committed.

---

是和否-通常，这要视情况而定。 文档严格说：

Read Committed是PostgreSQL中的默认隔离级别。

事务使用此隔离级别，因此SELECT查询（没有
a FOR UPDATE / SHARE子句）只看到在查询
开始之前提交的数据；它不会在并发事务执行查询期间看到未提交的数据或已提交的更改
。实际上，从查询
开始运行时起，SELECT
查询就可以看到数据库的快照。但是，SELECT确实会看到先前
更新在其自身事务中执行的效果，即使它们尚未提交
。还要注意，即使在单个事务内，两个连续的SELECT命令仍可以看到
的不同数据，如果
其他事务在第一个SELECT启动后提交了更改，而
在第二个SELECT开始之前提交了更改。

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.

UPDATE，DELETE， SELECT FOR UPDATE 和SELECT FOR SHARE命令
在搜索目标方面的作用与SELECT相同行：它们
将仅查找命令开始
时已提交的目标行。但是，这样的目标行可能在发现
时已被另一个并发事务更新（或删除或锁定了
）。 在这种情况下，可能的更新程序将等待第一个
更新事务提交或回滚（如果仍在
进度中）。如果第一个更新程序回滚，则其作用被否定
，第二个更新程序可以继续更新最初找到的
行。如果第一个更新程序提交，则第二个更新程序
将忽略该行，如果第一个更新程序删除了该行，否则它将
尝试将其操作应用于该行的更新版本。命令的
搜索条件（WHERE子句）重新评估为
，以查看该行的更新版本是否仍与搜索
条件匹配。如果是这样，第二个更新程序将使用
行的更新版本继续其操作。对于SELECT FOR UPDATE和
SELECT FOR SHARE，这意味着
被锁定并返回给客户端的行是更新版本。

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 command 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 would-be updater 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 second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated version of the row that is locked and returned to the client.

换句话说，只是SELECT与SELECT FOR UPDATE / DELETE / UPDATE不同。


您可以创建一个简单的测试用例来观察这种行为：< br>


会话1

In other word, simply SELECT differs from SELECT FOR UPDATE/DELETE/UPDATE.

You can create simple test case to observe that behaviour:

Session 1

test=> START TRANSACTION;
START TRANSACTION
test=> SELECT * FROM test;
 x
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)


test=> DELETE FROM test;
DELETE 10
test=>

---

现在登录另一个会话2：

test=> START TRANSACTION;
START TRANSACTION
test=> SELECT * FROM test;
 x
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)


test=> SELECT * FROM test WHERE x = 5 FOR UPDATE;

最后一条命令后 SELECT ... FOR UPDATE 会话1挂起并正在等待某事...

After the last command SELECT ... FOR UPDATE session 1 "hangs" and is waiting for something ......

返回会话1

test=> insert into test select * from generate_series(1,10);
INSERT 0 10
test=> commit;
COMMIT

---

现在当您返回会话2时，会看到以下内容：

test=> SELECT * FROM test WHERE x = 5 FOR UPDATE;
 x
---
(0 rows)


test=> select * from test;
 x
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

---

也就是说-简单的 SELECT 仍然看不到任何变化，而 SELECT ... FOR UPDATE 确实看到行已被删除。但是它看不到会话1插入的新行。

---

That is - simple SELECT still doesn't see any changes, while SELECT ... FOR UPDATE does see that rows have been deleted. But it doesn't see new rows inserted by session 1

实际上，您看到的序列是：

In fact a sequence you are seeing is:

• 进程A开始事务


• 进程A删除表T中的所有内容


• 进程B开始交易


• 进程B尝试对表T中的一行进行选择更新


• 进程B挂起并等待直到会话A进行提交或回滚


• 进程A从传入数据重新填充表T


• 进程A提交其事务


• 进程B空出（会话A提交后0行）并调用回滚

• process A starts its transaction
• process A deletes everything from table T
• process B starts its transaction
• process B attempts a select for update on one row in table T
• process B "hangs" and is waiting until session A does a commit or rollback
• process A repopulates table T from incoming data
• process A commits its transaction
• process B comes up empty (0 rows- after session A commit) and calls rollback

这篇关于PostgreSQL中明显的事务隔离冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！