Duplicate key with NOT EXISTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicate key with NOT EXISTS

Author

Topic

erickong
Starting Member

6 Posts

Posted - 2008-10-16 : 00:16:22

I am working on a table which has many client inserting data.
but sometime different client may insert same record at the same time. The way I use to avoid inserting duplicate key is NOT EXISTS

IF NOT EXISTS (SELECT ItemID FROM TABLE_A WHERE ItemID = @ItemId)
BEGIN
INSERT INTO TABLE_A
(ItemId)VALUES(@ItemId)
END

However I always facing the problem of
"Violation of PRIMARY KEY constraint"

Is there any better way to handle this case?

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-16 : 01:10:12

The code looks good. Is it the exact code used in your app or you just gave an example here. If so, please post the exact code. Since you've mentioned you receive the error "always", something else might also be a problem. You can also consider -

1. Insert into Table
Select @ItemID
From Table
Where Not Exists ...

2. Use a try catch and do nothing in the catch.

erickong
Starting Member

6 Posts

Posted - 2008-10-16 : 01:48:52

The store proc is very simple as above (just with more columns, but they are not a key)
There are more than 1 application keep running and it only insert data continuously with the above store proc, the volume is around 5k Insert per minutes

Just thinking if there will be a case:
App1 and App2 (or more) check the "IF NOT EXISTS" at the same time, but both of them cannot find any record and start the same insert statement?

I already tried to use transaction but not working
It there any locking in SQL could help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 01:55:41

whats the transaction isolation level used?

erickong
Starting Member

6 Posts

Posted - 2008-10-16 : 02:05:31

I didn't change the transaction isolation level. Just simply:

BEGIN TRANSACTION
IF NOT EXISTS (SELECT ItemID FROM
...
COMMIT TRANSACTION

Shall I use "READ COMMITTED" ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 02:31:36

quote:
Originally posted by erickong

I didn't change the transaction isolation level. Just simply:

BEGIN TRANSACTION
IF NOT EXISTS (SELECT ItemID FROM
...
COMMIT TRANSACTION

Shall I use "READ COMMITTED" ?

Use READ COMMITED or SERIALIZABLE

erickong
Starting Member

6 Posts

Posted - 2008-10-16 : 02:47:03

quote:
Originally posted by visakh16

quote:
Originally posted by erickong

I didn't change the transaction isolation level. Just simply:

BEGIN TRANSACTION
IF NOT EXISTS (SELECT ItemID FROM
...
COMMIT TRANSACTION

Shall I use "READ COMMITTED" ?

Use READ COMMITED or SERIALIZABLE

If I use SERIALIZABLE, would it affect the normal select query?
sometimes the query may take >10 minutes with the large amount of data. Could they run together?

erickong
Starting Member

6 Posts

Posted - 2008-10-17 : 03:26:21

I tried different transaction isolation level
But seems that its still not working

maybe because even transaction isolation level is set, the first statement still return the same result
(SELECT ItemID FROM TABLE_A WHERE ItemID = @ItemId)

Is there any way to prevent other to READ when a transaction start?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-10-17 : 06:55:14

The following should work with all isolation levels except snapshot:

INSERT INTO TableA(ItemId)
SELECT @ItemId
WHERE NOT EXISTS
(
    SELECT *
    FROM TableA WITH (UPDLOCK)
    WHERE ItemId = @ItemId
)

With snapshot you will just have to handle the exception.

erickong
Starting Member

6 Posts

Posted - 2008-10-20 : 23:07:57

Finally everything works fine when I use

SELECT * FROM TableA WITH (UPDLOCK, HOLDLOCK)

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources