关于并发控制和插入的问题 [英] Question about conccurrency control and Insert
问题描述
嗨
我目前使用pgsql 7.2.4（但在
pgsql 7.3.3上也有以下内容），并设置了事务级别读取已提交。
它做了很多小测试来理解并发控制是如何工作的。
让我们看看这个场景：
我们有一个名为test_count的表格和一个名为count的字段
该表包含1个计数= 1的条目
客户1：
BEGIN;
SELECT count FROM test_count FOR UPDATE; - >返回唯一条目1
...
客户2：
BEGIN;
SELECT count FROM test_count FOR UPDATE; - >此查询已被屏蔽，确定
...
我们继续：
客户1：
INSERT INTO test_count VALUES（2）;
COMMIT;
客户2 :(提交客户1后）
[被阻止的选择现在是免费的。但结果是
第一行包含1。我对这个结果感到惊讶]
SELECT count FROM test_count; - >现在返回两行，
包含" 1"，另一行包含" 2"
COMMIT;
所以我的问题是：为什么SELECT ... FOR UPDATE of client 2，当
unblocked时，只返回一行，并且同一
交易中的后续SELECT返回两排？有没有我不明白的机制？
感谢您的回复。
Hi
I currently use pgsql 7.2.4 (but the following has also been seen on
pgsql 7.3.3) with a transaction level set to "read committed".
It do a lot of little tests to understand how concurrency control works.
Let see this scenario:
We have a table named "test_count" and a field named "count"
The table contains 1 entry with count=1
Client 1:
BEGIN;
SELECT count FROM test_count FOR UPDATE; --> returns the only entry "1"
...
Client 2 :
BEGIN;
SELECT count FROM test_count FOR UPDATE; --> this query is blocked, ok
...
We continue :
Client 1:
INSERT INTO test_count VALUES (2);
COMMIT;
Client 2: (after commit of client 1)
[The select that was blocked is now free. But the result is the
first row containing "1". I''m surprised by this result]
SELECT count FROM test_count; --> now returns the two rows, on
containing "1", the other containing "2"
COMMIT;
So my question is : why the SELECT...FOR UPDATE of client 2, when
unblocked, returns only one row, and a following SELECT in the same
transaction returns two rows ? Is there a mechanisme I don''t understand ?
Thanks for your response.
推荐答案
On 2003年9月10日星期三08：34，StéphaneCazeaux写道：
On Wednesday 10 September 2003 08:34, Stéphane Cazeaux wrote:
客户1：
BEGIN;
SELECT count FROM test_count FOR UPDATE; - >返回唯一的条目1
客户端2：
BEGIN;
SELECT count FROM test_count FOR UPDATE; - >此查询被阻止，确定
我们继续：
客户端1：
INSERT INTO test_count VALUES（2）;
COMMIT;
客户2 :(在提交客户端1之后）
[被阻止的选择现在是免费的。但结果是
第一行包含1。我对这个结果感到惊讶]
SELECT count FROM test_count; - >现在返回两行，
包含1，另一行包含2
COMMIT;
所以我的问题是：为什么SELECT ... FOR客户端2的更新，当取消阻止时，只返回一行，同一个
事务中的后续SELECT返回两行？有没有我不明白的机制？
Client 1:
BEGIN;
SELECT count FROM test_count FOR UPDATE; --> returns the only entry "1"
Client 2 :
BEGIN;
SELECT count FROM test_count FOR UPDATE; --> this query is blocked, ok
We continue :
Client 1:
INSERT INTO test_count VALUES (2);
COMMIT;
Client 2: (after commit of client 1)
[The select that was blocked is now free. But the result is the
first row containing "1". I''m surprised by this result]
SELECT count FROM test_count; --> now returns the two rows, on
containing "1", the other containing "2"
COMMIT;
So my question is : why the SELECT...FOR UPDATE of client 2, when
unblocked, returns only one row, and a following SELECT in the same
transaction returns two rows ? Is there a mechanisme I don''t understand ?
Client2'的第一个SELECT在你提交INSERT之前开始，第二个
提交后SELECT开始了。由于您使用的是READ COMMITTED，因此您可以在当前对帐单之前阅读提交的交易结果*
开始*
见Ch 9.2.1（在并发控制中）了解详细信息：
"因为在Read Committed模式下，每个新查询都以新快照开始，
包括所有提交的事务即时，后续查询
同一交易将在任何情况下看到承诺的并发
交易的影响。
你会想要SERIALIZABLE交易隔离，如果你不想要这个
发生。
-
Richard Huxton
Archonet有限公司
---------------------------（播出结束）----- ----------------------
提示6：您是否搜索了我们的列表档案？
http://archives.postgresql.org
Client2''s first SELECT started before you commited the INSERT, the second
SELECT started after you commited. Since you are using READ COMMITTED you can
read the results of transactions committed *before the current statement
started*
See Ch 9.2.1 (in Concurrency Control) for details:
"Since in Read Committed mode each new query starts with a new snapshot that
includes all transactions committed up to that instant, subsequent queries in
the same transaction will see the effects of the committed concurrent
transaction in any case."
You''ll be wanting "SERIALIZABLE" transaction isolation if you don''t want this
to happen.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Richard Huxton写道：
Richard Huxton wrote:
2003年9月10日星期三08：34，StéphaneCazeaux写道：
On Wednesday 10 September 2003 08:34, Stéphane Cazeaux wrote:
客户端1：
BEGIN;
SELECT count FROM test_count FOR UPDATE; - >返回唯一的条目1
客户端2：
BEGIN;
SELECT count FROM test_count FOR UPDATE; - >此查询被阻止，确定
我们继续：
客户端1：
INSERT INTO test_count VALUES（2）;
COMMIT;
客户2 :(在提交客户端1之后）
[被阻止的选择现在是免费的。但结果是
第一行包含1。我对这个结果感到惊讶]
SELECT count FROM test_count; - >现在返回两行，
包含1，另一行包含2
COMMIT;
所以我的问题是：为什么SELECT ... FOR客户端2的更新，当取消阻止时，只返回一行，同一个
事务中的后续SELECT返回两行？有没有我不理解的机制？
Client2'在你提交INSERT之前的第一个SELECT开始，第二个
SELECT在你提交后开始。由于您使用的是READ COMMITTED，您可以在当前声明开始之前读取已提交的事务的结果*
Client 1:
BEGIN;
SELECT count FROM test_count FOR UPDATE; --> returns the only entry "1"
Client 2 :
BEGIN;
SELECT count FROM test_count FOR UPDATE; --> this query is blocked, ok
We continue :
Client 1:
INSERT INTO test_count VALUES (2);
COMMIT;
Client 2: (after commit of client 1)
[The select that was blocked is now free. But the result is the
first row containing "1". I''m surprised by this result]
SELECT count FROM test_count; --> now returns the two rows, on
containing "1", the other containing "2"
COMMIT;
So my question is : why the SELECT...FOR UPDATE of client 2, when
unblocked, returns only one row, and a following SELECT in the same
transaction returns two rows ? Is there a mechanisme I don''t understand ?
Client2''s first SELECT started before you commited the INSERT, the second
SELECT started after you commited. Since you are using READ COMMITTED you can
read the results of transactions committed *before the current statement
started*
我对此很满意，但是，如果我尝试完全相同的场景，我用
用DELETE替换INSERT，客户端2的第一个SELECT不会
返回任何行。这与UPDATE的行为相同。如果客户端1
更新了行和提交，则客户端2的第一个SELECT将返回
更新的行。为什么INSERT的行为不一样？
有关详细信息，请参阅Ch 9.2.1（在并发控制中）：
因为在Read Committed模式下，每个新查询都以新快照，包括截至该瞬间所承诺的所有交易，同一交易中的后续查询将在任何情况下都会看到已提交的并发交易的影响。
<你会想要SERIALIZABLE吗？如果您不希望这种情况发生，可以进行交易隔离。
I''m ok about this, but, if I try exactly the same scenario, where I
replace the INSERT by a DELETE, the first SELECT of the client 2 won''t
return any row. This is the same behaviour with an UPDATE. If client 1
updates the row and commits, the first SELECT of client 2 will return
the updated row. Why isn''t it the same behaviour with INSERT ?
See Ch 9.2.1 (in Concurrency Control) for details:
"Since in Read Committed mode each new query starts with a new snapshot that
includes all transactions committed up to that instant, subsequent queries in
the same transaction will see the effects of the committed concurrent
transaction in any case."
You''ll be wanting "SERIALIZABLE" transaction isolation if you don''t want this
to happen.
这篇关于关于并发控制和插入的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
