I have a situation with SQL Server 2012 Express and an Index that throws a "Duplicate Key Exception" without a reason. This code works on multiple machines, but in a particular QA department's Workstation this error appear sometimes, I can´t figure out the cause and I can't replicate this in other machines.
In context:
The information is generated by third party equipment, and the software tries to synchronize that information with its own database. The software receives the information when asking for it or with a notification event generated by the equipment. It tries to insert the data and always inserts the data but sometimes SQL Server throws a "Duplicate Key Exception". I added several logs to diagnose the problem and I could discard a double execution of the saving function and saw that the problem is the "Duplicate Key Exception" on the index, even with a successful inserted data.
I use .NET Core 6 and System.Data.SqlClient (4.8.2) library.
I tried several ways to insert the data without success.
I tried the ExecuteNonQuery, ExecuteScalar and ExecuteReader functions with different SQL code to insert.
I tried using OUTPUT INSERTED.TransactionID, checking existence of the key before inserting, returning 0 or 1 to differentiate the successful insert from the failed one, but each attempt finished with the same problem returning the "Duplicate Key Exception".
This code creates the table if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FOO].[Transactions]') AND type in (N'U'))
BEGIN
CREATE TABLE [FOO].[Transactions](
[TransactionID] [bigint] NOT NULL,
[DeviceID] [tinyint] NOT NULL,
[TransactionNumber] [bigint] NOT NULL,
[Material] [text] NULL,
[Volume] [decimal](15, 3) NOT NULL,
[StartDateTime] [datetime2](7) NOT NULL,
[EndDateTime] [datetime2](7) NOT NULL,
[StartingVolume] [decimal](15, 3) NOT NULL,
[EndingVolume] [decimal](15, 3) NOT NULL,
PRIMARY KEY
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END;
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[FOO].[transactions]') AND name = N'IX_transaction_number')
CREATE INDEX [IX_transaction_number] ON [FOO].[transactions]
(
[TransactionNumber] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[FOO].[transactions]') AND name = N'IX_device_sale')
CREATE UNIQUE NONCLUSTERED INDEX [IX_device_sale] ON [FOO].[transactions]
(
[DeviceID] ASC,
[TransactionNumber] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
This one of the variant code to makes the insert:
INSERT INTO [FOO].[Transactions]
([TransactionID],[DeviceID],[TransactionNumber],[Material],[Volume],[StartDateTime],[EndDateTime],[StartingVolume],[EndingVolume])
VALUES
(@TransactionID ,@DeviceID,@TransactionNumber,@Material,@Volume,@StartDateTime,@EndDateTime,@StartingVolume,@EndingVolume);
This SQL code return 0, but in the StatementCompleted event of the SqlCommand class is triggered with 1 affected row from the insert.
IF EXISTS (SELECT TOP 1 * FROM [SCD].[Transactions] WHERE [SaleID] = @SaleID)
BEGIN
SELECT 0;
END
ELSE
BEGIN
INSERT INTO [SCD].[Transactions]
([TransactionID],[DeviceID],[TransactionNumber],[Material],[Volume],[StartDateTime],[EndDateTime],[StartingVolume],[EndingVolume])
VALUES
(@TransactionID ,@DeviceID,@TransactionNumber,@Material,@Volume,@StartDateTime,@EndDateTime,@StartingVolume,@EndingVolume);
SELECT 1;
END
This is the C# code for insert:
private int SaveTransaction(SqlConnection connection, TransactionInfo transaction)
{
try
{
_logger.LogInformation("[REPOSITORY] Starting to save TransactionID:{0} at {1} ns",transaction.TransactionID,Stopwatch.GetTimestamp());
using SqlCommand cmd = new SqlCommand(INSERT_TRANSACTION, connection);
cmd.Parameters.Add("TransactionID", System.Data.SqlDbType.BigInt).Value = transaction.TransactionID;
cmd.Parameters.Add("DeviceID", System.Data.SqlDbType.SmallInt).Value = transaction.PumpID;
cmd.Parameters.Add("TransactionNumber", System.Data.SqlDbType.BigInt).Value = transaction.TransactionNumber;
cmd.Parameters.Add("Material", System.Data.SqlDbType.Text).Value = transaction.Material;
cmd.Parameters.Add("Volume", System.Data.SqlDbType.Decimal).Value = transaction.Volume;
cmd.Parameters.Add("StartDateTime", System.Data.SqlDbType.DateTime2).Value = transaction.StartDateTime;
cmd.Parameters.Add("EndDateTime", System.Data.SqlDbType.DateTime2).Value = transaction.EndDateTime;
cmd.Parameters.Add("StartingVolume", System.Data.SqlDbType.Decimal).Value = transaction.StartingVolume;
cmd.Parameters.Add("EndingVolume", System.Data.SqlDbType.Decimal).Value = transaction.EndingVolume;
var res = cmd.ExecuteNonQuery();
_logger.LogInformation("[REPOSITORY] Ending to save TransactionID:{0} at {1} ns Result:{2}", transaction.TransactionID, Stopwatch.GetTimestamp(),res);
return res > 0 ? 1 : 0;
}
catch (Exception ex)
{
_logger.LogInformation("[REPOSITORY] Error saving TransactionID:{0} at {1} ns \n {2}", transaction.TransactionID, Stopwatch.GetTimestamp(), ex.Message);
_logger.LogError(ex, ex.Message);
return -1;
}
}
The questions: What SQL Server configuration or detail can cause this kind of exception? Where can I look for the source of the problem?
EDIT This is the calling function, here the connection is created and closed.
public int SaveTransaction(TransactionInfo transaction)
{
using SqlConnection connection = new SqlConnection(_connectionString);
try
{
connection.Open();
var res = SaveTransaction(connection, transaction);
return res;
}
catch (Exception ex)
{
_logger.LogError(ex, ex.Message);
return -1;
}
finally
{
connection.Close();
}
}
