This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 45%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EB%3C/text%3E%3C/svg%3E)
I am encountering an issue while attempting to call a stored procedure from a .NET Framework application to a Microsoft SQL Server instance. The error message returned is: "Violation of PRIMARY KEY constraint 'PK_Mytable'. Cannot insert duplicate key in object 'dbo.Mytable'. The duplicate key value is (c026da75-0528-47a9-9117-124c6690ec4f, b4f0b604-d2e2-4ce0-b3a7-1973dce6f27d, acd220ec-85a0-4999-8ebc-e0b42ec2ae07, XXX-00007449-XX). Transaction (Process ID 181) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. The statement has been terminated."
Additional Details:
The stored procedure in question is being called from a .NET Framework C# application.
The records being inserted are unique; there are no duplicate records and 100% sure about it.
Both the business logic and stored procedure do not contain any transactional scope level.
When running the stored procedure directly in SQL Server, it executes without errors and produces the expected results.
At the time of execution, my application makes only one call to the stored procedure.
Thank you for your assistance.
Cannot insert duplicate key in object 'dbo.Mytable'.
As the message clearly says, you can not insert duplicate keys. But you provided only less information, so it's not possible to say where the bug in you code is. You have to post more details, like SQL / .NET code.
Thank for your Reply
public static Dictionary<string, object> ExecuteNonQuery2(SqlConnection connection, string storedProcedure, params SqlParameter[] parameters) {
if (connection == null)
throw new ArgumentNullException("connection");
if (string.IsNullOrEmpty(storedProcedure))
throw new ArgumentException("Unexpected null or empty string.", "storedProcedure");
if (connection.State == ConnectionState.Closed)
connection.Open();
Dictionary<string, object> list = new Dictionary<string, object>();
using (var command = new SqlCommand(storedProcedure, connection)) {
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 900;
if (parameters != null && parameters.Length != 0)
command.Parameters.AddRange(parameters);
int outV = command.ExecuteNonQuery();
foreach (SqlParameter p in command.Parameters) {
if (p.Direction == ParameterDirection.ReturnValue ||
p.Direction == ParameterDirection.Output ||
p.Direction == ParameterDirection.InputOutput)
list.Add(p.ParameterName, p.Value);
}
}
return list;
}
Stored Procedure:
ALTER PROCEDURE SP_InsertMytable
@companyId UniqueIdentifier,
@reportMonth nvarchar(7),
@RecordsAffected int output
AS
BEGIN
SET NOCOUNT ON;
DELETE from MyTable where FK_CompanyID=@companyId
INSERT MyTable (PreviewID, FK_CompanyID, FK_TransactionDetailsID, FK_TransactionHeaderID, TransactionNo)
SELECT
NEWID(),t1.CompanyID, t2.TransactionDetailsID,t3.TransactionHeaderID, t2.TransactionNo
FROM
Table1 t1
INNER JOIN
Table2 t2 ON t1.CompanyID = t2.FK_CompanyID
INNER JOIN
Table3 t3 ON t2.TransactionDetailsID= t3.FK_TransactionDetailsID
WHERE
t1.companyId = @companyId
AND t2.ReportPeriod = @reportMonth;
select @RecordsAffected = count(1) from MyTable where FK_CompanyID = @cpyID;
END
Since I don't anything about the tables, my answer is that apparently your SELECT is able to generate non-unique results.
To disprove that hypotheses, you would need to post CREATE TABLE statements, including the key definitions. We would prefer to see the unmasked names, because if you alter the information, we are less keen on trusting the information. Yes, I understand that you don't want to expose intellectual property, but you need to be very careful that you mask consistently.
To get information about deadlocks on your server, you can use this query:
; WITH CTE AS (
SELECT CAST(event_data AS xml) AS xml
FROM sys.fn_xe_file_target_read_file(
N'system_health*.xel', DEFAULT, DEFAULT, DEFAULT)
WHERE object_name = 'xml_deadlock_report'
)
SELECT xml.query('/event/data[1]/value[1]/deadlock[1]'),
xml.value('/event[1]/@timestamp', 'datetime2(3)') AS timestamp_utc
FROM CTE
ORDER BY timestamp_utc DESC
I am 100% sure that if I run the stored proc on the SQL server, it will return the expected results and all the records are unique. The previous month, it worked fine, but nobody changed the source code or stored proc. Is the IIS server responsible for this because the deadlock happens exactly 30 seconds after the stored proc is run by the source code?
CREATE TABLE [dbo].[Mytable](
[PreviewID] [uniqueidentifier] NOT NULL,
[FK_CompanyID] [uniqueidentifier] NOT NULL,
[FK_TransactionDetailsID] [uniqueidentifier] NOT NULL,
[FK_TransactionHeaderID] [uniqueidentifier] NOT NULL,
[TransactionNo] [varchar](150) NOT NULL,
CONSTRAINT [PK_Mytable] PRIMARY KEY NONCLUSTERED
(
[FK_CompanyID] ASC,
[FK_TransactionDetailsID] ASC,
[FK_TransactionHeaderID] ASC,
[TransactionNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
is the IIS server responsible for this because the deadlock happens exactly 30 seconds after the stored proc is run by the source code?
IIS does of course not run any code on its own initiative, but apparently there is something going on in your application.
I am 100% sure that if I run the stored proc on the SQL server, it will return the expected results and all the records are unique
That may be true when the system is idle. But if there is concurrent activity?
The previous month, it worked fine, but nobody changed the source code or stored proc
But the data may have changed.
Anyway, you have not shared much more details that permits us to gives us more advice. I stand by my original conclusion: the query you have is able to produce to duplicate rows. But, of course, it could also be that there are parallel executions for the same @companyID. (Slapping a BEGIN TRANSACTION around the DELETE + INSERT could help here.)
I can give one more piece advice, if more from a professional point of view. When troubleshooting things like this, you need to be open-minded. "There are no duplicates", "It ran last month" etc are all bad starts to approach a problem like this. In many cases, the reason is due to an incorrect assumption somewhere.
And, oh, don't forget to look at the deadlock XML to see what other operation you are clashing with. Maybe there is a hint there.