September 27, 2018 at 6:30 am
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.
September 27, 2018 at 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.
September 27, 2018 at 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
September 27, 2018 at 8:02 am
adaddio - Thursday, September 27, 2018 7:28 AMYou 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.
September 27, 2018 at 8:06 am
John Mitchell-245523 - Thursday, September 27, 2018 7:42 AMDoes 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
September 29, 2018 at 3:53 am
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)
September 29, 2018 at 4:02 am
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?
October 1, 2018 at 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.
October 1, 2018 at 11:00 am
siggemannen - Monday, October 1, 2018 10:42 AMAs 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/
October 1, 2018 at 11:19 am
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
September 13, 2020 at 12:09 pm
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
September 13, 2020 at 1:02 pm
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