UNIONs + PK-violations

  • Hello,
    Some background first: I have some XML Export tracking order changes.
    For this we use change tracking on order / row tables and "advance" the change tracking number all the time to get new changes.

    The code looks something like following (simplified but not too much):

    CREATE TABLE outorder (comp_id SMALLINT, outorder INT, status INT, orderdate DATETIME PRIMARY KEY(outorder, comp_id))
    CREATE TABLE outorderrow (comp_id SMALLINT, outorder INT, outorderrow INT, art_id INT PRIMARY KEY (outorder, comp_id, outorderrow)

    CREATE TABLE #t_outorder_changed (
             outorder  INT
        ,    del_no INT        NOT NULL DEFAULT 0
        ,    status INT
        ,    outorder_source INT
        PRIMARY KEY (outorder, del_no, outorder_source)
        )

    DECLARE @currentChangeVersion BIGINT
    , @cidOutorder SMALLINT = 0
    , @cidOutorderrow SMALLINT = 0
    -- Get previous change
    EXEC    dbo.SPRC_LASTID_GET
                @pLastid_type        = 'outorder_tracking_export'
            ,    @pResultId        = @currentChangeVersion OUTPUT

    IF @currentChangeVersion > 0
        BEGIN
            INSERT INTO #t_outorder_changed
            (
                outorder, status, outorder_source
            )
            SELECT    ct.outorder, o.status, 0
            FROM    (
                    SELECT    ct.outorder
                    FROM    CHANGETABLE(CHANGES outorder, @currentChangeVersion) ct
                    WHERE    ct.comp_id = @cidOutorder
                    UNION
                    SELECT    ct.outorder
                    FROM    CHANGETABLE(CHANGES outorderrow, @currentChangeVersion) ct
                    WHERE    ct.comp_id = @cidOutorderrow
                ) ct (outorder)
            INNER JOIN outorder o (NOLOCK)
                ON    o.comp_id = @cidOutorder
                AND    o.outorder = ct.outorder

        END

    The problem, that i'm hitting very seldom, is that i get a PK-violation for that insert.
    And in my understanding, the UNION inside the derived table should take care of these duplicates.
    So, where is the problem? Buggy SQL Server, buggy code or that i need to take SQL preschool again 🙂
    Sure, i can probably slap a DISTINCT on the 
    I have gone through all places, and the only other inserts are performed with a different outorder_source, so the problem is most definitely in that specific first insert.

  • You said you're getting a PK violation so maybe you're trying to insert some combination of:
    outorder, status, outorder_source
    ...that already exists in the table? So you may not have duplicates in your actual select that you're trying to insert...but you may be trying to insert values that already exist in #t_outorder_changed ?

    I don't see any where not exists logic on that insert statement.

  • Does the outorder table have a primary key or unique constraint on the outorder column?  If not, there could be duplicates in that table and that would be why you're getting duplicates when you join to it.

    John

  • adaddio - Thursday, September 27, 2018 7:28 AM

    You said you're getting a PK violation so maybe you're trying to insert some combination of:
    outorder, status, outorder_source
    ...that already exists in the table? So you may not have duplicates in your actual select that you're trying to insert...but you may be trying to insert values that already exist in #t_outorder_changed ?

    I don't see any where not exists logic on that insert statement.

    The insert is the first thing that refers to the #t-table, so there's nothing there before, and the table is created inside the procedure.

  • John Mitchell-245523 - Thursday, September 27, 2018 7:42 AM

    Does the outorder table have a primary key or unique constraint on the outorder column?  If not, there could be duplicates in that table and that would be why you're getting duplicates when you join to it.

    John

    The definition is: pk_outorder    clustered, unique, primary key located on PRIMARY    outorder, comp_id

    The other funny thing is that, when i reran it, it doesn't crash, and also, there's no specific pattern that i can see which would trigger the behaviour

  • Can this code be called by multiple overlapping callers? If so, you almost certainly have a race condition if multiple calls get the same result from SPRC_LASTID_GET before then attempting the insert. It's better to use a SEQUENCE or an identity column if you need to guarantee a continually incrementing value (being aware you can potentially gets gaps in either case)

  • It's unlikely since it's a job, but yeah, it's possible. But anyway, it's inserting into a temp table so overlapping shouldn't matter i guess?

  • As a followup. I added a DISTINCT into the #t-insert and the problem went away.
    Will try to create a test case to see if the problem can be reproducible, it might be a bug, the query is a bit "funky" and perhaps optimizer messes something up.

  • siggemannen - Monday, October 1, 2018 10:42 AM

    As a followup. I added a DISTINCT into the #t-insert and the problem went away.
    Will try to create a test case to see if the problem can be reproducible, it might be a bug, the query is a bit "funky" and perhaps optimizer messes something up.

    Highly unlikely the optimizer is "messing something up"

    This will identify the duplicates

    SELECT  COUNT(*), ct.outorder, o.status
       FROM  (
          SELECT  ct.outorder
          FROM  CHANGETABLE(CHANGES outorder, @currentChangeVersion) ct
          WHERE  ct.comp_id = @cidOutorder
          UNION
          SELECT  ct.outorder
          FROM  CHANGETABLE(CHANGES outorderrow, @currentChangeVersion) ct
          WHERE  ct.comp_id = @cidOutorderrow
        ) ct (outorder)
       INNER JOIN outorder o (NOLOCK)
        ON  o.comp_id = @cidOutorder
        AND  o.outorder = ct.outorder
        GROUP BY ct.outorder, o.status
        HAVING COUNT(*) > 1

    And, why are you using NOLOCK?  That may be part of the problem.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Well, outorder-table is PK:ed on outorder, so it cannot have any dupes ever.
    So the problem "must" be inside the derived table, but how can it be dupes if i have a UNION?
    I have triple-checked everything so there's no bug in the rest of the code, and this is definitely the first insert to the #t-table.

    NOLOCK - mostly for historical reasons and to avoid unnecessary locking.
    But the hint here shouldn't matter i guess?

    But, it might be i'm missing something obvious, and need to get back to SQL preschool again.
    Will try to recreate a simple case to see if i'm missing something obvious

  • Follow-up for someone interested in this 🙂

    The issue was due to dirty reads. In some rare occurances, outorder.status was updated in the middle of that insert, leading two values to be returned from the same outorder row with different status values

     

  • which adds to justify not using nolock unless absolutely required and with knowledge that such cases will not occur.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply