Best way of doing an upsert in SQL 2000

Question

question

My Other Me asked Feb 23, '10

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)

sql-server-2000 stored-procedures

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-23T09:41:42Z

Kev Riley answered Feb 23, '10

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

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

My Other Me commented · Feb 23, 2010 at 10:08 AM

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)

0 ·

Kev Riley ♦♦ commented · Feb 23, 2010 at 10:15 AM

no - if this is in a stored procedure then there is an implicit transaction

0 ·

Kev Riley ♦♦ commented · Feb 23, 2010 at 10:19 AM

I wasn't trying to give you the whole solution, just different methods :)

0 ·

Kev Riley ♦♦ commented · Feb 23, 2010 at 10:36 AM

never had any issues with it in my high-concurrency environments

0 ·

Answer 2 · 2010-02-23T10:03:53Z

Brimstedt answered Feb 23, '10

Here is one alternative

UPDATE  w
SET     popularity = popularity + 1
FROM    Words w
WHERE   Word = @word

IF(@@ROWCOUNT = 0)
BEGIN
    INSERT INTO Words (Word, Popularity)
    VALUES(@word, 1)
END

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

My Other Me commented · Feb 23, 2010 at 10:11 AM

Same problem as the Solution from Kev Riley: Not safe for use from multiple threads.

0 ·

Brimstedt commented · Feb 23, 2010 at 10:38 AM

It's a matter of transaction type and locking.

0 ·

Brimstedt commented · Feb 23, 2010 at 10:41 AM

Thanks for fixing code, Kev :-)

0 ·

Answer 3 · 2010-02-23T10:40:20Z

Brimstedt answered Feb 23, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-02-23T13:00:01Z

Steve Jones - Editor answered Feb 23, '10

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?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 5 · 2010-02-23T18:26:11Z

Matt Whitfield answered Feb 23, '10

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).

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question