.net Oracle Data Provider 事务回滚不起作用

发布于 2024-08-19 21:48:02 字数 619 浏览 2 评论 0原文

所以这就是问题所在。

我需要插入 tableA 并获取其新行 id。之后，我必须将该 id 插入到 tableB 中。我必须在插入 tableA 之后提交，以便当我尝试插入 tableB 时不会出现外键异常。

现在，我的理解是，如果在插入 tableB 的函数中引发异常，当 try-catch 块捕获异常时，原始插入表的操作将被回滚。它没有这样做。

我在某个地方犯了错误，但我不知道在哪里。有没有办法完成我在这里需要的东西？

try
    {

        tableAinsert.ExecuteNonQuery();
        transaction.Commit();

        id= Int32.Parse(tableAinsert.Parameters["id"].Value.ToString());

        if (vsType == "I")
        {
            tableBinsert(vsType, eventId, id);
        }

    }
    catch (Exception err)
    {
         transaction.Rollback();
        throw (err);
    }

原文

So this is the problem.

I need to insert into tableA and get its new row id. After that, I must insert that id into tableB. I must commit after the insert into tableA so that when I attempt to insert into tableB I won't get a foreign key exception.

Now, my understanding was that if an exception was raised in the function that inserts into tableB, when the try-catch block catches the exception the original insertion into the table would be rollbacked. It isn't doing that.

I am making a mistake somewhere, but I don't know where. Is there a way to accomplish what I need here?

try
    {

        tableAinsert.ExecuteNonQuery();
        transaction.Commit();

        id= Int32.Parse(tableAinsert.Parameters["id"].Value.ToString());

        if (vsType == "I")
        {
            tableBinsert(vsType, eventId, id);
        }

    }
    catch (Exception err)
    {
         transaction.Rollback();
        throw (err);
    }

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

痴情换悲伤 2024-08-26 21:48:02

在那里，我的一位同事指出了这个问题。

似乎必须使用相同的连接和相同的事务才能回滚这样的多步骤插入。并且每个事务只能有一次提交。

这意味着我的每个具有自己的连接和事务命令的函数都必须进行修改，以首先接受 OracleConnection 和 OracleTransaction 参数，并删除 commit() 代码和其他相关行。

所以修改我发布的代码，它看起来像这样

OracleConnection conn = new OracleConnection();
 // .... create your command, set the connection string, etc, etc
var transaction = conn.BeginTransaction();
cmd.Transaction = transaction;

try
{

    tableAinsert.ExecuteNonQuery();


    id= Int32.Parse(tableAinsert.Parameters["id"].Value.ToString());

    if (vsType == "I")
    {
        tableBinsert(vsType, eventId, id, conn, transaction);
    }

    transaction.Commit();  //Moved this commit to the end of the block

}
catch (Exception err)
{
     transaction.Rollback();
    throw (err);
}

there, a coworker of mine pointed out the problem.

It seems that one must use the same connection and the same transaction to be able to rollback a multi-step insert like this. And that there can only be a single commit per transaction.

This means that each of my functions that had their own connection and transaction commands had to be modified to first accept OracleConnection and OracleTransaction arguments and to strip out the commit() code and other related lines.

So modifying the code I posted, it would look like this

OracleConnection conn = new OracleConnection();
 // .... create your command, set the connection string, etc, etc
var transaction = conn.BeginTransaction();
cmd.Transaction = transaction;

try
{

    tableAinsert.ExecuteNonQuery();


    id= Int32.Parse(tableAinsert.Parameters["id"].Value.ToString());

    if (vsType == "I")
    {
        tableBinsert(vsType, eventId, id, conn, transaction);
    }

    transaction.Commit();  //Moved this commit to the end of the block

}
catch (Exception err)
{
     transaction.Rollback();
    throw (err);
}

回复收藏 0 原文

~没有更多了~