System.Transaction 隐式事务扰乱了我的其他连接

发布于 2024-07-10 20:27:30 字数 937 浏览 8 评论 0原文

我正在尝试使用 System.Transaction.TransactionScope 创建一个事务来调用一些存储过程，但它似乎不会自行清理。一旦事务完成（无论是否提交，并且事务范围对象已释放），与数据库的后续连接都会以可序列化的读提交级别打开，而不是像通常那样以读提交级别打开。

我正在为每个调用打开和关闭一个连接（很好地关闭并返回到连接池，就像 .NET 中的正常情况一样），当我使用完连接进行事务时，我是否缺少某种显式重置连接的方法？我认为 System.Transaction.TransactionScope 背后的想法是隐藏所有的复杂性。

所以我的代码看起来像这样：

            using (var scope = new TransactionScope())
            {
                ... make my 3 stored procedure calls ...

                scope.Complete();

                return returnCode;
            }

我想这是正常的方法。但是，如果我查看 sqlserver profiler，我可以看到打开的连接会

set transaction isolation level serializable

扰乱后续的非事务相关的数据库活动，而且速度显然也不那么快。我可以通过设置事务选项来明确使用 ReadCommited 执行事务来解决此问题，但在我看来，这不是此操作的理想行为。

我还尝试显式创建一个 Commitabletransaction 对象，创建显式的新事务而不是使用环境事务，但仍然没有成功。

任何有关如何解决此问题的想法将不胜感激，因为任何使用可序列化连接的调用如果尝试使用 readpast 锁定提示都会抛出错误。

原文

I'm trying to use System.Transaction.TransactionScope to create a transaction to call a few stored procedures but it doesn't seem to clean up after itself. Once the transaction is finished (commited or not and the transaction scope object is disposed) subsequent connections to the database open up with the read commit level of serializable instead of read commited like they normally would.

I'm opening and closing a connection for each call (well closing and returning to a pool of connections like normal in .NET), am I missing some way to explicitly reset the connection when I'm done using it for a transaction? I thought the idea behind System.Transaction.TransactionScope was to hide all the complexity.

So the code I have looks like this:

            using (var scope = new TransactionScope())
            {
                ... make my 3 stored procedure calls ...

                scope.Complete();

                return returnCode;
            }

which I guess is the normal way to do it. But then if I look in sqlserver profiler I can see connections being opened with

set transaction isolation level serializable

which is messing with subsequent non-transaction related database activity and also is apparently not as fast. I can get around this by setting a transaction option to explicity do the transaction with ReadCommited but this is not the ideal behaviour for this operation in my opinion.

I've also tried explicitly creating a Commitabletransaction object, creating explict new transactions instead of using the ambient one and still no luck.

Any ideas on how to fix this would be much appreciated as any calls that use the serializable connection will throw an error if they try to use a readpast locking hint.

分享到QQ

分享到微博

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

发布评论

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

哀由 2024-07-17 20:27:31

使用 TransactionOptions.IsolationLevel

通过默认，它是可序列化的

TransactionOptions transactionoptions1 = new TransactionOptions();
transactionoptions1.IsolationLevel = IsolationLevel.ReadCommitted;
using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionoptions1))
{
    ... make my 3 stored procedure calls ...

    scope.Complete();

    return returnCode;
}

Use TransactionOptions.IsolationLevel

By default, it's serializable

TransactionOptions transactionoptions1 = new TransactionOptions();
transactionoptions1.IsolationLevel = IsolationLevel.ReadCommitted;
using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionoptions1))
{
    ... make my 3 stored procedure calls ...

    scope.Complete();

    return returnCode;
}

回复收藏 0 原文