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
tSET 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
