Autonomous transactions for Oracle

Source: Internet
Author: User
Tags anonymous

An autonomous transaction (autonomous transaction) allows you to create a "transaction in a transaction" that can be committed or rolled back independently of its parent transaction. With an autonomous transaction, you can suspend the currently executing transaction, start a new transaction, complete some work, and then commit or roll back, all without affecting the state of the currently executing transaction. Autonomous transactions provide a new way to control transactions with PL/SQL, which can be used to:

    • Top-level anonymous block;

    • Local (procedure in process), independent or packaged functions and processes;

    • The method of the object type;

    • Database triggers.

Use examples to demonstrate how autonomous transactions work

--Create a test table for saving information [email protected]>create table t  ( MSG VARCHAR2 ( )); table created.--creating a stored procedure for an autonomous transaction [email protected]>create or replace procedure  autonomous_insert  2  as        pragma  Autonomous_transaction;---indicates an autonomous transaction statement   4  begin  5           insert into t values  (  ' Autonomous insert '  );   6          commit;  7  end;   8  /procedure created.--creating a normal stored procedure [email protected]>create or  Replace procedure nonautonomous_insert  2  as  3  begin   4          insert into t values   (  ' Nonautonomous insert '  );  5          commit;  6   end;  7  /procedure created.

Observe the behavior of Non-Self-governing transactions using PL/SQL code

[Email protected]>begin 2 insert into t values (' Anonymous Block ');  3 Nonautonomous_insert;  4 rollback;  5 end; 6/pl/sql procedure successfully completed. [Email protected]>select * from t; MSG---------------------------------------------------------------------------Anonymous blocknonautonomous Insert

A commit in the process of observing a non-autonomous transaction also commits the parent transaction that called it, and the rollback in the parent transaction does not work.

Observe the behavior of Non-Self-governing transactions using PL/SQL code

[Email protected]>delete from t;2 rows deleted. [Email protected]>commit; Commit complete.        [email protected]>begin insert INTO t values (' Anonymous Block ');        Autonomous_insert;  Rollback;end; 6/pl/sql procedure successfully completed. [Email protected]>select * from t; MSG---------------------------------------------------------------------------Autonomous Insert

As you can see, a commit in an autonomous transaction commits only its own transaction, and the statement for the parent transaction does not work, and the rollback in the parent transaction has no effect on the statements in the autonomous transaction.


This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1888528

Autonomous transactions for Oracle

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.