Autonomous Transactions and Deadlocks (2024)

A player wrote in response to the 17 February quiz:

You state that "The autonomous transaction blocks do not share locks with the "outer" session in which the first block was executed and locks were placed on rows". My question is then: why then does the dead lock occur? It must be exactly because the blocks DO share locks. Or what?? Could you elaborate more on, why the AUTONOMOUS_TRANSACTION declaration causes the deadlock yo occur. Wouldn't it be more precise if you said something like: The three blocks each have their own scope, and as such they do not block for each other (some argument with Oracle read consistency), and without autonomous transactions no deadlocks would occur, the AUTONOMOUS_TRANSACTIONs, causes the deadlocks to occur, (but why??). Also you should comment on the role of ROLLBACK in this quiz.

The quiz presented a set of three blocks, to be executed in the specified order, in the same session, simplified as follows:

BEGIN
   ...DML statements...
END;
/

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN   
  ...DML statements...
   ROLLBACK;
END;
/

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  ...DML statements...
   ROLLBACK;
       
END;
/

The first block executes and in the process locks, say, two rows. Those uncommitted changes and the associated locks are a part of the schema's transaction.

The second and third blocks, however, use the AUTONOMOUS_TRANSACTION pragma to specify that these blocks are to be executed as autonomous transactions. As stated in the Oracle documentation:

"An autonomous transaction is an independent transaction that can be called from another transaction, called the main transaction. You can suspend the calling transaction, perform SQL operations and commit or undo them in the autonomous transaction, and then resume the calling transaction.

"Autonomous transactions are useful for actions that must be performed independently, regardless of whether the calling transaction commits or rolls back. For example, in a stock purchase transaction, you want to commit customer data regardless of whether the overall stock purchase goes through. Additionally, you want to log error messages to a debug table even if the overall transaction rolls back.

"Autonomous transactions have the following characteristics:

• "The autonomous transaction does not see uncommitted changes made by the main transaction and does not share locks or resources with the main transaction.
  
• "Changes in an autonomous transaction are visible to other transactions upon commit of the autonomous transactions. Thus, users can access the updated information without having to wait for the main transaction to commit."
  

To conclude: each block executed in a session has its own scope in terms of identifiers declared in that block. From the standpoint of a transaction, however, all blocks are part of the same "main" transaction of the session - unless you use the AUTONOMOUS_TRANSACTION pragma.

I hope this clarifies the intent and lesson of the quiz.