Re: Question about conccurrency control and Insert

Поиск
Список
Период
Сортировка
От Stéphane Cazeaux
Тема Re: Question about conccurrency control and Insert
Дата
Msg-id 3F5F10DC.3040708@netcentrex.net
обсуждение исходный текст
Ответ на Re: Question about conccurrency control and Insert  (Richard Huxton <dev@archonet.com>)
Ответы Re: Question about conccurrency control and Insert  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Question about conccurrency control and Insert  (Richard Huxton <dev@archonet.com>)
Список pgsql-general


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. 

В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Question about conccurrency control and Insert
Следующее
От: Francois Suter
Дата:
Сообщение: Re: The ..... worm