Re: Question about conccurrency control and Insert

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.
>
>