Re: locking question

Brian Hirt <bhirt(at)mobygames(dot)com> writes:
> I don't have much experience with locking, because I haven't really
> needed to use it. Any advice would be greatly helpful. Belew is
> basically the transaction I'm running -- it fails when a 2nd one starts
> while the 1st is still running.

> BEGIN WORK
> delete from blah_stats where id = 1
> insert into blah_stats select id,count(*) from blah where id = 1 group
> by id
> COMMIT WORK

That will fail if both xacts are trying to act on id 1 (I'm assuming
there are various different id values that could be involved?). The
most general solution is to add a retry loop and use SERIALIZABLE mode.
(In general, writer transactions in SERIALIZABLE mode will always need
a retry loop.)

Another possibility, if the set of id's in use is not changing fast,
is to assume that there's probably already a row with the right ID
value and just update it.

BEGIN;
UPDATE blah_stats SET count = (select count(*) from blah where
id = 1) WHERE id = 1;
IF zero rows updated THEN insert as above; END IF;
COMMIT;

You need to run this in READ COMMITTED mode so that concurrent UPDATEs
won't burp. This can still fail, if two transactions try to insert the
same new row at about the same time, but you may not care too much (the
first one probably inserted the right value, or close enough...)

Plan C is to take out a write-exclusive lock on blah_stats at the start
of the transaction. This avoids all the funny cases at the cost of
preventing concurrent updates for different ID values. If your usage
pattern is such that that's not a big hit, this is the way to go.

regards, tom lane