是对 ExecuteNonQuery() 原子的单个调用 [英] Is a single call to ExecuteNonQuery() atomic

问题描述

对 ExecuteNonQuery() 的单个调用是原子调用还是在单个 DbCommand 中有多个 sql 语句时使用事务是否有意义?

Is a single call to ExecuteNonQuery() atomic or does it make sense to use Transactions if there are multiple sql statements in a single DbCommand?

请参阅我的示例以进行说明:

See my example for clarification:

using (var ts = new TransactionScope())
{
    using (DbCommand lCmd = pConnection.CreateCommand())
    {
        lCmd.CommandText = @"
            DELETE FROM ...;
            INSERT INTO ...";
      lCmd.ExecuteNonQuery();
    }
    ts.Complete();
}

推荐答案

如果你不要求交易，你(大部分)就得不到.SQL Server 需要事务中的所有内容，因此默认情况下(没有其他事务管理)，对于每个单独的语句，SQL Server 将创建一个事务并自动提交它.因此，在您的示例中(如果没有 TransactionScope)，您将获得两个单独的事务，它们都独立提交或回滚(出错).

If you don't ask for a transaction, you (mostly) don't get one. SQL Server wants everything in transactions and so, by default (with no other transaction management), for each separate statement, SQL Server will create a transaction and automatically commit it. So in your sample (if there was no TransactionScope), you'll get two separate transactions, both independently committed or rolled back (on error).

(除非您已将 IMPLICIT_TRANSACTIONS 在该连接上，在这种情况下，您将获得一笔交易，但您需要明确的 COMMIT或 ROLLBACK 最后.我发现唯一使用这种模式的人是从 Oracle 移植并试图最小化更改的人.我不建议在未开发的工作中打开它，因为它只会混淆习惯于 SQL Server 的默认值的人)

(Unless you've turned IMPLICIT_TRANSACTIONS on on that connection, in which case you'll get one transaction but you need an explicit COMMIT or ROLLBACK at the end. The only people I've found using this mode are people porting from Oracle and trying to minimize changes. I wouldn't recommend turning it on for greenfield work because it'll just confuse people used to SQL Server's defaults)

这篇关于是对 ExecuteNonQuery() 原子的单个调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！