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

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company