Предотвратить тупик в совершенном чтении SELECT

Question

Я извлекаю данные из бизнес-системы, предоставленной третьей стороной, для использования в отчетности. Я использую один оператор SELECT, выданный из компонента источника задачи потока данных служб SSIS, который объединяет несколько таблиц в исходной системе для создания нужного набора данных. Мы используем уровень изоляции по умолчанию для фиксации чтения.

К моему удивлению, я регулярно нахожу, что этот извлекающий запрос блокируется и выбирается в качестве жертвы. Я не думал, что SELECT в транзакции с фиксацией чтения мог бы сделать это, но в соответствии с этим ответом SO это возможно: может ли уровень изоляции с фиксацией чтения когда-либо приводить к тупику (Sql Server)?

С помощью флагов трассировки 1204 и 12222 я определил конфликтующее утверждение, а также объект и индекс, о которых идет речь. По сути, конкуренция происходит из-за страницы данных в первичном ключе одной из таблиц. Мне нужно извлечь из этой таблицы, используя соединение по ее ключу (поэтому я снимаю блокировку S), конфликтующий оператор выполняет INSERT и запрашивает блокировку IX на странице данных индекса.

(Примечание: вышеупомянутый SO говорит об этой проблеме, возникающей с некластеризованными индексами, но, похоже, это происходит в кластеризованном PK. По крайней мере, это то, во что я верю, основываясь на моей интерпретации информации о взаимоблокировке в журнале событий и свойство relatedObjectId.)

Вот мои ограничения:

1. Конфликтующее заявление содержится в зашифрованной хранимой процедуре, предоставленной третьей стороной как часть готового программного обеспечения. Нет возможности получить открытый текст или изменить его.
2. Я не хочу использовать "грязное чтение", так как мне нужны мои извлеченные данные для поддержания их целостности.
3. Мне не ясно, как или если реструктуризация моего запроса извлечения может предотвратить это. Блокировка находится на ПК таблицы, которая меня больше всего интересует, и я не вижу альтернативы использованию ПК.
4. Я не возражаю против того, чтобы мой запрос извлечения был жертвой, поскольку я предпочитаю это прерыванию оперативного использования исходной системы. Однако это приводит к сбою выполнения служб SSIS, поэтому, если это так, я бы хотел более четкий и изящный способ справиться с этой ситуацией.

Может кто-нибудь предложить способы, желательно, чтобы предотвратить тупик, или если нет, то лучше обработать ошибку?

Answer 1

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

Добавьте сортировку в поток данных, так как это полностью блокирующее преобразование (см. Ниже о блокирующих преобразованиях). Это означает, что SELECT потребуется для завершения загрузки всех данных в буфер конвейера, прежде чем любые данные будут переданы в место назначения. В противном случае SSIS пытается вставить данные, когда есть блокировка таблицы / индекса. Вы можете быть в состоянии проявить творческий подход с вашими стратегиями индексации здесь (я не пробовал это). Но полностью блокирующее преобразование сделает свое дело и устранит необходимость в каких-либо дополнительных индексах для таблицы (и связанных с этим издержках).

Примечание: никогда не используйте подсказки запроса NOLOCK при выборе данных из таблицы, чтобы попытаться обойти это. Я никогда не пробовал этого и не собираюсь этого делать. Вы (королевский вы) рискуете вставить незафиксированные данные в ваш ETL.

Ссылка:

https://jorgklein.com/2008/02/28/ssis-non-blocking-semi-blocking-and-fully-blocking-components/