Insert and Update pattern for multiple threads application with SQL Server

10 במאי 2009

6 comments

Hi, 


Have you ever try to insert a row in to a SQL table if the key does not exist and update a row if a key exists. Well this is very common scenario.


Usually you would achieve this goal by writing SQL like:


if exists (select * from TestTable where ID = @id)


begin


    update TestTable set myCount = myCount+1  where ID = @id

end

else

begin

  insert into TestTable values (@id, 1)


end


This code will work fine for single threaded applications but will not work for multiple threads application. To solve this issue you need to handle primary key violations and fix up the transactional integrity of the batch.


 So now let's see the solutions:


Option 1:

begin tran

if exists (select * from TestTable with (updlock,serializable) where ID = @id)


begin


  update TestTable set myCount = myCount+1 where ID = @id


end


else


begin


  insert TestTable (ID, myCount) values (@id, 1)


end


commit tran


Option 2:


begin tran

  update TestTable with (serializable) set myCount = myCount + 1 where ID = @id
if @@rowcount = 0

begin


  insert TestTable (ID, myCount) values (@id,1)

end

commit tran


That's all.


Enjoy Rotem

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

6 comments

  1. Yaniv11 במאי 2009 ב 10:42

    Referring to Option 1:
    I understand that if the EXISTS condition yields TRUE your shared locks are successfully converted to exclusive locks due to the UPDLOCK hint inside the transaction and that row is exclusively locked thus preventing (blocking) any access for other connections.
    However, if the EXISTS yield false and you INSERT a new row that ID may have already been inserted by another connection.
    In addition, the explicit transaction is required only for the purpose of the hints taking effect as it only handles a single DML operation that when working with the default isolation level READ-COMMITTED is handled as an implicit transaction by sql server any way.

    Reply
  2. Rotem Bloom11 במאי 2009 ב 16:07

    Hi Yaniv,
    Thanks 4 your comments, Basically I agree but I'm not sure you are right on the INSERT issue you mention.

    Can you suggest but can you suggest solution?

    Thanks,
    Rotem

    Reply
  3. Shlomi11 במאי 2009 ב 18:00

    Check out the new MERGE function in SQL 2008 🙂

    Reply
  4. Rotem Bloom12 במאי 2009 ב 8:59

    Well Yaniv,
    I try test your comments on the " if the EXISTS yield false and you INSERT…" and you are not right it works as I expected. Lets say the ID column is Identity column I will never got primary key violations.

    Thanks,
    Rotem

    Reply
  5. Rotem Bloom12 במאי 2009 ב 11:02

    Shlomi thanks,
    I know the MERGE function in SQL 2008 but I don't have 2008 on my current application.

    thanks 4 your comment

    Reply
  6. software developers23 באוקטובר 2009 ב 15:25

    That was an inspiring post,

    Option 2 is clearly the one i will be going for

    Keep up the good work

    Reply