July 10, 2012 at 9:24 am
Hey everyone. Getting a small issue in some of our code and was hoping for an explanation on why it is occuring and how to prevent it.
Here is the code:
CREATE TABLE [Types]
(TypeID INT
, Description NVARCHAR(100)
)
create table Table1
(CustomerID INT
, TypeID INT
, Amount MONEY DEFAULT 0
, UNIQUE (CustomerID, TypeID)
)
DECLARE @CustomerID INT
-- Insert any missing Types for the specific customer into the table
INSERT INTO Table1
SELECT @CustomerID
, T.TypeID
FROM Types as T
WHERE NOT EXISTS (
SELECT 'X'
FROM Table1 as T1
WHERE T1.CustomerID = @CustomerID
AND T1.TypeID =T.TypeID
)
As you can see, a pretty simply setup. 99.9% of the time it works just fine. However, every now and then, we get the following error:
Violation of UNIQUE KEY constraint 'Table1__UQ'. Cannot insert duplicate key in object 'dbo.Table1'
Now, I am guessing that it is a timing thing that two statement on a heavy system are just hitting it at the same time. However, I need to stop it from happening.
Anyone got any ideas on how that can be done.
Note: I am running a SQL Server 2005 system, so the MERGE statement isn't an option.
Thanks
Fraggle
July 10, 2012 at 1:30 pm
YOu could provide a locking hint on the insert statement to lock the table, thus blocking any other process accessing the table until the transaction is complete.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2012 at 6:55 am
Jack,
Was afraid you were going to say it. Would you recommend a table hint of SERIALIZABLE or TABLOCK or something different?
Nathan
July 11, 2012 at 7:09 am
I'd use TABLOCK as specified in BOL(http://msdn.microsoft.com/en-us/library/ms174335(v=sql.90).aspx)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 12, 2012 at 3:42 am
Hello Nathan,
have you discarded the obvious option?, maybe your Types table contains a duplicate TypeID.
Francesc
July 12, 2012 at 7:28 am
The types table has a PK on the ID column, so it can't have duplicates.
Fraggle
July 12, 2012 at 8:11 am
Fraggle-805517 (7/12/2012)
The types table has a PK on the ID column, so it can't have duplicates.Fraggle
Just an FYI, your DDL in your original post doesn't show that, so it was a good thought.
Fraggle-805517 (7/10/2012)
Hey everyone. Getting a small issue in some of our code and was hoping for an explanation on why it is occuring and how to prevent it.Here is the code:
CREATE TABLE [Types](TypeID INT
, Description NVARCHAR(100)
)
create table Table1
(CustomerID INT
, TypeID INT
, Amount MONEY DEFAULT 0
, UNIQUE (CustomerID, TypeID)
)
DECLARE @CustomerID INT
-- Insert any missing Types for the specific customer into the table
INSERT INTO Table1
SELECT @CustomerID
, T.TypeID
FROM Types as T
WHERE NOT EXISTS (
SELECT 'X'
FROM Table1 as T1
WHERE T1.CustomerID = @CustomerID
AND T1.TypeID =T.TypeID
)
As you can see, a pretty simply setup. 99.9% of the time it works just fine. However, every now and then, we get the following error:
Violation of UNIQUE KEY constraint 'Table1__UQ'. Cannot insert duplicate key in object 'dbo.Table1'Now, I am guessing that it is a timing thing that two statement on a heavy system are just hitting it at the same time. However, I need to stop it from happening.
Anyone got any ideas on how that can be done.
Note: I am running a SQL Server 2005 system, so the MERGE statement isn't an option.
Thanks
Fraggle
Question, is this piece of code by itself, or part of something larger? If by itself in a stored procedure, I'd go with serializable. If part of something bigger, a locking hint.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply