Re: Autonomous Transaction is back

Noah Misch wrote:
> > > 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.

> My starting expectation is that the semantics of an autonomous transaction
> will be exactly those of dblink/pg_background. (I said that during the
> unconference session.) The application would need to read data from tables
> before switching to the autonomous section. Autonomous transactions are then
> a performance and syntactic help, not a source of new semantics. Does any
> database have autonomous transactions that do otherwise?

Oracle behaves like that, i.e. it deadlocks with your example:

SQL> SELECT * FROM t;

C
----------
1

SQL> CREATE PROCEDURE proc2 IS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 UPDATE t SET c = 3 WHERE c = 1;
5 UPDATE t SET c = 4 WHERE c = 2;
6 COMMIT;
7 END;
8 /

Procedure created.

SQL> CREATE PROCEDURE proc1 IS
2 BEGIN
3 UPDATE t SET c = 2 WHERE c = 1;
4 proc2;
5 ROLLBACK;
6 END;
7 /

Procedure created.

SQL> CALL proc1();
CALL proc1()
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "LAURENZ.PROC2", line 4
ORA-06512: at "LAURENZ.PROC1", line 4

Yours,
Laurenz Albe