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

On Fri, Aug 07, 2015 at 11:26:08AM -0400, Robert Haas wrote:
> On Thu, Aug 6, 2015 at 11:04 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> > 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.
> 
> I don't accept that.  We've already GOT cases where a query can be
> suspended and other queries can be running in the same backend.  You
> can do that via cursors.  Those cases work fine, and the deadlock
> detector doesn't know anything about them.  How is this any different?

In today's scenarios, the later query cannot commit unless the suspended query
also commits.  (Changing that is the raison d'être of autonomous
transactions.)  If the autonomous transaction can interact with uncommitted
work in a way that other backends could not, crazy things happen when the
autonomous transaction commits and the suspended transaction aborts:

CREATE TABLE t (c) AS SELECT 1;
BEGIN;
UPDATE t SET c = 2 WHERE c = 1;
BEGIN_AUTONOMOUS;
UPDATE t SET c = 3 WHERE c = 1;
UPDATE t SET c = 4 WHERE c = 2;
COMMIT_AUTONOMOUS;
ROLLBACK;

If you replace the autonomous transaction with a savepoint, the c=3 update
finds no rows, and the c=4 update changes one row.  When the outer transaction
aborts, only the original c=1 row remains live.  If you replace the autonomous
transaction with a dblink/pg_background call, the c=3 update waits
indefinitely for c=2 to commit or abort, an undetected deadlock.

Suppose you make the autonomous transaction see tuples like in the savepoint
case.  The c=3 update finds no rows to update, and the c=4 update changes one
row.  When the outer transaction aborts, you have two live rows (c=1 and c=4).
Suppose you instead make the autonomous transaction see tuples like in the
dblink case, yet let c=3 ignore the lock and change a row.  If both the
autonomous transaction and the outer transaction were to commit, then you get
two live rows (c=2 and c=3).  Neither of those outcomes is acceptable, of
course.  In today's tuple update rules, c=3 must deadlock[1].  Other credible
tuple update rules may not have this problem, but nothing jumps to mind.


[1] That's not to say it must use the shmem lock structures and deadlock
detector.