x

Best way of doing an upsert in SQL 2000

I have the following table:

create table Words
(
     Word       nvarchar(255) primary key not null
    ,Popularity int not null
)

And the following stored procedure:

CREATE PROCEDURE TrackWord
(
    @Word nvarchar(255)
)
AS
BEGIN
    begin transaction
        if not exists (select 1 from [dbo].[Words] with (updlock,holdlock,rowlock) where [Word] = @Word )
        begin
            --waitfor delay '00:00:05'
            insert into [dbo].[Words]( [Word],[Popularity] ) values ( @Word , 1 )   
        end
        else
        begin
            update [dbo].[Words] set [Popularity] = [Popularity] + 1 where [Word] = @Word 
        end
    commit transaction
END

This works as I expect it to, and I can uncomment the waitfor delay line and run it in different query windows and it works as expected when used from multiple places at the same time.

Now my question is this: Is there is more efficient/elegant way to do this (In SQL 2000)

more ▼

asked Feb 23 '10 at 09:28 AM in Default

My Other Me gravatar image

My Other Me
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort oldest

Here's a method I have used before.

declare @updated bit set @updated = 0

update Words set popularity = popularity + 1, @updated = 1 where Word = @word

insert into Words (Word, Popularity) select @word, 1 where @updated = 0
more ▼

answered Feb 23 '10 at 09:41 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 44 49 76

But that could result in a multiple threads trying to execute the insert and resulting in a primary key violation: Thread A runs the update and updates nothing. Thread B runs the update and updates nothing. Thread A insert the word. Thread B tries to insert the word and fails because the word was inserted by thread A. You can replicate this by adding a waitfor delay '00:00:10' before the insert and running it in 2 windows (within 10 seconds)
Feb 23 '10 at 10:08 AM My Other Me
no - if this is in a stored procedure then there is an implicit transaction
Feb 23 '10 at 10:15 AM Kev Riley ♦♦
I wasn't trying to give you the whole solution, just different methods :)
Feb 23 '10 at 10:19 AM Kev Riley ♦♦
never had any issues with it in my high-concurrency environments
Feb 23 '10 at 10:36 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

Here is one alternative

UPDATE w SET popularity = popularity + 1 FROM Words w WHERE Word = @wordIF(@@ROWCOUNT = 0) BEGIN  INSERT INTO Words (Word, Popularity)  VALUES(@word, 1) END 
more ▼

answered Feb 23 '10 at 10:03 AM

Brimstedt gravatar image

Brimstedt
226 3 3 4

Same problem as the Solution from Kev Riley: Not safe for use from multiple threads.
Feb 23 '10 at 10:11 AM My Other Me
It's a matter of transaction type and locking.
Feb 23 '10 at 10:38 AM Brimstedt
Thanks for fixing code, Kev :-)
Feb 23 '10 at 10:41 AM Brimstedt
(comments are locked)
10|1200 characters needed characters left

Another solution,

INSERT INTO Words (Word, Popularity) SELECT @word, 1 WHERE NOT EXISTS (  SELECT 1  FROM Words  WHERE Word = @word ) IF(@@ROWCOUNT = 0) BEGIN  UPDATE w  SET popularity = popularity + 1  FROM Words w  WHERE Word = @word END 
more ▼

answered Feb 23 '10 at 10:40 AM

Brimstedt gravatar image

Brimstedt
226 3 3 4

(comments are locked)
10|1200 characters needed characters left

My typical method for upsert in the past has been like this, though I've built it to work with multiple rows, not just a parameter.

  • update matching rows delete matching rows from update table insert remaining rows

In this case I might

update words
 set popularity = popularity + 1
 where word = @word

if @@rowcount = 0
  insert Words select @word, 1

You'd have to wrap in a transaction, and concurrency could be an issue. If you dropped the changes into a table and wrapped a series of updates/insert together, it ought to handle multiple threads better. Is there a PK on word?

more ▼

answered Feb 23 '10 at 01:00 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 76 79 82

(comments are locked)
10|1200 characters needed characters left

No, but - do bear in mind that the only reason that yours works is because the query optimiser doesn't actually really give a crap about your ROWLOCK table hint. It only pays attention to HOLDLOCK / UPDLOCK. And because you are effectively saying 'Hold the lock on the entire table' it works as you expect it to. Do a bit of digging, run sp_lock and examine the results that you get while your proc is running.

Think about it, how can you hold a row-level lock on a row that doesn't yet exist?

So - tread carefully, because the concurrency on this will suck. An alternative way to do it would be as the other people have posted, but using a UNIQUE constraint on the table, and catching that on the INSERT. (i.e. try to update, try to insert, try to update).

more ▼

answered Feb 23 '10 at 06:26 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472
x402

asked: Feb 23 '10 at 09:28 AM

Seen: 1638 times

Last Updated: Feb 23 '10 at 09:28 AM