Best way of doing an upsert in SQL 2000

Question

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)

Answer 1

0

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

answered Feb 23 '10 at 09:41 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

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)

Answer 2

0

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

answered Feb 23 '10 at 10:03 AM

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)

Answer 3

0

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

answered Feb 23 '10 at 10:40 AM

Brimstedt
226 ● 3 ● 3 ● 4

(comments are locked)

Answer 4

0

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?

answered Feb 23 '10 at 01:00 PM

Steve Jones - Editor ♦♦
5.1k ● 74 ● 78 ● 82

(comments are locked)

Answer 5

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

Best way of doing an upsert in SQL 2000

Follow this question

Related Questions