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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…