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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers