n this Document
| Purpose |
| Scope |
| Details |
| What is an Autonomous Transaction? |
| Characteristics of Autonomous Transactions |
| Autonomous Transactions and Deadlocks |
| Example of Autonomous Deadlock |
| ORA-60 Deadlock Trace File |
| References |
APPLIES TO:
Oracle Database - Personal Edition - Version 8.1.7.0 and laterOracle Database - Enterprise Edition - Version 8.1.7.0 and later
PL/SQL - Version 8.1.7.0 and later
Oracle Database - Standard Edition - Version 8.1.7.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.
PURPOSE
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
This document explains how deadlocks may occur with autonomous transactions
SCOPE
This is article is intended for DBA's, Application Users and Support Engineers using autonomous transactions
DETAILS
What is an Autonomous Transaction?
An autonomous transaction is an independent transaction that can be called from within another transaction (called the main transaction). The autonomous transaction allows you to suspend the calling (main) transaction and then start the autonomous transaction to perform SQL operations and commit or undo them completely independently. Once the autonomous transaction is complete, you can then resume the calling transaction. An autonomous transaction is totally independent of the main transaction that called it. It is 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 may want to commit customer data regardless of whether the overall stock purchase goes through.
Characteristics of Autonomous Transactions
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.
- Autonomous transactions can start other autonomous transactions. There are no limits, other than resource limits, on how many levels of autonomous transactions can be called.
Autonomous Transactions and Deadlocks
As stated above, the parent and child transactions are independent of each other and therefore act as separate transactional entities. Consequently, all the rules regarding locking between two transactions apply. If a parent transaction locks a resource needed by the child transaction then a deadlock situation occurs because the main transaction is momentarily suspended while the child autonomous transaction is active. The child transaction is in turn waiting for the main transaction to free the locked resource. We therefore have a situation where the parent transaction cannot progress until the autonomous transaction completes and the autonomous transaction is waiting for the parent transaction to free the locked resource. In this case, the offending statement will automatically be rolled back with an "ORA-00060: deadlock detected while waiting for resource" error raised within the child and a trace file is created as for all ORA-60 deadlocks. As there is actually only one session involved in the deadlock, the deadlock graph in the trace file contains a single-resource graph as seen below in the ORA-60 Deadlock Trace File
The only solution in this case to rewrite the code in order to avoid deadlock situations. Try to design your code such that two independent transactions do not need to accessing the same lock at the same time or if possible commit or rollback a transaction before and after the autonomous transaction. Another possibility is to add exception handling routines to the PL/SQL code. For more information on PL/SQL error handling see:
Example of Autonomous Deadlock
-- Login to user/password , create table and populate --
sqlplus user/password
CREATE TABLE test_emp AS SELECT * FROM emp;
INSERT INTO test_emp
VALUES (9999,
'<User>',
99,
99,
SYSDATE,
10000,
0,
10);
COMMIT;
-- Create Procedure using autonomous transaction --
CREATE OR replace PROCEDURE Proc_a
AS
PRAGMA autonomous_transaction;
BEGIN
UPDATE test_emp
SET sal = sal + 5000
WHERE empno = 9999;
COMMIT;
END;
/
-- Create Main Procedure --
CREATE OR REPLACE PROCEDURE Main_p
AS
BEGIN
UPDATE test_emp
SET comm = 5000
WHERE empno = 9999;
proc_a;
COMMIT;
END;
/
-- Call Main Procedure --
SQL> exec main_p
BEGIN main_p; END;
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "PROC_A", line 5
ORA-06512: at "MAIN_P", line 4
ORA-06512: at line 1
ORA-60 Deadlock Trace File
As with other occurrences of ORA-60 deadlocks, a trace file is created that includes a deadlock graph and information on the session.
With an autonomous transaction, there is only one session involved and the deadlock graph contains only one row where both blocker and waiter are the same session as shown in the following example:
With an autonomous transaction, there is only one session involved and the deadlock graph contains only one row where both blocker and waiter are the same session as shown in the following example:
As well as the deadlock graph, the trace file contains PROCESS STATE information. The information below shows different sections of the trace file that may be of interest:
Self deadlock can occur for a number of diverse reasons. Usually the deadlock is a standard application deadlock introduced in a single session by some means. However if you encounter such a deadlock that does not match this profile then the recommended action is to consider create a Service Request with Support having collected trace diagnostics using the following article:
Document 1552194.1 ORA-00060 Deadlock Graph Not Matching any Examples: Suggested Next Steps
