TransactionScope和sp_execute_remote超时问题 [英] TransactionScope and sp_execute_remote timeout problem

问题描述

我有一个在Azure中运行的WebJob.该作业应运行几个存储过程.我想将它们包装在交易中.
使用SqlTransaction，它适用于所有对象，但一个特殊的存储过程由于超时问题而总是失败.
如果这个特殊的sp在本地运行，则需要一秒钟.

我说这很奇怪，因为它在一个SQL数据库上运行多个查询，最后，它在外部数据源中运行sp_execute_remote.

我也尝试使用TransactionScope，打开连接时发生以下异常:

Hi,

I have a WebJob running in Azure. This job should run several stored procedures. I'd like to wrap them in a transaction.
With a SqlTransaction, it works for all, except for one peculiar stored procedure that always fails because of a timeout problem.
If this peculiar sp is ran locally, it takes one second.

I'm saying it is peculiar because it runs several queries on one SQL database and in the end,  it runs sp_execute_remote in an external datasource.

I have also tried to use TransactionScope and an the exception below occurs when opening the connection:

    e n System.RuntimeMethodHandle.InvokeMethod(对象目标，Object []参数，签名sig，布尔值构造函数)
    en System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(对象obj，Object []参数，Object []参数)
    en System.Reflection.RuntimeMethodInfo.Invoke(对象obj，BindingFlags invokeAttr，活页夹活页夹，Object []参数，CultureInfo文化)
    en System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
    en System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
    en System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(交易交易)
    en System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owneringObject，DbConnectionInternal obj，Transaction transaction)
    en System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owneringObject，UInt32 waitForMultipleObjectsTimeout，布尔值allowCreate，仅布尔值OneCheckConnection，DbConnectionOptions userOptions，DbConnectionInternal& connection)
    en System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject，TaskCompletionSource`1重试，DbConnectionOptions userOptions，DbConnectionInternal& Connection)
    en System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection，TaskCompletionSource`1重试，DbConnectionOptions userOptions，DbConnectionInternal oldConnection，DbConnectionInternal& connection)
    en System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection externalConnection，DbConnectionFactory connectionFactory，TaskCompletionSource`1重试，DbConnectionOptions用户选项)
    en System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1重试)
    en System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1重试)
    en System.Data.SqlClient.SqlConnection.Open()

   如果我在没有事务上下文的情况下运行代码，则存储过程成功，但是我不希望这种方法.

是否可以在事务中包装这种包装sp_execute_remote查询的过程?

预先感谢，

   en System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   en System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   en System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   en System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   en System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   en System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   en System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
   en System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   en System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   en System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   en System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   en System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   en System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   en System.Data.SqlClient.SqlConnection.Open()

   If I run the code without a transactional context, the stored procedure succeeds, but I would not like this approach.

Is it possible to wrap in a transaction this type of procedures which wrap a sp_execute_remote query?

Thank in advance,

推荐答案

Hello,

Please increase the timeout property on the client request side. Sometimes when high DTU usage occurs transactions may take longer to complete and depending on the timeout value specified on the client side the transaction may timeout.

Hope this helps.

Regards,

SQLCoffee.com

Alberto Morillo
SQLCoffee.com

这篇关于TransactionScope和sp_execute_remote超时问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！