c# - SqlCommand 在 C# 中创建数据库竞争条件

Question

如果我创建一个数据库，然后尝试在 2 秒内使用 MS Sync Framework 连接到它，它会抛出异常“无法打开数据库...”

添加

Thread.Sleep(4000);

会导致代码正常工作，但我不希望设置困难时间，因为此代码将在平板电脑和其他弱硬件上运行，因此生成数据库的时间可能会有很大差异。

我可以在创建后检查数据库是否存在，并且它总是立即执行，所以条件等待(旋转)不是一个选项。

using (SqlConnection masterConnection = new SqlConnection(masterConnectionString)) {
    using (SqlCommand sqlCommand = new SqlCommand(string.Format("IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{0}') CREATE DATABASE {0}", databaseName), masterConnection)) {
        masterConnection.Open();
        sqlCommand.ExecuteNonQuery();
    }
}

bool databaseExist = false;
while (!databaseExist) {
    using (SqlConnection masterConnection = new SqlConnection(masterConnectionString)) {
        using (SqlCommand verifySqlCommand = new SqlCommand(string.Format("SELECT database_id FROM sys.databases WHERE name = '{0}'", databaseName), masterConnection)) {
            masterConnection.Open();
            databaseExist = (int)verifySqlCommand.ExecuteScalar() > 0; // Always true
        }
    }
    Thread.Sleep(1000);
}
...
using (SqlConnection sqlConnection = new SqlConnection(CONNECTION_STRING)) {
    SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist 
}

问题似乎出在 SqlConnection 上，第一个 SqlConnection 可以看到数据库存在，但第二个(新的)在前几秒内看不到它存在(在 SqlConnection.Open() 上失败)。

堆栈跟踪

at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.Synchronization.Data.SyncUtil.TryOpenConnection(IDbConnection connection)
at Microsoft.Synchronization.Data.SyncUtil.OpenConnection(IDbConnection connection)
at Microsoft.Synchronization.Data.SqlServer.SqlEditionHelper.GetEdition(SqlConnection connection)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.set_Connection(SqlConnection value)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning..ctor(SqlConnection connection, DbSyncScopeDescription scopeDescription, SqlSyncScopeProvisioningType provisioningType, Boolean expectConnection)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning..ctor(SqlConnection connection)

Mark II 基于@CharlieBrown 的回答

@CharlieBrown 发布的代码本身确实有效，但在我的设置中不起作用，以下代码演示了崩溃。如果您取消注释手动抛出，则代码将正常工作，否则不会。

try {
    //If I manually throw this exception then code in catch will run fine, otherwise not.
    //throw new Exception("Cannot open database testDB"); 
    using (var sqlConnection = new SqlConnection(CONNECTION_STRING)) {
        SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist 
    }
} catch (Exception exception) {
    if (exception.Message.StartsWith("Cannot open database")) { // Database does not exist, try to create
        try {
            using (var masterConnection = new SqlConnection(masterConnectionString)) {
                using (var sqlCommand = new SqlCommand(string.Format("IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{0}') CREATE DATABASE {0}", databaseName), masterConnection)) {
                    masterConnection.Open();
                    sqlCommand.ExecuteNonQuery();
                }
            }
            using (var sqlConnection = new SqlConnection(CONNECTION_STRING)) {
                SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist 
            }
        } catch (Exception ex) { // Always end up here
            // ex = System.Data.SqlClient.SqlException (0x80131904): Cannot open database "testDB" requested by the login. The login failed. Login failed for user ...
        }
    } else { // Other exception
        // Never hit
    }
}

编辑

根据使用建议更新了代码，添加了堆栈跟踪。添加了另一个代码示例来演示问题。

解决方案

在与@CharlieBrown 交谈后，似乎没有解决此问题的方法，因此我将通过始终运行 SqlCommand 来创建数据库(如果不存在)来解决此问题，而不是尝试使用数据库然后在 catch 中创建() 如果异常。此代码仅在应用启动时运行。

Answer 1

您对 SqlSyncScopeProvisioning 的调用在 SQL Server 有时间让数据库准备好同步之前被调用。我找不到临时时间的引用，但在创建数据库后，会发生几个触发器来准备数据库。

工作代码:针对 LocalDb、Sql Server 2008r2、Sql Server 2012 进行了测试

var connectionString = @"Data Source=(LocalDb)\v11.0;Initial Catalog=master;Integrated Security=True;";
var databaseName = "TestRepl";

using (var masterConnection = new SqlConnection(connectionString)) {
    using (var sqlCommand = new SqlCommand(string.Format("IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{0}') CREATE DATABASE {0}", databaseName), masterConnection)) {
        masterConnection.Open();
        sqlCommand.ExecuteNonQuery();
    }
}

var syncConnectionString = string.Format(@"Data Source=(LocalDb)\v11.0;Initial Catalog={0};Integrated Security=True;", databaseName);
using(var sqlConnection = new SqlConnection(syncConnectionString)){
    SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist 
}