(Sorry for incomplete prev message).
Tom Lane wrote:
>
> I wonder whether this doesn't have a problem with concurrent access:
>
> 1. Transaction A does 'Select count into cnt', gets (say) 200.
> 2. Transaction B does 'Select count into cnt', gets 200.
> 3. Transaction A writes 201 into hits record.
> 4. Transaction B writes 201 into hits record.
>
> and variants thereof. (Even if A has already written 201, I don't think
> B will see it until A has committed...)
You're right, Tom.
> I am not too clear on MVCC yet, but I think you need "SELECT FOR UPDATE"
> or possibly an explicit lock on the hits table in order to avoid this
> problem. Vadim, any comments?
SELECT FOR UPDATE will not help: if there was no record for
particular key then nothing will be locked and two records with
the same key will be inserted.
Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE.
Vadim
