Re: autonomous transactions

On 3 Sep. 2016 20:27, "Greg Stark" <stark(at)mit(dot)edu> wrote:
>

> Well using a separate process also requires rewriting locking and
> deadlock detection since a reasonable user might expect that second
> process to have access to data locked in their current transaction.

The user is going to hit some confusing issues.

Xact1: alter table... Add column...
Xact2: inserts data with new format
Xact2: autonomous commits leaving xact1 running
Xact2: rollback

There are similar issues with FKs and and sorts. It isn't limited to schema
changes.

I don't see how autonomous xacts that inherit parent xact's uncommitted
data, locks, etc can ever really be safe. Useful, but use at your own
(considerable) risk and unlikely to be worth the issues they introduce.

If they inherit locks but don't see uncommitted data it'd be even worse.

See the autonomous commit subxact thread for more on these issues.

>
> Parallel query is currently restricted to read-only queries however.
> Autonomous transactions will certainly need to be read-write so the
> question then is what problems led to the restriction in parallel
> query and are they any more tractable with autonomous transactions?

Parallel query is fairly different because it doesn't have it's own xact.
This should mean no need to be able to wait on a separate virtualxid or
xid, no need to have their own TransactionID allocated, no complex
considerations of visibility and most importantly can't commit separately.