Re: Question about conccurrency control and Insert

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

--
Richard Huxton
Archonet Ltd