Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> 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.
Well, if we added a listener, you could SELECT the desired key, and
be notified of a conflicting insert, but that's not really what
you're looking for. It does seem to me that you could solve this
one by inserting the tuple and then updating it at the end, but I
suppose you're looking to avoid the resulting dead tuple. Perhaps a
listener could be fed to a "cancel the conflicting query" routine?
In any event, the only resolution to such a conflict is to kill
something, right? And right now, a write/write conflict would occur
which would resolve it, you just want to be able to "reserve" the
slot up front, so your transaction isn't canceled after doing a
bunch of work, right?
> (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).
I don't see how that can be resolved without killing something, do
you? You would just have to replace the current deadlock with some
other form of serialization failure. (And no, I will never give up
the position that a deadlock *is* one of many forms of serialization
failure.)
> (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.
I must be missing something. Please explain how this would work
*without* serialization failures. As far as I can see, you can
replace deadlocks with some other form, but I don't see the point.
Basically, I think we should change the deadlock SQLSTATE to '40001'
and any code which needs to deal with such things treats that
SQLSTATE as meaning "that wasn't a good time to try that
transaction, try again in a bit."
Or, if you just want it to do nothing if the row already exists,
perhaps the new MERGE code would work?
> (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.
I would have thought that advisory locks covered this. In what way
do they fall short for this use case?
> (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.
Hmmmm.... Assuming fine enough granularity (like from an index for
which a range could be locked to detect the conflict) adding a
listener to the SIRead lock handling would be good for this. Well,
as long as the transactions were serializable.
-Kevin