Re: Question about conccurrency control and Insert

It's now clearer for me. Thanks a lot for you long explanation.

--
Stéphane

Richard Huxton wrote:

>On Wednesday 10 September 2003 12:54, Stéphane Cazeaux wrote:
>
>
>>Richard Huxton wrote:
>>
>>
>>>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 ?
>>
>>
>
>Client2's first select can't decide whether to block until it has built a
>"candidate list" of rows it wants to return. Having built its list, it
>notices that one is locked by Client1 and waits to see what happens.
>
>If Client1 deletes the row, Client2 can't return it. If Client1 inserts an
>additional row, the "candidate list" is already built so it doesn't get
>included.
>
>For Client2 to notice any "new" rows, it would have to re-run the query.
>Obviously, for some queries this could be very expensive.
>If that wasn't bad enough, if another client was updating the table you'd have
>to wait again until it committed/rolled back its transaction to see what to
>do. With a busy database you could end up running the query dozens of times
>to check whether any data you were interested in had been inserted. What's
>worse, all your other clients might well be doing the same waiting for
>Client2.
>
>If you use a SERIALIZABLE transaction level, then it guarantees Client2 only
>sees data that was committed before Client2's transaction started. This means
>within your transaction, your view is guaranteed to be consistent.
>
>If you want to guarantee that Client2 sees all relevant data and that no more
>can be added while Client2 is operating, then you need to get an exclusive
>write lock on the table. Of course this can kill your performance with
>multiple updating clients.
>
>Phew! That was longer than I was expecting. Does it make it any clearer?
>Concurrency issues can get involved - there are books and university courses
>that just deal with this one topic. Hopefully I haven't got anything wrong
>above. Oh - the description of "candidate list" stuff above isn't necessarily
>precisely the way that PG does it, but that's how I think of it.
>
>HTH
>
>
>