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

On Tue, Aug 18, 2015 at 8:17 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch <noah@leadboat.com> wrote:
>> 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.
>
> This footnote goes to my point.
>
> It seems clear to me that having the autonomous transaction "see" the
> effects of the outer uncommitted transaction is a recipe for trouble.
> If the autonomous transaction updates a row and commits, and the outer
> transaction later aborts, the resulting state is inconsistent with any
> serial history.  I'm fairly certain that's going to leave us in an
> unhappy place.
>
> Even more obviously, ending up with two committed row versions that
> are both updates of a single ancestor version is no good.
>
> So, I agree that this scenario should be an error.  What I don't agree
> with is the idea that it should be the deadlock detector's job to
> throw that error.  Rather, I think that when we examine the xmax of
> the tuple we can see - which is the original one, not the one updated
> by the outer transaction - we should check whether that XID belongs to
> an outer transaction.

Hm: do you mean 'an' outer transaction (meaning, basically, any in
progress transaction) or the outer transaction of the AT.  I think you
mean outer transaction of the AT, which makes a lot of sense and
should be easy and fast to test.  It's like an implied NOWAIT if the
locker is the AT and the lockee is the parent.

Can you get away with only looking at tuples though?  For example,
what about advisory locks?  Table locks?


merlin