Primary Key Violation

Hi Everyone
I am occasionally getting the following error ......
Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplicat
e key in object 'TABLE1'
The sProc segment that is causing this error is ....
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field2 = field2 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
----
Where field1 and field2 is the composite primary key.
Any ideas how to modify my sProc to stop the primary key violation happening
Cheers
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Try,
IF EXISTS (SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
if exists(SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2
+ 1)
print 'tell us what to do in this case.'
else
UPDATE
TABLE1
SET
field2 = field2 + 1
WHERE
field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
AMB
"Peter" wrote:

> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplic
ate key in object 'TABLE1'
> The sProc segment that is causing this error is ....
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field
2)
> UPDATE TABLE1
> SET field2 = field2 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
> ----
-
> Where field1 and field2 is the composite primary key.
> Any ideas how to modify my sProc to stop the primary key violation happeni
ng
> Cheers
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||"Peter" <peter@.dwstech.com> wrote in message news:427c1f0e$1_2@.127.0.0.1...
> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert
> duplicate key in object 'TABLE1'
You need to check if the row referenced by (@.field1, @.field2 + 1) exists
before you try to UPDATE. For instance, consider the following:
Field1 | Field2
100 | 100
100 | 101
In this instance if @.field1 = 100 and @.field2 = 100, the following will
cause a PK violation:
UPDATE TABLE1
SET field2 = field2 + 1
WHERE field1 = @.field1
AND field2 = @.field2
What exactly are you trying to accomplish? Maybe someone can help with the
logic, if you can supply more info...|||Oooops, my bad
The sProc code shouls have read ...
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
----
The object of the table is to act as a simple counter. If the primary key al
ready exists in the table then update the counter column (field3) by increme
nting by 1. If the primary key does not exist then insert the primary key an
d set the counter column to
1.
Sorry for the confusion.
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Are you still getting the error? If so then I would guess this to be a
concurrency issue. How busy is this database? Is it likely that two
processes would cause this to occur? If so, there are two things you can
do:
--easy, no other changes to you system possiblilty
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--we don't want anyone to touch the row:
BEGIN TRANSACTION
--lock it exclusively so no one else can read it
IF EXISTS (SELECT 1 FROM TABLE1 (xlock) WHERE field1 = @.field1 AND field2 =
@.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--alternately, change this from a counter into a very thin table:
create table counter
(
field1 int --needs a different name
counter bigint identity,
actiontime datetime,
primary key (field1, counter)
)
Then just insert rows into this table and use aggregates. You can store
more information about each activity, if you want to get a richer set of
information. This method remove all contention on insert other than the
picking of the next counter value, and that is extremely fast.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427c3742$1_2@.127.0.0.1...
> Oooops, my bad
> The sProc code shouls have read ...
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 =
> @.field2)
> UPDATE TABLE1
> SET field3 = field3 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
> ----
-
> The object of the table is to act as a simple counter. If the primary key
> already exists in the table then update the counter column (field3) by
> incrementing by 1. If the primary key does not exist then insert the
> primary key and set the counter column to 1.
> Sorry for the confusion.
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Louis & Michael
Thank you both for your replies.
I will expand a little further on the purpose of this table.
The table is for counting how many times any particular url on a website is
clicked. The primary key fields of the table are click_hour and url_id. url_
id is passed to the sProc and click_hour is calculated within the sProc as d
atediff(hour,0,getutcdate()
). If an entry exists within the table for the particular click_hour and url
_id, then the click_count field is incremented by 1, else an entry is added
to the table for that click_hour and url_id with the click_count field set w
ith an initial value of 1.
I have chosen this method as only one row needs to exist in the table for ea
ch url for each hour regardless of the number of clicks ... thus if one url
is clicked 1000 times each hour for 24 hours there is only 24 rows in the ta
ble as opposed to 24,000.
This sProc can be called anywhere up to 50,000 times a day so the database i
s reasonably busy.
Michael, the suggestion you gave reverts back to the one entry per click sce
nario which I want to avoid as does the second suggestion offered by Louis.
Therefore, unless anyone can come up with a better suggestion, I am looking
at implementing Louis' first suggestion. Louis, I am just wondering what per
formance impact XLOCK will have on the sProc. Also, is XLOCK a better option
than TABLOCK and if so cou
ld you please explain why.
Thanks again to both of you for taking the time to reply.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||sorry for the slow reply. I have been way busy of late:
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first
>suggestion. Louis, I am just wondering what performance impact XLOCK will
>have on the sProc. Also, is >XLOCK a better option than TABLOCK and if so
>could you please explain why.
>
The important thing difference between xlock and tablock is that one takes a
type of lock, the other locks a certain type of resource. You want SQL
Server to take a lock that means no one else can even look at it., but we
only want it to lock the row we are concerned with, not the entire table.
The one entry per click solution is the best solution because no contention.
Make a view of the data to give you your count, and even update your column
once a day and add these rows to count of the newly inserted ones, but as
long as you don't have too many people trying to update the same row, the
fact that you are single threading acess to the row in TABLE1 should not be
too concerning.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427df11b$1_1@.127.0.0.1...
> Louis & Michael
> Thank you both for your replies.
> I will expand a little further on the purpose of this table.
> The table is for counting how many times any particular url on a website
> is clicked. The primary key fields of the table are click_hour and url_id.
> url_id is passed to the sProc and click_hour is calculated within the
> sProc as datediff(hour,0,getutcdate()). If an entry exists within the
> table for the particular click_hour and url_id, then the click_count field
> is incremented by 1, else an entry is added to the table for that
> click_hour and url_id with the click_count field set with an initial value
> of 1.
> I have chosen this method as only one row needs to exist in the table for
> each url for each hour regardless of the number of clicks ... thus if one
> url is clicked 1000 times each hour for 24 hours there is only 24 rows in
> the table as opposed to 24,000.
> This sProc can be called anywhere up to 50,000 times a day so the database
> is reasonably busy.
> Michael, the suggestion you gave reverts back to the one entry per click
> scenario which I want to avoid as does the second suggestion offered by
> Louis.
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first suggestion. Louis, I am just
> wondering what performance impact XLOCK will have on the sProc. Also, is
> XLOCK a better option than TABLOCK and if so could you please explain why.
> Thanks again to both of you for taking the time to reply.
> Regards
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--