Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
696 views
in Technique[技术] by (71.8m points)

c# - Using "nested" transactions in 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;
/

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();
}   
...

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

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...