Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    39

    Unanswered: select/update/insert question

    Hello

    I have the following problem -

    if exists (select on pk from Table A)
    update (some non pk column in Table A)
    where (pk on Table A)
    else
    insert (values into Table A)
    end

    This block is not in any explicit transaction.

    When executed in multi-threaded environment, clearly there is a race here and two threads can fall into the insert block, leading to duplicate key insert error.

    Can someone provide some guidance on the best way to solve this.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Posts
    16
    Have you try the cursors ?

  3. #3
    Join Date
    May 2002
    Posts
    39
    Thanks. Can you please elaborate ? How is this done with cursors to ensure two threads do not try and insert at the same time.

  4. #4
    Join Date
    Jan 2007
    Posts
    16
    For that, you lock the table like this.

    BEGIN transaction

    lock table TABA in exclusive mode

    ...

    commit transaction

  5. #5
    Join Date
    Jun 2006
    Posts
    16
    lock the table before starting your tran

  6. #6
    Join Date
    May 2002
    Posts
    39
    Is this really the most effective way ? Is it not going to compromise concurrency immensely ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •