ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Принудительный ordered scan по временной таблице
25 сообщений из 62, страница 1 из 3
  1  все
Принудительный ordered scan по временной таблице
    #39879207
Eleanor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Eleanor
Участник
Добрый день,

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

Разбираюсь с дедлоками между параллельными вставками в таблицу.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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 - это слишком.
...
Рейтинг: 0 / 0
21.10.2019, 11:59
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879223
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Eleanor,
а что вы этим добиваетесь? в EXISTS... особенно в контексте "вставок"
авторFROM dbo.TaskQueue B WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
...
Рейтинг: 0 / 0
21.10.2019, 12:18
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879233
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
EleanorМожно ли как-то сделать скан #TaskQueue_Sorted сортированным
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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?
...
Рейтинг: 0 / 0
21.10.2019, 12:35
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879278
Eleanor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Eleanor
Участник
invm,

Спасибо.
C MERGE сложнее запись, потому что уже появилась бездумная привычка предварительно фильтровать целевую таблицу в CTE (иначе на некоторых условиях match, не помню каких, возникал scan).
...
Рейтинг: 0 / 0
21.10.2019, 13:14
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879296
Андрей Юниор
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Юниор
Участник
EleanorC MERGE сложнее запись, потому что уже появилась бездумная привычка предварительно фильтровать целевую таблицу в CTE
Вопрос не по теме. Так стоит или не стоит предварительно фильтровать целевую таблицу в CTE при MERGE? Я так делаю даже без явной необходимости...
...
Рейтинг: 0 / 0
21.10.2019, 13:33
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879431
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
Андрей ЮниорEleanorC MERGE сложнее запись, потому что уже появилась бездумная привычка предварительно фильтровать целевую таблицу в CTE
Вопрос не по теме. Так стоит или не стоит предварительно фильтровать целевую таблицу в CTE при MERGE? Я так делаю даже без явной необходимости...
стоит, если например, нужно перезагружать таблицу полностью по фильтру
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
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
;
...
Рейтинг: 0 / 0
21.10.2019, 16:02
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879501
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
EleanorC MERGE сложнее записьКак по мне, то это дело вкуса.
Зато у MERGE гибче OUTPUT и не нужно явно указывать updlock.
...
Рейтинг: 0 / 0
21.10.2019, 17:04
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879507
Андрей Юниор
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Юниор
Участник
Руслан Дамировичстоит, если например, нужно перезагружать таблицу полностью по фильтру
Когда по фильтру - да. У меня есть такие слияния: по месяцам, по кварталам с составными ключами.

А в таком случае:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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;


это вредно? Смысл хоть какой-то есть?
...
Рейтинг: 0 / 0
21.10.2019, 17:16
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879510
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
Андрей ЮниорРуслан Дамировичстоит, если например, нужно перезагружать таблицу полностью по фильтру
Когда по фильтру - да. У меня есть такие слияния: по месяцам, по кварталам с составными ключами.

А в таком случае:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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;



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

Сравните планы.
Думаю, они будут идентичны.
...
Рейтинг: 0 / 0
21.10.2019, 17:21
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879529
Фотография StarikNavy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
StarikNavy
Участник
Eleanor,

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

вам точно (UPDLOCK, ROWLOCK, HOLDLOCK) внутри эксиста нужны?
...
Рейтинг: 0 / 0
21.10.2019, 18:02
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879534
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
StarikNavy,

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

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

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

Взаимоисключающие блокировки сами напрашиваются. Запрос надо разделить на две части - в первой произвести выборку пригодных для вставки строк, например, во временную таблицу или путем удаления из исходной таблицы, во второй части выполнить вставку этих строк. Даже при прочтении кода будет проще понять - что намеревался сделать автор.
...
Рейтинг: 0 / 0
21.10.2019, 18:13
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879544
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Владислав КолосовЗапрос надо разделить на две части - в первой произвести выборку пригодных для вставки строк, например, во временную таблицу или путем удаления из исходной таблицы, во второй части выполнить вставку этих строк.А теперь расскажите нам, каким образом обеспечить сохранность пригодности для вставки в целевую таблицу строк, отобранных во временную таблицу?
...
Рейтинг: 0 / 0
21.10.2019, 18:46
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879626
Eleanor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Eleanor
Участник
Владислав КолосовПроблема у автора возникает из-за этого:

В чем была проблема, invm уже помог исправить. Дедлоков на вставках больше нет.
Очень приятно, что на форуме есть люди подобного уровня.
...
Рейтинг: 0 / 0
21.10.2019, 23:28
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879814
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
invmВладислав КолосовЗапрос надо разделить на две части - в первой произвести выборку пригодных для вставки строк, например, во временную таблицу или путем удаления из исходной таблицы, во второй части выполнить вставку этих строк.А теперь расскажите нам, каким образом обеспечить сохранность пригодности для вставки в целевую таблицу строк, отобранных во временную таблицу?

Точно так же как достигается повторяемость чтения в подзапросе с сортировкой, полагаю. Не вижу разницы.
...
Рейтинг: 0 / 0
22.10.2019, 12:09
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879816
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Eleanor,

недостатком этого решения является то, что вы будете вынуждены заставлять" дуть ветер " в одну сторону при обновлениях этой таблицы из других процедур другими запросами. Т.е. равновесие, образно говоря, такого запроса будет неустойчивым, хорошо работать в частном случае.
...
Рейтинг: 0 / 0
22.10.2019, 12:11
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879821
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Переписать эту дичь на LEFT JOIN и убрать WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
Сортировать это костыль
...
Рейтинг: 0 / 0
22.10.2019, 12:17
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879837
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Владислав КолосовТочно так же как достигается повторяемость чтения в подзапросе с сортировкой, полагаю. Не вижу разницы.Т.е. в итоге получится исходный запрос, только заполняться будет временная таблица. Потом уже из этой временной переливаться в основную. И все это в явной транзакции.
В чем профит?

PS: Рекомендую поразмышлять - зачем нужны хинты updlock и holdlock. Подсказка: рассмотрите вариант, когда параллельные сессии обрабатывают одну и ту же строку, которой нет в целевой таблице.
...
Рейтинг: 0 / 0
22.10.2019, 12:39
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879843
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
invm,

автор обрабатывают одну и ту же строку, которой нет в целевой таблице.
целевая таблица TaskQueue , запрос на записей которых нет из TaskQueue - их не будет для всех сессий
...
Рейтинг: 0 / 0
22.10.2019, 12:50
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879852
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
TaPaKцелевая таблица TaskQueue , запрос на записей которых нет из TaskQueue - их не будет для всех сессийТеперь подумайте о последствиях.
...
Рейтинг: 0 / 0
22.10.2019, 12:55
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879860
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
TaPaKПереписать эту дичь на LEFT JOIN и убрать WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
Сортировать это костыль

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

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

Для быстрого исправления, наверное, можно применить хинты.
...
Рейтинг: 0 / 0
22.10.2019, 13:00
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879862
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
invmTaPaKцелевая таблица TaskQueue , запрос на записей которых нет из TaskQueue - их не будет для всех сессийТеперь подумайте о последствиях.
сдаюсь.
До момента вставки первого их не будет ни для кого, что будет под (UPDLOCK, ROWLOCK, HOLDLOCK) ? всё?
...
Рейтинг: 0 / 0
22.10.2019, 13:01
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879870
Eleanor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Eleanor
Участник
Владислав Колосовнедостатком этого решения является то, что вы будете вынуждены заставлять" дуть ветер " в одну сторону при обновлениях этой таблицы из других процедур другими запросами. Т.е. равновесие, образно говоря, такого запроса будет неустойчивым, хорошо работать в частном случае.
В данной системе дедлоки на вставках относительно частые, а на обновлениях 1-2 раза в сутки. Связано с разными размерами пачек и разной частотой вызова разных ХП.

Не ставлю целью на 100% избавиться от всех дедлоков. Все равно в случае ошибок идут повторные вызовы.
Система в целом очень подвержена дедлокам, и часто приходится с ними разбираться, т.к. разработчики практически любое действие выполняют в 10 потоках на нескольких машинах.
...
Рейтинг: 0 / 0
22.10.2019, 13:09
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879879
Eleanor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Eleanor
Участник
TaPaKПереписать эту дичь на LEFT JOIN и убрать WITH (UPDLOCK, ROWLOCK, HOLDLOCK)
Сортировать это костыль
Можно и переписать, но подсказки убирать не надо. План запроса в итоге не изменяется.
...
Рейтинг: 0 / 0
22.10.2019, 13:18
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879884
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Eleanor,

в моем случае наилучшим оказалось решение с раздельной подготовкой данных и выполнением обновления. Поэтому и предложил.
...
Рейтинг: 0 / 0
22.10.2019, 13:26
| Ответить | Цитировать | Написать  
Принудительный ordered scan по временной таблице
    #39879888
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
TaPaKinvmпропущено...
Теперь подумайте о последствиях.
сдаюсь.
До момента вставки первого их не будет ни для кого, что будет под (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)
...
Рейтинг: 0 / 0
22.10.2019, 13:29
| Ответить | Цитировать | Написать  
25 сообщений из 62, страница 1 из 3
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Принудительный ordered scan по временной таблице
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]