#json #sql-server #temp-tables #sqlperformance #database-deadlocks
#json #sql-сервер #временные таблицы #производительность SQL #база данных-взаимоблокировки
Вопрос:
У нас возникла странная проблема в разработке, и мы ищем объяснение проблемы самоблокировки с a SELECT INTO
во временной таблице.
У нас есть процедура для преобразования некоторых довольно сложных документов JSON в табличную форму. В настоящее время мы выполняем это с помощью OPENJSON
, что в целом работает просто отлично.
Это преобразование происходит в контексте триггера. Когда в таблицу вставляется одна или несколько строк, генерируется массив документов JSON, сохраняется в одной переменной и передается в приведенную ниже процедуру. Это выглядит примерно так:
SELECT
a.[ID],
b.[Some stuff here...]
INTO #MyTempTable
FROM OPENJSON(@MyJSONDocuments)
WITH (
ID VARCHAR(40),
nestedDocument NVARCHAR(MAX) AS JSON) a
CROSS APPLY OPENJSON(nestedDocument ,'$') b
Когда мы запускаем это в SSMS, это работает просто отлично. Временная таблица генерируется и заполняется, проблем нет. Когда мы перемещаем его в триггер и вставляем только одну строку в базовую таблицу (т. Е. @MyJSONDocuments
Представляет собой массив из одного документа), он также работает просто отлично. Когда мы вставляем две или более строк и @MyJSONDocuments
содержит несколько документов в массиве, мы получаем страшную:
Transaction (Process ID x) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Когда мы заключаем SELECT INTO
оператор в a BEGIN TRAN / COMMIT TRAN
в SSMS, мы также получаем ту же ошибку взаимоблокировки.
После некоторого исследования мы обнаружили, что проблема может заключаться в проблемах параллелизма из нескольких потоков, которые одновременно анализируют JSON и блокируют временную таблицу, отсюда и взаимоблокировка. Когда мы используем подсказку OPTION MAXDOP(1)
, т. Е. принудительно выполняем однопоточный запрос, взаимоблокировок нет. Аналогично, если мы сначала создадим временную таблицу, а затем создадим INSERT
, это также сработает.
У нас есть два жизнеспособных решения проблемы, но мне все еще неясно, почему это проблема. Я думаю, мои вопросы:
1 / Какова реальная причина, по которой SELECT INTO
возникает самоблокировка во временной таблице?
2 / Почему ошибка возникает только в контексте транзакции?
3 / Почему взаимоблокировка возникает только с SELECT INTO
, а не на регулярной INSERT
основе?
Спасибо всем!
Редактировать: график взаимоблокировки ниже
Комментарии:
1. можете ли вы опубликовать график взаимоблокировки из одного из случаев?
2. Какая версия SQL Server? Вчера я впервые столкнулся с этой точной проблемой. Это может быть связано: feedback.azure.com/forums/908035-sql-server/suggestions /…
3. @MartinSmith, я отредактировал сообщение выше
4. @MattG Мы используем SQL Azure (специально управляемый экземпляр)
5. У меня есть идея по этому поводу, но я не могу воспроизвести проблему. Какие изменения необходимо внести здесь для воспроизведения? dbfiddle.uk /…
Ответ №1:
Не уверен в полном ответе на этот вопрос, но я считаю, что проблема связана с параллельным доступом к результатам OPENJSON в контексте транзакции. У меня точно такая же проблема, и она решается путем установки ПАРАМЕТРА (MAXDOP 1) для принудительного последовательного плана для этой части запроса.
Попробуйте это:
SELECT
a.[ID],
b.[Some stuff here...]
INTO #MyTempTable
FROM OPENJSON(@MyJSONDocuments)
WITH (
ID VARCHAR(40),
nestedDocument NVARCHAR(MAX) AS JSON) a
CROSS APPLY OPENJSON(nestedDocument ,'$') b
OPTION (MAXDOP 1)
Комментарии:
1. Я согласен, что это решает проблему (это то, что я использую сейчас, в отличие от создания временной таблицы отдельно, а затем вставки в нее.) Я также думаю, что это происходит только тогда,
CROSS APPLY
когда у меня есть это. Я думаю, я просто ищу техническую причину, по которой это происходит, а также почему важно, задействованы транзакции или нет.2. Я согласен, это решает проблему, но вместо того, чтобы выполнять запрос за запросом, мы адаптировали параллелизм, чтобы привести его в соответствие с количеством ядер, используя: EXEC sp_configure ‘показать дополнительные параметры’, 1; ПЕРЕЙДИТЕ К ПЕРЕНАСТРОЙКЕ С ПЕРЕОПРЕДЕЛЕНИЕМ; ПЕРЕЙДИТЕ К EXEC sp_configure ‘максимальная степень параллелизма’, 4;ПЕРЕЙДИТЕ К ПЕРЕНАСТРОЙКЕ С ПЕРЕОПРЕДЕЛЕНИЕМ; ПЕРЕЙДИТЕ