Re: Concurrent MERGE

On 8/5/10 12:33 PM, Kevin Grittner wrote:
> I don't know whether this is the right time to discuss those 9
> different uses, but just so everyone knows, the SIRead locks needed
> for the SSI implementation in the current serializable patch have
> some characteristics which may be exactly what you want (if you want
> cache invalidation or some such) or may render them totally useless
> from some purposes.

Yeah, I haven't wrapped my head around your stuff enough yet. I would
say that having such locks available only for serializable transactions
limits some of the uses I'm thinking of.

Anyway, here's some of the uses I'm thinking of:

(1) Pre-insert lock: you know that you're going to insert a record with
PK="X" later in a long-running SP, so you want to lock out other inserts
of PK="X" at the beginning of the procedure.

(2) FK Locking: you plan to modify or delete a parent FK record in this
transaction, so you want to prevent any updates or inserts on its
related child records. (in my experience, FK-releated sharelocks are the
#1 cause of deadlocking).

(3) No-duplicate queueing: you want to create a queue table which
doesn't accept duplicate events, but you don't want it to be a source of
deadlocks. This is a variant of (1), but a common case.

(4) Blackouts: records of type "x" aren't supposed to be created during
period "y to y1" or while procedure "z" is running. Predicate locking
can be used to prevent this more easily than adding and removing a trigger.

(5) Debugging: (variant of 4) records of type "x" keep getting inserted
in the table, and you don't know where they're coming from. You can
predicate lock to force an error and debug it.

... that's off the top of my head.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com