Delete and Insert transaction still causing violation c#

Question

I have been running an application and have just looked at the error log it produces. I am getting multiple errors for primary key violations. Having looked at the source code that performs the action I cannot understand why this is occurring.

The application is written in C# on Dotnet Core and does not use an ORM framework instead it uses SqlObjects.

The command updates the table using a DbCommand transaction. AS I said I am getting a Primary Key violation even though the command itself removes the entry before resubmitting with an updated time. I cannot seem to understand why this is the case especially when SqlCommand is supposed to be a transaction.

Command

_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 Log

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

Updated

Below is the definition of the serviceHeartbeat table

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
)
)

Share the table definition and the definition of `PK_pf_ServiceHeartbeat`. Without this info, it's guesswork. — spender, Jul 11 '17 at 13:32
`The duplicate key value is (PopForums.Email.EmailApplicationService, MACHINENAME).' it says MACHINENAME is a PK. Check table definition. — Serg, Jul 11 '17 at 13:35
choose a unique value for column `MACHINENAME` the name you are inserting already exists in this column. — ARr0w, Jul 11 '17 at 13:41
@ARr0w the app is running on the same machine and therefore the delete statement should have removed it. Should it not? — sjc_w, Jul 11 '17 at 13:49
@Serg But there is a delete statement before the insert. So it should not be there when the insert takes place hence there should be no primary key violation — sjc_w, Jul 11 '17 at 15:28
@LaurentLequenne If I reapply the parameters again then I get a duplicate variable name declared. Also, you can see the parameter within the stack trace e.g. `PopForums.Email.EmailApplicationService` — sjc_w, Jul 11 '17 at 15:44
What's `SqlObjects`? Because it looks like ADO.NET, pops exceptions with stack traces to ADO.NET, and everything is just so ADO.NET, except the way you work with it. — Alex, Jul 11 '17 at 19:43
Dear OP @sjc_w, have you found a solution yet? I'm facing the same problem, on .NET Core 2.1, where after `DELETE` all records, `INSERT` a record caused me `Violation of PRIMARY KEY`. — Mr.K, Nov 18 '19 at 03:43

score 0 · Answer 1 · answered Jul 11 '17 at 19:54

I replicated your situation, except for the SqlObjects for which Google is silent.

SQL Server 2017 preview, on 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)
)

Project (csproj) file:

<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# code (Dotnet Core 2.0 Preview 1, on 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();
    }
}

Works fine every time I run it, with, or without the transaction

Since I don't see any difference from your implementation with SqlObjects, maybe you should try working directly with ADO.NET instead? Fewer intermediary levels to cause bugs.

You have not set the primary key to be clustered? If you do does that make any difference? — sjc_w, Jul 19 '17 at 14:07
MSSQL by default creates primary keys as clustered indexes. `CLUSTERED` therefore is superfluous. Try it without, and check with, say, DBeaver, whether it's a clustered or non-clustered index. https://stackoverflow.com/questions/10706992/database-primary-key-clustered-or-nonclustered . Regardless, it works. — Alex, Jul 19 '17 at 20:14

Delete and Insert transaction still causing violation c#

1 Answers1