使用“嵌套的"消息.甲骨文中的交易 [英] 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.

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