Re: Question about conccurrency control and Insert

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