c# - Azure - SqlBulkCopy 抛出超时过期异常

Question

我在虚拟机上使用 azure sql 数据库 (v12)。我有两个不同的数据库实例 - 一个用于暂存，一个用于生产。我正在尝试从暂存中获取数据，然后单击按钮将其插入生产中。该代码“有时”会成功运行，这意味着它随机会成功。否则我会收到以下错误:

BULK COPY Commit Exception Type: {0}System.Data.SqlClient.SqlException BULK COPY Message: {0}Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=1; handshake=17; [Login] initialization=0; authentication=0; [Post-Login] complete=0;

这是我用来完成此任务的代码，也许存在我没有看到的缺陷。通过转储 StringBuilder，我可以看到 SELECT 查询有效，DELETE 查询也有效，但当我尝试使用 SqlBulkCopy 复制数据时，会抛出错误。任何帮助将不胜感激。我已经浏览了一堆 MSDN 文档，但没有运气 -> 添加更长的 CommandTimeouts，添加更长的 BulkCopyTimeout，并在防火墙上重新配置端口。还是没有运气。

我使用过的资源:https://social.msdn.microsoft.com/Forums/en-US/1467d64f-69ae-4c1f-91a2-349fc5d514ae/sqlbulkcopy-fails-with-timeout-expired-error?forum=adodotnetdataproviders

https://azure.microsoft.com/nb-no/documentation/articles/sql-database-develop-direct-route-ports-adonet-v12/

Timeout expired with SqlBulkCopy

public static object SyncData()
{
    StringBuilder sb = new StringBuilder();
    sb.AppendLine("Internal Connection...");
    string internalConnectionString = GetConnectionString("ConnectionString");
    using (SqlConnection internalConnection = new SqlConnection(internalConnectionString))
    {
        internalConnection.Open();              
        SqlCommand selectCommand = internalConnection.CreateCommand();
        selectCommand.CommandTimeout = 180;
        try
        {
            selectCommand.CommandText = "SELECT * FROM dbo.test";
            SqlDataReader reader = selectCommand.ExecuteReader();

            sb.AppendLine("External Connection...");
            string externalConnectionString = GetConnectionString("ExternalConnectionString");
            using (SqlConnection externalConnection = new SqlConnection(externalConnectionString))
            {
                externalConnection.Open();              
                SqlCommand CRUDCommand = externalConnection.CreateCommand();
                CRUDCommand.CommandTimeout = 180;
                SqlTransaction transaction = externalConnection.BeginTransaction("test");
                CRUDCommand.Connection = externalConnection;
                CRUDCommand.Transaction = transaction;
                try
                {
                    CRUDCommand.CommandText = "DELETE FROM dbo.test";
                    sb.AppendLine("DELETE: Number of rows affected = " + CRUDCommand.ExecuteNonQuery());
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(externalConnection, SqlBulkCopyOptions.KeepIdentity, transaction))
                    {
                        try
                        {
                            bulkCopy.DestinationTableName = "dbo.test";
                            bulkCopy.BatchSize = 100;
                            bulkCopy.BulkCopyTimeout = 180;
                            bulkCopy.WriteToServer(reader);

                            sb.AppendLine("Table data copied successfully");

                            transaction.Commit();
                            sb.AppendLine("Transaction committed.");
                        }
                        catch (Exception ex)
                        {
                            sb.AppendLine("BULK COPY Commit Exception Type: {0}" + ex.GetType());
                            sb.AppendLine("  BULK COPY Message: {0}" + ex.Message);
                            try
                            {
                                transaction.Rollback();
                            }
                            catch (Exception ex2)
                            {
                                sb.AppendLine("Rollback Exception Type: {0}" + ex2.GetType());
                                sb.AppendLine("  Message: {0}" + ex2.Message);
                            }
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                }
                catch (Exception ex)
                {
                    sb.AppendLine("Commit Exception Type: {0}" + ex.GetType());
                    sb.AppendLine("  Message: {0}" + ex.Message);

                    try
                    {
                        transaction.Rollback();
                    }
                    catch (Exception ex2)
                    {
                        sb.AppendLine("Rollback Exception Type: {0}" + ex2.GetType());
                        sb.AppendLine("  Message: {0}" + ex2.Message);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            sb.AppendLine("Commit Exception Type: {0}" + ex.GetType());
            sb.AppendLine("  Message: {0}" + ex.Message);
        }
    }
    return sb.ToString();
}

Answer 1

创建 SqlBulkCopy 实例时，您将传递连接字符串 externalConnectionString，从而打开一个新连接。这可能会导致两个连接尝试修改同一个表时出现死锁问题。

您是否尝试将现有连接 externalConnection 传递给 SqlBulkCopy 构造函数而不是连接字符串？