The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

19 February 2011

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:

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.

2 comments:

The autonomous transaction is encapsulated within the calling transaction and must complete all outstanding business before returning to that calling transaction. In other words, an un-comitted change will not be propagated to the calling transaction.

Oracle reminds you of this by raising the error:ORA-06519 active autonomous transaction detected and rolled back

Therefore, if the autonomous transaction does any DML, it must commit or rollback the changes.

In the quiz, this error would have messed up the overall intend and purpose ;-)

Hello All,Regarding the question of the initial poster,about "why the autonomous transaction causes the deadlock to occur".

First of all, when the documentation saysthat "autonomous transactions DO NOT share lockswith the main transaction", the meaning is thatwhen the main transaction is holding a lockbefore starting an autonomous transaction,then the autonomous transaction DOES NOT "inherit" (or "share") that lock, but should instead acquire its own lock for its own actions that need such a lock, just like any other independent transaction.

And this is exactly what causes the deadlock:The autonomous transaction needs its own lock for updating a row (and WAITS for it, because the main transaction holds a lock on the same row(s)).On the other hand, the main transaction, though NOT waiting for a lock to be released, it is simply waiting for the autonomous transaction to finish, because the autonomous transaction is part of the same processing thread.

So, each one of the two transactions is prevented from proceeding because of the other one, which causes the deadlock.