Re: Autonomous Transaction is back - Mailing list pgsql-hackers

On Thu, Aug 6, 2015 at 4:15 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Aug 3, 2015 at 9:09 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> hm.  OK, what's the behavior of:
>>
>> BEGIN
>>   UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>>
>>   BEGIN WITH AUTONOMOUS TRANSACTION
>>     UPDATE foo SET x = x + 1 WHERE foo_id = 1;
>>   END;
>>
>>   RAISE EXCEPTION ...;
>> EXCEPTION ...
>>
>> END;
>
> Sure, so that case might need a little bit of special handling.  That
> doesn't mean it's a good idea for heavyweight locks to conflict in
> general.  I think you're going to find that implementing the latter is
> an extremely unrewarding task, and that the benefits are seriously
> negative.  For example, consider:
>
> BEGIN
> UPDATE foo SET x = x + 1 WHERE foo_id = 1;
> BEGIN WITH AUTONOMOUS TRANSACTION
> UPDATE foo SET x = x + 1 WHERE foo_id = 2;
> END;
> END;
>
> Now, suppose that a concurrent session does LOCK TABLE foo after the
> first UPDATE and before the second one.  That's now a soft deadlock.
> But the only way the deadlock detector can see that is if the main
> transaction and the autonomous transaction have separate PGPROC
> entries, which is a design we explicitly rejected because it puts a
> tight limit on the number of ATs that can be in progress and the level
> to which those ATs can be nested.  But let's say you don't care, so we
> go back to that design.  The deadlock detector will have to be taught
> that the outer transaction can't help but wait for the inner
> transaction, so we teach it that.  Now it can see that the only way to
> resolve the deadlock without aborting any transactions is to reorder
> the lock request from the autonomous transaction ahead of the
> concurrent session that is seeking a full table lock.  So the
> autonomous transaction acquires the lock without blocking after all.
> You have exactly the same result that you would have had anyway but
> with a phenomenal amount of additional code and complexity.
>
> And for what?  In the original example, the way the deadlock is going
> to be reported is like this:
>
> ERROR: deadlock detected
> DETAIL: Process 12345 waits for ShareLock on transaction 1000; blocked
> by process 12345.
> Process 12345 waits for ShareLock on transaction 1001; blocked by process 12345.
>
> That is not a model of clarity.  On the other hand, if you just make a
> rule that attempting to update or delete a tuple that an outer
> transaction has already updated throws a bespoke error, you can do
> something like this:
>
> ERROR: tuple to be updated was already modified by a suspended outer transaction
>
> ...which has precedent in an existing message in trigger.c.
> Similarly, if you try to drop a table that the outer transaction has
> locked, the natural thing is for CheckTableNotInUse() to catch that
> and report it this way:
>
> ERROR: cannot DROP TABLE "foo" because it is being used by active
> queries in this session
>
> If you work hard enough, you can instead make that generate a deadlock
> error message, but you're going to have to work pretty hard, and the
> result is worse.
>
> I'd really like to hear some more *specific* scenarios where it's
> valuable for locks to conflict between the outer transaction and the
> AT.  I grant that tuple updates are a case where the conflict has to
> be detected somehow, but I don't accept that the lock manager is the
> best way to do that, and I don't accept that there are a large number
> of other cases that will need similar handling.

I don't necessarily disagree with what you're saying, but it's not
clear to me what the proposed behavior is.  Since the AT can commit
before the outer, ISTM *any* ungranted lock requested by the AT but
held by the outer leads to either A: functional deadlock (regardless
of implementation details) or B: special behavior.  Deadlocks would
certainly require some acrobatics to detect and resolve due to the
fact that one party to the lock is not in fact blocked on a lock but
on the outer's execution state.  So maybe the right thing to do is to
simply ignore the problem and hang both transactions until timeout or
cancel; this isn't really much different vs. ghetto dblink style AT
that is done today in my experience.

merlin