使用“嵌套"oracle中的交易 [英] Using "nested" transactions in oracle

问题描述

我在 Oracle 中遇到事务问题.我有一些这样的程序:

I have troubles with transactions in Oracle. I have some procedures like this:

create or replace procedure myschema.DataSave(v_value IN NUMBER)
as
begin

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

begin
insert/update/delete...
exception when OTHERS then goto error;
end;

COMMIT;
return;

<<error>>
ROLLBACK;
return;
end;
/

我正在以这种形式从 c# 项目调用此过程:

I am calling this procedures from c# project in this form:

...
string conn_str = "...";
OracleConnection con = new OracleConnection(conn_str);
con.Open();
OracleCommand cmd = new OracleCommand("", con); 

try
{
    cmd.Transaction = cmd.Connection.BeginTransaction();

    for (int i = 0; i < 10; i++)
    {
        // this condition simulates incorrect situations
        if (i == 5)
        {
            throw new Exception("Something is wrong.");
        }

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "myschema.DataSave";
        cmd.Parameters.Clear();
        cmd.Parameters.Add("v_value", OracleDbType.Int32, i, ParameterDirection.Input);
    }

    cmd.Transaction.Commit();
}
catch (Exception ex)
{
    cmd.Transaction.Rollback();
}
finally
{
    con.Close();
    con.Dispose();
}   
...

所以我试图在数据库层使用一个内部"或嵌套"事务，在应用程序层使用另一个外部"事务.但是当应用程序中的异常被抛出时，回滚不起作用(之前的数据 - 1,2,3,4 - 保留在数据库中).但为什么?我不必以这种形式使用 mssql 和存储过程来面对这个问题:

So I am trying to use one "inner" or "nested" transaction on database layer and another "outer" transaction on application layer. But when the exception in application is thrown, the rollback doesnt work (the data seved previously - 1,2,3,4 - remain in the database). But why? I didnt have to face this problem using mssql and stored procedures in this form:

create procedure myschema.DataSave
@id as int
as
begin

    begin transaction

    insert/update/delete...
    if @@error > 0 goto error

    commit transaction
    return

    error:
    rollback transaction
    return
end
go

我是 Oracle 的新手，找不到与此类似的解决方案.请有人告诉我我做错了什么.

Im new to Oracle and couldnt find solution silimilar to this. Please somebody tell me what I am doing wrong.

推荐答案

Oracle 不支持嵌套事务.如果一个事务提交，它就会提交.这就是为什么您通常不想在存储过程中提交(或回滚)事务的原因，如果您的事务语义不同，则很难在其他地方重用该过程.

Oracle doesn't support nested transactions. If a transaction commits, it commits. That's why you generally don't want to commit (or rollback) a transaction in a stored procedure, that makes it difficult to reuse the procedure elsewhere if your transaction semantics differ.

但是，您可以在过程开始时声明一个保存点，并在出现错误时回滚到该保存点.如果随后删除提交，则事务仅由应用程序代码控制，而不是由数据库代码控制

You can, however, declare a savepoint at the beginning of your procedure and rollback to that savepoint in the case of an error. If you then remove the commit, then the transaction is solely controlled by the application code not by the database code

begin
  savepoint beginning_of_proc;

  insert/update/delete...

exception 
  when OTHERS then 
    rollback to beginning_of_proc;
    raise;
end;

不过，在这种情况下，我的偏见是不在代码中设置保存点，不进行回滚，也不捕获异常，除非您正在用它做一些有用的事情.只需执行 DML，抛出任何异常，然后在您的应用程序中处理它们.

In this case, though, my bias would be not to have a savepoint in the code, not to have a rollback, and not to catch the exception unless you're doing something useful with it. Just do the DML, let any exceptions get thrown, and handle them in your application.

这篇关于使用“嵌套"oracle中的交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！