Question about conccurrency control and Insert
| От | Stéphane Cazeaux |
|---|---|
| Тема | Question about conccurrency control and Insert |
| Дата | |
| Msg-id | 3F5ED3F7.2080900@netcentrex.net обсуждение исходный текст |
| Ответы |
Re: Question about conccurrency control and Insert
Re: Question about conccurrency control and Insert |
| Список | 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.
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.
В списке pgsql-general по дате отправления: