Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2012
    Posts
    5

    Angry Unanswered: Select before insert problem

    Hi,

    I have a stored procedure which in short does a select to check a condition and then based on that inserts a record into a table or not. Aim here is to insert the record only once and do nothing if it's already there.

    Something like (pseudo code):

    var x = select x from ...
    if ( !x ) then insert ...

    The problem is the check which sometimes returns no records when the proc is called from different sessions. The proc then tries to insert which fails on unique constraint.

    What are the correct settings for transaction isolation or perhaps locks that should be used for that to make sure the entire stored proc will be correctly "synchronized"?

    Thanks
    Piotr

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what should be done when X already exists?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2012
    Posts
    5
    Nothing at all

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    so, blindly do the INSERT & ignore any error
    It INSERT succeeds, all is OK.
    If INSERT fails, all is OK.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2012
    Posts
    5
    Ok, the check is to find out if the exact same record is already in the table. If so, don't do the insert. If not, do the insert but fail if the unique constraints bark.

    Imagine that the table is basically a map between two sets of values with columns (X,Y). When adding a record, say (1,2), a check is performed if there is an entry like that already and then insert of the new record if there's nothing. Therefore values (1,3) will return nothing in the check but the following insert will fail on the unique constraint on X column because value 1 already exists.

  6. #6
    Join Date
    Jul 2012
    Posts
    5
    The description above is the algorithm of the stored proc. The problem is that the check sometimes returns nothing when two operations of "add (1,2)" are being performed concurrently, giving a "false" unique constraint error. That seems to be a race condition I wanted to avoid by setting some locks but maybe my thinking is wrong...

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Do as anacedent said: just insert the values and ignore any unique constraint error. You might want to wrap that with a savepoint to "de-couple" it from the rest of your transaction.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I wanted to avoid by setting some locks but maybe my thinking is wrong
    how do you "set a lock" on a row that does NOT exist?
    Since real elapsed time exists between "check" & INSERT, always a chance that new row get inserted between the two SQL statements.
    You "could" lock the whole table which devolves the application into single user mode.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jul 2012
    Posts
    5
    Another thing I forgot to mention . I want to catch the "real" uinque constraint error. It's not allowed to change the mapping once it's been set and that should come back to the user as a message.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by wish80 View Post
    Another thing I forgot to mention . I want to catch the "real" uinque constraint error. It's not allowed to change the mapping once it's been set and that should come back to the user as a message.

    nobody here prevents you from implementing these requirements.
    You have our permission to proceed to code as necessary.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Have a look at Exception handling in PL/SQL. The one that should interest you the most is DUP_VAL_ON_INDEX.

Posting Permissions

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