Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2008
    Posts
    2

    Question Unanswered: Sybase DML Statement

    I have a stored procedure which has a insert statement.Before executing the insert query i am checking with "if not exists" condition for the availability of the record in the table. This is working fine, but occassionally duplicate(2 rows with same values for all columns) rows are inserted into that table. Can any one suggest what is the remedy??

    FYI.. the table does not have any key constaints.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    1. set up a unique key on the table
    2. make sure what you're selecting in the insert statement is unique ( see step 1 ) otherwise ...
    3. insert ... select distinct ...

  3. #3
    Join Date
    Jul 2008
    Posts
    2
    we cannot change the description of the table as this table is in production. The source table does always has only one record. Find the below sample code from the stored procedure which is inserting duplicate records
    if not exists(select 1 from tablename where a = @value and b = null and c = null)
    begin
    insert into tablename() select a1,a2,a3 from tablename where...
    end

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I think you may be in the following situation:
    1. user A evaluates the not exists() condition - returns true
    2. user B evaluates the same not exists() condition - returns true
    3. user A makes the insert statement
    4. user B makes the same insert

    I think you have to lock the table.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Obviously it's difficult to debug whole system from 2 lines of code but …

    I’m guessing that if this code gets called by multiple processes (users) at the same time then the following could happen – there is no data in the target table – then 2 parallel jobs could both test to see if no record exists in the target table – both would jobs would say there is no data – then both jobs would try to insert a rec – you end up with a duplicate.

    You could change the code so it does the check and the insert in one statement ie :
    Code:
    insert target
    select …
    from source
    where not exists( select 1 from target )

    I personally would still create unique index on source and target tables – I know they should only ever contain one row and if that’s what they do hold then no part of the system should be affected. If suddenly either of the tables contains a duplicate then you’d get an error message immediately pointing you to the code with the problem.

    As a last alternative you could just use select distinct when you read data from the target table.

    MBK

Posting Permissions

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