Question about conccurrency control and Insert

From: Stéphane Cazeaux <stephane(dot)cazeaux(at)netcentrex(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Question about conccurrency control and Insert
Date: 2003-09-10 07:34:15
Message-ID: 3F5ED3F7.2080900@netcentrex.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.


Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno BAGUETTE 2003-09-10 08:02:20 RE : How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?
Previous Message Tom Lane 2003-09-10 06:27:45 Re: Find overlapping time intervals, how?