Question about conccurrency control and Insert

[Stéphane Cazeaux]

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.

[Richard Huxton]

On Wednesday 10 September 2003 08:34, Stéphane Cazeaux wrote:

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*

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

[Stéphane Cazeaux]

Richard Huxton wrote:

On Wednesday 10 September 2003 08:34, Stéphane Cazeaux wrote:

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*

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.