On Mon, Aug 3, 2015 at 9:09 AM, Merlin Moncure <mmonc...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
