Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use the autonomous affairs of Oracle". In the daily operation, I believe that many people have doubts about how to use the autonomous affairs of Oracle. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to use the autonomous affairs of Oracle". Next, please follow the editor to study!

Autonomous affairs of Oracle

Background: recently, I encountered a query in a project that occasionally stuck for three or four seconds, but there was no error report. The amount of data queried was very small, and it was not always stuck, which is quite strange. It turned out to be the fault of autonomous affairs.

I. the meaning of autonomous affairs

Autonomous transaction: AUTONOMOUS TRANSACTION

If an independent transaction is opened in something, the internal transaction operation will not affect the uncommitted content of the external transaction of the same session, and the internal transaction will not be affected by the external transaction.

For example, if a b transaction is opened in A transaction, no matter whether A successfully commits or fails, b transaction can be committed independently of A.

II. Usage of autonomous affairs

1. The beginning of the stored procedure

PRAGMA AUTONOMOUS_TRANSACTION

To declare autonomous affairs.

2. The autonomous transaction must be committed or rolled back at the end, otherwise an error is reported:

ORA-06519: active autonomous transaction detected and rolled back

3. Autonomous transactions are generally used for logging. Regardless of whether the business (general service) successfully commits or fails to roll back. Logs (autonomous transactions) need to be committed to record the necessary parameters.

III. Deadlocks caused by improper use of autonomous affairs

Recently, I encountered a query in the project that occasionally stuck for three or four seconds, but there was no error report, and the amount of data queried was very small, and it was not always stuck, which is quite strange. It turned out to be the fault of autonomous affairs.

Outer transaction = one query interface + one autonomous transaction B

A query interface A: first delete the record N1 that meets the condition m from the temporary table. Then insert an entry to record N2 and commit it in subsequent internal autonomous transactions.

An autonomous transaction B: delete the record N1 that meets condition m from the temporary table and commit it.

Finally, the outer layer big transaction rollback.

1. When the temporary table has no corresponding deleted data, the execution is normal.

2. When the temporary table happens to have data that needs to be deleted, the outer transaction locks N1, and autonomous transaction B deletes N1, which will cause deadlock. Three seconds later the deadlock reported an error. It's over. The outer things continue to be carried out.

IV. suggestions for the use of autonomous affairs

It is best to separate the business of autonomous affairs and general affairs, and do not operate on the same table. The best application scenario is still for logging.

At this point, the study on "how to use the autonomous affairs of Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.