Почему этот код SQL время от времени создает потерянные записи?

Question

Отказ от ответственности: я не эксперт по SQL. Я пытаюсь вставить записи в дочернюю таблицу, прежде чем вставлять их в родительскую таблицу. (После того, как я сказал, что я начинаю задаваться вопросом, является ли это даже хорошей идеей.) Запись родительской таблицы содержит ссылку на запись дочерней таблицы, и said-reference не может быть нулевой. Это требует, чтобы я сначала вставил в дочернюю таблицу, а затем связался с родительской таблицей во время вторичной вставки.

В любом случае, по какой-то причине этот код случайным образом создает потерянные записи в таблице IdentifyingData (child), например, у них нет записи в таблице FraudScore (parent), даже если они должны.

Вот почему я запутался. Пытаясь решить эту проблему, я начал сбрасывать содержимое таблицы @tempFraudScore в таблицу физического аудита, чтобы точно видеть, что происходит во время преобразования данных. Когда я переключаю приведенный ниже код, который вставляется в FraudScore из @tempFraudScore, для вставки из таблицы аудита, все дочерние записи успешно получают родительскую запись. Это не имеет смысла для меня.

insert into IdentifyingData (EntryDateTime, IdentifyingDataTypeId, Value, Source)
select distinct GETDATE(), tfs.IdentifyingDataTypeId, tfs.Value, 'SSIS'
from @tempFraudScore tfs
where not exists (
    select id.IdentifyingDataTypeId, id.Value
    from IdentifyingData id
    where tfs.IdentifyingDataTypeId = id.IdentifyingDataTypeId
        and tfs.Value = id.Value
);

update tfs
set tfs.IdentifyingDataId = id.Id
from @tempFraudScore tfs
    inner join IdentifyingData id on
        tfs.Value = id.Value and
        tfs.IdentifyingDataTypeId = id.IdentifyingDataTypeId;

insert into FraudScore (EntryDateTime, FraudCriteriaId, AccountId, IdentifyingDataId, Score, Source)
select distinct
    GETDATE() EntryDateTime,
    tfs.FraudCriteriaId,
    tfs.AccountId,
    tfs.IdentifyingDataId,
    tfs.Score,
    'SSIS'
from @tempFraudScore tfs
    inner join FraudCriteria fc on
        tfs.FraudCriteriaId = fc.Id
            and fc.UniqueEntryPeriod = 0
where not exists (
    select fs.AccountId, fs.FraudCriteriaId, fs.IdentifyingDataId
    from FraudScore fs
    where tfs.AccountId = fs.AccountId
        and tfs.FraudCriteriaId = fs.FraudCriteriaId
        and tfs.IdentifyingDataId = fs.IdentifyingDataId
);

@tempFraudScore поставляется со всеми необходимыми полями, кроме IdentifyingDataId; это должно быть создано сначала вставкой в ​​IdentifyingData, а затем обновлением таблицы переменных с созданным идентификатором. Ниже приведена структура таблицы переменных:

declare @tempFraudScore table(
    FraudCriteriaId int,
    AccountId bigint,
    IdentifyingDataId bigint,
    IdentifyingDataTypeId smallint,
    Value varchar(100),
    Score int
);

Может кто-нибудь сказать мне, что может быть причиной этих осиротевших записей IdentifyingData? Должен ли я пересмотреть, как структурированы отношения между этими двумя таблицами? Я пытаюсь сделать так, чтобы после внесения в систему определенной записи IdentifyingData она не дублировалась; на него будут ссылаться только что созданные записи FraudScore.

Прикрепленноередактирование- это снимок экрана с таблицей аудита, который показывает ход преобразования данных для одного значения (столбец "Значение" - это то же значение для этих записей; я размываю его из соображений конфиденциальности). Обратите внимание, что, несмотря на сообщение "Вставка после FraudScore", данная запись фактически никогда не вставлялась в таблицу FraudScore.

Edit2 (6/6/2018): я добавил следующий код в хранимую процедуру, пытаясь устранить эту проблему. У меня было значение (99999), которое появилось в столбце "Значение" таблицы _Audit, но не в столбце "Значение" второй таблицы, несмотря на то, что код просто сбрасывал все данные в эти две таблицы из одного источника! Я не уверен, имеет ли это значение, но эта хранимая процедура запускается из задачи "Выполнение SQL" пакета SSIS с IsolationLevel "Serializable". Тем не менее, я нигде явно не использую транзакции в коде, и TransactionOption для этой задачи "Выполнение SQL" установлен как "Поддерживается". Я понятия не имею, будет ли это как-то связано с проблемой.

insert into FraudScoreIdentifyingData_Audit
select 'Post-IdentifyingData Update', GETDATE(), FraudCriteriaId, AccountId, IdentifyingDataId, IdentifyingDataTypeId, Value, Score
from @tempFraudScore;

insert into FraudScoreIdentifyingData
select GETDATE(), FraudCriteriaId, AccountId, IdentifyingDataId, IdentifyingDataTypeId, Value, Score, 1
from @tempFraudScore;

Вот схемы двух таблиц:

Answer 1

Оказалось, что в одной из моих больших хранимых процедур была похоронена единственная инструкция удаления, которая вызывала проблему.

В поисках причины этой проблемы я также сидел со мной администратором базы данных, и он определил часть моего процесса SSIS, которая реорганизовала индексы; но он делал это, поскольку пакет продолжал работать и заполнял все необходимые базовые таблицы (включая таблицу с потерянными записями). По его словам, реорганизация или перестройка индексов в таблицах при одновременной попытке добавления или удаления записей в этих таблицах также может вызвать эту проблему; хотя в моем конкретном случае это было неправильно написано, единственное заявление об удалении.

Answer 2

Не могу сказать, что вызвало проблему.

Parent Table=FraudScore

Child Table=IdentifyingData

как они связаны? Сначала вы вставляете запись в FraudScore затем используя предложение вывода, если у вас есть более одной вставки, вставьте запись в IdentifyingData

Но это идеальная ситуация для использования OUTPUT clause даже если проблему не решить из-за этого.

    --data type similar to IdentifyingData
declare @tbl table(Id int,Value int,IdentifyingDataTypeId int)
declare @CurrentDateTime datetime=GETDATE()

begin try
begin transaction

insert into IdentifyingData (EntryDateTime, IdentifyingDataTypeId
, Value, Source)
OUTPUT INSERTED.Id, INSERTED.Value, INSERTED.IdentifyingDataTypeId  
        INTO @tbl  
select distinct @CurrentDateTime, tfs.IdentifyingDataTypeId
, tfs.Value, 'SSIS'
from @tempFraudScore tfs
where not exists (
    select id.IdentifyingDataTypeId, id.Value
    from IdentifyingData id
    where tfs.IdentifyingDataTypeId = id.IdentifyingDataTypeId
        and tfs.Value = id.Value
);


update tfs
set tfs.IdentifyingDataId = id.Id
from @tempFraudScore tfs
    inner join @tbl id on
        tfs.Value = id.Value and
        tfs.IdentifyingDataTypeId = id.IdentifyingDataTypeId;

insert into FraudScore (EntryDateTime, FraudCriteriaId, AccountId, 
IdentifyingDataId, Score, Source)
select distinct
    @CurrentDateTime EntryDateTime,
    tfs.FraudCriteriaId,
    tfs.AccountId,
    tfs.IdentifyingDataId,
    tfs.Score,
    'SSIS'
from @tempFraudScore tfs
    inner join FraudCriteria fc on
        tfs.FraudCriteriaId = fc.Id
            and fc.UniqueEntryPeriod = 0
where not exists (
    select fs.AccountId, fs.FraudCriteriaId, fs.IdentifyingDataId
    from FraudScore fs
    where tfs.AccountId = fs.AccountId
        and tfs.FraudCriteriaId = fs.FraudCriteriaId
        and tfs.IdentifyingDataId = fs.IdentifyingDataId
);
COMMIT
end TRY
begin CATCH
if(@@trancount>0)
ROLLBACK
end CATCH