Re: Autonomous Transaction is back

On Tue, Aug 18, 2015 at 3:06 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Aug 18, 2015 at 8:17 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch <noah(at)leadboat(dot)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?

Well, that's an interesting question. Can we get away with regarding
those things as non-conflicting, as between the parent and child
transactions?

If we want to insist that they conflict, the easy case is easy: when
acquiring a lock, we can consult the local lock table hash and see if
an outer transaction already holds such a lock. If so, we error out.
The problem is when somebody else is waiting for the lock we hold.
That gets complicated, because the deadlock detector can't work out
that we're deadlocking with ourselves unless there are two separate
PGPROCs, one for the inner transaction and one for the outer
transaction, and even then it might think it can rearrange the lock
queue so that the outer transaction goes first, which in fact won't
work here.

I'm not exactly sure how to make this work out without involving the
deadlock detector, but I think we should try hard to get there.

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