子女父母交易回滚 [英] Child Parent Transactions roll back

问题描述

我有一个场景，我必须处理多个.sQL文件，每个文件包含3-4个插入或更新查询，现在当文件中的任何查询失败时，我就会执行rollback整个事务意味着整个文件回滚，然后提交该文件之前执行的所有其他文件，我想要一个选项，用户可以rollback整个事务表示已执行文件中的所有查询以及该特定文件中包含错误的所有已执行文件，以及用户是否要执行跳过带有错误的特定文件，我们将仅rollback一个包含错误的文件，所有其他文件都将提交，我现在正在使用SQL事务，没有TransactionScope，但是显然，如果需要并且可能的话，我也可以切换TransactionScope()， 目前，我的代码(我想要的是伪代码)如下

I have a scenario in which I have to process multiple .sQL files, every file contains 3-4 insert or Update queries, now when any query in a file fails I do rollback whole transaction means whole file we be rolled back , and all other files executed before that file will get committed, I want an option where user can rollback entire transaction means all queries in a file executed and all files executed before that particular file containing error, and if user wants to skip that particular file with error we will just rollback single file which contains error all other files will get committed, I am using SQL Transaction right now , no TransactionScope but obviously I can switch too TransactionScope() if needed and possible, Currently pseudo for my code (what i want) is as follows

Var Files[]
for each (string query in Files)
{
  Execute(Query)
IF(TRUE)
CommitQuery()
Else
result=MBOX("IF You want to abort all files or skip this one")
if(result=abort)
rollbackall()
else
QueryRollBack()
}

推荐答案

似乎您正在寻找不支持SavePoints ，因此您需要直接与本机提供程序打交道(例如，SqlClient如果您的RDBMS是Sql Server). (即，您无法利用TransactionScope的功能来实现与SavePoints等价的DTC等效功能，例如在分布式数据库，不同的RDBMS或并行事务中实现)

It seems you are looking for SavePoints, i.e. the option to partially roll back and then resume a larger transaction. AFAIK TransactionScope doesn't support SavePoints so you'll need to deal directly with the native provider (e.g. SqlClient if your RDBMS is Sql Server). (i.e. you cannot leverage the ability of TransactionScope to implement DTC equivalent of SavePoints, e.g. across distributed databases, disparate RDBMS, or parallel transactions)

也就是说，我建议用户在交易处理开始之前选择跳过或中止的策略，因为等待UI响应的代价很高，而大量行仍被锁定-这很可能会引起争用问题.

That said, I would suggest a strategy where the user elects to skip or abort up front, before transactional processing begins, as it will be expensive awaiting UI response while a large number of rows are still locked - this will likely cause contention issues.

修改

这里是使用SavePoints的一小部分示例.插入Foo1和Foo3，Foo2回滚到先前的保存点.

Here's a small sample of using SavePoints. Foo1 and Foo3 are inserted, Foo2 is rolled back to the preceding save point.

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Foo"].ConnectionString))
{
    conn.Open();
    using (var txn = conn.BeginTransaction("Outer"))
    {
        txn.Save("BeforeFoo1");
        InsertFoo(txn, "Foo1");

        txn.Save("BeforeFoo2");
        InsertFoo(txn, "Foo2");
        txn.Rollback("BeforeFoo2");

        txn.Save("BeforeFoo3");
        InsertFoo(txn, "Foo3");
        txn.Commit();
    }
}

InsertFoo所在的位置:

private void InsertFoo(SqlTransaction txn, string fooName)
{
    using (var cmd = txn.Connection.CreateCommand())
    {
        cmd.Transaction = txn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "INSERT INTO FOO(Name) VALUES(@Name)";
        cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar)).Value = fooName;
        cmd.ExecuteNonQuery();
    }
}

基础表是:

create table Foo
(
    FooId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50)
)

这篇关于子女父母交易回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！