c# - 删除和插入事务仍然导致违规 c#

Question

我一直在运行一个应用程序，刚刚查看了它产生的错误日志。我收到多个主键违规错误。查看执行该操作的源代码后，我无法理解为什么会发生这种情况。

该应用程序是在 Dotnet Core 上用 C# 编写的，不使用 ORM 框架，而是使用 SqlObjects。

该命令使用 DbCommand 事务更新表。正如我所说，即使命令本身在使用更新时间重新提交之前删除了条目，我也遇到了主键违规。我似乎无法理解为什么会出现这种情况，尤其是当 SqlCommand 应该是一个事务时。

命令

_sqlObjectFactory.GetConnection()
    .Using(connection =>
    {
        var command = connection.Command(
                _sqlObjectFactory, "DELETE FROM pf_ServiceHeartbeat WHERE ServiceName = @ServiceName AND MachineName = @MachineName")
            .AddParameter(_sqlObjectFactory, "@ServiceName", serviceName)
            .AddParameter(_sqlObjectFactory, "@MachineName", machineName);
        command.ExecuteNonQuery();
        command.CommandText = "INSERT INTO pf_ServiceHeartbeat (ServiceName, MachineName, LastRun) VALUES (@ServiceName, @MachineName, @LastRun)";
        command.AddParameter(_sqlObjectFactory, "@LastRun", lastRun);
        command.ExecuteNonQuery();
    });

错误日志

Error: 11/07/2017 12:50:20 - SqlException: Violation of PRIMARY KEY constraint 'PK_pf_ServiceHeartbeat'. Cannot insert duplicate key in object 'dbo.pf_ServiceHeartbeat'. The duplicate key value is (PopForums.Email.EmailApplicationService, MACHINENAME).
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at PopForums.Data.Sql.Repositories.ServiceHeartbeatRepository.<>c__DisplayClass3_0.b__0(DbConnection connection) in E:\Projects\Forum\src\PopForums.Sql\Repositories\ServiceHeartbeatRepository.cs:line 40
at PopForums.Data.Sql.Extensions.Using(DbConnection connection, Action`1 action) in E:\Projects\Forum\src\PopForums.Sql\Extensions.cs:line 53
at PopForums.Data.Sql.Repositories.ServiceHeartbeatRepository.RecordHeartbeat1(String serviceName, String machineName, DateTime lastRun) in E:\Projects\Forum\src\PopForums.Sql\Repositories\ServiceHeartbeatRepository.cs:line 32
at PopForums.Services.ServiceHeartbeatService.Add(String serviceName, String machineName) in E:\Projects\Forum\src\PopForums\Services\ServiceHeartbeatService.cs:line 37
at PopForums.Services.ServiceHeartbeatService.RecordHeartbeat(String serviceName, String machineName) in E:\Projects\Forum\src\PopForums\Services\ServiceHeartbeatService.cs:line 25
at PopForums.Services.ApplicationServiceBase.Execute(Object sender) in E:\Projects\Forum\src\PopForums\Services\ApplicationServiceBase.cs:line 50

HelpLink.ProdName: Microsoft SQL Server
HelpLink.ProdVer: 13.00.4422
HelpLink.EvtSrc: MSSQLServer
HelpLink.EvtID: 2627
HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink
HelpLink.LinkId: 20476

已更新

下面是serviceHeartbeat表的定义

CREATE TABLE [dbo].[pf_ServiceHeartbeat](
    [ServiceName] [nvarchar](256) NOT NULL,
    [MachineName] [nvarchar](256) NOT NULL,
    [LastRun] [datetime] NOT NULL,
 CONSTRAINT [PK_pf_ServiceHeartbeat] PRIMARY KEY CLUSTERED 
(
    [ServiceName] ASC,
    [MachineName] ASC
)
)

Answer 1

我复制了您的情况，但 Google 对此保持沉默的 SqlObjects 除外。

SQL Server 2017 预览版，在 Ubuntu 16.04 上

CREATE TABLE test.dbo.pf_ServiceHeartbeat (
    ServiceName nvarchar(256) NOT NULL,
    MachineName nvarchar(256) NOT NULL,
    LastRun datetime NOT NULL,
    CONSTRAINT PK_pf_ServiceHeartbeat PRIMARY KEY (ServiceName,MachineName)
)

项目(csproj)文件:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="System.Data.Common" Version="4.3.0" />
    <PackageReference Include="System.Data.SqlClient" Version="4.3.1" />
  </ItemGroup>
</Project>

C# 代码(Dotnet Core 2.0 Preview 1，在 Ubuntu 16.04 上)

var scsb = new SqlConnectionStringBuilder();
scsb.Password="....";
scsb.UserID="sa";
scsb.DataSource="localhost";
scsb.InitialCatalog="test";
using(SqlConnection conn = new SqlConnection(scsb.ConnectionString)) 
{
    conn.Open();
    using(var tran=conn.BeginTransaction()) {
        using(var comm = new SqlCommand("DELETE FROM pf_ServiceHeartbeat WHERE ServiceName = @ServiceName AND MachineName = @MachineName", conn, tran)) {
            comm.Parameters.AddWithValue("@ServiceName", "PopForums.Email.EmailApplicationService");
            comm.Parameters.AddWithValue("@MachineName", "MACHINENAME");
            comm.ExecuteNonQuery();
            comm.CommandText="INSERT INTO pf_ServiceHeartbeat (ServiceName, MachineName, LastRun) VALUES (@ServiceName, @MachineName, @LastRun)";
            comm.Parameters.AddWithValue("@LastRun", DateTime.Now);
            comm.ExecuteNonQuery();
        }
        tran.Commit();
    }
}

每次我运行它时都工作正常，有或没有交易

由于我没有发现您使用 SqlObjects 的实现有任何不同，也许您应该尝试直接使用 ADO.NET 来代替？更少的中间级别会导致错误。