在程序中将提交放在 DML 之后有什么影响?
我在一个包中创建了一个程序,该程序在表中执行插入/删除操作,并在成功事务后提交.
像这样:
创建或替换包pac是程序 pr_test(emp_id 编号)是开始-- 一些代码如果有那么插入别的删除犯罪;结束 pr_test;结束 pac ;
我应该以 AUTONOMOUS_TRANSACTION 的身份进行此交易吗?在程序中放置这样的提交有什么影响?
解决方案把这样的commit放在程序里有什么作用?
Oracle 文档 描述了COMMIT
为:
目的
使用 COMMIT
语句来结束您当前的事务并使永久事务中执行的所有更改.交易是一个Oracle 数据库将其视为单个 SQL 语句的序列单元.此语句还会删除事务中的所有保存点并释放事务锁.
如果你有三个 PROCEDURE
并且每个都包含一个 COMMIT
语句,那么你不能运行所有三个然后,如果在后一个中发生异常,ROLLBACK
它们全部,因为前两个的更改已经 COMMIT
ted.
作为一般规则,您应该不要在 PROCEDURE
或 FUNCTION
中使用 COMMIT
但应该保留它由调用者COMMIT
事务,以便他们可以将多个操作捆绑在一起.
当然,有些用例您会想要违反此规则,但您应该分别考虑每种情况,并在违反此规则之前花时间充分了解您的业务逻辑,以便您知道什么是 COMMIT
在每个实例中都进行了标记.
我应该以AUTONOMOUS_TRANSACTION
的身份进行这笔交易吗?
一个用例是记录 - 您可能有一个 PROCEDURE
调用另一个 PROCEDURE
来记录用户的操作,无论初始操作是成功还是失败想要保留操作的日志并确保日志是 COMMIT
ted.在这种情况下,日志记录 PROCEDURE
应该是一个 AUTONOMOUS_TRANSACTION
并包含一个 COMMIT
语句,而调用语句应该(可能)两者都没有.>
因此,如果一个 PROCEDURE
的 COMMIT
始终是必需的,并且与调用者 COMMIT
的其他数据是否无关,则使 PROCEDURE
一个 AUTONOMOUS_TRANSACTION
.如果 PROCEDURE
可以捆绑在一起,然后 ROLLBACK
作为一个组,那么您不想让它们成为 AUTONOMOUS_TRANSACTION
.
I have created a procedure in a package which is doing insert/delete in the table and after successful transaction, commit is done.
like this:
create or replace package pac is
procedure pr_test(emp_id number)
is
begin
-- some code
if something then
insert
else
delete
commit;
end pr_test;
end pac ;
Should I make this transaction as AUTONOMOUS_TRANSACTION? What is the effect of placing the commit like this in program?
解决方案What is the effect of placing the commit like this in program?
The Oracle Documentation describes COMMIT
as:
Purpose
Use the
COMMIT
statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.
If you have three PROCEDURE
and each one contains a COMMIT
statement then you cannot run all three then, if an exception occurs in a latter one, ROLLBACK
them all as the changes from the first two will already be COMMIT
ted.
As a general rule, you should not use COMMIT
in a PROCEDURE
or FUNCTION
but should leave it up to the caller to COMMIT
the transaction so they can bundle multiple actions together.
There are, of course, use cases where you will want to violate this rule but you should consider each case separately and take time to fully understand your business logic before you break this rule so you know what is COMMIT
ted in each instance.
Should I make this transaction as
AUTONOMOUS_TRANSACTION
?
One use-case is logging - you may have a PROCEDURE
which calls another PROCEDURE
to log the user's actions and, regardless of whether the initial action succeeds or fails you want to keep a log of the action and ensure that log is COMMIT
ted. In this case, the logging PROCEDURE
should be an AUTONOMOUS_TRANSACTION
and contain a COMMIT
statement and the calling statement should (probably) have neither.
So, if the COMMIT
of one PROCEDURE
is always required and is independent of whether the caller COMMIT
s other data then make the PROCEDURE
an AUTONOMOUS_TRANSACTION
. If the PROCEDURE
s can be bundled together and then ROLLBACK
as a group then you do not want to make them AUTONOMOUS_TRANSACTION
s.
相关文章