Oracle autonomous transactions

Autonomous transactions

Summarize and analyze the problems encountered in the field. The core issue is that the Oracle transaction commit mechanism is not fully understood, and Oracle autonomous transactions are ignored.

 

Definition

1. An autonomous transaction is initiated by a parent transaction or a primary transaction. An autonomous transaction is operated independently of its parent transaction.

1. Rollback or commit is used in autonomous transactions. Errors for some reason do not affect other transactions.

1. The autonomous transaction must display the execution of commit or rollback.

Use Cases

1. Record application events/actions. You do not need to consider the results of actions. However, the processing of successful or failed records does not affect the application itself.

Test

-- Create or replace procedure wyc_at_log (log_str in varchar2) is Pragma autonomous_transaction; begin insert into wyc_autot_test (log_str) values (log_str); Commit; exception when others then rollback; end wyc_at_log;

-- Test create or replace procedure wyc_at_test isbegin insert into db_pm_gather (pk_id, proc_code) values ('1. 1', '1. 1 '); wyc_at_log ('1. 1 _ log'); insert into db_pm_gather (pk_id, proc_code) values ('2. 1', '2. 1 '); wyc_at_log ('1. 2 _ log'); if (1 = 1) Then raise_application_error (-10001, 'exception rollback'); end if; commit; Exception when others then wyc_at_log (sqlcode ); rollback; end wyc_at_test;

-- Result -- The db_pm_gather result set must be empty select * From db_pm_gather; -- two data select * From wyc_autot_test In the result set;