Re: [GENERAL] Question about conccurrency control and Insert

[St�phane Cazeaux]

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.