Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Принудительный ordered scan по временной таблице  [new]
Eleanor
Member

Откуда:
Сообщений: 2618
Добрый день,

Заранее спасибо за любые предложения.

Разбираюсь с дедлоками между параллельными вставками в таблицу.
INSERT INTO dbo.TaskQueue 
	SELECT *
	FROM #TaskQueue_Sorted A
	WHERE NOT EXISTS (
						SELECT 1
						FROM dbo.TaskQueue B WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
						WHERE A.UserId = B.UserId
							AND A.TaskQueueFunctionTypeId = B.TaskQueueFunctionTypeId
							AND A.TaskQueueTypeId = B.TaskQueueTypeId
							AND A.ReferenceId = B.ReferenceId
							AND A.PlannedBeginDate = B.PlannedBeginDate
							AND B.TaskQueueStateId IN (1, 2, 3)
					)


Вставка происходит, только если вставляемых записей еще нет в таблице.
Однако, при проверке наличия записи, блокировки на ключи поискового индекса IDX_TaskQueue_ReferenceId_UserId (ReferenceId, TaskQueueFunctionTypeId, TaskQueueTypeId, PlannedBeginDate, UserId, TaskQueueStateId) в разных потоках накладываются в разном порядке и возникают дедлоки.

#TaskQueue_Sorted кластеризован по тем же полям, что и индекс IDX_TaskQueue_ReferenceId_UserId, но в плане он сканируется с Ordered = False.

Можно ли как-то сделать скан #TaskQueue_Sorted сортированным, чтобы не было дедлоков?
План запроса приложила.

Если просто добавить в запросе Order by, то он в запросе добавляется слишком поздно, сразу после Nested Loops.
Merge join, конечно, даст искомый ordered = true, но сканировать IDX_TaskQueue_ReferenceId_UserId - это слишком.

К сообщению приложен файл (insert_queue.sqlplan - 38Kb) cкачать
21 окт 19, 11:59    [21998823]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Eleanor,
а что вы этим добиваетесь? в EXISTS... особенно в контексте "вставок"
автор
FROM dbo.TaskQueue B WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
21 окт 19, 12:18    [21998854]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
Eleanor
Можно ли как-то сделать скан #TaskQueue_Sorted сортированным
INSERT INTO dbo.TaskQueue 
	SELECT *
	FROM (select top (cast(0x7fffffffffffffff as bigint)) * from #TaskQueue_Sorted order by ...) A
	WHERE NOT EXISTS (
						SELECT 1
						FROM dbo.TaskQueue B WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
						WHERE A.UserId = B.UserId
							AND A.TaskQueueFunctionTypeId = B.TaskQueueFunctionTypeId
							AND A.TaskQueueTypeId = B.TaskQueueTypeId
							AND A.ReferenceId = B.ReferenceId
							AND A.PlannedBeginDate = B.PlannedBeginDate
							AND B.TaskQueueStateId IN (1, 2, 3)


Почему INSERT, а не MERGE?
21 окт 19, 12:35    [21998879]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Eleanor
Member

Откуда:
Сообщений: 2618
invm,

Спасибо.
C MERGE сложнее запись, потому что уже появилась бездумная привычка предварительно фильтровать целевую таблицу в CTE (иначе на некоторых условиях match, не помню каких, возникал scan).
21 окт 19, 13:14    [21998946]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Андрей Юниор
Member

Откуда: Москва
Сообщений: 471
Eleanor
C MERGE сложнее запись, потому что уже появилась бездумная привычка предварительно фильтровать целевую таблицу в CTE

Вопрос не по теме. Так стоит или не стоит предварительно фильтровать целевую таблицу в CTE при MERGE? Я так делаю даже без явной необходимости...
21 окт 19, 13:33    [21998969]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Андрей Юниор
Eleanor
C MERGE сложнее запись, потому что уже появилась бездумная привычка предварительно фильтровать целевую таблицу в CTE

Вопрос не по теме. Так стоит или не стоит предварительно фильтровать целевую таблицу в CTE при MERGE? Я так делаю даже без явной необходимости...

стоит, если например, нужно перезагружать таблицу полностью по фильтру
WITH
cte AS (
  SELECT
    [month],
    [id] 
  FROM
    [dst]
  WHERE
    [month] = '20191001'
)
MERGE INTO
  cte
USING
  src
ON (
   src.[id] = cte.[id] )
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED BY TARGET THEN INSERT ( ... ) VALUES ( ... )
WHEN NOT MATCHED BY SOURCE THEN DELETE
;
21 окт 19, 16:02    [21999174]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
Eleanor
C MERGE сложнее запись
Как по мне, то это дело вкуса.
Зато у MERGE гибче OUTPUT и не нужно явно указывать updlock.
21 окт 19, 17:04    [21999249]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Андрей Юниор
Member

Откуда: Москва
Сообщений: 471
Руслан Дамирович
стоит, если например, нужно перезагружать таблицу полностью по фильтру

Когда по фильтру - да. У меня есть такие слияния: по месяцам, по кварталам с составными ключами.

А в таком случае:
DECLARE @id INT = 1,
    @Name NVARCHAR(255) = 'Name';

WITH Trg AS (SELECT Id, Name
             FROM SomeTable
             WHERE Id = @Id),
    Src AS (SELECT @Id AS Id, @Name AS Name)
MERGE Trg AS T
USING Src AS S
ON (T.Id = S.Id)
WHEN NOT MATCHED BY TARGET
    THEN
    INSERT (Id, Name)
    VALUES (S.Id, S.Name)
WHEN MATCHED
    AND NOT (T.Name = S.Name)
    THEN
    UPDATE
    SET T.Name = S.Name;

это вредно? Смысл хоть какой-то есть?
21 окт 19, 17:16    [21999260]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
msLex
Member

Откуда:
Сообщений: 7726
Андрей Юниор
Руслан Дамирович
стоит, если например, нужно перезагружать таблицу полностью по фильтру

Когда по фильтру - да. У меня есть такие слияния: по месяцам, по кварталам с составными ключами.

А в таком случае:
DECLARE @id INT = 1,
    @Name NVARCHAR(255) = 'Name';

WITH Trg AS (SELECT Id, Name
             FROM SomeTable
             WHERE Id = @Id),
    Src AS (SELECT @Id AS Id, @Name AS Name)
MERGE Trg AS T
USING Src AS S
ON (T.Id = S.Id)
WHEN NOT MATCHED BY TARGET
    THEN
    INSERT (Id, Name)
    VALUES (S.Id, S.Name)
WHEN MATCHED
    AND NOT (T.Name = S.Name)
    THEN
    UPDATE
    SET T.Name = S.Name;


это вредно? Смысл хоть какой-то есть?


Сравните планы.
Думаю, они будут идентичны.
21 окт 19, 17:21    [21999263]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2374
Eleanor,

Д.Короткевич рекомендовал все таки не использовать MERGE
(кроме удобства написания, все аналогично, только есть ряд покапотных глюков)

вам точно (UPDLOCK, ROWLOCK, HOLDLOCK) внутри эксиста нужны?
21 окт 19, 18:02    [21999289]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
StarikNavy,

дык подкапотность возникает, если не читать справку, а делать "по аналогии". Что, в общем-то, недопустимо.

Проблема у автора возникает из-за этого:

INSERT INTO dbo.TaskQueue
SELECT *
FROM #TaskQueue_Sorted A
WHERE NOT EXISTS (
SELECT 1
FROM dbo.TaskQueue B WITH (UPDLOCK, ROWLOCK, HOLDLOCK)

Взаимоисключающие блокировки сами напрашиваются. Запрос надо разделить на две части - в первой произвести выборку пригодных для вставки строк, например, во временную таблицу или путем удаления из исходной таблицы, во второй части выполнить вставку этих строк. Даже при прочтении кода будет проще понять - что намеревался сделать автор.
21 окт 19, 18:13    [21999297]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
Владислав Колосов
Запрос надо разделить на две части - в первой произвести выборку пригодных для вставки строк, например, во временную таблицу или путем удаления из исходной таблицы, во второй части выполнить вставку этих строк.
А теперь расскажите нам, каким образом обеспечить сохранность пригодности для вставки в целевую таблицу строк, отобранных во временную таблицу?
21 окт 19, 18:46    [21999311]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Eleanor
Member

Откуда:
Сообщений: 2618
Владислав Колосов
Проблема у автора возникает из-за этого:

В чем была проблема, invm уже помог исправить. Дедлоков на вставках больше нет.
Очень приятно, что на форуме есть люди подобного уровня.
21 окт 19, 23:28    [21999423]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
invm
Владислав Колосов
Запрос надо разделить на две части - в первой произвести выборку пригодных для вставки строк, например, во временную таблицу или путем удаления из исходной таблицы, во второй части выполнить вставку этих строк.
А теперь расскажите нам, каким образом обеспечить сохранность пригодности для вставки в целевую таблицу строк, отобранных во временную таблицу?


Точно так же как достигается повторяемость чтения в подзапросе с сортировкой, полагаю. Не вижу разницы.
22 окт 19, 12:09    [21999689]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
Eleanor,

недостатком этого решения является то, что вы будете вынуждены заставлять" дуть ветер " в одну сторону при обновлениях этой таблицы из других процедур другими запросами. Т.е. равновесие, образно говоря, такого запроса будет неустойчивым, хорошо работать в частном случае.
22 окт 19, 12:11    [21999691]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Переписать эту дичь на LEFT JOIN и убрать WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
Сортировать это костыль
22 окт 19, 12:17    [21999697]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
Владислав Колосов
Точно так же как достигается повторяемость чтения в подзапросе с сортировкой, полагаю. Не вижу разницы.
Т.е. в итоге получится исходный запрос, только заполняться будет временная таблица. Потом уже из этой временной переливаться в основную. И все это в явной транзакции.
В чем профит?

PS: Рекомендую поразмышлять - зачем нужны хинты updlock и holdlock. Подсказка: рассмотрите вариант, когда параллельные сессии обрабатывают одну и ту же строку, которой нет в целевой таблице.
22 окт 19, 12:39    [21999722]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
invm,

автор
обрабатывают одну и ту же строку, которой нет в целевой таблице.

целевая таблица TaskQueue , запрос на записей которых нет из TaskQueue - их не будет для всех сессий
22 окт 19, 12:50    [21999734]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
TaPaK
целевая таблица TaskQueue , запрос на записей которых нет из TaskQueue - их не будет для всех сессий
Теперь подумайте о последствиях.
22 окт 19, 12:55    [21999747]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
TaPaK
Переписать эту дичь на LEFT JOIN и убрать WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
Сортировать это костыль


Интересный вариант.

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

Для быстрого исправления, наверное, можно применить хинты.
22 окт 19, 13:00    [21999758]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
invm
TaPaK
целевая таблица TaskQueue , запрос на записей которых нет из TaskQueue - их не будет для всех сессий
Теперь подумайте о последствиях.

сдаюсь.
До момента вставки первого их не будет ни для кого, что будет под (UPDLOCK, ROWLOCK, HOLDLOCK) ? всё?
22 окт 19, 13:01    [21999761]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Eleanor
Member

Откуда:
Сообщений: 2618
Владислав Колосов
недостатком этого решения является то, что вы будете вынуждены заставлять" дуть ветер " в одну сторону при обновлениях этой таблицы из других процедур другими запросами. Т.е. равновесие, образно говоря, такого запроса будет неустойчивым, хорошо работать в частном случае.

В данной системе дедлоки на вставках относительно частые, а на обновлениях 1-2 раза в сутки. Связано с разными размерами пачек и разной частотой вызова разных ХП.

Не ставлю целью на 100% избавиться от всех дедлоков. Все равно в случае ошибок идут повторные вызовы.
Система в целом очень подвержена дедлокам, и часто приходится с ними разбираться, т.к. разработчики практически любое действие выполняют в 10 потоках на нескольких машинах.
22 окт 19, 13:09    [21999773]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Eleanor
Member

Откуда:
Сообщений: 2618
TaPaK
Переписать эту дичь на LEFT JOIN и убрать WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
Сортировать это костыль

Можно и переписать, но подсказки убирать не надо. План запроса в итоге не изменяется.
22 окт 19, 13:18    [21999785]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
Eleanor,

в моем случае наилучшим оказалось решение с раздельной подготовкой данных и выполнением обновления. Поэтому и предложил.
22 окт 19, 13:26    [21999796]     Ответить | Цитировать Сообщить модератору
 Re: Принудительный ordered scan по временной таблице  [new]
msLex
Member

Откуда:
Сообщений: 7726
TaPaK
invm
пропущено...
Теперь подумайте о последствиях.

сдаюсь.
До момента вставки первого их не будет ни для кого, что будет под (UPDLOCK, ROWLOCK, HOLDLOCK) ? всё?


под UPDLOCK + HOLDLOCK будут range локи, не дающие "завершить" exists по одним и тем же отсутствующим записям, т.е. вторая сессия будет ждать на range локи, пока первая не разберется, вставлять ли новую запись в справочник или нет.


ЗЫ

Range локи накладываются на предыдущую запись, что приводит к двум проблемам

1. При попытке вставить две разные отсутствующие записи в один пустой диапазон ключей (например, есть записи 1 и 5, а вставляются в разных коннектах 2 и 3), получим ожидание до конца 1-й транзакции

2. При попытке вставить две записи, одна из которых уже есть а другая стоит в пустом диапазоне справа от нее (например, есть запись 2 и 5 пытаемся вставить 2 и 3), получим ожидание до конца 1-й транзакции


Если нет удалений, то со второй проблемой можно справиться "двухфазным" not exists

Сначала без (UPDLOCK, ROWLOCK, HOLDLOCK) выбираем записи которых нет в отдельную # таблицу, а потом
из # таблицы вставляем записи с повторной проверкой, но уже с (UPDLOCK, ROWLOCK, HOLDLOCK)

Сообщение было отредактировано: 22 окт 19, 13:32
22 окт 19, 13:29    [21999800]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить