Respawner未重置数据库：测试随机触发Duplicate key错误

运行测试时，两个测试中的一个会随机抛出Duplicate key错误。尝试调整Reset操作的位置并等待执行完成，但即使将Reset放在测试方法内部也无法解决问题。

现有代码 #

Fixture代码 #

public sealed class FooFixture : IAsyncLifetime {
    private readonly MsSqlTestcontainer _msSqlTestContainer;
    private Respawner _respawner = default!;

    public FooFixture() {
        _msSqlTestContainer = new TestcontainersBuilder<MsSqlTestcontainer>().WithDatabase(new MsSqlTestcontainerConfiguration {
                                                                                               Password = "Toeter01!"
                                                                                           })
                     .Build();
    }

    public string ConnectionString { get; private set; } = default!;
    public SqlConnection Connection { get; private set; } = default!;

    public Task Reset() => _respawner.ResetAsync(Connection);

    public async Task InitializeAsync() {
        await _msSqlTestContainer.StartAsync();

        var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(_msSqlTestContainer.ConnectionString) {TrustServerCertificate = true};
        ConnectionString = sqlConnectionStringBuilder.ConnectionString;
        Connection = new SqlConnection(ConnectionString);

        await Connection.OpenAsync();
        _respawner = await Respawner.CreateAsync(Connection,
                                                 new RespawnerOptions {
                                                          DbAdapter = DbAdapter.SqlServer,
                                                          SchemasToInclude = new[] {"dbo"},
                                                          CommandTimeout = 60
                                                      });
        await Connection.ExecuteAsync(@"CREATE TABLE dbo.bar (foo int NOT NULL UNIQUE);");
    }

    public async Task DisposeAsync() {
        await _msSqlTestContainer.DisposeAsync();
    }
}

Collection代码 #

[CollectionDefinition(nameof(FooCollection))]
public class FooCollection : ICollectionFixture<FooFixture> {
}

测试代码 #

[Collection(nameof(FooCollection))]
[Trait("Category", "Integration")]
public class RespawnTest : IAsyncLifetime {
    private readonly FooFixture _fixture;

    public RespawnTest(FooFixture fixture) {
        _fixture = fixture;
    }

    [Fact]
    public async Task One() {
        await _fixture.Connection.ExecuteAsync("INSERT INTO dbo.bar (foo) values (1) ");
    }

    [Fact]
    public async Task Two() {
        await _fixture.Connection.ExecuteAsync("INSERT INTO dbo.bar (foo) values (1) ");
    }

    public async Task InitializeAsync() => await _fixture.Reset();

    public Task DisposeAsync() => Task.CompletedTask;
}

解决方案 #

问题核心是共享连接的线程安全冲突和Reset执行时机不匹配，可通过以下方式修复：

1. 避免全局共享SqlConnection
   移除Fixture中全局的Connection属性，改为每次操作使用独立连接，避免多测试线程争抢同一连接：

   // 修改FooFixture的Reset方法
   public async Task Reset() 
   {
       using var connection = new SqlConnection(ConnectionString);
       await connection.OpenAsync();
       await _respawner.ResetAsync(connection);
   }
   
   // 移除public SqlConnection Connection属性
   

2. 每个测试前后单独执行Reset
   仅在测试类初始化时执行一次Reset，无法应对并行测试的执行顺序问题。修改测试方法，确保每个测试执行前都重置数据：

   [Collection(nameof(FooCollection))]
   [Trait("Category", "Integration")]
   public class RespawnTest
   {
       private readonly FooFixture _fixture;
   
       public RespawnTest(FooFixture fixture) { _fixture = fixture; }
   
       [Fact]
       public async Task One()
       {
           await _fixture.Reset();
           using var connection = new SqlConnection(_fixture.ConnectionString);
           await connection.OpenAsync();
           await connection.ExecuteAsync("INSERT INTO dbo.bar (foo) values (1) ");
       }
   
       [Fact]
       public async Task Two()
       {
           await _fixture.Reset();
           using var connection = new SqlConnection(_fixture.ConnectionString);
           await connection.OpenAsync();
           await connection.ExecuteAsync("INSERT INTO dbo.bar (foo) values (1) ");
       }
   }
   

3. 禁用测试并行执行（可选兜底方案）
   若上述调整仍有问题，可强制测试按顺序运行，避免并行冲突。在测试项目的.csproj文件中添加：

   <PropertyGroup>
       <VSTestMaxParallelThreads>1</VSTestMaxParallelThreads>
   </PropertyGroup>
   

内容的提问来源于stack exchange，提问作者Duncan Roosma